Re: database link, dynamic sql and stored procedure

"Marco Toci" <m.toci_at_amnesty.it> wrote in news:10941bafaeb7709c08dcf3eb560dca31.49383_at_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

Your English is MUCH better than my Italian! ;-)

I suggest that you build the complete UPDATE statement as a single VARCHAR2 variable and just display it via DBMS_OUTPUT without trying to EXECUTE IMMEDIATE. then cut & paste the results into SQL*Plus to see where the actual errors are.

Here is a free clue, single quote marks are needed around strings when the are used within WHERE clauses; but you already knew that. Right? Received on Mon Jul 19 2004 - 20:53:13 CDT