CODEWITHSUNDEEP
oracle-databaseplsql
sreekem bose
sreekem bose

Reputation: 471

referencing a variable from another procedure in plsql

I have written a package : 

CREATE OR REPLACE
    PACKAGE BODY NEW_HIRE_PKG
    AS
    PROCEDURE load_emp(
        errbuf OUT VARCHAR2,
        retcode OUT VARCHAR2 )
    AS
      CURSOR cur_person_info
      IS
        SELECT * FROM table_abc;
      CURSOR cur_person_adr
      IS
        SELECT * FROM table_adr;
      l_person_id      NUMBER;
      l_emp_num        NUMBER;
      lv_add_type      VARCHAR2(100);
      lv_address_line1 VARCHAR2(100);
    BEGIN
      FOR person_info_rec IN cur_address_info
      LOOP
        hr_employee_api.create_employee ( p_validate => FALSE,
        --INPUT Parameter
        P_HIRE_DATE =>person_info_rec.DATE_START,
        -- output
        p_employee_number => lc_employee_number, p_person_id => ln_person_id );
      END LOOP ;
    END;

PROCEDURE load_add(
            errbuf OUT VARCHAR2,
            retcode OUT VARCHAR2 )
as 
ln_person_id number;

        BEGIN
          FOR address_info_rec IN cur_address_info
          LOOP
            BEGIN
              hr_person_address_api.create_person_address 
              (p_validate => FALSE,
              p_effective_date => TRUNC(SYSDATE),
              p_person_id=> ln_person_id,   
              --output
              p_address_type => lv_add_type,
              p_address_line1 => lv_address_line1);

           end; 
         end loop;
        end;
      end;

Now in the procedure the load_add there is a variable ln_person_id which should be the person ids generated in the procedure load_emp. I want to pass it in this procedure one by one. Can i do it by making ln_person_id an object ?

Upvotes: 0

Views: 171

Answers (1)

Christian Palmer
Christian Palmer

Reputation: 1302

Judging by your code you have two concurrent programs - one that calls load_emp() and one that calls load_add(). If that's really the structure you want then the two calls will run in separate sessions and there's nothing you can do to pass a variable from one to the other. The best you could do would be to hold all the person_id values from load_emp in a custom table. The data could then later be consumed by load_add().

However I would restructure your package. Why not call load_add() from within the LOOP in load_emp() ?

Upvotes: 2

Related Questions