0

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.

Sumaiya Qureshi
  • 31
  • 9
  • 2
    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 Jul 11 '20 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 Jul 11 '20 at 20:04
  • 1
    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 Jul 11 '20 at 22:38

1 Answers1

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

ismetguzelgun
  • 1,090
  • 8
  • 16