Copy package from schema B to schema C

oracle

I am in the next situation: I am using oracle 11g. I am connected to an user, lets say schema1, where are a lot of permissions. I want to create a script which will copy the Package1 (and its body) from schema2 to schema3.

The script will be executed from schema1.

PS: I already look for a solution and I am not interested in export/import or in using other tools from toad, sql developer etc.

Best Answer

One way of doing this on a low level basis is by reading the USER_SOURCE for the corresponding package. In PL/SQL you could loop over the lines and add each line to an array (/table) of type DBMS_SQL.VARCHAR2A, lets call this variable 'sqlTable'.

The first line of your sqlTable contains the package name, with a little String manipulation you should be able to change the name from 'PACKAGE_NAME' to 'SCHEMA'.'PACKAGE_NAME'.

Then using DBMS_SQL package perform the following steps:

  1. cur = DBMS_SQL.OPEN_CURSOR; -- cur is of type pls_integer
  2. DBMS_SQL.PARSE(cur, sqlTable, sqlTable.first, sqlTable.last, false, DBMS_SQL.NATIVE);
  3. DBMS_SQL.execute(cur)

Another way is to spool the output of the USER_SOURCE to a file, connect to the other schema and load / execute the file there. See this.

Related Question