In PL/SQL, can I pass the table schema of a cursor FROM clause via a stored procedure parameter?

Question

In PL/SQL, I would like to pass in a "source" schema as a parameter to a stored procedure. For instance:

BEGIN
    CURSOR my_cursor IS
      SELECT my_field FROM <schema>.my_table
...

I want the 'schema' value to come from an input parameter into the stored procedure. Does anyone know how I could do that?

P.S. Sorry if this is a stupid simple question, but I'm new to PL/SQL and must get some functions written quickly.

Answer 1

This has to be done with dynamic sql.

Either the DBMS_SQL package or the Execute Immediate statement.

You can't use variables in the FROM clause.

A potential solution may be to

ALTER SESSION SET Current_Schema = '' <-- the schema you want.

That command changes the default schema. SO if you have a bunch of identically named tables you can save yourself dynamic SQL and make a Dynamic Alter Session.

Answer 2

In addition to what Mark Brady said, another dynamic SQL option is to use a REF CURSOR. Since your sample code includes a cursor this would be the most relevant.

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;