Call for a function with a variable name?

Question

I have one package that has different procedures, and one main procedure through which I am calling other procedures.

Through the front end, I am passing the procedure name into main(). Is there any way by which the procedure can be called just writing the parameter name containing('Procedure Name that is need to be called')?

CREATE OR REPLACE PACKAGE BODY UPLOAD_PKG
IS

  --This procedure will populate LOG with messages
  PROCEDURE PRINT_LOG_PR IS
  BEGIN
    fnd_file.put_line(fnd_file.LOG,'ABC');
  END PRINT_LOG_PR;

  --This procedure will populate LOG with messages
  PROCEDURE PRINT_LOG1 IS
  BEGIN
    fnd_file.put_line(fnd_file.LOG, 'XYZ');
  END PRINT_LOG1;

  PROCEDURE Main( p_obj_type VARCHAR2
                 , errbuf VARCHAR2
                 , retcode VARCHAR2) IS
  BEGIN 
    -Is this possible for eg i have passed PRINT_LOG1 here and calling PRINT_LOG1
     p_obj_type ;-
  END main;
END UPLOAD_PKG

Answer 1

Use:

CREATE OR REPLACE PACKAGE BODY UPLOAD_PKG
IS

  --This procedure will populate LOG with messages
  PROCEDURE PRINT_LOG_PR IS
  BEGIN
    fnd_file.put_line(fnd_file.LOG,'ABC');
  END PRINT_LOG_PR;

  --This procedure will populate LOG with messages
  PROCEDURE PRINT_LOG1 IS
  BEGIN
    fnd_file.put_line(fnd_file.LOG, 'XYZ');
  END PRINT_LOG1;

  PROCEDURE MAIN( p_obj_type VARCHAR2
                 , errbuf VARCHAR2
                 , retcode VARCHAR2) IS
  BEGIN 

    CASE p_obj_type
      WHEN 'PRINT_LOG_PR' THEN UPLOAD_PKG.PRINT_LOG_PR;
      WHEN 'PRINT_LOG1' THEN UPLOAD_PKG.PRINT_LOG1;
    END CASE;

  END MAIN;

END UPLOAD_PKG

The CASE statement I used in the MAIN stored procedure is the PLSQL CASE statement, not the ANSI SQL CASE. You can tell because the PLSQL version needs END CASE to end the CASE statement.

Answer 2

Yes.

PROCEDURE Main( p_obj_type VARCHAR2
              , errbuf VARCHAR2
              , retcode VARCHAR2) IS
BEGIN 
  CASE p_obj_type
  WHEN 'PRINT_LOG_PR' THEN PRINT_LOG_PR;
  WHEN 'PRINT_LOG1' THEN PRINT_LOG1;
  END CASE;
END main;

Answer 3

As well as the CASE solution, it is possible to do this using dynamic PL/SQL.

PROCEDURE MAIN( p_obj_type VARCHAR2
             , errbuf VARCHAR2
             , retcode VARCHAR2) IS
 BEGIN 
    EXECUTE IMMEDIATE 'begin upload_pkg.'||p_obj_type|| '; end;';
END MAIN;

Simple parameters (Date, Varhar2, Number) can be passed IN OUT with the USING command.

The key question is whether it is advisable.

As with any dynamic language, it leaves scope for errors that will only be found at runtime, rather than compile time - i.e. passing in a value for p_obj_type that does not exist. You could mitigate this through constants or abstract data types.

Also each dynamic sql or pl/sql command incurs a small parsing overhead vs actual compiled code. This overhead is small, but becomes noticeable if performed inside a loop.

Lastly, the called code must have the same parameter signature.

Answer 4

Hi!

If you don't want to use dynamic pl/sql, then I would suggest to use constant variables in package header to choose between procedures (avoiding hard coding much as possible).

Main procedure call would be for example:

UPLOAD_PKG.MAIN(UPLOAD_PKG.C_PRINT_LOG_PR, v_errbuf, v_retcode);

And in the Main body you would use case like this:

CASE p_obj_type
  WHEN C_PRINT_LOG_PR THEN UPLOAD_PKG.PRINT_LOG_PR;
  WHEN C_PRINT_LOG1   THEN UPLOAD_PKG.PRINT_LOG1;
  ELSE RAISE SOME_ERROR;
END CASE;

In header you can define constant variables to contain whatever:

CREATE OR REPLACE PACKAGE UPLOAD_PKG
IS
  C_PRINT_LOG_PR CONSTANT VARCHAR2(22) := 'What ever';
  C_PRINT_LOG1   CONSTANT VARCHAR2(22) := 'What ever2';
  ...

But some cases client applications cannot refer global variables of packages, so you need to create function for every constant variable to return those. But this will go little bit too complicated, if you just could call those correct procedures...

But for the curiosity can you tell us why do you need to use package this way?