Oracle PL/SQL Scripting

when running a pl/sql script all roles will get disabled. so if a user normally has create table priv, when running a pl/sql script, the script will not be able to create a table.

test by:

set role none;

then create table by hand to see if you get permission denied.

reenable the roles by

set role all;

now give create table prevs directly to the user

grant create table, create view to <user>;

---

PS/SQL Packages and Procedures

1.NOT ONLY GIVE YOU THE LIST OF OBJECTS BUT TO SEE THE SOURCE TEXT ITSELF:

select  owner,s.name,s.type,s.text
from dba_source s
where s.type in ('FUNCTION','PROCEDURE','PACKAGE')
AND owner not in ('SYS','SYSTEM')
order by owner, name;

2.TO SEE IN YOUR USER BY LINE OF YOUR SOURCE TEXT:

select  s.name,S.type,s.line,s.text
from USER_source s
where s.type in ('FUNCTION','PROCEDURE','PACKAGE')
ORDER BY s.name,s.line

3.TO SEE ONLY LOCAL LIST WITH DETAILS:

select  o.object_name ,o.object_type,o.last_ddl_time
from user_objects o
where object_type in ('FUNCTION','PROCEDURE','PACKAGE')