Re: Cannot call function in query
Date: 1997/06/10
Message-ID: <01bc759c$13934da0$4621ea9e_at_UKP01436.logica.co.uk>#1/1
Both functions in queries and inline views started with 7.1.
However, you will need to set the compatability parameter in init.ora to
get it to work.
EG Database version 7.1.6.x.x
AFAIK, Compatability usually defaults to the lowest number in the release
(for my example 7.1.0.0.0), and can be set to something totally different
as an aid to database migration and upgrade (eg 6.0.32.0.0, or 7.1.6.0.0
when running server 7.2 or 7.3).
If it still doesnt work after that (and previous comments about standalone
functions, restrict references, and all parameters to the function being in
parameters of standard database types, rather than PL/SQL types) then you
have a bug, call Oracle!
Thought also occurs to me that you can only gaurantee that these will work
in SQL*Plus; Tools such as Dev2k etc often refuse functions in queries and
inline views unless they are real recent versions, since the tools
pre-parse the SQL, and dont understand the new features (hide it in a
database view).
compatability=7.1.6.x.x
--
Miles Thomas
Logica UK Ltd
thomasm "at" logica "dot" com
The above are personal opinions, and are
not necessarily the opinions of my employer.
Thomas Kyte <tkyte_at_us.oracle.com> wrote in article
<339d43d2.17079989_at_newshost>...
> On Mon, 09 Jun 1997 09:44:26 GMT, jurij.modic_at_mf.sigov.mail.si (Jurij
Modic)
> wrote:
>
> >On Mon, 09 Jun 1997 11:09:03 +0800, Steve Corbett
> ><stevec_at_fcs.wa.gov.au> wrote:
> >
> >>Jurij Modic wrote:
> >>>
> >>> This functionality is available from version 7.2 on. In earlier
> >>> releases you are not able to call PL/SQL functions from within SQL.
> >>
> >>we are using v7.1.6.2 and we can call pl/sql functions.
> >>[pl/sql v 2.1.6, SqlPlus v 3.1.3.7]
> >>
> >>steve.
> >
> >Yes, but your functions are most probably standalone stored functions.
> >Here we are talking about functions included in packages where pragma
> >RESTRICT_REFERENCES is neaded to allow calls from SQL.
> >
> >AFAIK this pragma is available since 7.2. Correct me if I am wrong.
> >
>
> Started with 7.1 of the database with the pragmas as well:
>
> Connected to:
> Oracle7 Server Release 7.1.6.2.0 - Production Release
> With the distributed, replication and parallel query options
> PL/SQL Release 2.1.6.2.0 - Production
>
> SQL> set echo on
> SQL> _at_test
> SQL> create or replace package pragma_test
> 2 as
> 3 function foo return varchar2;
> 4 pragma restrict_references( foo, wnds, rnds, wnps, rnps );
> 5 end;
> 6 /
>
> Package created.
>
> SQL> create or replace package body pragma_test
> 2 as
> 3 function foo return varchar2
> 4 is
> 5 begin
> 6 return 'Hello World';
> 7 end;
> 8 end;
> 9 /
>
> Package body created.
>
> SQL> select pragma_test.foo from dual;
>
> FOO
> -------------------------------------------
> Hello World
>
>
> the documentation for the pragmas with the 7.1 database is in the 7.1
> documentation addendum, its not in the pl/sql or application developers
guide
> for that release.
>
> By 7.2 it found it's way into the pl/sql manuals and application
developers
> guide.
>
> >Regards,
> >
> >============================================================
> >Jurij Modic Republic of Slovenia
> >tel: +386 61 178 55 14 Ministry of Finance
> >fax: +386 61 21 45 84 Zupanciceva 3
> >e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000
> >============================================================
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Bethesda MD
>
> http://govt.us.oracle.com/ -- downloadable utilities
>
>
----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>
Received on Tue Jun 10 1997 - 00:00:00 CEST