Re: Please help - stored procedure

Hi Michel,

Thanks for the prompt response with sample code.

We are using Oracle 8.0.5 and guess I can not use "execute immediate". I tried the second method using DBMS package. It works if the 'owner' of the stored proc runs its. If a different user tries, I get the following message.

SQL> execute sysadm.change_password('winter')

begin sysadm.change_password('winter'); end;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYSADM.EXECUTESQL", line 15
ORA-06512: at "SYSADM.CHANGE_PASSWORD", line 5
ORA-06512: at line 1

Please note that I have given 'execute' permission on executeSQL, change_password, DBMS_sql, DBMS_utility, DBMS_output to the user (and public)
who is trying to change the password. Again, the idea is to keep this stored proc in one of the
repository schema and then give permission to others to use it. What else is required?

Any help is appreciated.

CGS
tcgs_at_hotmail.com

Michel Cadot <micadot_at_netcourrier.com> wrote in message news:8e499j$275b$1_at_news4.isdnet.net...
> You can't do that in a procedure, you have to use
> dynamic SQL. That either 'execute immediate'
> statement (if you are in Oracle 8i) or dbms_sql
> package.
>
> For example:
> create procedure change_password (new_psw varchar2) is
> username varchar2(30;
> begin
> select user into username from dual;
> execute immediate 'alter user '||username||' identified by '||new_psw;
> end;
> /
>
> or with Oracle7, you create a procedure executeSQL:
> create or replace procedure ExecuteSQL (codeSql varchar2) is
> cid integer;
> dummy integer;
> begin
> cid := dbms_sql.open_cursor;
> dbms_sql.parse (cid, codeSql, dbms_sql.native);
> dummy := dbms_sql.execute (cid);
> dbms_sql.close_cursor (cid);
> exception
> when others then
> dbms_output.put_line(dbms_utility.format_error_stack);
> if dbms_sql.is_open (cid) then
> dbms_sql.close_cursor (cid);
> end if;
> raise;
> end ExecuteSQL;
> /
>
> and then:
>
> create procedure change_password (new_psw varchar2) is
> username varchar2(30;
> begin
> select user into username from dual;
> executeSQL ('alter user '||username||' identified by '||new_psw);
> end;
> /
>
> --
> Have a nice day
> Michel
>
>
> CGS <tcgs_at_hotmail.com> a écrit dans le message :
 _shN4.1147$Ip.36081_at_cac1.rdr.news.psi.ca...
> > Hi Experts,
> >
> > I am new to Oracle & trying to create a following stored procedure.
> >
> > create procedure change_password (userid in varchar2, passwd in
 varchar2)
> > as begin
> > alter user userid identified by passwd;
> > end;
> >
> > The idea is to create a stored proc and allow users to change their
 password
> > from a
> > front-end application. I get the following message when I try to save
 the
> > procedure.
> >
> > PLS-00103: Encountered the symbol "ALTER" when expecting one of the
> > following:
> > begin declare exit for goto if loop mod null pragma raise
> > return select update while <an identifier>
> > <a double-quoted delimited-identifier> <a bind variable> <<
> > close current delete fetch lock insert open rollback
> > savepoint set sql commit <a single-quoted SQL string>
> >
> > Can anyone tell me what is wrong with this code? If there is a better
 way to
> > do it, please show me
> >
> > Thanks
> >
> > CGS
> > tcgs_at_hotmail.com
> >
> >
> >
> >
>
>
Received on Tue Apr 25 2000 - 00:00:00 CDT