?
Solved

Procedures in Oracle

Posted on 2009-04-06
2
Medium Priority
?
423 Views
Last Modified: 2013-12-18
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
Comment
Question by:priyanka_kothari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 2000 total points
ID: 24075064
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
 
LVL 32

Expert Comment

by:awking00
ID: 24077378
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question