Discussion between hud and Alex Poole - 2024-06-27

10:20 PM

-1

Q: Creation of new package is giving error in Oracle

I have created a new Package in Schema with name as PKG_FIBER_INV_OTHER_LEASE_V1 But in log I am getting error as "ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body "APP_FIBERINV.PKG_FIBER_INV_OTHER_LEASE_V1" has been invalidated ORA-04065: not e...

oracle stored-procedures package

The first error might just be because your package has state (essentially variables declared at package level, rather than within a procedure or function), but the rest suggests a real problem. Compile the package and see what errors are reported; or query user_errors (as the owner). If you can't see the problem you'll need to include your code (which might be unreasonably large) and error in the question.

@AlexPoole : here is my fiddle dbfiddle.uk/einYluoa

Sorry, updated the fiddle

Nearly 4000 lines of code seems unreasonably long... the error says the identifier at line 2941 is too long. TBL_FIBER_INV_JOB_PROGRESS_OL_h is 31 characters. In 18c it errors because you haven't supplied the package specification, just the body - the error in the question hints at that too. Have you actually created the package spec before the body?

but it got compiled easily.. is that error is due to long characters ?

Also, can I format it properly as its totally got long and spaced?

It compiled with errors. Which errors depends on your version, tables, and whether there is a spec, and what's in it. In 11g the long name would break it. IN later versions maybe not. You need to fix the errors. Starting with creating the package specification if you haven't already.

10:20 PM

i will drop and create again to check that

You're doing create or replace so dropping it won't really add anything. You need to look at the errors you get in your environment, which will be different to the fiddle environment. Version, other tables, spec...

ok

how can i try that?

is it due to lower version ?

i can try in higher version for the same

Also I mentioned state earlier; that's because of the error_code and error_message variables. That's not necessarily wrong per se, but that's why you get that initial error. Any session connected when the package is recompiled will get the state-discarded error. Normally it can then carry on - but only if the package is valid.

Look at user_errors and see what it is actually complaining about.

If you're compiling it in SQL*Plus or similar you can also show errors afterwards but that won't work in all clients.

how to check at user_errors ?

select * from user_errors;

Like in the two fiddles I linked to already.

10:31 PM

no errors in user_errors

When connected as the package owner, and after compiling the package?

no let me check after compiling

If there are really no errors then it is just a state problem, and that resolves itself after the first call to the package after it's recompiled. You said it gave those errors after logging in, which is confusing.

state problem ?

i am calling this proc in the package

PROCEDURE GET_R4GSTATE_BY_MAINTZONE
(
pmaintzonecode IN CLOB,
pr4gstatedata OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN pr4gstatedata FOR
WITH maintzone_data AS
(
SELECT DISTINCT mzb.jiostatename, mzb.jiostatecode FROM r4g_lb.mantainenceboundary_evw mzb
WHERE
mzb.maintenancezonecode IN (
SELECT regexp_substr( to_char(pmaintzonecode), '[^,]+', 1, level ) AS maintzonecode
FROM dual
CONNECT BY regexp_substr( to_char(pmaintzonecode), '[^,]+', 1, level ) IS NOT NULL ))
SELECT DISTINCT r4gb.r4gstatename, r4gb.r4gstatecode FROM maintzone_data

docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/…

10:36 PM

so how to resovle this

as its seriously not fetching me data for above proc

And the earlier references, and the duplicate MT0 linked to.

"not fetching me data" - does that mean the error has gone away?

no error is still thier

how to use DBMS_SESSION.RESET_PACKAGE ?

is it exec DBMS_SESSION.RESET_PACKAGE() ?

I still don't know if you're getting compilation errors or just have a session that is still connected while you're recompiling.

I'm going to have to leave you to it. Good luck.

i dnot have cimpilation error

i guess its sesion error

In which case call the procedure again and it should now work.

10:42 PM

u mean to say remove package and call directly the procedure ?

No. I mean what I said. Call the procedure again.

ok

yes called

no return data

First time after package recompilation you get the state-discarded error, because the package is stateful. After that it should be fine. Until you recompile the package again.

what is this error

"PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;
The symbol ";" was substituted for "end-of-file" to continue.
"

Returning no data is not an error. Now you need to look into why the query in the procedure didn't find anything - which may be valid too.

10:45 PM

no its not valid

the same query is working in existing package

and its returning data

So why duplicate the procedure? Anyway we don't have your tables or data so can't tell you why it's not finding anything. Maybe it's erroring - in which case the package state variables might show something.

Good luck...

Transcript for

Jun27

Discussion between hud and Alex Poole

Imported from a comment discussion on stackoverflow.com/questi...

oracle package stored-procedures

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile