?
Solved

Store Procedure by Database Link

Posted on 2008-01-30
10
Medium Priority
?
1,093 Views
Last Modified: 2013-12-19
How can I call a store procedure in another database by DBLink from a local store procedure ?
This outside store procedure have few parameters and return cursor.
0
Comment
Question by:andrewyu
  • 3
  • 2
  • 2
7 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 20784596
To refer to objects in databases other than your local database, follow the object name with the name of the database link to that database:
your_procedure_in_another_database@database_link_name(parameters);

I hope this will help you.
0
 

Author Comment

by:andrewyu
ID: 20784616
I tried it already, but, i got following error when compile this store.

Line # = 8 Column # = 6 Error Text = PLS-00201: identifier 'SM1.SP1@LINKTO_DB2' must be declared
Line # = 8 Column # = 6 Error Text = PL/SQL: Statement ignored
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 400 total points
ID: 20784683
This is problem of privilegies:

PLS-00201: identifier "string" must be declared
Cause: You tried to reference either an undeclared variable, exception, procedure, or other item, or an item to which no privilege was granted or an item to which privilege was granted only through a role.
Action:
 1) Check your spelling and declaration of the referenced name.
 2) Verify that the declaration for the referenced item is placed correctly in the block structure.
 3) If the referenced item is indeed declared but you don"t have privileges to refer to that item, for security reasons, you will be notified only that the item is not declared.
 4) If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior. Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 13

Expert Comment

by:sonicefu
ID: 20784712

exec testproc@remote_db(1);
 
or
 
CREATE OR REPLACE SYNONYM testproc FOR remote_db.testproc;
 
exec testproc(1);

Open in new window

0
 

Author Comment

by:andrewyu
ID: 20793114
How can I handle "RETURN" sursor from store procedure in another server ?
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 20794464
According to my knowledge
Although a PL/SQL stored procedure or function can open a cursor variable and pass it back to a calling subprogram, the calling and called subprograms must be in the same instance. You cannot pass or return cursor variables to procedures and functions called through database links.

---------------
Why don't make procedure local, and execute statements on remote database?

Create or Replace Procedure Local_Proc
is
Begin
Update remote_table@db_link
set col = val
where ------;
End;

Open in new window

0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month3 days, 11 hours left to enroll

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question