Problem Trying to Create Procedure

[marksmithy69]

I am a newbie, so this may be a simple question. I have a sql script called
p_chp_proc.sql, which creates a procedure called p_chp:

CREATE OR REPLACE PROCEDURE p_chp( encoded IN VARCHAR )
IS
len int := length(encoded);
i int;
new_password varchar(30);
BEGIN
-- decode password
for i IN 1..len loop
new_password := substr(encoded, i*2, 1);
end loop;

-- change password
EXECUTE IMMEDIATE 'ALTER USER db_audit IDENTIFIED BY ' || new_password;

END p_chp;


When I try and run the script I get the error "SP2-0734: unknown command beginning "p_chp_proc..." - rest of line ignored." Does anyone know what I am missing?
Thanks a lot.

 

[carp]

How are you trying to run the script?
It should look something like this:

SQL> @p_chp_proc.sql

It sounds like you may have omitted the @.

 

[marksmithy69]

I know that is the common issue when you receive this type of erro, but I get it regardless to whether I use the @ or not:

SQL> @p_chp_proc.sql
SP2-0734: unknown command beginning "p_chp_proc..." - rest of line ignored.
SQL> p_chp_proc.sql
SP2-0734: unknown command beginning "p_chp_proc..." - rest of line ignored.

 

[carp]

Well, you've got me!
Your script runs fine on my database, and I don't see anything on Metalink that looks applicable.
About the only other thing I might suggest is that you take out the blank lines and try it again.

 

[carp]

One other thing to try is to build the procedure interactively instead of via a script. It might throw the exception at a specific point, and that might provide a clue.

 

[Beilstwh]

When you run the script and you get en error, type

show errors

and it will show you what the error is.

Bill
Oracle DBA/Developer
New York State, USA

 

[BJCooperIT]

Is your SQL*Plus session is pointed to the same directory where p_chp_proc.sql resides?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[marksmithy69]

Following up with Beilstwh, if I run 'show errors', it says no errors:

SQL> @p_chp_proc.sql
SP2-0734: unknown command beginning "p_chp_proc..." - rest of line ignored.
SQL> show errors
No errors.

 

[hoinz]

Let me ask a silly question:
Could it be that your filename p_chp_proc.sql is in the first line of your file p_chp_proc.sql ?

 

[marksmithy69]

No...you can view the script in my original entry.

 

[SantaMufasa]

Marksmith,

Do you have a slash ("/") following your last line of PL/SQL code?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[marksmithy69]

There is no slash. I tried with and without a slash, with the same result. The weird thing is, it executes fine on other databases, so the script is fine. We just have a handfull of databases where we get this error.

 

[SantaMufasa]

Marksmithy,

Unfortunately, part of our problems as troubleshooters is that we are perhaps relying upon our faulty imaginations instead of looking at what is really happening.

Therefore, could you please do a literal copy-and-paste, directly from your screen, of everything you are trying to do, down through the error message?

We're not trying to make you jump through hoops...we're trying to help resolve your issue...I promise.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]