database link, dynamic sql and stored procedure

From: Marco Toci <m.toci_at_amnesty.it>
Date: Mon, 19 Jul 2004 16:04:15 +0000 (UTC)
Message-ID: <10941bafaeb7709c08dcf3eb560dca31.49383@mygate.mailgate.org>

Hi all
I'm a newbie in pl/sql , and i have a problem i cannot solve

My problem is: i need a stored procedure to insert a row in a table that is in a remote database

I need to pass the link to the procedure as parameter because i may have more than one table (and more than one server) to insert into.

the table i'm trying to insert into is ibf_members, and it is in a schema named ipb_stage. The database name for both server and client is EMDB (i have only one server for testing purpose , so i'm trying to make the server to connect itself on a different schema)

I definded this database link

create database link emdb.forum_cv connect to ipb_stage identified by mypwd using 'ORACLE2';

now, if i try to execute this

PROCEDURE add_to_cv (u_id in number, cv_list in varchar2)

    AS
....

    BEGIN
....

  	update 
  		ibf_members_at_emdb.forum_cv
  	set
  		org_perm_id =cv_list where CV_MEMBERS.id=u_id;

....

  END; it works, but if i try this ( i try to send the table name as parameter in table_link)

PROCEDURE add_to_cv (u_id in number, cv_list in varchar2, table_link in varchar2)

    AS
....

    BEGIN
....

       execute immediate 'alter session set global_names = true';  

       myquery:='update 
  	             '||table_link||' 
  	          set
  		     org_perm_id =:1 where CV_MEMBERS.id=:2;';

       execute immediate myquery using  cv_list, u_id;

....

  END;    I have this error

SQL error code: 6550
SQL error message: ORA-06550: line 1, column 20: PL/SQL: ORA-04052: error occurred when looking up remote object IPB_STAGE.IBF_MEMBERS_at_EMDB.FORUM_CV ORA-00604: error occurred at recursive SQL level 2 ORA-02085: database link EMDB.FORUM_CV connects to EMDB ORA-06550: line 1, column 8: PL/SQL: SQL Statement ignored ORA-06512: at "STAGE.MDT", line 292 ORA-06512: at "STAGE.LOGIN_IPB_USER", line 4 ORA-04088: error during execution of trigger 'STAGE.LOGIN_IPB_USER'

and if i try

       execute immediate 'alter session set global_names = false';

all i have is this:

SQL error code: 2069
SQL error message: ORA-02069: global_names parameter must be set to TRUE for this operation

I tried this too

cur_hdl := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(
    cur_hdl, my_query, DBMS_SQL.NATIVE);   DBMS_SQL.BIND_VARIABLE
    (cur_hdl, ':1', u_id);
....(all the binding)
 rows_processed := dbms_sql.execute(cur_hdl);  DBMS_SQL.CLOSE_CURSOR(cur_hdl);

instead of execute immediate, but i have the same errors. what can i do?

(please, forgive my poor english)
thanks in advice

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Mon Jul 19 2004 - 11:04:15 CDT