| How to Create a Stored Procedure [message #371596] |
Fri, 10 November 2000 13:33  |
Sussette Blasa
Messages: 2
Registered: November 2000
|
Junior Member |
|
|
Hi, I've been trying to find the correct way to create a stored procedure in Oracle. I just want it to output from this Select statement:
SELECT state_id, state_name
FROM state;
How would you call the procedure to get its output?
Also, if anyone can point me to a good Oracle book that has this info, I'd appreciate that, too.
thanks,
--Sussette
|
|
|
|
| Re: How to Create a Stored Procedure [message #371598 is a reply to message #371596] |
Sat, 11 November 2000 13:42  |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi!
The best book will be ORACLE PL/SQL.
Okay, to create a stored procedure. You should have your code look something like this.
CREATE OR REPLACE PROCEDURE procedure_name(parameters)
AS
--- Declarations (local variables)
BEGIN
--- PL/SQL statements
EXCEPTION
END ;
/
Example;
CREATE OR REPLACE PROCEDURE proc_get_state_info
AS
CURSOR c1
IS
SELECT state_id
,state_name
FROM state ;
v_state_id state.state_id%TYPE ;
v_state_name state.state_name%TYPE ;
BEGIN
DBMS_OUTPUT.PUT_LINE ('State ID' || ' ' || 'State Name') ;
DBMS_OUTPUT.PUT_LINE('-------------------------');
OPEN c1 ;
LOOP
FETCH c1 INTO v_state_id
,v_state_name ;
EXIT WHEN c1%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(v_state_id || ' ' || v_state_name) ;
END LOOP ;
CLOSE c1 ;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'-'||SQLERRM) ;
END proc_get_state_info ;
/
After Compiling...without errors
You have to either call this procedure from the SQL prompt or write a PL/SQL script...
SQL> set serveroutput on
SQL> exec proc_get_state_info
Hope this helps!
Good Luck!
Babu
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to Create a Stored Procedure [message #371664 is a reply to message #371596] |
Tue, 21 November 2000 03:37 |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi,
Whenever we create a stored procedure or function or package, it resides in all_source table and user_source table. You can give a query to see your procedure that is compiled.
SELECT text FROM all_source where
name = upper('your procedure name') ;
Try this one!
Good Luck!
Babu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
