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

Harsh Kohli wrote:

> 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

My impression is that you are trying to reinvent the wheel. Before you go any further with this take a look at the data stored in USER_TAB_COLUMNS and ALL_TAB_COLUMNS.

And there is no need to ever initialize a variable such as you have done with "w_COL_NAMES := NULL;". These statements accomplish nothing.

Daniel Morgan Received on Wed Jan 08 2003 - 21:05:12 CST