Re: Please help - stored procedure

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/04/25
Message-ID: <8e499j$275b$1@news4.isdnet.net>#1/1

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