Stored Procedure Creation and Execution Questions [message #354619] |
Mon, 20 October 2008 10:34  |
jerry8989
Messages: 5 Registered: October 2008
|
Junior Member |
|
|
I'm very new to oracle and i'm having problems creating and executing a stored procedure. This is my code:
----------------------------------------------------------------
create or replace
PROCEDURE UpdateStateCity(pStateName IN VARCHAR2)
AS
BEGIN
select UpdateStateCity.pStateName from dual
END UpdateStateCity;
----------------------------------------------------------------
After running this I get this message
Warning: execution completed with warning
PROCEDURE UpdateStateCity(pStateName Compiled.
Where can I go to find out the warning?
Next I run this line to see if it works
execute UpdateStateCity('NewYORK');
it returns:
Error starting at line 1 in command:
execute UpdateStateCity('NewYORK');
Error report:
ORA-06550: line 1, column 7:
PLS-00905: object JWARRA.UPDATESTATECITY is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
any help would be very apprceiated.
Thank You
|
|
|
|
Re: Stored Procedure Creation and Execution Questions [message #354627 is a reply to message #354619] |
Mon, 20 October 2008 10:41   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@jerry8989,
I am not sure what client you are using. If you are compiling your SQL*Plus try something like this:
For instance after running your code in my SQL*Plus I get the following errors:
SQL> create or replace
2 PROCEDURE UpdateStateCity(pStateName IN VARCHAR2)
3 AS
4 BEGIN
5 select UpdateStateCity.pStateName from dual
6 END UpdateStateCity;
7 /
Warning: Procedure created with compilation errors.
SQL> show err;
Errors for PROCEDURE UPDATESTATECITY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PL/SQL: SQL Statement ignored
5/6 PL/SQL: ORA-00933: SQL command not properly ended
5/21 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe
Regards,
Jo
|
|
|
|
Re: Stored Procedure Creation and Execution Questions [message #354636 is a reply to message #354631] |
Mon, 20 October 2008 11:23   |
jerry8989
Messages: 5 Registered: October 2008
|
Junior Member |
|
|
This is the error I get
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4 PL/SQL: SQL Statement ignored
7/9 PL/SQL: ORA-00933: SQL command not properly ended
7/25 PLS-00103: Encountered the symbol "/" when expecting one of the
following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
|
|
|
Re: Stored Procedure Creation and Execution Questions [message #354638 is a reply to message #354619] |
Mon, 20 October 2008 11:31   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If that's the entirity of your procedure you've posted and not a cut down version then I can see at least three syntax errors.
1. You can't reference procedures in select statements.
2. If you're doing a select in a procedure you really need to tell it where to put the result.
3. You're missing a semicolon.
|
|
|
Re: Stored Procedure Creation and Execution Questions [message #354639 is a reply to message #354638] |
Mon, 20 October 2008 11:37   |
jerry8989
Messages: 5 Registered: October 2008
|
Junior Member |
|
|
CookieMonster,
Thanks for your help.
I removed the SP name from in front of the parameter name. I added a semi-colon to the end of the select.
I'm lost on your second point
---------------------------------------------------------------
If you're doing a select in a procedure you really need to tell it where to put the result.
---------------------------------------------------------------
Isn't selecting out the Parameter telling it to return it?
I'm a big SQL Server guy and I'm starting to move into Oracle which is a bit different. LOL
Thanks for your help and any input you can give me.
|
|
|
|
|
|