CODEWITHSUNDEEP
oracle-databaseplsqldata-dictionary
Debasish Dutta
Debasish Dutta

Reputation: 11

Queries written inside Oracle Package/Procedure/Trigger/Functions

I want to fetch all the queries (Select queries) that has been written inside Oracle PLSQL Package/Procedure/Functions/Triggers. My question is are those queries retained somewhere in Oracle when those Packages/Procedures etc are executed, like in DBA_HIST% tables or v$SQL/SQLAREA/SQLTEXT tables. If not, is there any way I can fetch them.

Any help would be appreciated.

Upvotes: 0

Views: 138

Answers (1)

William Robertson
William Robertson

Reputation: 16001

From 12.2 onwards, PL/Scope can collect details of SQL statements within stored PL/SQL.

If you compile one or more (or all) PL/SQL program units with (for example):

alter session set plscope_settings='STATEMENTS:ALL';

call dbms_utility.compile_schema(user);

then the SQL statements will appear in the USER|ALL|DBA_STATEMENTS views:

select type, object_name, object_type, line, sql_id, text
from   user_statements;
TYPE OBJECT_NAME OBJECT_TYPE LINE SQL_ID TEXT
INSERT ADD_JOB_HISTORY PROCEDURE 10 252ppswgq6pwn INSERT INTO JOB_HISTORY (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) VALUES(:B5 , :B4 , :B3 , :B2 , :B1 )

Upvotes: 1

Related Questions