How Sql Procedure return mulltiple values with IN OUT :PLS-00103 error

Question

i was using oracle 10g i want to make a sql procedure that return mulltiple values i am using IN OUT in my procedure

    create or replace procedure proc_mull_val
(
e_id in chr_emgt_employee.employee_code%type,
head_id out chr_emgt_employee.employee_code%type,
zone_id out chr_emgt_employee.employee_code%type
)
is
begin

SELECT   (SELECT   employee_code
            FROM   chr_emgt_employee
           WHERE   EMPLOYEE_ID = emgt1.SUPERVISOR_EMP_ID)
            , (SELECT   employee_code
                                FROM   chr_emgt_employee
                               WHERE   employee_id = EMGT2.SUPERVISOR_EMP_ID)

  INTO   head_id,zone_id
  FROM   chr_emgt_employee emgt1, chr_emgt_employee emgt2
 WHERE   EMGT2.employee_id = emgt1.SUPERVISOR_EMP_ID
         AND emgt1.employee_code = e_id;

end;

procedure is successfuly created but when i execute this

declare
head_id  chr_emgt_employee.employee_code%type;
zone_id  chr_emgt_employee.employee_code%type;
begin
execute proc_mull_val(151018,head_id,zone_id);
end;

i face this error when i execute it

PLS-00103: Encountered the symbol "PROC_MULL_VAL" when expecting one of the following: := . ( @ % ; immediate The symbol ":=" was substituted for "PROC_MULL_VAL" to continue.

if someone has better suggestion about this query or my question then please inform me

Remove the `execute` in call to your procedure. Besides, your procedure seems to insert many values into variables; if you need to do so, you'll better think of `BULK COLLECT` and data structures — Aleksej, Jan 14 '16 at 08:57
yes my procedure insert a subquery in variable. but i am sorry i am not getting you **BULK COLLECT** what is that — usman, Jan 14 '16 at 09:03
So, if one of your queries can return more than one value, you can not use a single variable. Try something like:`DECLARE TYPE t_tab IS TABLE OF NUMBER; tab t_tab; BEGIN SELECT LEVEL BULK COLLECT INTO tab FROM DUAL CONNECT BY LEVEL < 3; for i in tab.first .. tab.last loop DBMS_OUTPUT.put_line('Size: ' || tab(i)); end loop; END; /` — Aleksej, Jan 14 '16 at 09:11
Do you mean multiple values as in a single scalar value in each of the two OUT parameters, so you get one value of each from a call to the procedure; or you want to retrieve multiple head_id and/or multiple zone_id values from a single call? — Alex Poole, Jan 14 '16 at 09:23

score 1 · Accepted Answer · answered Jan 14 '16 at 09:33

1

i think your procedure has no issue just your way of calling is change

   DECLARE
   head_id   varchar2 (20);
   zone_id   varchar2 (20);
BEGIN
   proc_mull_val ('151018', head_id, zone_id);

   DBMS_OUTPUT.put_line (head_id || '..... ' || zone_id);
END;

just try this and tell me if not solve change the data type and try again

answered Jan 14 '16 at 09:33

Hafiz Usman aftab

300
2
5
15

1

thanks you are very close the issue is with data type i am big fool sorry and thanks for your patient – usman Jan 14 '16 at 09:48

score 0 · Answer 2 · answered Jan 14 '16 at 09:05

0

Execute is an SQL*PLUS comand thus you cannot use it inside a pl/sql anonymous block.

answered Jan 14 '16 at 09:05

Emil Moise

373
1
8

score 0 · Answer 3 · edited May 23 '17 at 10:28

0

First create collection object in your database. and use that collection object as out parameter. Refer Passing an array of data as an input parameter to an Oracle procedure

edited May 23 '17 at 10:28

Community

1
1

answered Jan 14 '16 at 09:10

Jaseer

52
9

How Sql Procedure return mulltiple values with IN OUT :PLS-00103 error

3 Answers3