• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

Procedures in Oracle

Can a procedure be called if its name is contained in a variable name?

I have a configuration table named "MY_CONFIG" having columns as source, request, succes_procedure.

Data will be like this :
Source                     Request                      Success_proc
S1                            R1                               Update_S1
S2                            R2                               Update_S2
S3                            R3                                null

I have another table where based on source and request and some other manipulations I have to update a status.
Table is like this
UniqueID                   Source                   Request               Status
1                               S1                          R1                        Success
2                               S2                          R2                        Faled.

If status is success and if there is an ebtry in success_proc column of table my_config, I have to do some more updates in tables.

I want to check in proceudre that if success procedure is present or not for a source and request like

SELECT success_proc into var_proc
FROM my_config
where source = S1 and request = R1;

Is there any way by which I can call proc like this :
var_proc and pass the required parameters to it
var_proc(P1,P2);



0
priyanka_kothari
Asked:
priyanka_kothari
1 Solution
 
flow01Commented:
You will have to use dynamic calling.
I wil add a function i use to check for the existence of a function / procedure. *1
You wil have to build your intended calls as a string and execute the "check_existence" with that string.
In my example "wrong number or types of arguments" is accepted : for your intended goal you probably have to return FALSE there.
*1 There are also some oracle-views and procedures that reflect the existing procedures/functions and their arguments but  I preferred this method.

CREATE OR REPLACE FUNCTION check_existence(
  p_proc_or_function IN VARCHAR2 )
RETURN BOOLEAN IS
BEGIN
  DECLARE
    v_sqlerrm varchar2(2000);
    FUNCTION STRIP_ERROR (error_in varchar2)
    RETURN VARCHAR2
    IS
      error_out varchar2(4000);
    BEGIN
      error_out := error_in;
      error_out := replace(error_out,'ORA-06550: line 1, column 7:' ||
  chr(10),null);
      error_out := replace(error_out,
         'PLS-00201: identifier ''MISTAKE$'' must be declared' || chr(10),
         null);
      error_out := replace(error_out,'PL/SQL: Statement ignored',null);
      RETURN error_out;
    END;
  BEGIN
 
  BEGIN
    -- by adding  MISTAKE$ a pl/sql-block is made that has on purpose a mistake in it
    -- to prevent executing while checking for existence
    EXECUTE IMMEDIATE 'begin MISTAKE$;' || p_proc_or_function || ';end;' ;
  EXCEPTION WHEN OTHERS THEN
    v_sqlerrm := sqlerrm;
    -- filter the intented MISTAKE from the error message
    v_sqlerrm := strip_error(v_sqlerrm);
  END;
  -- de identifier
  -- PLS-00201 identifier 'string' must be declared
  IF instr(v_sqlerrm,'PLS-00221') > 0 THEN
    -- PLS-00221 'string' is not a procedure or is undefined
    -- check if not checking a procedrue but a function or package variable
          v_sqlerrm := NULL;
    BEGIN
    EXECUTE IMMEDIATE 'begin MISTAKE$;declare chr varchar2(4000);begin chr :=' ||
        p_proc_or_function || ';end;end;' ;
    EXCEPTION WHEN OTHERS THEN
      v_sqlerrm := sqlerrm;
      v_sqlerrm := strip_error(v_sqlerrm);
      v_sqlerrm := replace(v_sqlerrm,
        'PLS-00201: identifier ''MISTAKE$'' must be declared',null);
    END;
  END IF;
  IF   v_sqlerrm IS NULL THEN
        RETURN TRUE;
  ELSIF    instr(v_sqlerrm,'must be declared') > 0 then
        RETURN FALSE;
  ELSIF instr(v_sqlerrm,'PLS-00306') > 0 then
    -- PLS-00306 wrong number or types of arguments in call to 'string'
    -- does exist
        RETURN TRUE;
  ELSE
       RAISE_APPLICATION_ERROR(-20000,'check_existence ' || v_sqlerrm ||chr(10)
            || '>>' || p_proc_or_function || '<<');
 
  END IF;
  END;
END check_existence;
/

0
 
awking00Commented:
How will you know what the required parameters are?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now