Re: DBMS_SQl.Parse problem

From: <bellintani_at_my-deja.com>
Date: Thu, 08 Jul 1999 12:58:08 GMT
Message-ID: <7m278q$ab5$1@nnrp1.deja.com>

here is the procedure causing a problem I have actually got it executing by hand. But the only problem is I cannot get to work automatically via trigger. There is a trigger connected to the table so when I insert a row into the table the trigger fires and executes the procedure.
Any help appreciated

many thanks in advance

Philippe

create or replace procedure INSERT_DATA_test3

(ref_table in varchar2, ref_table_pk_name in varchar2, ref_table_pk_value in integer)
is
 cid integer; /*declare cursor variables */  w integer;
 server_name varchar2(20);
 delcur integer;
 d integer;
 cursor servercur is

       select server_name
  from P_S
  where priority != 0;

begin

 SAVEPOINT insert_table;
 open servercur;
 LOOP   FETCH servercur INTO server_name;
  EXIT WHEN servercur %NOTFOUND;

  cid := dbms_sql.OPEN_CURSOR;
  dbms_sql.PARSE(cid,

   'insert into '||ref_table||'@'||server_name|| /*see above cursor */
   ' select * from '||ref_table||
   ' where '||ref_table_pk_name||'= '||ref_table_pk_value,

dbms_sql.V7);
  w := dbms_sql.EXECUTE(cid in integer);   dbms_sql.CLOSE_CURSOR(cid);

      end loop;

 close servercur;

 end insert_data_test3;

/

In article <SUBWXPA$W1g3Ew36_at_ahardy.demon.co.uk>,   Andy Hardy <aph_at_ahardy.demon.co.uk> wrote:
> In article <7lvc09$9l8$1_at_nnrp1.deja.com>, bellintani_at_my-deja.com
writes
> >I was wondering if someone could help me with a plsql problem,
> >specifically DBMS_SQl.Parse. I have created a procedure that will be
> >executed by a trigger to insert a row into a table on a different
> >server. However the procedure compiles ok but it will not execute:
I
> >am receiving the following error code when I try to execute it from
the
> >SQL cmd prompt:
> >
> > SQL> execute insert_data_test('application', 'applicationid');
> >begin insert_data_test('application', 'applicationid'); end;
> >
> >*
> >ERROR at line 1:
> >ORA-01008: not all variables bound
> >ORA-06512: at "SYS.DBMS_SYS_SQL", line 348
> >ORA-06512: at "SYS.DBMS_SQL", line 141
> >ORA-06512: at "PRODUCT_USER.INSERT_DATA_TEST", line 22
> >ORA-06512: at line 1
> >
> >I have not bound any variable because I am only inserting rows into a
> >table, what variables do I need to bind.

>

> It would be useful if you could include the procedure that is causing
> the problem...

>

> Andy
> --
> Andy Hardy. PGP key available on request
> ===============================================================
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 08 1999 - 07:58:08 CDT