How to Execute Stored Procedure in Oracle SQL Developer with Parameters

How to Execute Stored Procedure in Oracle SQL Developer with Parameters The Oracle PL/SQL code block’s IN and OUT parameters / arguments can be used to execute the stored procedure in SQL Developer with parameters. The value from the stored procedure is sent to the PL/SQL code block through the IN argument. The IN parameter variable can be used as a local variable in the stored procedure. One or more stored procedure values are returned to an Oracle PL/SQL code block via the OUT parameter. The output of the stored procedure can be stored in the OUT variable. The OUT Variable value can be used outside of the PL/SQL block or procedure calling code block.

Oracle SQL Developer’s output is not printed in the console window. The console window should be enabled directly using the server output command. Otherwise, the output of the Oracle stored procedure will not show in the console window. To see the console window, first run the server output command. The IN and OUT parameters must be specified when creating the stored procedure in Oracle. Oracle allows you to declare an unlimited number of IN and OUT parameters in a stored procedure.



Enable Console output

By default, Oracle console output is disabled in SQL Developer. The server output should be used to enable the console output. Only when you’ve enabled the server output in SQL Developer can you see the stored procedure’s output. To enable the console output, run the following command in Oracle SQL Developer.

SET serveroutput on;

The following command is used to disable console output.

SET serveroutput off;


Create Stored Procedure with Parameter

The following example demonstrates how to use Oracle SQL Developer to create a stored procedure with parameters. IN and OUT parameters are the two types of parameters. The IN parameter is used to deliver values to the stored procedure from the caller PL/SQL code. The value from the stored procedure is sent to the calling PL/SQL code block through the OUT parameter.

A stored procedure with one IN parameter and one OUT parameter is shown in the example below.

Code

CREATE OR REPLACE Procedure myprocedure
   ( nameIn IN varchar2, msgOut OUT varchar2 )

IS
    msg varchar2(100);
begin
    msg := 'Hello '||nameIn;
    msgOut := msg;
    dbms_output.put_line('nameIn = ' || nameIn);
    dbms_output.put_line('msg = ' || msg);
    dbms_output.put_line('msgOut = ' || msgOut);
end;

Output

Procedure MYPROCEDURE compiled


Execute Stored Procedure in Oracle SQL Developer with Parameters

In the SQL Developer editor, you may run an Oracle stored procedure. The stored procedure might have or not have parameters. It is possible to run any stored procedure. The stored procedure’s output can be saved in a PL/SQL code block’s local variable and the value of the local variable printed in the console window.

Code

declare 
    msgvar varchar2(100);
begin
    myprocedure('test', msgvar);
    dbms_output.put_line('msgvar = ' || msgvar);
end;

Output

nameIn = test
msg = Hello test
msgOut = Hello test
msgvar = Hello test


PL/SQL procedure successfully completed.



Leave a Reply