Solved

Procedures in Oracle

Posted on 2009-04-06
2
409 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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

13 Experts available now in Live!

Get 1:1 Help Now