Clone database objects with dynamic SQL
However, if you want to do the same thing with any other database object, such as packages, procedure, functions, or views, there is no simple command. You need to manually find the code and run it against the new instance. It would be nice if you could simply say "clone object" and have another user's object under your own instance. This would be particularly useful for creating a test schema for development. In this example, I create a package that allows you to do this for most objects (with some limitations).
The main problem to tackle is getting the source code. The object owner has access to source code through the view USER_SOURCE. Other users can only see selected source code through the view ALL_SOURCE if the object has been granted EXECUTE privileges to that user. We can encapsulate the request "give me the source for your object" through a single procedure. View Listing A. Notice that I didn't say "UPPER(name)." This means you must match the case of the procedure. (Java stored procedures use a lot of mixed case.)
If this procedure is owned by the object owner, then the source code for that object will be exported through a REF CURSOR variable. If this procedure is granted EXECUTE privilege to another user, then that user will be able to call this procedure and view the source code for any database object--even to objects that aren't granted to them and to objects that don't show up in ALL_SOURCE, such as TYPE declarations. To show that this works, try the code in Listing B from SQL*Plus.
With the source code from the object owner, the caller can create the object in their own schema. We need Dynamic SQL to build the object from text strings. Another problem to deal with is that the source for some database objects can exceed 32,767 characters, the maximum limit for VARCHAR2 strings. That rules out using a simple VARCHAR2 string to hold the SQL. There is a little-used variant of DBMS_SQL.PARSE that allows you to store source code as a table of VARCHAR2 lines. It can be used to store SQL much larger than the 32,767 limit. (In a real application, you would also need to wrap any lines that exceed 256 characters since USER_SOURCE stores up to 4,000 character lines.) Listing C contains the code that you would use if you were going to implement this as a SQL*Plus script with command-line arguments.
As an example, say a schema wants to clone a function called "FOO" in SCOTT's schema. SCOTT would own a copy of CLONER and grant execute privilege to users allowed to clone SCOTT's objects. The other user could issue the SQL*Plus command:
SQL> connect ANOTHER USERSQL> @clone SCOTT FUNCTION FOO
This works but it still requires SQL*Plus sessions and scripts. I want everything in SQL so any application can perform this function. To do that, we need to take the previous SQL*Plus script and wrap it in another dynamic SQL statement, in which we can concatenate the owner name and bind in the owner and type parameters. You can do this with the code in Listing D.
Notice that I have to add AUTHID CURRENT_USER so the procedure will have enough privileges to create database objects. Now you can call this procedure from any product capable of calling Oracle stored procedures. Here is the same example as above but in SQL*Plus:
SQL> @clone_objSQL> exec clone_obj('SCOTT','FUNCTION','FOO');
There is some security in place but not much. Only users who have been granted execute to GET_SOURCE can read the source code for objects that they can't normally view. In an ideal situation, you might create a user that only contains "GET_SOURCE" and a set of template objects.
The program isn't complete, but it's an example of what is possible. Besides needing to wrap the 4,000 character source into the 256 character destination lines, it could also be extended to scan for the object name and insert an owner name so a DBA owner could clone objects from one user to another.