database link, dynamic sql and stored procedure

[Marco Toci]

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

[Sybrand Bakker]

On Mon, 19 Jul 2004 16:04:15 +0000 (UTC), "Marco Toci"
<m.t...@amnesty.it> wrote:

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

Make sure the link name equals the global database name of the remote
database. Check the global database with select * from global_names at
the remote database.

--
Sybrand Bakker, Senior Oracle DBA

[Ana C. Dent]

"Marco Toci" <m.t...@amnesty.it> wrote in
news:10941bafaeb7709c08d...@mygate.mailgate.org:

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?

[Marco Toci]

"Sybrand Bakker" <sybr...@hccnet.nl> wrote in message
news:i30of092q9jlpqok5...@4ax.com

> On Mon, 19 Jul 2004 16:04:15 +0000 (UTC), "Marco Toci"
> <m.t...@amnesty.it> wrote:
>
> >instead of execute immediate, but i have the same errors. what can i do?
>
>
> Make sure the link name equals the global database name of the remote
> database. Check the global database with select * from global_names at
> the remote database.
>

in this case "emdb" is the global database name.
For testing purposes i'm using the same server, trying to estabilish a
loopback link from a schema to another.

so if i try

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

i obtain

ORA-02082: a loopback database link must have a connection qualifier

and if i try

create database link emdb@cv_forum connect to ipb_stage identified by
mypwd using 'ORACLE2';

i can create the link, but when i use it in the execute immediate
statement, i have the error messages i described in my other posts

(the database link is not pubblic, because the user that created it is
the same user that will execute the stored procedure... could this be
the problem?)

any suggestion?
many thanks :)

[Marco Toci]

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

oh, sorry, i forget to say that "CV_MEMBERS" is a synonym for
ibf_m...@emdb.forum_cv, so the query should be

update
ibf_m...@emdb.forum_cv
set
org_perm_id =cv_list
where

ibf_m...@emdb.forum_cv.id=u_id;

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

well... i tried that, and the query displayed works on sql*plus, but
still doesn't works on the "execute immediate" statement.

i tried this:

In sql*plus i tried

update ibf_m...@emdb.forum_cv set org_perm_id ='1,2,4' where
ibf_m...@emdb.forum_cv.id=100;

and it works
but if i put it in my stored procedure, using
execute immediate ' begin update ibf_m...@emdb.forum_cv set
org_perm_id =''1,2,4'' where ibf_m...@emdb.forum_cv.id=100; end;'

i have
ORA-00904: : invalid identifier
ORA-02063: preceding line from EMDB@FORUM_CV
ORA-06512: at line 1
ORA-06512: at "STAGE.MDT", line 297
ORA-06512: at line 1

so ... what else may i try? :-(
many, many thanks

[Ana C. Dent]

"Marco Toci" <m.t...@amnesty.it> wrote in

news:97464bc39a60d0a873f...@mygate.mailgate.org:

go to http://asktom.oracle.com/
do a keyword search on "EXECUTE IMMEDIATE"
and try to learn from the examples in the various responses.