Home » SQL & PL/SQL » SQL & PL/SQL » Using a stored proc through a db-link, in a package, on a pass-through-account?
icon4.gif  Using a stored proc through a db-link, in a package, on a pass-through-account? [message #267527] Thu, 13 September 2007 11:05 Go to next message
ashtonkm
Messages: 10
Registered: August 2007
Junior Member
Does anyone know how to execute a remote procedure, in a package, on a pass-through-account through a database link?

I have found examples on the internet of using a stored proc through a db-link. The syntax for this is:

Link
exec testproc@remote_db(1);


I have tried the following, neither one works:

exec CMIS40.CMIS_TES.SEARCHBYMRN@CMIS(mrn_in => p_mrn ,results_cursor => ResultsCursor); 

exec CMIS40.CMIS_TES.SEARCHBYMRN(mrn_in => p_mrn ,results_cursor => ResultsCursor)@CMIS;


CMIS40 = schema that owns the package
CMIS_TES = package
SEARCHBYMRN = procedure
CMIS = database link

It returns the following error:
SQL> show errors
Errors for FUNCTION MRN_UNIT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
18/9     PLS-00103: Encountered the symbol "CMIS40" when expecting one of
         the following:
         := . ( @ % ;
         The symbol ":=" was substituted for "CMIS40" to continue.


I'm not sure where my syntax is breaking down. Any ideas or pointers would be appreicated
Re: Using a stored proc through a db-link, in a package, on a pass-through-account? [message #267533 is a reply to message #267527] Thu, 13 September 2007 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you REALLY have.
There is nothing like line 18 in what you post.
How can you expect we find something with nothing?

In addition, Oracle version is not optional.

Regards
Michel
Re: Using a stored proc through a db-link, in a package, on a pass-through-account? [message #267537 is a reply to message #267533] Thu, 13 September 2007 11:22 Go to previous messageGo to next message
ashtonkm
Messages: 10
Registered: August 2007
Junior Member
Michael thanks for the prompt reply. I'm not exactly sure what you wanted me to post so I just put the whole function. As you can see I'm not doing anything with the Reference Cursor yet because first I want the remote stored proc to execute correctly. I am running Oracle 10.2. The remote database (CMIS) is running 9.2.

SQL> CREATE OR REPLACE FUNCTION mrn_unit (
  2    p_mrn  VARCHAR2)
  3    RETURN number IS
  4    
  5    type cursorType is ref cursor;
  6    ResultsCursor cursorType;
  7    results_mrn             varchar2(13);
  8    results_name            varchar2(80);
  9    results_unit            number(7) := 5;
 10    results_birthdate       varchar2(8);
 11    results_gender          varchar2(1);
 12  
 13    
 14  BEGIN
 15  
 16         exec CMIS40.CMIS_TES.SEARCHBYMRN@CMIS(mrn_in         => p_mrn
 17                            ,results_cursor => ResultsCursor); 
 18  
 19    RETURN results_unit;
 20  END mrn_unit;
 21  /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION MRN_UNIT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
16/13    PLS-00103: Encountered the symbol "CMIS40" when expecting one of
         the following:
         := . ( @ % ;
         The symbol ":=" was substituted for "CMIS40" to continue.

SQL> 

[Updated on: Thu, 13 September 2007 11:24]

Report message to a moderator

Re: Using a stored proc through a db-link, in a package, on a pass-through-account? [message #267538 is a reply to message #267527] Thu, 13 September 2007 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
> exec CMIS40.CMIS_TES.SEARCHBYMRN@CMIS
It appears you are confused by the difference between PL/SQL & SQL*PLUS.
When inside a PL/SQL procedure you should not & do NOT use EXEC
Re: Using a stored proc through a db-link, in a package, on a pass-through-account? [message #267540 is a reply to message #267538] Thu, 13 September 2007 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's what I wanted you posted.
EXEC a SQL*Plus command to execute a procedure.
Inside a PL/SQL block just give the procedure without "exec".

Regards
Michel
Re: Using a stored proc through a db-link, in a package, on a pass-through-account? [message #267542 is a reply to message #267540] Thu, 13 September 2007 11:33 Go to previous message
ashtonkm
Messages: 10
Registered: August 2007
Junior Member
Thanks Michael! Seems like it is always something simple like that.
Previous Topic: Simpler Query
Next Topic: howto know a indexed column?
Goto Forum:
  


Current Time: Wed Dec 07 18:30:09 CST 2016

Total time taken to generate the page: 0.12635 seconds