Oracle Run Procedure with one in parameter and multiple out parameter

oracleplsqlstored-procedures

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.

Best Answer

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;
Related Topic