Solved

Procedures in Oracle

Posted on 2009-04-06
2
421 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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 take different types of Oracle backups using RMAN.

738 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