Write Oracle Loop in a Script File

Typically any code I write in Oracle it is most often in a procedure or function in a package. At times a stand alone procedure.

The coding performed in an Oracle Procedure can be done in a script file but since I do not do that often I created this post for a copy-paste sample that can be run as a script.
--Declare the VARS outside the begin
ln_count number;

begin -- Start of Coding

for i in (select constraint_name
          from all_constraints
          where constraint_type = 'R'
          and owner = 'MDSYS'
          and status = 'ENABLED') 
  dbms_output.put_line('constraint_name' || i.constraint_name);
end loop;

end; -- End Coding

Here is the DBMS Output from the query code above.


Cannot Add Shared Printer Windows 10 to Windows 7

I have not been able to connect to a Windows 7 Laptop I use for a shared Printer. I am on Windows 10 PRO, my wife has Windows 10 PRO and she can connect.

When I attempt to add a printer or click on the laptop name under network settings I get "Logon failure: the user has not been granted the requested logon type for this computer."

Option I below worked for me. (finally found this on Microsoft site after seriously giving up over a year ago).

I. Try checking the Advanced Sharing Settings from Network and Sharing Center. To do this, follow these steps.

1. Click Start, Control Panel and then Network and Sharing Center.
2. Now click Change Advanced Sharing Settings.
3. Now make sure File and Printer Sharing is Turned On and Password Protected Sharing is Turned Off.

II. If that does not resolve the problem, try to check the Group Policy Settings.

1. Click Start and type gpedit.msc in the start search box and press Enter.
2. Navigate to the following location
Computer Configuration/ Windows Settings/ Security Settings/ Local Policies
3. Under this click on User Rights Assignment.
4. Double click Access this computer from the network and check that EVERYONE is added to the list.
5. If not add it by clicking Add User or group and then type EVERYONE, click OK and then Apply and

Oracle Find Invalid Objects (Packages, Views, Procedures, Functions...)

Working on database refreshes can be very time consuming to detect invalid objects when Oracle Database Links have been changed. 

The one that causes the most frustration is looking for Oracle Views that have an invalid database link, because the database link is stores as a LONG type. The queries below will find all the invalid objects including views and provide a list of objects to recompile.  I have a uses  global recompile script and will post at a later time. This will search objects looking for either invalid objects or specific values (such as database links in views) 
--Get Invalid Objects
select distinct o.OWNER, o.OBJECT_TYPE, o.OBJECT_NAME
from  dba_objects o
where o.OWNER in ('XXXX','XXXXX')
and status != 'VALID'
order by 1;

--Look for any code that has DB Link of name XXXXXX
select *
from DBA_Source s
where lower(s.text) like '%XXXXXX%'

--VIEWS TWO OPTIONS 1: Dump to XLS or 2: Run a Cursor

-- 1. Export to Excel and search the XLS Doc
select v.owner, v.VIEW_NAME, v.text
from DBA_Views v
where OWNER in ('XXXXX','XXXXX')
order by 1,2;

 -- 2. Run Cursor to DBMS Dump
     cursor v_cur is select * from all_views where owner != 'SYS';
   for v_rec in v_cur
       if (instr(lower(v_rec.text), 'XXXXX') > 0) then
            dbms_output.put_line('Match found in ' || v_rec.owner || '.' || v_rec.view_name);
       end if;
   end loop;