PL/SQL

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')

Comments