Help With PL/SQL Code !!!!
Date: Thu, 15 Feb 2001 15:12:33 -0500
Message-ID: <96henv$fv00$1_at_newssvr06-en0.news.prodigy.com>
[Quoted] [Quoted] 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; [Quoted] 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); [Quoted] *** this Line WORKS...
END LOOP;
[Quoted] sql_ret := dbms_sql.execute(exec_cursor); dbms_sql.close_cursor(exec_cursor);END IF;
END; thanks
Howard
hsmith_at_geotelec.com Received on Thu Feb 15 2001 - 21:12:33 CET