Warning: The
following article can create serious security holes if used carelessly. Please
understand the ramifications thoroughly before using it in a non-test
environment.

If you want to take a copy of one table and make a copy of
it in another schema (under another username), you can use the SQL*Plus COPY
command, or a simple <code>”create
table foo as select * from other.foo;”</code>,
as long as you have select privileges on the other user’s table.

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 USER

SQL> @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_obj

SQL> 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.

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!