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

I really don't have a lot of documentation. Any other help will be greatly appreciated.

thanks ....

Daniel A. Morgan <dmorgan_at_exesolutions.com> wrote in message news:3A8CD3AC.7A8EE616_at_exesolutions.com...
> If I understand what you are trying to do you need to use dynamic SQL. If
 you
> have Oracle 8i look up Native Dynamic SQL. If an earlier version, or
 working
> 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 Sun Feb 18 2001 - 19:42:21 CET