Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    14

    Unanswered: Table Name as parameter to procedure + cursor

    i am writing a plsql procedure that will automatically take in the name of a table and place it in a cursor.

    I have the following

    Code:
    CREATE OR REPLACE PROCEDURE RUN_COMMANDS(
    					table_name IN VARCHAR2) IS
    
    	-- Cursor called to get names of tables 
    	CURSOR sql_commands IS
    		select command from table_name order by seq_num;
    BEGIN
    ...
    ...
    ...
    END;
    but when I run this into the DB I get

    Code:
    PL/SQL: ORA-00942: table or view does not exist
    Any one with any ideas??

    Thanks in advance

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You need to be using dynamic SQL ... Try building the sql statement in
    the procedure, then executing it ...

    v_sql varchar2(1000);
    ...
    ...
    v_sql := 'select command from '||table_name||' order by seq_num';
    execute immediate v_sql into v_numrows;
    ...
    ...

    hth
    Gregg

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Out of interest why would you have more than one table with this information in an identically named column? And what business is it of the calling procedure where the data is stored? Just curious.

  4. #4
    Join Date
    Nov 2004
    Posts
    14
    thanks guys i got it to work

    the whole point of this procedure is to run a batch load of commands from a table. this table can be stored in multiple schemas. The table name could be different but the structure of the table will be the same

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •