How to pass bind variable as IN OUT parameter in PLSQL procedure

Question

I want to use 1st parameter of my procedure EMP_ID as IN OUT parameter. Originally it's IN parameter and this procedure is working fine, but as the last line concern

htp.p('Inserted for Employee-id '||EMP_ID);

I want to use this line in anonymous block and most importantly it should be a bind variable because I am creating the REST API in which user will only enter values and it will be taken as bind variable in oracle Apex and the below procedure is working fine with respect of IN parameter.

create or replace procedure att_time_ins (EMP_ID in  varchar2, ORG_ID in number,V_TIME_STATUS in number) is
  BEGIN
INSERT INTO TIME_ATTENDANCE_POOL
(EMPLOYEE_ID, ATTENDANCE_DATE,TIME_HOURS,TIME_MINUTES,TIME_STATUS,LOCATION_ID,ORG_ID,PREPARED_ON )  
VALUES  
(EMP_ID, to_date(sysdate,'DD/MM/YYYY'),to_char(sysdate,'HH24') ,to_char(sysdate,'MI'),V_TIME_STATUS,null,ORG_ID,
to_date(sysdate,'DD/MM/YYYY') );   
COMMIT;

time_management.create_attendance_sheet(v_org_id => ORG_ID,
                                          v_employee_id => EMP_ID,
                                          target_date =>  to_date(sysdate,'DD/MM/YYYY'));
 htp.p('Inserted for Employee-id '||EMP_ID);



end att_time_ins;

I am calling my procedure in this way

begin
 
  att_time_ins(:employee_id,:org_id,:time_status);
 
end;

Please help me to modify this stuff according to IN OUT Parameter i.e Employee_id should be IN OUT parameter. There is no proper documentation regarding passing bind variables as in out prameter in PLSQL Block.

Why do you think you need to do anything differently in the call, once you've changed the procedure from EMP_ID in varchar2 to EMP_ID in out varchar2? I don't understand why you need it to be in/out as you aren't changing the value within the procedure. It's unclear what actual problem you are trying to solve. — Alex Poole
– Alex Poole, Commented Jul 11, 2020 at 12:51
Within a procedure ,Oracle or User written, all formal parameters are always generated as bound variables by the compiler. The formal parameters for your procedure includes EMP_ID. So it is a bound variable. But beyond that your statement does NOT use it as an out. In a procedure an in out parameter may be used as either a left or right operand in assignment, ie you can change it's value; your desired statement does not change the value so it not not an OUT. Note: a variable used IN OUT( or OUT) ) in not the same as output. Your procedure is working - don't change anything. — Belayer
– Belayer, Commented Jul 11, 2020 at 20:04
But do change to_date(sysdate,'DD/MM/YYYY'). You should not call to_date with something that is already a date. When to you that you are implicitly converting sysdate to a string using NLS settings. Someone else calling your procedure from a session with different settings will get different behaviour, and quite likely an error. If the intention is to 'remove' the time then use trunc(sysdate) instead, which by default truncates the time to midnight. (You can't have a date without a time, of course.) — Alex Poole
– Alex Poole, Commented Jul 11, 2020 at 22:38

ismetguzelgun · Accepted Answer · 2020-07-11 14:38:17Z

1

Let us say you have a procedure named PR_PROC, you can use VARIABLE statement for passing IN OUT or OUT kind of variables.

CREATE OR REPLACE PROCEDURE PR_PROC (EMP_NAME   IN     VARCHAR2,
                                     EMP_ID     IN OUT VARCHAR2)
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE (EMP_NAME||EMP_ID);

END;
   
VARIABLE KURSOR VARCHAR2
BEGIN
 :KURSOR:='4';
 PR_PROC('SENIOR',:KURSOR);
END;

Note: If you are using TOAD Editor you can press F5 to make it work.

Oracle VARIABLE

edited Jul 11, 2020 at 14:38

answered Jul 11, 2020 at 14:31

ismetguzelgun

1,1058 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass bind variable as IN OUT parameter in PLSQL procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related