Solved

Procedures in Oracle

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now