Stack Exchange
plsql - dynamically change table name in cursor
i'm getting below error while running the procedure.  is the select correct.. trying to pass table name as parameter.  Pls help.
PLS-00364: loop index variable 'CURSOR1' use is invalid
  PROCEDURE generate_uniqueId(p_table_name IN VARCHAR2)  is
  --
  --
  CURSOR unique_id_cur
   IS
    SELECT /*PARALLEL(20)*/ unique_id
    FROM   p_table_name;

--
        v_file          UTL_FILE.file_type;
        V_file_name     Varchar2 (150);
    V_file_parm     Varchar2:= ora11g/test/;
    v_output    varchar2(200);


  BEGIN
         v_file_name := p_table_name || '.lst';
        v_file := UTL_FILE.fopen ('ora11g/test/', 'unique_ID_file', 'A');
        FOR cursor1 IN unique_id_cur
        LOOP
                BEGIN
                v_output := cursor1.unique_id;
                UTL_FILE.put_line (v_file, v_output);
                END;
        END LOOP;
        UTL_FILE.fflush (v_file);
        UTL_FILE.fclose (v_file);
  END generate_uniqueId ;


I don't think you can do that. But here is an example which works pretty fine. https://stackoverflow.com/a/31536690/5414063

Does table "p_table_name" contains in your database, or you are passing table name via "p_table_name" parameter to the select statement within cursor.

Your PL/SQL code having many syntax issue.

You can pass the table name in cursor declaration in form of "from p_table_name".


CURSOR unique_id_cur
IS
SELECT /*PARALLEL(20)*/ unique_id
FROM   p_table_name;



If you are using "VARCHAR2" data type then you have to provide the length of data should contain a variable. Also the string "ora11g/test/" should kept in single quota -'- as 'ora11g/test/' 


V_file_parm     Varchar2:= ora11g/test/;


The directory that you are passing, am not sure whether it is going to accept or not. But, should create a logical directly in Oracle using "CREATE DIRECTORY" and then pass the newly directory created in "UTL_FILE.FOPEN".

Here is the sample idea to create a procedure that will compile and work for you, create or real procedure like below.
create or replace  PROCEDURE generate_uniqueId(p_table_name IN VARCHAR2 default 'abc')
as
CURSOR unique_id_cur
   IS
    SELECT object_id as unique_id
    FROM   all_tables
    where rownum <=5;

    v_file          UTL_FILE.file_type;
    V_file_name Varchar2(150);
    V_file_parm Varchar2(200) := 'ora11g/test/';
    v_output varchar2(200);

  BEGIN
       v_file_name := p_table_name || '.lst';
       v_file := UTL_FILE.fopen ('ora11g/test/', 'unique_ID_file', 'A');
        FOR cursor1 IN unique_id_cur
        LOOP
                BEGIN
                v_output := cursor1.unique_id;
                UTL_FILE.put_line (v_file, v_output);
                END;
        END LOOP;
        UTL_FILE.fflush (v_file);
        UTL_FILE.fclose (v_file);
  END generate_uniqueId ;

EDIT-1
create or replace  PROCEDURE generate_uniqueId(p_table_name IN VARCHAR2 default 'abc')
as
type t1 is table of varchar2(255) index by pls_integer;
v_unique_ids t1;
v_sql varchar2(4000);
    v_file          UTL_FILE.file_type;
    V_file_name Varchar2(150);
    V_file_parm Varchar2(200) := 'ora11g/test/';
    v_output varchar2(200);

  BEGIN
       v_file_name := p_table_name || '.lst';
       v_file := UTL_FILE.fopen ('ora11g/test/', 'unique_ID_file', 'A');

    v_sql := 'select /* PARALLEL(20) */ unique_id  from '|| p_table_name;
    execute immediate v_sql bulk collect into v_unique_ids;

        FOR cursor1 IN v_unique_ids.first..v_unique_ids.last
        LOOP
                BEGIN
                v_output := v_unique_ids(cursor1);
                UTL_FILE.put_line (v_file, v_output);
                END;
        END LOOP;
        UTL_FILE.fflush (v_file);
        UTL_FILE.fclose (v_file);
  END generate_uniqueId ;


If you want to change the table name of cursor dynamically then you should think about "EXECUTE IMMEDIATE" and store the output into collection.

can u provide an example of execute immediate within cursor for above?

---Remove the Cursor declaration unique_id_cur:
---Declare a type as "type t1 is table of varchar2(255) index by pls_integer;"
---Declare a variable of new type as "v_unique_ids t1;"
---Declare a variable to store the SQL as "v_sql varchar2(4000);"
---Inside Begin, before loop: "v_sql := select /* PARALLEL(20) */ unique_id bulk collect into v_unique_ids from '|| p_table_name;"
---Replace the first line of For Loop as "FOR cursor1 in v_unique_ids.first..v_unique_ids.last LOOP"
---eplace the line within second BEGIN for assiging the value to v_ouput as "v_ouput := v_unique_ids(cursor1);"

I'm new to plsql. :S Can u pls try to amend it in above codes?

thxs. During execution got errors: Is there any changes to be done?? ORA-00942: table or view does not exist
ORA-06512: at "sys.generate_uniqueId", line 18
ORA-06512: at line 6

ORA-00942, mean the table name that you are passing as parameter is not available, re-verify your schema and table name.

procedure compiled in the schema itself containing the table.  should we change: >> type t1 is table of varchar2(255) index by pls_integer;
v_rec_id t1;

That is for declaration of user defined data type, and v_rec_id will store all the values return by the EXECUTE IMMEDIATE. It is different, the value that you are passing in "p_table_name" variable while calling to procedure that table does not exists in database. For example I call the procedure "generate_uniqueId" and pass the value to it as "Table_1" and "Table_1" does not exists in database then it return the erro ORA-00942
