Home » Open Source » Programming Interfaces » EXECUTE IMMEDIATE - Bind Variable - DB-Link (Ora10)
EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285701] Wed, 05 December 2007 06:02 Go to next message
panzertape
Messages: 20
Registered: August 2007
Junior Member
Hi all!

I have a problem using bind variables in a EXECUTE IMMEDIATE-Statement over a database link to MS SQL Server.
I am trying it this way:
EXECUTE IMMEDIATE 'insert into table@"DB-LINK" values (:1,5)'
USING variable;
COMMIT;

table has two fields: text-field and a primary key field. (B1=text, 5=PK)
I'm ending up with a ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][A00A] Execute query failed, datasource is 'ora', SQL text is 'INSERT INTO "table" ("varc","pk") VALUES (?,5)'

Why it isn't recognizing my bind variable??
What am I doing wrong??

The DB-Link and a 'normal' INSERT is working:
insert into varch_test@db_link values ('sample_text',5);

But I need to pass the contents of a variable to the other DB.
Do you have any ideas for a solution / workaround ?

thanks in advance
panzertape
Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285704 is a reply to message #285701] Wed, 05 December 2007 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this the only message you got?
You should have a message from ODBC driver, in addition to "execute query failed".
Are you sure there is a table named "table" at the other end?

Regards
Michel
Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285745 is a reply to message #285704] Wed, 05 December 2007 07:28 Go to previous messageGo to next message
panzertape
Messages: 20
Registered: August 2007
Junior Member
Yes, the table is really there.
I can make a insert this way:
insert into varch_test@db_link values ('sample_text',5);
But it isn't working when I'm trying to insert the content of a variable instead of 'sample_text'

That's the complete error message I get:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][A00A] Execute query failed, datasource is 'ora', SQL text is 'INSERT  INTO "VARCH_TEST" ("varc","pk") VALUES (?,5)'
ORA-02063: preceding 2 lines from XXX1
ORA-06512: in "SYSTEM.XMLGEN", Zeile 30
ORA-06512: in Zeile 2

VARCH_TEST: is the table in MS SQL Server
"varc": is the text - column name
"pk": is the primary key - column name
XXX1: is the name of the database link
Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285750 is a reply to message #285745] Wed, 05 December 2007 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can do it this way but there is no relation with the statement you posted apart this is an insert.
Don't you see this is not the same table?

Regards
Michel
Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285754 is a reply to message #285701] Wed, 05 December 2007 08:07 Go to previous messageGo to next message
panzertape
Messages: 20
Registered: August 2007
Junior Member
ok, now I'm confused...
insert into varch_test@"XXX1" values ('sample_text',5);
is working
EXECUTE IMMEDIATE 'insert into varch_test@"XXX1" values (:1,5)'
 USING varc_var;
 COMMIT;

is not working.

I think i don't get what you mean Embarassed

Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285755 is a reply to message #285754] Wed, 05 December 2007 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now copy and paste the result you have (the full error message) with the correct table.

Regards
Michel
Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285756 is a reply to message #285701] Wed, 05 December 2007 08:16 Go to previous messageGo to next message
panzertape
Messages: 20
Registered: August 2007
Junior Member
EXECUTE IMMEDIATE 'insert into varch_test@"XXX1.REGRESS.RDBMS.DEV.US.ORACLE.COM" values (:1,5)'
  USING varc_var;
  COMMIT;

===>
Connecting to the database master.
ORA-28500: Verbindung von ORACLE zu Fremdsystem gab diese Nachricht zurück:
[Generic Connectivity Using ODBC][A00A] Execute query failed, datasource is 'ora', SQL text is 'INSERT  INTO "VARCH_TEST" ("varc","pk") VALUES (?,5)'
ORA-02063: vorherige 2 lines von XXX1
ORA-06512: in "SYSTEM.XMLGEN", Zeile 22
ORA-06512: in Zeile 2
Process exited.
Disconnecting from the database master.

Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285757 is a reply to message #285756] Wed, 05 December 2007 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where does this SYSTEM.XMLGEN come from?

Regards
Michel
Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285761 is a reply to message #285701] Wed, 05 December 2007 08:44 Go to previous messageGo to next message
panzertape
Messages: 20
Registered: August 2007
Junior Member
XMLGEN is just the name of my stored procedure where this statement is located...
Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285762 is a reply to message #285761] Wed, 05 December 2007 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you translate the messages.

Regards
Michel
Re: EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285764 is a reply to message #285701] Wed, 05 December 2007 09:03 Go to previous message
panzertape
Messages: 20
Registered: August 2007
Junior Member
sure,

ORA-28500: Verbindung von ORACLE zu Fremdsystem gab diese Nachricht zurück:
[Generic Connectivity Using ODBC][A00A] Execute query failed, datasource is 'ora', SQL text is 'INSERT INTO "VARCH_TEST" ("varc","pk") VALUES (?,5)'

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

ORA-02063: vorherige 2 lines von XXX1
ORA-02063: preceding 2 lines from XXX1

ORA-06512: in "SYSTEM.XMLGEN", Zeile 22
ORA-06512: in "SYSTEM.XMLGEN", Line 22

ORA-06512: in Zeile 2
ORA-06512: in Line 2

thanks for your effort!
Previous Topic: informatica 8.1.1
Next Topic: Connection with the server (merged)
Goto Forum:
  


Current Time: Mon Dec 22 16:42:12 CST 2014

Total time taken to generate the page: 0.12336 seconds