Home » SQL & PL/SQL » SQL & PL/SQL » error while executing the procedure
error while executing the procedure [message #205308] Fri, 24 November 2006 04:08 Go to next message
sound_palani
Messages: 4
Registered: November 2006
Location: Pune
Junior Member
Hi

I created a procedure and executed as below.
-------------------------
DECLARE
X NUMBER;

BEGIN
X := NULL;

test1.PS_XX ( X );
COMMIT;
END;
----------------------------
It gave a error that
ORA-06550: line 5, column 9:
PLS-00302: component 'PS_XX' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored


If i remove the schema name, its working fine....
I don't know why....
Re: error while executing the procedure [message #205312 is a reply to message #205308] Fri, 24 November 2006 04:20 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Cross check the following.
1. In which schema you are in?
2. In which schema the procedure has been created?
3. Have you granted the privilege execute to the working schema?

By
Vamsi
Re: error while executing the procedure [message #205316 is a reply to message #205312] Fri, 24 November 2006 04:29 Go to previous messageGo to next message
moparthy99
Messages: 13
Registered: July 2006
Junior Member
i guiess due INVOKER RIGHTS and DEFINER rights
if u want execute over the shema we have to be mention invoker rights(Authid current_user ) while define procedure /function/package.


If it is a same schema just call the procedure name with paramter

[Updated on: Fri, 24 November 2006 04:37]

Report message to a moderator

Re: error while executing the procedure [message #205320 is a reply to message #205316] Fri, 24 November 2006 04:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That last post is largely wrong.

All you need to execute a procedure in another schema is to have been granted the EXECUTE privilege on that procedure by it's owner (or anyone else who can grant that privilege).
Invoker / Definer rights can make a difference to how it runs, but are not relevant in considering if you are allowed to run it.

In reply to the OP.

Does the schema TEST1 contain a procedure called PS_XX?
If so, do you have execute Privs on that procedure?
Re: error while executing the procedure [message #205325 is a reply to message #205308] Fri, 24 November 2006 04:48 Go to previous messageGo to next message
sound_palani
Messages: 4
Registered: November 2006
Location: Pune
Junior Member
Hi

I am in the schema test1
and created the procedure in test1
and executing in test1

Re: error while executing the procedure [message #205331 is a reply to message #205325] Fri, 24 November 2006 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I assume by 'In the schema test1' you mean that you are connected to the database as the user Test1.

Run this and show us the results:
SELECT * 
FROM   user_objects 
WHERE  object_name = 'PS_XX';
Re: error while executing the procedure [message #205335 is a reply to message #205308] Fri, 24 November 2006 05:00 Go to previous messageGo to next message
sound_palani
Messages: 4
Registered: November 2006
Location: Pune
Junior Member
Frineds...
I am in the schema 'test1'
and the procedure is created in 'test1'
and i am running it in 'test1' only...

the problem is if i mension that "test1.ps_xx", it is giving error.

If i remove the schema name, it is working. the procedure 'ps_xx is only available in this schema.

I just want to know that why it is behaving like this...

JRowbottom,

here it is....
'PS_XX', 134182, 'PROCEDURE', TO_DATE('11/24/2006 13:22:11', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/24/2006 14:54:27', 'MM/DD/YYYY HH24:MI:SS'), '2006-11-24:14:54:27', 'VALID', 'N', 'N', 'N')

Re: error while executing the procedure [message #205342 is a reply to message #205335] Fri, 24 November 2006 05:33 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you follow these steps:
C:\>SQLPLUS test1/password@database

SQL> execute ps_xx(null);

SQL> execute test1.ps_xx(null);

Please, post output of the entire session (including SQLPLUS command - omit password and database when posting your answer, but I'd like to see username).
Re: error while executing the procedure [message #205343 is a reply to message #205335] Fri, 24 November 2006 05:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't believe it is working like that, I'm afraid.
Let's see if we can work out what is actually going on.

Can you run this and cut and paste the query and results into your reply (use CODE tags to help us read it if you could)
select sys_context('USER','current_schema') from dual
union all 
select user from dual;
Re: error while executing the procedure [message #205344 is a reply to message #205343] Fri, 24 November 2006 05:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've got it - you have a package called TEST1, and when you refer to TEST1.PS_XX, the order of priority is to first look for package.procedure, and then schema.procedure.

Run this:
SELECT object_name,object_type 
FROM    user_objects 
WHERE  object_name = 'TEST1'
Re: error while executing the procedure [message #205354 is a reply to message #205344] Fri, 24 November 2006 05:46 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Bingo! ./fa/1581/0/ hopefully ...
Re: error while executing the procedure [message #205360 is a reply to message #205308] Fri, 24 November 2006 06:06 Go to previous messageGo to next message
sound_palani
Messages: 4
Registered: November 2006
Location: Pune
Junior Member
YES JRowbottom

You are correct. I had the table "test1"

Thanks a lot JRowbottom

thank you friends...
Re: error while executing the procedure [message #205383 is a reply to message #205360] Fri, 24 November 2006 07:11 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A TABLE 'test1'? Should it not be a PACKAGE?
Previous Topic: HELP ON validating CLOB and converting to XML
Next Topic: Ref cursor
Goto Forum:
  


Current Time: Fri Dec 09 23:01:56 CST 2016

Total time taken to generate the page: 0.09788 seconds