Store Procedure by Database Link

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.
andrewyuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
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
andrewyuAuthor Commented:
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
Helena Markováprogrammer-analystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sonicefuCommented:

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

Open in new window

0
andrewyuAuthor Commented:
How can I handle "RETURN" sursor from store procedure in another server ?
0
sonicefuCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.