Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Executing non-oracle procedure from Oracle database
rajesh_kdba
post Apr 19 2010, 12:34 PM
Post #1


Newbie
*

Group: Members
Posts: 8
Joined: 18-April 10
Member No.: 42,949



Hi,

i want to Execute a non-oracle procedure from Oracle database.
i am using DG4ODBC generic HS connectivity.

i want to execute a procedure located in sql 2000 from Oracle 11g database.

my remote stored procedure looks like this
sp_check_emp(empid in int)

My Heterogenous database link name , say HSLINK1

when i am triying to execute the procedure from Oracle with the following
syntax , its throwing an error invalid fuction.

SQL> call sp_check_emp@HSLINK1(1000) ;

can anyone provide info on executing a nonoracle procedure from oracle.
is there any way to execute a nonoracle procedure from oracle
Go to the top of the page
 
+Quote Post
Brian Carr
post Apr 20 2010, 12:36 PM
Post #2


Advanced Member
***

Group: Members
Posts: 242
Joined: 14-August 07
Member No.: 11,137



Is your dblink name really HSLINK1(1000)? I've never seen a db link name accept a parameter. What are you doing here?

Consider reading this article - http://www.dba-oracle.com/t_heterogeneous_..._sql_server.htm


--------------------
Go to the top of the page
 
+Quote Post
rajesh_kdba
post Apr 20 2010, 01:16 PM
Post #3


Newbie
*

Group: Members
Posts: 8
Joined: 18-April 10
Member No.: 42,949



i have already configured heterogenous dblink to sql 2000.

and we are using it.

the thing is i want to execute a sql 2000 procedure from oracle.

consider HSLINK as the database link name to sql 2000

i give you the generic syntax for calling the remote procedure

sql>call remote_stored_procedure@HSDBLINK(procedure_input_paramter);

that's wat i have used.

SQL> call sp_check_emp@HSLINK(1000) ;

here:

sp_check_emp -> is Sql 2000 procedure name
HSDBLINK -> database link name to sql 2000
1000 -> is the input value to the procedure.
Go to the top of the page
 
+Quote Post
Brian Carr
post Apr 20 2010, 08:21 PM
Post #4


Advanced Member
***

Group: Members
Posts: 242
Joined: 14-August 07
Member No.: 11,137



I'd recommend passthrough - http://download.oracle.com/docs/cd/B19306_...a**.htm#i997379

Similar to:
CODE
declare
  ret integer;
  parm1 number;
begin
  ret := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@HSLINK1('sp_check_emp "' || parm1 || '"');
end;
/


--------------------
Go to the top of the page
 
+Quote Post
rajesh_kdba
post Apr 22 2010, 11:17 AM
Post #5


Newbie
*

Group: Members
Posts: 8
Joined: 18-April 10
Member No.: 42,949



Thanks.

I have tried it and its working fine.

two more questions.
1. Can you give the syntax for returning an output variable from a remote procedure.
2. How to capture the value returned by executing a remote function.
Go to the top of the page
 
+Quote Post
HAL9000
post Apr 22 2010, 02:27 PM
Post #6


Advanced Member
***

Group: Members
Posts: 884
Joined: 25-September 07
Member No.: 12,336



1. Can you give the syntax for returning an output variable from a remote procedure.

See you API specs: http://download.oracle.com/docs/cd/B28359_...19/d_hspass.htm

2. How to capture the value returned by executing a remote function.

See SQLCODE: http://www.fast-track.cc/t_easyoracle_pl_s...ode_sqlerrm.htm
Go to the top of the page
 
+Quote Post
Moro
post Apr 15 2017, 05:36 AM
Post #7


Newbie
*

Group: Members
Posts: 1
Joined: 15-April 17
Member No.: 51,907



I have the same problem, i use passthrough but I'm not able to read output parameter from MSSQL stored procedure.
How can solve? Can you give me an example?
Thanks
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd June 2017 - 03:22 PM