Re: Execute Immediate in PL/SQL (Oracle 8i)

Hi Harsh,

You can't use the "INTO ..." - clause inside the execute immediate Statement.
It is :
EXECUTE IMMEDIATE sqlstring INTO variable;

Rgds

    Dirk

"Harsh Kohli" <harshkohli007_at_yahoo.com> schrieb im Newsbeitrag news:9173b8d7.0301081424.4b23b08_at_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 Mon Jan 13 2003 - 05:43:31 CST