Use dynamic SQL to clone database objects

If you need to COPY a table and create a COPY of the table in another schema under another username), you can use the COPY command of SQL * Plus, or use the simple statement "create table foo as select * from other. foo; ", as long as you have enough select permissions on another user's table.

However, if you need to do the same thing for any other database objects, such as packages, processes, functions, or views, there is no such simple command. You need to manually find out its code and run it in the new instance. It would be a pleasure to simply "clone an object" and make another user's object appear in his/her own instance. This is useful for creating a test solution for the development process. In this example, I will create a package, which has some limitations on cloning most objects ).

The main problem to solve is to obtain the source code. The object owner can access the source code through the view USER_SOURCE. For other users, if the object is granted the EXECUTE permission to a user, the user can only see the source code selected through the view ALL_SOURCE. We can encapsulate the "give me the source for your object to your object source code" request through a process:

Create or replace procedure get_source
(
P_type varchar2,
P_name varchar2,
P_cursor out sys_refcursor
)
As
Begin
Open p_cursor
Select text from user_source
Where type = upper (p_type) and name = p_name
Order by line;
End get_source;
/
Show errors;

Note that "UPPER (name)" is not used )". This means that you must match the uppercase and lowercase letters of the stored procedure. Java stored procedures use a lot of mixed names.

If this process is owned by the object owner, the source code of the object can be exported using a ref cursor variable. If this process is granted EXECUTE permissions to other users, then the user will be able to call this process and view the source code of any database object-even those objects that are not authorized to them and those that are not given in ALL_SOURCE, such as the TYPE declaration. To verify that this method is feasible, enter the following code in SQL * Plus:

SQL> connect scott/tiger
SQL> create function foo return varchar2 as begin return 'Hello world'; end;
SQL>/
SQL> @ get_source. SQL
SQL> variable c refcursor;
SQL> exec get_source ('function', 'foo',: c );
SQL> print c

With the source code obtained from the object owner, the caller can create an object in his/her own solution. We need dynamic SQL to build objects from text strings. Another issue to be addressed is that the source code length of some database objects may exceed 32767 characters, that is, the maximum length of the VARCHAR2 string is exceeded. In this way, the SQL statement cannot be saved using a simple VARCHAR2 string. In Oracle, there is a rarely used variable DBMS_ SQL .PARSE, which can be used to store the source code as a table consisting of VARCHAR2 character rows. Such a table can be used to store SQL statements that exceed the 32767 length limit. In practical applications, you may also need to wrap up any code lines with more than 256 characters, because USER_SOURCE can only store up to 4000 lines of characters ). The following code implements an SQL * Plus script with command line parameters:

Declare
Ipls_integer: = 1;
Rochelle source dbms_ SQL .varchar2s;
Rochelle line varchar2 (256 );
Rochelle cursorsys_refcursor;
C pls_integer;
R pls_integer;
Begin
& 1 .. get_source ('& 2',' & 3', l_cursor );
Rochelle source (I): = 'create or replace ';
Loop
Fetch l_cursor into l_line;
Exit when l_cursor % notfound;
I: = I + 1;
L_source (I): = l_line.text;
End loop;
Close l_cursor;
If I = 1 then
Raise_application_error (-20000, 'object does not exist ');
End if;
C: = dbms_ SQL .open_cursor;
Dbms_ SQL .parse (c, l_source, 1, l_source.count, true, dbms_ SQL .native );
Dbms_ SQL .close_cursor (c );
End;
/
Show errors;

For example, assume that a scheme needs to clone the "FOO" function in SCOTT's scheme. SCOTT will have a copy of CLONER and grant the EXECUTE Permission to users who can clone SCOTT objects. Other users can issue the following SQL * Plus command:

SQL> connect ANOTHER USER
SQL> @ clone SCOTT FUNCTION FOO

This method is feasible, but still requires SQL * Plus sessions and scripts. I want to put everything in SQL so that any application can execute this function. To achieve this idea, we need to wrap the preceding SQL * Plus script into another dynamic SQL statement, in this statement, we can add the name of the owner and combine the owner and type parameters. You can use the following process:

Create or replace procedure clone_obj
(
P_owner varchar2,
P_type varchar2,
P_name varchar2
)
Authidcurrent_user
Is
Lf char: = chr (10 );
Begin
Execute immediate
'Desc' | lf
| 'Ipls_integer: = 1; '| lf
| 'L_source dbms_ SQL .varchar2s; '| lf
| 'L_line varchar2 (4000); '| lf
| 'L_cursorsys_refcursor; '| lf
| 'C pls_integer; '| lf
| 'R pls_integer; '| lf
| 'Begin' | lf
| ''| P_owner | '. get_source (: 1,: 2, l_cursor);' | lf
| 'L_source (I): = ''create or replace ''; '| lf
| 'Loan' | lf
| 'Fetch l_cursor into l_line; '| lf
| 'Exit when l_cursor % notfound; '| lf
| 'I: = I + 1;' | lf
| 'L_source (I): = l_line; '| lf
| 'End loop; '| lf
| 'Close l_cursor; '| lf
| 'If I = 1 then' | lf
| 'Raise_application_error (-20000 ,'
| '''Object does not exist ''); '| lf
| 'End if; '| lf
| 'C: = dbms_ SQL .open_cursor; '| lf
| 'Dbms_ SQL .parse (c, l_source, 1, l_source.count ,'
| 'True, dbms_ SQL .native); '| lf
| 'Dbms_ SQL .close_cursor (c); '| lf
| 'End; '| lf
Using p_type, p_name;
End clone_obj;
Show errors;

Note: to make the process have sufficient permissions to create database objects, I must add AUTHID CURRENT_USER. Now you can use any product that can call the Oracle stored procedure to call this process. The following example is the same as the previous one, but this example is written in SQL * Plus:

SQL> @ clone_obj
SQL> exec clone_obj ('Scott ', 'function', 'foo ');

There are some security issues here, but not many. Only users granted the EXECUTE Permission to GET_SOURCE can read the source code of objects that they normally cannot see. Ideally, you can create a user that only contains "GET_SOURCE" and a group of template objects.

The above program is incomplete, but it can still be used as an example. In addition to packaging the source code with 4000 characters into a target line with a length of 256 characters, you may also need to expand it to scan the Object Name and insert an owner name, this allows the DBA owner to clone an object from a user to another user.