| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with first stored procedure
A copy of this was sent to Dieresis <dieresis_at_my-dejanews.com>
(if that email address didn't require changing)
On Fri, 05 Mar 1999 20:18:57 GMT, you wrote:
>I am new to PL/SQL and trying to compile my first PL/SQL stored
>procedure. Following is the code. The compilation errors I get are
>
>LINE/COL ERROR
>-------- ---------------------------------------------------------
>7/2 PL/SQL: SQL Statement ignored
>7/21 PLS-00201: identifier 'RPSCHEMA.V_USERS' must be declared
>
>
>Can anyone help? What am I doing wrong?
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant select on v_users to <OWNER>;
>
>/* Procedure to retrieve userid given login and partner */
>
>CREATE OR REPLACE PROCEDURE userid_get
>(
> login_in IN VARCHAR2 ,
> partner_in IN VARCHAR2 ,
> userid_out OUT INTEGER
>)
>AS BEGIN
> SELECT userid
> INTO userid_out
> FROM USERS
> WHERE login = login_in
> AND partner = partner_in;
>END userid_get;
>/
>
>---Dieresis [dieresis_at_my-dejanews.com]
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |