| Unable to execute private procedure at SQL prompt [message #628826] |
Fri, 28 November 2014 15:22  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
I have created a private procedure in a package and am trying to call it at SQL * PLUS (prompt) through the following statement:
exec BWAKPLNS.p_sendmail('stanjore@abcxxx.ca','stanjore@abcxxx.ca','stanjore@abcxxx.ca', NULL, NULL);
I am constantly getting an error :
ORA-06550: line 1, column 16:
PLS-00302: component 'P_SENDMAIL' must be declared
Question is can a private procedure be referenced or called in the SQL prompt and if not, how does one call it?
(I have never worked on private procedures; the private procedure has compiled successfully yesteraday)
I looked into Oracle manual and this is what it says:
For example, a package might contain ten procedures. However, the package can be defined so that only three procedures are public and therefore available for execution by a user of the package; the remainder of the procedures are private and can only be accessed by the procedures within the package.
The first part says that those three procedures can be accessed by a user who has the privilegs via package name.procedure name ; however not sure what he means by "the remainder of the
procedures are private and can only be accessed by the procedures within the package."
Can any one help me?
Thanks,
Sandeep
|
|
|
|
|
|
| Re: Unable to execute private procedure at SQL prompt [message #628829 is a reply to message #628828] |
Fri, 28 November 2014 15:37  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
BWAKPLNS is a package. so I am calling packagename.privateprocedure name at SQL*PLUS.
I kind of see and make sense about being issued GRANT privilege. I am new in this firm and will have to ascertain if I have the necessary rights to execute objects or not.
How come it does not say "insufficient privileges" if I don't have the privilege as opposed to a different error message?
|
|
|
|
|
|
|
|
|
|
| Re: Unable to execute private procedure at SQL prompt [message #628833 is a reply to message #628831] |
Fri, 28 November 2014 15:55 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
So which means that a private procedure cannot be called outside of the package (if it has not been declared in the package specs). After reading private and public procedures from Oracle documentation(https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch14.htm), my idea too leaned towards the same. However it pays to get a second opinion form veterans like you.
So how can we execute a private procedure at SQL * Plus? there is either no way or the only way is to define the procedure in the package specs? is that right?
Which version of Windows 8 running the DB system. Well, here it is:
SELECT * FROM V$VERSION
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
[Updated on: Fri, 28 November 2014 15:57] Report message to a moderator |
|
|
|
|
|
| Re: Unable to execute private procedure at SQL prompt [message #628835 is a reply to message #628834] |
Fri, 28 November 2014 16:14 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
BlackSwan:
I ran the SELECT * FROM V$VERSION and pasted the results what I saw.
I just asked a simple question (can a private procedure be executed outside of the package; in essence you have replied to it but I am trying to find out how to execute a private procedure and unfortunately cannot). You are confusing yourself by digging questions that could be out of scope for this issue
|
|
|
|
|
|
| Re: Unable to execute private procedure at SQL prompt [message #628837 is a reply to message #628836] |
Fri, 28 November 2014 16:29 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
No. I cannot prove you wrong as the proof of pudding lies in it's eating 
I am unable to get the private procedure (which has been declared as "forward declarations" and defined in the package body; hence it's a private procedure) execute at SQL * PLUS besides Oracle documentation confirming the same. Lastly you are a veteran on this forum with tons of subject matter expertise. In this case your answer is right.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
