Dynamic PL/SQL

Question

In PL/SQL,I would like to pass a source as well as the target schema as a parameter to a stored procedure. For source we can use:

PROCEDURE select_from_schema( the_schema VARCHAR2)
IS
 TYPE my_cursor_type IS REF CURSOR;
 my_cursor my_cursor_type;
BEGIN
 OPEN my_cursor FOR 'SELECT my_field FROM '||the_schema||'.my_table';
 -- Do your FETCHes just as with a normal cursor
 CLOSE my_cursor;
END;

For the target insert or update statement, how can we use that schema inside that insert or update statement....Does anyone know how could I do that???

P.S. Excuse me; I am a beginner and must get some functions written quickly.

• Jonathan Leffler: Beware SQL injection. What happens if 'the_schema' string contains the snippet from xkcd.com/327?

Answer 1

You can do the same thing for an INSERT or UPDATE that you did for a SELECT - use dynamic SQL like this:

EXECUTE IMMEDIATE 'INSERT INTO '||target_schema||'.my_table (col1,col2...) VALUES(:val1, :val2...)' USING my_row.col1, my_row.col2...;