Re: select inside function

From: William Robertson <williamr2019_at_googlemail.com>
Date: Sat, 20 Oct 2007 01:11:02 -0700
Message-ID: <1192867862.312656.233880@v29g2000prd.googlegroups.com>

On Oct 17, 2:55 pm, abdoul85 <abdou..._at_gmail.com> wrote:
> create or replace function checkVersion return boolean is
> a varchar(1000);
> Res boolean;
> begin
> select version into a from v$instance;
> if (a like '10%') then
> Res := TRUE;
> else
> begin
> dbms_output.put_line('Skip...');
> Res := FALSE;
> end;
> end if;
> return(Res);
> end checkVersion;
>
> It works fine on oracle 8 database, but on 9 and 10 version:
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 4/3 PL/SQL: Statement ignored
> 4/7 PLS-00905: obiekt QGUARADM.CHECKVERSION is incorrect

After creating a PL/SQL object it helps to enter

SHOW ERRORS or you can come back later and

SHOW ERRORS FUNCTION checkversion

(Or the equivalent commands if not using SQL*Plus.) That would have given you some clues:

SQL> show errors
Errors for FUNCTION CHECKVERSION:

LINE/COL ERROR

---

---

5/3        PL/SQL: SQL Statement ignored
5/30       PL/SQL: ORA-00942: table or view does not exist

You might try switching to v$version:

create or replace function checkVersion return boolean is

    a varchar(1000);
    Res boolean;
begin

    select banner into a from v$version where banner like 'Oracle Database %';

    if a like '10%' then

        Res := TRUE;
    else

        dbms_output.put_line('Skip...');
        Res := FALSE;

    end if;
    return(Res);
end checkVersion;

Or perhaps even PRODUCT_COMPONENT_VERSION:

create or replace function checkVersion return boolean is

    a varchar(1000);
    Res boolean;
begin

    select version into a from product_component_version     where product like 'PL/SQL%';
    if a like '10.%' then

        Res := TRUE;
    else

        dbms_output.put_line('Skip...');
        Res := FALSE;

    end if;
    return Res;
end checkVersion;

or even:

create or replace function checkVersion return boolean is

    v_version product_component_version.version%TYPE; begin

    select version into v_version from product_component_version     where product like 'PL/SQL%';
    return v_version like '10.*';
end checkVersion;

To be useful in the future you might also consider adding a version parameter to check for.

btw you don't need brackets for IF or RETURN expressions. I removed a stray BEGIN END pair that wasn't doing anything either. Received on Sat Oct 20 2007 - 03:11:02 CDT