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.

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