In PL / SQL, can I pass the FROM cursor suggestion table schema through a stored procedure parameter?

Question

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

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

I want the value of "schema" to come from an input parameter to a stored procedure. Does anyone know how I can do this?

PS Sorry if this is a stupid simple question, but I'm new to PL / SQL and should get some features quickly.

Answer 1

In addition to what Mark Brady said, another dynamic SQL option is to use REF CURSOR. Since your sample code contains a cursor, this will be 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;

Answer 2

This needs to be done using dynamic sql.

DBMS_SQL, Execute Immediate.

FROM.

ALTER SESSION SET Current_Schema = '' < - , .

. SO, , SQL .