Re: Stored procedure compilation problem

patrick_hutchison_at_hotmail.com (Patrick Hutchison) wrote in message news:<7f85260f.0406300644.382851e5_at_posting.google.com>...
> 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.

Pat, in the database you are connecting to create a synonym to the remote object and try to reference the synonym in your java. See if this get around the problem.

HTH -- Mark D Powell -- Received on Thu Jul 01 2004 - 08:43:47 CDT