How to find the dependencies of table

Today i come across one OTN thread and learn about dbms_utility. dependency plsql package.

with this package we can check the objects dependency on another objects.

 SQL} create table TEST
  2  (col varchar2(10));


Table created.




SQL} create or replace procedure PR_TEST is
  2  i number;
  3  begin
  4  select count(*) into i from test;
  5  dbms_output.put_line(to_char(i));
  6  end;
  7  /


Procedure created.


SQL} set serveroutput on




SQL} exec dbms_utility.get_dependency('TABLE','TAJ','TEST');
-
DEPENDENCIES ON TAJ.TEST
------------------------------------------------------------------
*TABLE TAJ.TEST()
*   PROCEDURE TAJ.PR_TEST()


PL/SQL procedure successfully completed.


There are other subprogram are listed below of dbms_utility plsql package.

Table 141-3 DBMS_UTILITY Package Subprograms

Subprogram	Description
ACTIVE_INSTANCES Procedure	Returns the active instance
ANALYZE_DATABASE Procedure	Analyzes all the tables, clusters and indexes in a database
ANALYZE_PART_OBJECT Procedure	Analyzes the given tables and indexes
ANALYZE_SCHEMA Procedure	Analyzes all the tables, clusters and indexes in a schema
CANONICALIZE Procedure	Canonicalizes a given string
COMMA_TO_TABLE Procedures	Converts a comma-delimited list of names into a PL/SQL table of names
COMPILE_SCHEMA Procedure	Compiles all procedures, functions, packages, views and triggers in the specified schema
CREATE_ALTER_TYPE_ERROR_TABLE Procedure	Creates an error table to be used in the EXCEPTION clause of the ALTER TYPE statement
CURRENT_INSTANCE Function	Returns the current connected instance number
DATA_BLOCK_ADDRESS_BLOCK Function	Gets the block number part of a data block address
DATA_BLOCK_ADDRESS_FILE Function	Gets the file number part of a data block address
DB_VERSION Procedure	Returns version information for the database
EXEC_DDL_STATEMENT Procedure	Executes the DDL statement in parse_string
FORMAT_CALL_STACK Function	Formats the current call stack
FORMAT_ERROR_BACKTRACE Function	Formats the backtrace from the point of the current error to the exception handler where the error has been caught
FORMAT_ERROR_STACK Function	Formats the current error stack
GET_CPU_TIME Function	Returns the current CPU time in 100th's of a second
GET_DEPENDENCY Procedure	Shows the dependencies on the object passed in.
GET_HASH_VALUE Function	Computes a hash value for the given string
GET_PARAMETER_VALUE Function	Gets the value of specified init.ora parameter
GET_TIME Function	Finds out the current time in 100th's of a second
INVALIDATE Procedure	Invalidates a database object and (optionally) modifies its PL/SQL compiler parameter settings
IS_CLUSTER_DATABASE Function	Finds out if this database is running in cluster database mode
MAKE_DATA_BLOCK_ADDRESS Function	Creates a data block address given a file number and a block number
NAME_RESOLVE Procedure	Resolves the given name
NAME_TOKENIZE Procedure	Calls the parser to parse the given name
OLD_CURRENT_SCHEMA Function	Returns the session value that was returned by SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
OLD_CURRENT_USER Function	Returns the session value that was returned by SYS_CONTEXT ('USERENV', 'CURRENT_USER')
PORT_STRING Function	Returns a string that uniquely identifies the version of Oracle and the operating system
SQLID_TO_SQLHASH Function	Converts a SQL ID into a hash value
TABLE_TO_COMMA Procedures	Converts a PL/SQL table of names into a comma-delimited list of names
VALIDATE Procedure	Makes invalid database objects valid

find more info : dbms_utility

How to wrap plsql code

Dear Friends,

Today i come across a requirement to UNWRAP plsql code. want to share with you all.

How to wrap plsql code (procedure,function,trigger)

1. There is client side OS utility "WRAP" through we can wrap plsql code.

2. What is the syntax and parameters

1. The syntax is very simple and there to parameter.

Syntax: cmd>wrap iname=test.sql oname=wrap_test.sql

Parameter:

iname = input plsql code file

oname = output with wrap plsql code file

eg:

SQL> host wrap iname=c:\test.sql oname=c:\test_unwrap.sql
PL/SQL Wrapper: Release 10.1.0.4.2- Production on Sat Aug 01 17:08:02 2009
Copyright (c) 1993, 2004, Oracle. All rights reserved.
Processing c:\test.sql to c:\test_unwrap.sql

SQL> @c:\test_unwrap.sql
Procedure created.
SQL> select text from user_source where name='TEST';
TEXT

--------------------------------------------------------------------------------
procedure TEST wrappeda000000b2abcdabcdabcdabcdabcdabcdabcdabcd
TEXT

--------------------------------------------------------------------------------
abcdabcdabcdabcdabcdabcdabcd723 55DcPgmpJJ2JAE/YKfa+Z8kj6scxUwg5nnm7+fMr2ywFznUrLL7sB0i8DAMv7ShpuZgQgti8CBxy3Jpqbo3ecD

Content of TEST.SQL file

SQL> host type c:\test.sql

create or replace procedure TEST

as

begin

null;

end;

/

Reference:

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/c_wrap.htm

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1484204932051

It is easy to wrap the plsql code but what happen when we need to unwrap the code without source code file :)

The same situation happen with me... but i found one link on google to unwrap plsql code...

I can't refer here for security reason. Sorry

Global Temporary Table + ORA-01031

If we are trying to create GLOBAL TEMPORARY TABLE inside a PROCEDURE then we are getting following error

SQL> create or replace procedure PRO_TEST
2 as
3 begin
4 execute immediate 'create global temporary table GTT_TEST (no number)';
5 end;
6 /
Procedure created.

SQL> exec PRO_TEST;

BEGIN PRO_TEST; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.PRO_TEST", line 4
ORA-06512: at line 1

Reason: PLSQL stored procedures execute with the base privs of the definer (owner) of the routine meaning that ROLES are not enabled.

It is very bad idea to execute DDL statement (create Global Temporary Table inside procedure) without any compling reason.

Solution: Grant explicit "CREATE TABLE" privilege to Schema which Create & Execute Procedure inside Global Temporary Table.

SQL> conn system@orcl
Enter password:
Connected.
SQL> grant create table to scott;
Grant succeeded.

SQL> create or replace procedure PRO_TEST
2 as
3 begin
4 execute immediate 'create global temporary table GTT_TEST (no number)';
5 end;
6 /
Procedure created.

SQL> exec PRO_TEST;
PL/SQL procedure successfully completed.