Home » SQL & PL/SQL » SQL & PL/SQL » how to store procedures in the database
how to store procedures in the database [message #293772] Mon, 14 January 2008 21:34 Go to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

Hi,

I wrote the following procedure;

CREATE OR REPLACE PROCEDURE emp_id ( i_id IN integer)
AS BEGIN
UPDATE employee
set pay_type = 'S'
WHERE emp_id = i_emp_id;
END emp_id;

but when i type this in the sqlplus window the line doesnt end when i press enter after the colon. and the procedure is not stored in the databse. i tried to do this in the Squirrel sql gui too and it gave an error saying invalid sql statement when i tried to run it in the sql window. can any one point me in the right direction as to how to store a procedure in the database.

Thank you

Dinuka
Re: how to store procedures in the database [message #293773 is a reply to message #293772] Mon, 14 January 2008 21:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>WHERE emp_id = i_emp_id;
What is i_emp_id?
Re: how to store procedures in the database [message #293777 is a reply to message #293772] Mon, 14 January 2008 22:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
but when i type this in the sqlplus window the line doesnt end when i press enter after the colon. and the procedure is not stored in the databse.
As stated in the documentation, found eg. online on http://tahiti.oracle.com/:

SQL*Plus User's Guide and Reference
Chapter 4 SQL*Plus Basics
Running PL/SQL Blocks
Creating Stored Procedures

Quote:
When you are done typing your PL/SQL subprogram, enter a period (.) on a line by itself to terminate PL/SQL mode. To run the SQL command and create the stored procedure, you must enter RUN or slash (/). A semicolon (Wink will not execute these CREATE commands.

I have never seen the period(.) usage, always used just slash(/) for terminating and running the stored procedure code.
Re: how to store procedures in the database [message #293778 is a reply to message #293772] Mon, 14 January 2008 22:52 Go to previous messageGo to next message
dinuka
Messages: 30
Registered: January 2008
Location: Sri Lanka
Member

Thanks every one. It worked. Appreciate it alot.
Re: how to store procedures in the database [message #293783 is a reply to message #293777] Mon, 14 January 2008 23:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Period (.) just put the PL/SQL block/instruction in SQL*Plus instruction buffer when slash (/) does the same and executes the block.

Regards
Michel
Re: how to store procedures in the database [message #293789 is a reply to message #293772] Tue, 15 January 2008 00:25 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
@dinuka: thank you for feedback

@Michel: thank you for clarification; it seemed a little confusing to me (maybe as I am not natively speaking English); however it is stated more clearly (at least for me) in the previous paragraph (Running PL/SQL Blocks):
Quote:
You can also terminate and execute a PL/SQL subprogram by entering a slash (/) by itself on a new line.
Previous Topic: RPAD
Next Topic: Array/In Ref Curosr
Goto Forum:
  


Current Time: Fri Dec 09 03:48:16 CST 2016

Total time taken to generate the page: 0.11486 seconds