Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06508: PL/SQL: could not find program unit being called (oracle database 10g)
ORA-06508: PL/SQL: could not find program unit being called [message #598465] Tue, 15 October 2013 03:27 Go to next message
njnbat
Messages: 19
Registered: September 2013
Junior Member
I am using oracle 10g and toad 11.5 . i am trying to call an api from an anonymous block . If i recompile the api after adding dbms_output.put_line and then try to execute the anonymous block ,it shows error as "ORA-06508: PL/SQL: could not find program unit being called". However if i end current session and open a new session , then the anonymous block will execute with out the error. Due to this issue, i am made to reconnect the session everytime i make a change to API. Can anyone help if this issue can be resolved by making any configurations in toad or database level.

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #598467 is a reply to message #598465] Tue, 15 October 2013 03:51 Go to previous messageGo to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Have you tried executing the anonymous block in SqlPlus? Does it give the same error as Toad?

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #598469 is a reply to message #598467] Tue, 15 October 2013 03:57 Go to previous messageGo to next message
njnbat
Messages: 19
Registered: September 2013
Junior Member
Yes tried now. I am getting the same error in sqlplus as well .

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #598471 is a reply to message #598469] Tue, 15 October 2013 03:58 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Post your code along with the output of your sqlplus session here.

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #598475 is a reply to message #598465] Tue, 15 October 2013 04:30 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
njnbat wrote on Tue, 15 October 2013 13:57
Can anyone help if this issue can be resolved by making any configurations in toad or database level.


Not necessarily it has to be DB issue. Since you are making changes to API, there is a chance that the connection property might have issues. Do you see the status of the object to be VALID or does it becomes INVALID?

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #600417 is a reply to message #598475] Wed, 06 November 2013 04:53 Go to previous messageGo to next message
njnbat
Messages: 19
Registered: September 2013
Junior Member
The status of the object is valid , but then the issue remains same .. In between when i try compiling it using F5 in toad, it doesnt give the error msg. However now , that is also not helping...F9 or F5 , it gives error. Due to this ,after making changes in the api , i always have to open a new session and execute the anonymous block

ORA-04061: existing state of package body "CUSTOMER.SYS_DATA_UPDATE" has been invalidated
ORA-04065: not executed, altered or dropped package body "CUSTOMER.SYS_DATA_UPDATE"
ORA-06508: PL/SQL: could not find program unit being called: "CUSTOMER.SYS_DATA_UPDATE"
<=>ORA-06512: at line 158

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #600418 is a reply to message #600417] Wed, 06 November 2013 04:58 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I would never depend on toad or a tool to make sure the object is compiled. Use SQL*Plus, compile your code and use SHOW ERRORS.

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #600419 is a reply to message #600417] Wed, 06 November 2013 05:01 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
This is your main problem:

njnbat wrote on Wed, 06 November 2013 10:53

ORA-04061: existing state of package body "CUSTOMER.SYS_DATA_UPDATE" has been invalidated


sys_data_update has global variables that are getting wiped when you recompile the package.
The error should go away the 2nd time you run it, not sure why it's not.
If you can get rid of the globals do so, otherwise you'll probably have to live with reconnecting whenever you change it.

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #600420 is a reply to message #600419] Wed, 06 November 2013 06:02 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
From where do you execute the anonymous block?

I have a somewhat "similar" problem, that one client-application uses a DB package that has global variables. My workaround is to have four "slots" (4 copies of the package, named package_a, package_b, package_c, package_d) of that package, and which package to use is configured in the application settings.

So people that log in use the package that was configured at application launch, say package_a. When I need to change something, I do the change in package_b, and then re-configure the app to use package_b. ~24 hours later all the people that used package_a have logged out and everybody uses package_b.

The next change then switches b->c, then c->d, then d->a again. (So that I can activate 3 new versions a day if need be, so far that has been enough.)

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #600455 is a reply to message #600419] Wed, 06 November 2013 22:38 Go to previous messageGo to next message
njnbat
Messages: 19
Registered: September 2013
Junior Member
This is why i am curious to know if any database changes took place. Before when i used to compile , even if it gave me the same error it would work in the second instance. But now i have to any how open the new session to make it work.
And yes. i have global variables in the package specification . So i am desperate to get a solution for this.

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #600456 is a reply to message #600455] Wed, 06 November 2013 22:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
how can we reproduce what you report?

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #600460 is a reply to message #600456] Wed, 06 November 2013 23:37 Go to previous messageGo to next message
njnbat
Messages: 19
Registered: September 2013
Junior Member
Code
------------------------------
CREATE OR REPLACE PACKAGE CUSTOMER.testingcompilation
AS
b NUMBER;
PROCEDURE testthis;
END;
/


CREATE OR REPLACE PACKAGE BODY CUSTOMER.testingcompilation
AS
a VARCHAR2 (100);

PROCEDURE testthis
IS
BEGIN
a := SYSDATE;
b := 5;

INSERT INTO LogsManagement (origin, messg)
VALUES ('testingcompilation', 'started' || a);

COMMIT;
END;
END;
/


DECLARE
BEGIN
DBMS_OUTPUT.put_line ('starts');
testingcompilation.testthis;
DBMS_OUTPUT.put_line ('ends');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
-------------------------------------------------------------------------------

try changing the variable from a to b , then compile and execute the script. In dbms output, you will see below message all the time you try to compile it until and unless you open a new editor or new session.
ORA-06508: PL/SQL: could not find program unit being called

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #600463 is a reply to message #600460] Thu, 07 November 2013 00:00 Go to previous message
njnbat
Messages: 19
Registered: September 2013
Junior Member
when we have a global variable and if it is referred in any procedure of the package body , then recompilation causes the package to be shown as invalid state in the execution tab ,and this message doesn't go off until and unless we paste the executing block of code in a new tab or open a new editor in toad.
If we dont use the global variable in any of the package procedures , then recompilation error will not come in the anonymous execution block.
In toad,if you dont want to change the editor or session there is a workaround which i found now.
1.remove the exception block , so invalid state error comes as a popup
2.press ok and use ctrl+z to undo the change , so that the exception block comes again.
3.toggle the dbms output off and on
4.execute by pressing f9.
I know that its not a wonderful solution , but somehow i now don't need toad to open new sessions again and again. That's a pain guys Smile

Report message to a moderator

Previous Topic: what array or collection type to use
Next Topic: Use of UPPER function with wild cards
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 18:23:16 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.