Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    26

    Unanswered: Declaring a Function

    Hi,

    I have a script to initialize or, if initialized, upgrade a database. The following block is a simplified part of that script. If myfunc (a function) doesn't exist, I don't call it. The PROBLEM is that even if I don't call it because it doesn't exists an error appears telling me that myfunc is not declared.
    My question is: how can I just declare a function in this case, or what should I do to run in a conditional way something that's in a script.

    DECLARE

    fCount NUMBER;

    BEGIN
    --first check if myfunc exists
    SELECT COUNT(OBJECT_NAME) INTO fCount FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION' AND OBJECT_NAME = 'MYFUNC';
    IF fCount >= 1 THEN
    IF myfunc <= 5 THEN --PROBLEM: this if fails because it doesn't find myfunc declared.

    -- we do something here

    END IF;
    END IF;

    END;

    Thanks,

    Federico

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    first, do the following code.

    DECLARE

    fCount NUMBER;

    BEGIN
    --first check if myfunc exists
    SELECT COUNT(OBJECT_NAME) INTO fCount FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION' AND OBJECT_NAME = 'MYFUNC';
    IF fCount >= 1 THEN
    -- we do something here
    else
    -- Do what you need to do if it doesn't exist
    END IF;

    END;


    Second
    You may NOT specify an object in a function that doesn't exist. It will NOT compile. You can get around this by using dynamic sql. See the following link.

    http://www.oracle.com/pls/tahiti/tah...?section=48788
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •