Re: DBMS_SQl.Parse problem

In article <7m278q$ab5$1_at_nnrp1.deja.com>, bellintani_at_my-deja.com writes
>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);

Well, that should really be w := dbms_sql.EXECUTE(cid);

Andy

> 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.

--
Andy Hardy. PGP key available on request