How to pass values to stored procedure in oracle?

Question

I have written a stored procedure where I'm trying to insert and update the value. I have concern when I'm performing insertion(It contains 5 parameters and different datatype) has different values and in time of updation(It contains 6 parameters and different datatype) has different values.I want to using single stored procedure for both cases(Insert/Update).How can I manage the parameter?

The procedure

 create or replace procedure PS_File_Update_Insert (
          Input_val in varchar2
          , p1 in varchar2
          , p2 in varchar2
          , p3 in varchar2
          , p4 in varchar2
          , p5 in varchar2
          )       
is
BEGIN        
if Input_val = 'Insert' then        
   insert into PS_FILE (SRC_FILE_ID
                     ,TRUSTED_PARTY_ID
                     ,FILE_NM,FILE_PROC_DT
                     ,FILE_STATUS) 
   values (SRC_FILE_ID_SEQ.nextval
                  ,1234
                  ,'zxcv'
                  ,SYSDATE
                  ,'NEW');
 elsif Input_val = 'update' then        
   update PS_FILE 
      set FILE_STATUS='STAGING'
      ,   REC_CNT='abcd'
      , ERR_CNT='123'
      , DUPE_CNT=NULL 
      where SRC_FILE_ID='1234';        
  end if;
end;

Answer 1

Put baldly this is a bad design. It's confusing for other developers and will lead to bugs in the application. Write two procedures, one for insertion and one for updates.

If you want to link them, to show that these two procedures are related functionality ( and why wouldn't you want to do that?) put them in a package. That's what packages are for. Find out more.

Answer 2

I think you have several ways to performe what you need:

1. The best way to resolve this issue consist to use a package, adding these two procedure inside it and using them, as you wish for insert or update, inside another procedure to connect them (of course the final procedure should be part of the package).
2. You can use DEFAULT VALUES (p1 VARCHAR2 DEFAULT NULL....) within the parameters of that single procedure you want to build. So with these default values you could perform an insert or update in the same procedure, but I'm still like the first option and I'm afraid will be confusing.

Review this link

Hope this help!!

Answer 3

I think best design is to have two overloaded procedure with one is having 5 parameter for insert and same name procedure with 6 parameter for update. for calling program it will look like same name but it will act differently.

for insert say..

    create or replace procedure PS_File_Update_Insert (
              Input_val in varchar2
              , p1 in varchar2
              , p2 in varchar2
              , p3 in varchar2
              , p4 in varchar2
                 ) 
-- insert code 

for update

    create or replace procedure PS_File_Update_Insert (
              Input_val in varchar2
              , p1 in varchar2
              , p2 in varchar2
              , p3 in varchar2
              , p4 in varchar2
              , p5 in varchar2
              ) 
-- update code.