CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Michael Sheely
Michael Sheely

Reputation: 1031

Oracle Run Procedure with one in parameter and multiple out parameter

I just started working with Oracle and I'm using SQL Developer to test before I start adding stuff to my application, but I'm running into issues because Oracle behaves differently than every other database engine I've worked with.

I created a dummy table:

CREATE TABLE "ROOT"."EMPLOYEES" 
(   "ID" NUMBER NOT NULL ENABLE, 
"FIRSTNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
"LASTNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
"EMAIL" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
 CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("ID")
)

And Then I created a Procedure:

 create or replace PROCEDURE get_employee
(
  emp_id IN NUMBER,
  m_FirstName OUT Varchar2,
  m_LastName OUT Varchar2,
  m_Email OUT Varchar2
)
AS
BEGIN
SELECT 
   FirstName
   ,LastName
   ,Email
INTO
   m_FirstName,
   m_LastName,
   m_Email
FROM EMPLOYEES
WHERE
  ID = emp_id;
END get_employee;

The problem is I get compilation errors when I try to run the procedure:

Declare x VARCHAR2(30);
y VARCHAR2(30);
z VARCHAR2(40);

Begin
exec GET_EMPLOYEE(1, :x, :y, :z);
SYS.DBMS_OUTPUT.PUT_LINE(x);
End;

I get this error:

ORA-06550: line 8, column 4: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with an identifier a double-quoted 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: PL/SQL procedure successfully completed.

I'm really not sure how to change my call to the procedure to make it work with SQL Developer. Any help would be greatly appreciated.

Upvotes: 4

Views: 27876

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Or an another version of stub execution.

var x varchar2(30);
var y varchar2(30);
var z varchar2(40);
exec GET_EMPLOYEE(1, :x, :y, :z);

Upvotes: 2

kevinskio
kevinskio

Reputation: 4551

This is the correct syntax

Declare 
x VARCHAR2(30);
y VARCHAR2(30);
z VARCHAR2(40);

Begin
GET_EMPLOYEE(1, x, y, z);
DBMS_OUTPUT.PUT_LINE(x);
End;

Upvotes: 7

Related Questions