Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure Creation and Execution Questions (Oracle SQL Developer 1.5.1)
Stored Procedure Creation and Execution Questions [message #354619] Mon, 20 October 2008 10:34 Go to next message
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 #354625 is a reply to message #354619] Mon, 20 October 2008 10:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SQL> SHOW ERRORS
Re: Stored Procedure Creation and Execution Questions [message #354627 is a reply to message #354619] Mon, 20 October 2008 10:41 Go to previous messageGo to next message
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:
show err;


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 #354631 is a reply to message #354627] Mon, 20 October 2008 10:47 Go to previous messageGo to next message
jerry8989
Messages: 5
Registered: October 2008
Junior Member
Thank You for your replies.

I'm using the SQL Developer tool to connect to the DB and using a query window to run the code.

I'm going to try and run it and see what the errors are.
Re: Stored Procedure Creation and Execution Questions [message #354636 is a reply to message #354631] Mon, 20 October 2008 11:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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.
Re: Stored Procedure Creation and Execution Questions [message #354642 is a reply to message #354636] Mon, 20 October 2008 11:45 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@jerry8989,

You will have to change your query.
select UpdateStateCity.pStateName from dual

to
select UpdateStateCity.pStateName from dual;


But still you will get an error unless you provide a variable to store the result of your query.
Please go through PL/SQL Reference Guide to learn more about PL/SQL.

Hope this helps.

Regards,
Jo

[**Added: Too late.... Sad ]

[Updated on: Mon, 20 October 2008 11:46]

Report message to a moderator

Re: Stored Procedure Creation and Execution Questions [message #354659 is a reply to message #354619] Mon, 20 October 2008 12:10 Go to previous messageGo to next message
jerry8989
Messages: 5
Registered: October 2008
Junior Member
Joicejohn,
Thank You for all your help. I will look at the guide.
Re: Stored Procedure Creation and Execution Questions [message #354661 is a reply to message #354639] Mon, 20 October 2008 12:29 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
jerry8989 wrote on Mon, 20 October 2008 17:37
Isn't selecting out the Parameter telling it to return it?


No. To return a value from a procedure you need to assign it to an OUT or IN OUT parameter, or alternatively make it a function and use the RETURN clause.
Previous Topic: List Partion Index and MV expected refresh datetime
Next Topic: Converting LONG to CLOB - ORACLE NOT CONECTED
Goto Forum:
  


Current Time: Sun Dec 11 02:09:50 CST 2016

Total time taken to generate the page: 0.11787 seconds