Main Tutorials

Oracle Stored Procedures Hello World Examples

List of quick examples to create stored procedures (IN, OUT, IN OUT and Cursor parameter) in Oracle database. PL/SQL code is self-explanatory.

1. Hello World

A stored procedure to print out a “Hello World” via DBMS_OUTPUT.


CREATE OR REPLACE PROCEDURE procPrintHelloWorld
IS
BEGIN
  
  DBMS_OUTPUT.PUT_LINE('Hello World!');

END;
/

Run it


exec procPrintHelloWorld;

Output


Hello World!

2. Hello World + IN Parameter

A stored procedure to accept a single parameter and print out the “Hello World IN parameter” + parameter value via DBMS_OUTPUT.


CREATE OR REPLACE PROCEDURE procOneINParameter(param1 IN VARCHAR2)
IS
BEGIN
  
  DBMS_OUTPUT.PUT_LINE('Hello World IN parameter ' || param1);

END;
/

Run it


exec procOneINParameter('mkyong');

Output


Hello World IN parameter mkyong

3. Hello World + OUT Parameter

A stored procedure to output/assign the “Hello World OUT parameter” value to OUT parameter.


CREATE OR REPLACE PROCEDURE procOneOUTParameter(outParam1 OUT VARCHAR2)
IS
BEGIN
  
  outParam1 := 'Hello World OUT parameter';

END;
/

Run it


DECLARE
  outParam1 VARCHAR2(100);
BEGIN
  procOneOUTParameter(outParam1);
  DBMS_OUTPUT.PUT_LINE(outParam1);
END;
/

Output


Hello World OUT parameter

4. Hello World + INOUT Parameter

A stored procedure to accept a INOUT parameter (genericParam), construct the output message and assign back to the same parameter name(genericParam) again.


CREATE OR REPLACE PROCEDURE procOneINOUTParameter(genericParam IN OUT VARCHAR2)
IS
BEGIN
  
  genericParam := 'Hello World INOUT parameter ' || genericParam;

END;
/

Run it


DECLARE
  genericParam VARCHAR2(100) := 'mkyong';
BEGIN
  procOneINOUTParameter(genericParam);
  DBMS_OUTPUT.PUT_LINE(genericParam);
END;
/

Output


Hello World INOUT parameter mkyong

5. Hello World + Cursor

A stored procedure, return a ref cursor and accept a IN parameter.


CREATE OR REPLACE PROCEDURE procCursorExample(
cursorParam OUT SYS_REFCURSOR, userNameParam IN VARCHAR2)
IS
BEGIN

  OPEN cursorParam FOR
  SELECT * FROM DBUSER WHERE USERNAME = userNameParam;

END;
/

Run it


DECLARE 
  dbUserCursor SYS_REFCURSOR;
  dbUserTable DBUSER%ROWTYPE;
BEGIN
  
  procCursorExample(dbUserCursor,'mkyong');
  
  LOOP
    
	FETCH dbUserCursor INTO dbUserTable;
	
    EXIT WHEN dbUserCursor%NOTFOUND;
    dbms_output.put_line(dbUserTable.user_id);
  
  END LOOP;

  CLOSE dbUserCursor;
  
END;
/

Output


List of the user_id which matched username='mkyong'

Reference

  1. http://www.oradev.com/ref_cursor.jsp
  2. http://psoug.org/reference/procedures.html
  3. http://www.devshed.com/c/a/Oracle/Working-with-REF-CURSOR-in-PL-SQL/
  4. http://www.codeproject.com/KB/database/Oracle_RefCursor_ADO_C__.aspx

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
15 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Nikash
11 years ago

Thanks..

evanliao
9 years ago

Thanks

rakshith
10 years ago

in sql cmd prompt after creating the basic procedure, when we run it with “exec procedure_name” getting output as “PL/SQL procedure successfully completed” not displaying the message given inside.

denver
9 years ago
Reply to  rakshith

Add “Set ServerOutput on” before executing the proc

anuj pandey
4 years ago
Reply to  denver

we should use below syntax:-
set serveroutput on;

mounika
10 years ago

Thanks for valuable post.

Could you please tell me which command should we use to execute example no-3(hello world +Out parameter)

Joseph
3 years ago
Reply to  mounika

It is described above already, but if you are not able to understand well check this post Run Oracle Procedure with IN and OUT parameter.

Lakshmu Naidu
10 years ago

Thank you. It is working

Diego Bernal
11 years ago

bloque anónimo terminado……………………….

CREATE OR REPLACE PROCEDURE SP_Fill_Municipios(p_Id_Departamento in MUNICIPIOS.ID_DEPARTAMENTO%TYPE,
C_DBUSER OUT SYS_REFCURSOR)
IS
BEGIN
OPEN C_DBUSER FOR
SELECT ID_MUNICIPIO,ID_DEPARTAMENTO,NOMBRE_MUNICIPIO
FROM MUNICIPIOS WHERE ID_DEPARTAMENTO = p_Id_Departamento;

END SP_Fill_Municipios;

DECLARE
C_DBUSER SYS_REFCURSOR;
temp_dbuser MUNICIPIOS%ROWTYPE;
BEGIN

SP_Fill_Municipios(1,C_DBUSER);

LOOP

–fetch cursor ‘c_dbuser’ into dbuser table type ‘temp_dbuser’
FETCH C_DBUSER INTO temp_dbuser;

–exit if no more records
EXIT WHEN C_DBUSER%NOTFOUND;

–print the matched username
dbms_output.put_line(temp_dbuser.ID_DEPARTAMENTO);

END LOOP;

CLOSE C_DBUSER;

END;

GOWRI SANKAR
8 years ago
Reply to  Diego Bernal

You should execute ‘set serveroutput on’ command so that DBMS_OUTPUT.PUT_LINE can actually print the output.

jigs
11 years ago

Easy learning

samantha
11 years ago

no work’s 🙁

PL/SQL procedure successfully completed.

vijay
11 years ago

A good post on procedures

Huy Hoang
5 years ago

Thank you

Giang Nguyen
7 years ago

Thank you.