Home » SQL & PL/SQL » SQL & PL/SQL » Invalid state when running first time after compile, error with schema name, permissns proc vs user (Oracle 9.2.0.8 on Solaris unix)
Invalid state when running first time after compile, error with schema name, permissns proc vs user [message #406861] Sat, 06 June 2009 16:24 Go to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
1) Often I will compile a package body without errors.
The first time I run it, I will get the package is in an invalid state. The second time I run the package it will run fine.
What is going on?

2)I created a partition on user2.table1 logged in as user1. When running from toad or sqlplus it executes fine, logged in as user1. When placed in a procedure in user1 schema, and executed from user1 I get invalid permissions error.
Any ideas?

3) Sometimes including schema name will lead to error.
Calling a package/procedure from same schema will only work when schema name not included.
Any ideas?

Thanks,
goo
Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #406862 is a reply to message #406861] Sat, 06 June 2009 16:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
1) When you have run the package already in the same session, and then run it again after it was re-compiled, Oracle warns the application that the package has changed. (and thus any possibly already initialized package variables are empty again, among other things)

2) roles don't work inside packages

3) Sounds weird. What error?

[Updated on: Sat, 06 June 2009 17:04]

Report message to a moderator

Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #406863 is a reply to message #406862] Sat, 06 June 2009 17:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oh, and ALWAYS include the exact error number and message when you get an error.

Also a complete copy and paste of the SQL*Plus session where it occured.
Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #406864 is a reply to message #406862] Sat, 06 June 2009 17:58 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
ThomasG wrote on Sat, 06 June 2009 16:56
1) When you have run the package already in the same session, and then run it again after it was re-compiled, Oracle warns the application that the package has changed. (and thus any possibly already initialized package variables are empty again, among other things)

So from an operational standpoint, does this mean other currently running sessions in a production system would get the same error?

Quote:
2) roles don't work inside packages

Wow. I will try to verify on DB.

3) Sounds weird. What error?[/quote]

logged in as services.
begin
p_journal(-1,'test', 'M','BEGIN','DWH','PCK_xyz','test');
end;

works correctly.

begin
services.p_journal(-1,'test', 'M','BEGIN','DWH','PCK_xyz','test');
end;

gives:
ORA-06550: line 2, column 14:
PLS-00302: component 'P_JOURNAL' must be declared
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored

Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #406865 is a reply to message #406861] Sat, 06 June 2009 18:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>logged in as services.
Provide proof.

>services.p_journal(-1,'test', 'M','BEGIN','DWH','PCK_xyz','test');
Provide proof that p_journal is a procedure actually owned by services schema.


SELECT OWNER, COUNT(LINE) FROM DBA_SOURCE WHERE NAME = 'P_JOURNAL' GROUP BY OWNER;

invoke SQL above & PASTE results back here.

Does P_JOURNAL contain any DML?
If so, does it contain COMMIT?
If so, will COMMIT always occur?

[Updated on: Sat, 06 June 2009 18:10]

Report message to a moderator

Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #406930 is a reply to message #406865] Sun, 07 June 2009 17:28 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
proof? seriously.

Simple case has same problem. Thought this was relatively common as happened at different company in previous job as well.

CREATE or replace PROCEDURE SERVICES.PROC1 AS
BEGIN
    NULL;
END; 

select user from dual;

begin
  proc1;
end;

begin
  SERVICES.proc1;
end;


Procedure created.

USER                          
------------------------------
SERVICES                      
1 row selected.
PL/SQL procedure successfully completed.
begin
  SERVICES.proc1;
end;
Error at line 12
ORA-06550: line 2, column 12:
PLS-00302: component 'PROC1' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Script Terminated on line 19.



[Updated on: Sun, 07 June 2009 17:35]

Report message to a moderator

Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #406932 is a reply to message #406861] Sun, 07 June 2009 18:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What you posted is an improvement & was helpful to some extent.

In the future please use sqlplus & show the whole complete session.
SQL> SET TERM ON ECHO on
SQL> @EXAMPLE.SQL

by doing so line numbers will be displayed.

In your last post, it is not clear which is the last line for procedure PROC1?

Please respond again following my suggestions & answering the question above.
Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #406957 is a reply to message #406930] Mon, 08 June 2009 00:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> CREATE or replace PROCEDURE scott.PROC1 AS
  2  BEGIN
  3      NULL;
  4  END;
  5  /

Procedure created.

SQL>
SQL> select user from dual;

USER
------------------------------
SCOTT

SQL>
SQL> begin
  2    proc1;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    scott.proc1;
  3  end;
  4  /

PL/SQL procedure successfully completed.

No problem here..


What tool did you use? This is definitely not sqlplus..

[Updated on: Mon, 08 June 2009 00:21]

Report message to a moderator

Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #407108 is a reply to message #406957] Mon, 08 June 2009 08:31 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
I used toad and pasted the output from the bottom at the end.

I do not normally use sqlplus.
Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #407109 is a reply to message #407108] Mon, 08 June 2009 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I do not normally use sqlplus.

You should, at least here then we can see and reproduce the same things.
quoting Ana (anacedent):
Those who live by the GUI, die by the GUI.


Regards
Michel
Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #407110 is a reply to message #406861] Mon, 08 June 2009 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't have a db object called services as well as a user do you?
Re: Invalid state when running first time after compile, error with schema name, permissns proc vs [message #407116 is a reply to message #406861] Mon, 08 June 2009 09:02 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
More specifically - do you have a package called SERVICES?
Previous Topic: dbms_sql.parse or execute immediate gives buffer too small error
Next Topic: Juliandate Query + last full week
Goto Forum:
  


Current Time: Sat Dec 10 03:28:56 CST 2016

Total time taken to generate the page: 0.11230 seconds