Pass parameter from one procedure to another PLSQL-CodePudding

I need to pass parameter "p_date" of procedure "Proc1" to procedure "Proc2" . It may be necessary to use a global variable or some other way. Thanks for the help.

CREATE OR REPLACE PACKAGE PKG_TEST  IS 
 
      PROCEDURE proc1 (p_date DATE );
      PROCEDURE proc2;
 
END;
/
 
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS 
 
PROCEDURE proc1 AS
  BEGIN
  INSERT INTO tbl_ins
  SELECT * FROM tbl WHERE dt = p_date;
  END proc1;
  
PROCEDURE proc2 AS
  BEGIN
    DELETE tbl_ins WHERE dt = p_date;
    
   END proc2;
   
 END PKG_TEST;

CodePudding user response：

Redefine your package so that PROC_2 also takes a DATE parameter:

CREATE OR REPLACE PACKAGE PKG_TEST  IS 
 
      PROCEDURE proc1 (p_date DATE);
      PROCEDURE proc2 (p_date DATE);
 
END;
/
 
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS 
 
PROCEDURE proc1(p_date DATE) AS
BEGIN
  INSERT INTO tbl_ins
    SELECT * FROM tbl WHERE dt = p_date;
END proc1;
  
PROCEDURE proc2(p_date DATE) AS
BEGIN
  DELETE tbl_ins WHERE dt = p_date;
END proc2;
   
END PKG_TEST;

CodePudding user response：

It's not good idea to make packages "stateful" without a real reason.

In your case, if you are going to call proc2 from proc1 (otherwise, I don't know why you may want to hard code it to the same value as your input parameter of proc1), you can do like this:

CREATE OR REPLACE PACKAGE PKG_TEST  IS 
    PROCEDURE proc1 (p_date DATE ); 
END;
/
 
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS 
 
  PROCEDURE proc1 IS
    PROCEDURE proc2 IS
    BEGIN -- proc2 is a local procedure of proc1
      DELETE tbl_ins WHERE dt = p_date;
    END proc2;
  BEGIN
    INSERT INTO tbl_ins
    SELECT * FROM tbl WHERE dt = p_date;
    PROC2();
  END proc1;
   
END PKG_TEST;
/