Help With PL/SQL Code !!!!

From: H Smith <hsmith00_at_prodigy.net>
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

Original text of this message