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 the Author

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

Comments

Leave a Reply

avatar
newest oldest most voted
Giang Nguyen
Guest
Giang Nguyen

Thank you.

evanliao
Guest
evanliao

Thanks

rakshith
Guest
rakshith

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
Guest
denver

Add “Set ServerOutput on” before executing the proc

mounika
Guest
mounika

Thanks for valuable post.

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

Lakshmu Naidu
Guest
Lakshmu Naidu

Thank you. It is working

Nikash
Guest
Nikash

Thanks..

Diego Bernal
Guest
Diego Bernal
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
Guest
GOWRI SANKAR

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

jigs
Guest
jigs

Easy learning

samantha
Guest
samantha

no work’s :(

PL/SQL procedure successfully completed.

vijay
Guest
vijay

A good post on procedures