Execute Immediate in PL/SQL (Oracle 8i)

From: Harsh Kohli <harshkohli007_at_yahoo.com>
Date: 8 Jan 2003 14:24:59 -0800
Message-ID: <9173b8d7.0301081424.4b23b08@posting.google.com>

I am a new PL/SQL user. I am trying to select columns from a table using column names stored in a control table in column TABLE_COL_NAMES. Also I am trying to capture these columns into variables stored in the control table in column TABLE_INTO_COL_NAMES. I have put some displays and sql_string displays the correct SQL statement but the job abends. Here is the code in PL/SQL :

     w_COL_NAMES := NULL;
     w_INTO_COL_NAMES := NULL;
     SELECT TABLE_COL_NAMES, TABLE_INTO_COL_NAMES INTO W_COL_NAMES,
W_INTO_COL_NAMES from STAR_COLS_TBL
            WHERE STAR_TXN_PROCS_TYP = 'VEHICLE'; 
       
     dbms_output.put_line ('TABLE_COL_NAMES - ' || W_COL_NAMES );
     dbms_output.put_line ('TABLE_INTO_COL_NAMES - ' ||
W_INTO_COL_NAMES );
     SQL_STRING:=NULL;
     sql_STRING := 'SELECT ' || w_COL_NAMES ||' INTO ' ||
W_INTO_COL_NAMES
                   ||' FROM WSJT104 '|| ' WHERE ' || ' WSJT104_SEQ_ID
'
                   ||' = ' || w_STAR_SEQ_NUMBER;   
     dbms_output.put_line ('* SQL_STRING * - ' || SQL_STRING );
   
     EXECUTE IMMEDIATE SQL_STRING;   
       
 

The procedure abends and gives the following message :

TABLE_COL_NAMES - COL03_TEXT, COL06_TEXT
TABLE_INTO_COL_NAMES - W_STAR_VIN,W_STAR_SALES_PROCESS_DATE
* SQL_STRING * - SELECT COL03_TEXT, COL06_TEXT INTO

W_STAR_VIN,W_STAR_SALES_PROCESS_DATE FROM WSJT104 WHERE WSJT104_SEQ_ID = 2863
BEGIN WSMSB616.main; END;

*
ERROR at line 1:

ORA-00905: missing keyword
ORA-06512: at "MZXZ5L.WSMSB616", line 115
ORA-06512: at line 1

WSMSB616 *** FAILED ***. Can I do this using Execute Immediate and if yes what is the erroe I am getting. If not then what is the alternative. Thanks in advance.

Harsh Received on Wed Jan 08 2003 - 16:24:59 CST