Re: Help With PL/SQL Code !!!!

[Quoted] If I understand what you are trying to do you need to use dynamic SQL. If you [Quoted] have Oracle 8i look up Native Dynamic SQL. If an earlier version, or working [Quoted] with CLOBs look at the DBMS_SQL package.

Daniel A. Morgan

H Smith wrote:

> In the code below, I don't want to hard code column names I want to pass
> variables. I have listed the
> two lines showing the variable I want to pass.When I run this I get the
> Variable in the table instead of the value.
> When I hard code it I get the value. I need to pass variables.....
>
> Can anyone help me correct this
>
> DECLARE
>
> TYPE bind_table IS TABLE OF sysxref%ROWTYPE INDEX BY BINARY_INTEGER;
> btbl bind_table;
>
> CURSOR cur IS
> SELECT gis_col, arms_col
> FROM sysxref
> ORDER BY seq;
>
> col_str varchar2(1000);
>
> sql_str varchar2(1000);
> val_str varchar2(1000);
>
> exec_cursor integer;
> sql_ret integer;
> bidx binary_integer;
>
> BEGIN
> bidx := 0;
> FOR idx IN cur LOOP
>
> IF idx.GIS_col IS NOT NULL THEN
> col_str := col_str || idx.gis_col;
> val_str := val_str || ':' || idx.gis_col;
>
> bidx := bidx + 1;
> btbl(bidx).gis_col := idx.gis_col;
> btbl(bidx).arms_col := ':new.'||idx.arms_col;
> END IF;
>
> END LOOP;
>
> IF bidx > 0 THEN
> sql_str := 'INSERT INTO tst_hydrant (' || col_str || ') VALUES (' ||
> val_str || ')';
>
> insert into tst_link (dt, string) values (sysdate,sql_str);
>
> exec_cursor := dbms_sql.open_cursor;
> dbms_sql.parse(exec_cursor, sql_str, dbms_sql.native);
>
> FOR i IN 1..bidx LOOP
> /*
> dbms_sql.bind_variable(exec_cursor,btbl(i).gis_col,btbl(i).arms_col);*/
> **** this Line Doesn't WORK...
> dbms_sql.bind_variable(exec_cursor,btbl(i).gis_col,:new.asset_id);
> *** this Line WORKS...
> END LOOP;
>
> sql_ret := dbms_sql.execute(exec_cursor);
> dbms_sql.close_cursor(exec_cursor);
> END IF;
> END;
>
> thanks
> Howard
> hsmith_at_geotelec.com
  Received on Fri Feb 16 2001 - 08:15:56 CET