Re: Oracle 9i VPD Predicate Function

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 4 Jun 2005 11:41:18 +0000 (UTC)
Message-ID: <d7s40u$sed$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"Chris McMahon" <NOSPAM_cochrane68_at_hotmail.com> wrote in message news:o7goe.33772$Fv.29970_at_lakeread01...

>
> I'm new to VPDs, and I'm not sure I totally understand what you said. Are
> you saying that my simple SQL statement:
>
> SELECT * FROM test;
>
> is first parsed (1st row in log table added) and then executed (2nd row in
> log table added)?

Correct.
Try running with sql_trace set to true, and check the trace file:

Do two tests
Test 1:

    select colX from test where rownum =1;
    select colX from test where rownum =1;
    select colX from test where rownum =1;
    select colX from test where rownum =1;
    select colX from test where rownum =1;
    select colX from test where rownum =1;
    select colX from test where rownum =1;
    select colX from test where rownum =1;
    select colX from test where rownum =1;
    select colX from test where rownum =1;

Test 2
declare

    m_v varchar2(32); -- or whatever your colX is begin

    for i in 1..10 loop

        select colX
        into m_v
        from test
        where rownum = 1
        ;

    end loop;
end;
.
/

In the first test, you should get 20 calls to the VPD function, in the second you should get 11 (one on the parse, and 10 on the executes) because pl/sql will hold the cursor open.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005

Received on Sat Jun 04 2005 - 06:41:18 CDT