sachin_raorane
asked on
Calling Stored procedure using dblink
Hi,
I have 2 oracle servers
Server A
Server B
each one comtain one oracle database
Server A - DB A
Server B - DB B
Server A has one stored procedure Proc C which accept 2 parameters param1 and param 2...param1 is input parameter and param2 is output parameter of datatype CLOB
Now my VB application is connected with server B - DB B....
How do i make a call to the said procedure from my VB application.
I used one command object to do that and in command test i passesd the following
objcmd.CommandText = "{call C@dblink(?)}"
objcmd.Parameters.Append param1
objcmd.Parameters.Append param2
objcmd.execute
str_out = objcmd.Parameters(2).value
bt it shows error "Invalid SQL statement"
HELP ME!!!!!!!!
Regards,
Sachin
I have 2 oracle servers
Server A
Server B
each one comtain one oracle database
Server A - DB A
Server B - DB B
Server A has one stored procedure Proc C which accept 2 parameters param1 and param 2...param1 is input parameter and param2 is output parameter of datatype CLOB
Now my VB application is connected with server B - DB B....
How do i make a call to the said procedure from my VB application.
I used one command object to do that and in command test i passesd the following
objcmd.CommandText = "{call C@dblink(?)}"
objcmd.Parameters.Append param1
objcmd.Parameters.Append param2
objcmd.execute
str_out = objcmd.Parameters(2).value
bt it shows error "Invalid SQL statement"
HELP ME!!!!!!!!
Regards,
Sachin
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
AFAIK you cannot run a stored procedure over a DBLINK. you have to connect to the database itself for that...
ASKER
I am using Oracle 8I... i saw one example on web which has shown the syntax as
Exec <Procedure Name>@<dblink name>(parameter list);
Exec <Procedure Name>@<dblink name>(parameter list);
I'm not sure if the problem is specifically with the link. I'm not up on straight VB calls but from VBscript or VB.Net, you need to add placeholders for both parameters:
objcmd.CommandText = "{call C@dblink(?,?)}"
If you still get an error and still think it's the DBlink causing it:
I found a note on Metalink that talks about a similar issue:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=648530.995
The work-around was to create a local stored procedure that in turn calls the remote procedure. Then call the local proc from the code.
objcmd.CommandText = "{call C@dblink(?,?)}"
If you still get an error and still think it's the DBlink causing it:
I found a note on Metalink that talks about a similar issue:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=648530.995
The work-around was to create a local stored procedure that in turn calls the remote procedure. Then call the local proc from the code.
Did u try this?
myCon.Execute myProc@dblnk(" & val1 & ", " & val2 & ")"
myCon.Execute myProc@dblnk(" & val1 & ", " & val2 & ")"
ASKER
aiklamha, thnx for suggestion, but my second paramet is output parameter so I cant just pass value...
ASKER
slightwv, i have added the placeholders, still the same error...also i am not able to follow ur lick..it as for username and password and when i tried to register it ask for some access code and country i dont know wht to enter there
Access to Metalink requires a support contract. I just added the link in case you had access and wanted to see the source of the work-around.
All it really said was what I posted:
The work-around was to create a local stored procedure that in turn calls the remote procedure. Then call the local proc from the code.
Try creating a wrapper procedure locally and see if it will work. I only have 10g around but I can try to come up with a test case if you wish.
All it really said was what I posted:
The work-around was to create a local stored procedure that in turn calls the remote procedure. Then call the local proc from the code.
Try creating a wrapper procedure locally and see if it will work. I only have 10g around but I can try to come up with a test case if you wish.
ASKER
thanx slightwv...I did that and it is working in SQ*Plus...it output me the value of output parameter...but still the same error in VB :(
bad luck....i think better i post this question in VB section as well...might get some solution...I am leveing this to administratorwhether to award you point or not ...dont feel bad..As accepting answer means approving your answer as solution...and i found that many time google redirct ppl to EE quistion and this will be misleading to thoes ppl...
So i am leaving Administrator if he wan he can award points to you.
Thanx once again.
bad luck....i think better i post this question in VB section as well...might get some solution...I am leveing this to administratorwhether to award you point or not ...dont feel bad..As accepting answer means approving your answer as solution...and i found that many time google redirct ppl to EE quistion and this will be misleading to thoes ppl...
So i am leaving Administrator if he wan he can award points to you.
Thanx once again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
how stupid i was...any way...that worked thanx a lot :)