REPOST: Re: WHY can't I call a function from within a formula column???
Date: Sun, 30 Dec 2001 05:06:04 GMT
Message-ID: <0$--$$-$-%%-%$$__$_at_news.noc.cabal.int>
John wrote:
> Hello,
> I am trying to modify pl/sql code behind a formula column (reports 2.5.7.17)
> and get this message when I try to compile:
> Function 'GETJOB' may not be used in SQL
>
> The code for the formula column:
> function CF_toolsFormula return Number is
>
> v_var1 VARCHAR2(10);
> begin
> BEGIN
> SELECT a.field1,
> INTO v_var1
> FROM table1 a,
> table2 b
> WHERE a.id = b.id
> AND a.job = :job
> AND rsi_attr.getjob(a.id, a.f1) = 1 ;
> END;
> END;
>
> The only line I added was the last line of WHERE clause (call to the
> function). Can you really not reference a function in a package from a
> formula column? I believe I did this with version 6. Is there a workaround
> to accomplish this in version 2?
Make sure you have the pragma statement in the package spec:
package rsi_attr is ...
function getjob ...
...
pragma restrict_references (getjob, WNDS, WNPS[, RNDS][, RNPS]);
end;
This assures Oracle that the function will not modify the database. Of course, you can only do this if the function really does *not* modify the database....
Tomm
- WAS CANCELLED BY =======: Path: news.sol.net!spool1-nwblwi.newsops.execpc.com!newsfeeds.sol.net!news-out.visi.com!hermes.visi.com!newsfeed.direct.ca!look.ca!newsfeed1.earthlink.net!newsfeed.earthlink.net!uunet!lax.uu.net!news.navix.net!u.n.a.c.4.n.c.3.l.l.e.r From: Tomm Carr <TommCatt_at_computer.org> Newsgroups: news.admin.censorship,alt.test,comp.databases.oracle.tools Subject: cmsg cancel <3C2EA0BB.DABB6C57_at_computer.org> Control: cancel <3C2EA0BB.DABB6C57_at_computer.org> Date: Wed, 2 Jan 2002 02:56:02 GMT Organization: Navix Internet Subscribers Lines: 2 Message-ID: <cancel.3C2EA0BB.DABB6C57_at_computer.org> NNTP-Posting-Host: 166.102.15.34 X-Trace: iac5.navix.net 1009948128 29203 166.102.15.34 (2 Jan 2002 05:08:48 GMT) X-Complaints-To: abuse_at_navix.net NNTP-Posting-Date: 2 Jan 2002 05:08:48 GMT X-No-Archive: yes Comment: Dude, where's my NewsAgent?
autocancel Received on Sun Dec 30 2001 - 06:06:04 CET