Re: Stored procedure compilation problem

Sybrand,

I'm sorry for the lack of details. I will try to fill in some of the blanks.

Just out of curiosity, how do you know that my problem is with the 01891 error as opposed to the 04052 one? I'm not doubting you, it's just unfamiliar to me as I'm used to debugging onward from the first reported error. Oh, I tried to local some information on the 01891 error and there isn't much out there at all.

As for the procedure itself, I don't think that's what is holding me up. I removed practically all of the surrounding code just to isolate the problem. Right now (for testing purposes) on the production server, I have this procedure:

PROCEDURE TEST AS
  a1 VARCHAR2(5);
BEGIN
  DELETE FROM table_at_dev
  WHERE policy_number = a1;
END; The above code will compile through SQL Plus, but not through JDeveloper. It's not teling me that the link does not exist (although it will if I give a false name), it just can't seem to reference it. However, if I remove the database link, it will compile through JDeveloper without any problems.

The Oracles Names Server contains the proper domain and SID settings for the two database instances. For simplicity sakes, I'll use 1.2.3.4 for PROD and 4.3.2.1 for DEV. The tnsnames.ora file on my local machine and the physical server machine both contain these matching values for the two schemas.

DEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 4.3.2.1)(PORT = 1521))     )
    (CONNECT_DATA =
      (SID = AUTF)
    )
  )

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1521))     )
    (CONNECT_DATA =
      (SID = AUTF)
    )
  )

Any ideas?

Thanks,
Pat

Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message news:<rtg4e0lmh4j2ghmp4rj391j2d4d01hgo5g_at_4ax.com>...
> On 29 Jun 2004 13:38:53 -0700, patrick_hutchison_at_hotmail.com (Patrick
> Hutchison) wrote:
>
> >comp.databases.oracle.server
> >
> >Hey guys,
> >
> >We recently moved two of our database servers to different boxes. We
> >have also set up "Oracle Names Servers" in addition to our standard
> >local TNS entries. Our local machines are set to check the ONS first
> >and then the local TNS settings next. And for the record, we are
> >using Release 8.1.7.0.0.
> >
> >Even though we are using Oracle 8i rather than 9i, I have always been
> >able to use Oracle9i JDeveloper successfully. Since the database
> >transitions, I've recently tried to create stored procedures involving
> >database links, and they will not compile through JDeveloper. But
> >interestingly enough, they WILL compile through SQL Plus. But the
> >errors that I'm getting in JDeveloper are:
> >ORA-04052: error occurred when looking up remote object
> >ORA-00604: error occurred at recursive SQL level 1
> >ORA-01891: Datetime/Interval internal error
> >
> >The suggestive action for the ORA-04052 error is:
> >"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."
> >
> >The KGLR.SQL script is not anywhere to be found on the servers. Does
> >something on the servers need to be reconfigured/reinstalled?
> >
> >The database links are currently set to public, but I have a feeling
> >that there is still a privilege problem, a problem with the Oracle
> >Names Servers, and/or the TNS files. I also should mention that the
> >TNS files on the servers do contain the appropriate domain and SID
> >settings for the "linked" servers that I am attempting to query.
> >
> >I apologize in advance if I'm using incorrect terminology and/or
> >confusing the hell out of everyone, but database administration is
> >COMPLETELY new to me (I'm a developer).
> >
> >I would greatly appreciate any help you may be able to provide!
> >
> >Thanks,
> >Pat
>
> You need to resolve ora-1891 not ora-4052.
> And without seeing more details like the code and the database link
> set up and tnsnames.ora your lengthy post just boils down to 'it
> doesn't work' and you leave the group shoot in the dark, using the
> crystall balls we don't have.
Received on Wed Jun 30 2004 - 10:01:52 CDT