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