CODEWITHSUNDEEP
sqloracle-databasestored-proceduresoracle-sqldeveloper
Mandy Boo
Mandy Boo

Reputation: 29

How to execute a stored procedure in SQL developer

I'm trying to execute a stored procedure in Oracle SQL developer. I have compiled the following successfully and it is linked to two separate tables containing data, which is connected through a foreign key.

create or replace procedure practice_run
(
    name out varchar2,
    emp_id out varchar2
)
AS
BEGIN
    SELECT emp_name, employeeID INTO name, emp_id FROM employee JOIN department ON employee.employeeID = department.employeeID WHERE department_name='research'; 
END practice_run;

So what I want this stored producer to output is the first name and last name of all employees working in the department 'research'

I have searched online for this and I found something like the below but it did not output any data for me.

DBMS_SQL.RETURN_RESULT(practice_run);

Upvotes: 0

Views: 11510

Answers (2)

Jacob
Jacob

Reputation: 14731

The procedure is executed in the following manner

VAR ename VARCHAR2(100);
VAR emp_id VARCHAR2(100);

BEGIN
   practice_run (ename, emp_id);
END;

PRINT ename;
PRINT emp_id;

You could leverage with the usage of DBMS_SQL.RETURN_RESULT, modify the procedure to have SYS_REFCURSOR 

CREATE TABLE emp
(
   emp_id   VARCHAR2 (12),
   ename    VARCHAR2 (12)
);


INSERT INTO emp (emp_id, ename)
     VALUES ('1234', 'James');

COMMIT;

CREATE OR REPLACE PROCEDURE practice_run (p_emp_id IN VARCHAR2)
AS
   c1   SYS_REFCURSOR;
BEGIN
   OPEN c1 FOR
      SELECT emp_id, ename
        FROM emp
       WHERE emp_id = p_emp_id;

   DBMS_SQL.return_result (c1);
END practice_run;

Execute the procedure by

BEGIN
   practice_run ('1234');
END;

Upvotes: 1

thatjeffsmith
thatjeffsmith

Reputation: 22412

Take a look at DBMS_SQL.RETURN_RESULT (Docs)

In T-SQL it's quite common to just run queries.

But in PL/SQL, it's more about interacting with the result of a query. If you want to just run a query and simply print the results, you'd historically have an OUT bound parameter of type SYS_REFCURSOR, and your program would pick those up.

In Database 12c, you can now say inside your code, you want to return the results, without changing the procedure call itself - no OUT parameter is needed.

Here's an example.

create or replace procedure sql_return_results as
 v_cursor SYS_REFCURSOR;
begin
 open v_cursor for
   select *
   from employees
   fetch first 15 rows only;
   dbms_sql.return_result(v_cursor); --this is the KEY line
end sql_return_results;
/

Now I"m going to execute this, and see what comes back.

enter image description here

Upvotes: 2

Related Questions