| EXECUTE IMMEDIATE - Bind Variable - DB-Link [message #285701] |
Wed, 05 December 2007 06:02  |
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 #285745 is a reply to message #285704] |
Wed, 05 December 2007 07:28  |
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 #285756 is a reply to message #285701] |
Wed, 05 December 2007 08:16 |
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 #285764 is a reply to message #285701] |
Wed, 05 December 2007 09:03 |
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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
