Home » SQL & PL/SQL » SQL & PL/SQL » how can I access a package through dblink?
icon9.gif  how can I access a package through dblink? [message #201991] Tue, 07 November 2006 14:32 Go to next message
beby.akselrad
Messages: 5
Registered: November 2006
Location: Argentina
Junior Member

Hi everybody! I'm new at this site, and sort of a newbie in pl/sql too. That's why I'm posting here, though I'm not sure if this question is "for newbies"... anyway:

I have two databases: pina and teld. I generally work in teld; but now I have to add a procedure which uses data from pina. The thing is that the tables from which I take this data are extremely big, (indexed, thankfully) and too many. So I found that if I add to this the link/connection time (I don't know how to call it, sorry, English is not my first language), it takes a really long time, meaning almost 20 minutes, to come back. Since this delay is absolutely unacceptable due to the nature of the procedure, I was asked to run the query directly in pina. I do it through a procedure which I added in a package. The package is extraccion_excedentes, and the procedure is consumos([many parameters here -hehe-]).
So, I created a synonym for the package, like this:

create public synonym pin_consumos for pin.Extraccion_excedentes@pina


right? And then I call it like this:

PROCEDURE llamar_consumos_pina (
      cuentainfranet   IN       NUMBER,
      tablasalida      OUT      parametros_excedentes
--this is a table of varchar2(500) indexed by binary integer
   )
   IS
      programname   VARCHAR2 (50)  := 'pin_ipass_loader';
      fechadesde    DATE           := TO_DATE ('01-mar-2006');
      fechahasta    DATE           := TO_DATE ('31-mar-2006');
      lineas        PLS_INTEGER;
      estado        NUMBER;
      motivo        VARCHAR2 (100);
   BEGIN
      pin_consumos.consumos (cuentainfranet,
                                              programname,
                                              fechadesde,
                                              fechahasta,
                                              tablasalida,
                                              lineas,
                                              estado,
                                              motivo
                                             );
   END llamar_consumos_pina;


ok. So when I compile it I get this message:

04:42:39 p.m.  Error: ORA-04052: error occurred when looking up remote object PIN.EXTRACCION_[email]EXCEDENTES@PINA.WORLD[/email]
04:42:39 p.m.  ORA-00604: error occurred at recursive SQL level 1
04:42:39 p.m.  ORA-01017: invalid username/password; logon denied
04:42:39 p.m.  ORA-02063: preceding line from PINA


What can I do??
I thank you in advance, Embarassed


B.-

[Updated on: Tue, 07 November 2006 14:32]

Report message to a moderator

Re: how can I access a package through dblink? [message #201994 is a reply to message #201991] Tue, 07 November 2006 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>ORA-01017: invalid username/password; logon denied
>What can I do??
supply a valid username/password combination.
What do you get from:
SQL> select count(*) from user_objects@pina;
Re: how can I access a package through dblink? [message #201997 is a reply to message #201994] Tue, 07 November 2006 14:58 Go to previous messageGo to next message
beby.akselrad
Messages: 5
Registered: November 2006
Location: Argentina
Junior Member

I get 1889.

What should I know now? How do I provide the password? (I already have it, I just don't know how to provide it)
Re: how can I access a package through dblink? [message #202001 is a reply to message #201997] Tue, 07 November 2006 15:27 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's a way to accomplish it. First, connected as 'scott', create a package:
SQL> create or replace package pkg_my is
  2    function fun_my return number;
  3  end pkg_my;
  4  /

Package created.

SQL> create or replace package body pkg_my is
  2    function fun_my return number is
  3    begin
  4      return(314);
  5    end;
  6  end pkg_my;
  7  /

Package body created.

SQL> grant execute on pkg_my to public;

Grant succeeded.

SQL>

Connect as another user, create a database link and test it:
SQL> connect mike/lion
Connected.
SQL> create database link db_scott
  2    connect to scott
  3    identified by tiger
  4    using 'ora10g';

Database link created.

SQL> select 'x' from dual@db_scott;

'
-
x

Still connected as 'mike', create a public synonym over the database link and, finally, use the function:
SQL> create public synonym syn_pkg_my for pkg_my@db_scott;

Synonym created.

SQL> select syn_pkg_my.fun_my from dual;

    FUN_MY
----------
       314

SQL>

Can you follow these steps? Did it bring any improvement to your problem?
Re: how can I access a package through dblink? [message #202004 is a reply to message #202001] Tue, 07 November 2006 15:55 Go to previous messageGo to next message
beby.akselrad
Messages: 5
Registered: November 2006
Location: Argentina
Junior Member

I did everything, except creating the dblink. The thing is that this is for my job, the dblink is already created and active. And it is working... I just can't make it work with this package.
I got the same error message again:

18:50:37  Error: ORA-04052: error occurred when looking up remote object PIN.EXTRACCION_EXCEDENTES@PINA.WORLD
18:50:37  ORA-00604: error occurred at recursive SQL level 1
18:50:37  ORA-01017: invalid username/password; logon denied
18:50:37  ORA-02063: preceding line from PINA
Re: how can I access a package through dblink? [message #202005 is a reply to message #202004] Tue, 07 November 2006 16:01 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What database version do you use? Did you try to Google for combination of "database version", "database link" and the "ORA-01017"?
Re: how can I access a package through dblink? [message #202006 is a reply to message #202005] Tue, 07 November 2006 16:18 Go to previous messageGo to next message
beby.akselrad
Messages: 5
Registered: November 2006
Location: Argentina
Junior Member

Ok, we managed!!! Thank you very much. I still don't know what in this world happened; but I asked someone from support at pina to drop the dblink and re-create it; and that did the trick.

Thank you again!!!


B.-
Re: how can I access a package through dblink? [message #202026 is a reply to message #201991] Tue, 07 November 2006 22:26 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

HI

check it

ORA-04052: error occurred when looking up remote object stringstringstringstringstring
Cause: An error has occurred when trying to look up a remote object.
Action: Fix the error. Make sure the remote database system has run KGLR.SQL to create necessary views used for querying/looking up objects stored in the database.


****************

HOPE THIS HELPS
MOHAMMAD TAJ
Re: how can I access a package through dblink? [message #202042 is a reply to message #202026] Wed, 08 November 2006 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, it helps if you read previously posted messages. If you had done it, you'd see that the problem has already been solved.

P.S. Now, I'd be grateful if there's a good soul among native English-speaking people here to grammatically correct my previous sentence. It *stinks* ... had done - would - has been solved ./fa/1685/0/
icon9.gif  Re: how can I access a package through dblink? [message #202151 is a reply to message #202042] Wed, 08 November 2006 07:09 Go to previous message
beby.akselrad
Messages: 5
Registered: November 2006
Location: Argentina
Junior Member

Hi, thanks for the tip. I actually tried reading previous posts, but I couldn't find it. Probably because I was trying the wrong key words. Embarassed

Ps. Your grammar is quite ok. I would have said it like this, though:
'If you had done it, you would have seen that the problem has already been solved'
Previous Topic: Help with sql query
Next Topic: a question regarding GRANT ALTER ON table TO user
Goto Forum:
  


Current Time: Wed Dec 07 18:48:28 CST 2016

Total time taken to generate the page: 0.27616 seconds