Re: Basic SQL and package question

<fitzjarrell_at_cox.net> schreef in bericht news:ec811124-01c9-4f3d-b3b7-ac261fe274e6_at_d21g2000prf.googlegroups.com...
> Comments embedded.
> On Dec 19, 3:23 pm, Guy <guh..._at_yahoo.com> wrote:
>> I have package procedure which returns 1 if a code is valid. Si I try:
>> SELECT
>> A,
>> B,
>> PACKAGE_1.PROC_1(A.CODE) AS CODE_PERMISSION
>> FROM
>> TABLE
>> WHERE
>> CODE_PERMISSION = 1
>>
>> This returns and error message: CODE_PERMISSION invalid identifier.
>
> And it should, as you cannot use a column alias from the SELECT list
> in the WHERE clause of the same SELECT statement.
>
>>
>> So I have to resort to:
>> SELECT
>> A,
>> B
>> FROM
>> TABLE
>> WHERE
>> PACKAGE_1.PROC_1(A.CODE) = 1
>>
>> But I have been told that calling a procedure in the "where" clause
>> was invalidating any index on this table, which is uge.
>
> And a function-based index could cure that. If you'd written a
> function instead of a procedure.
>
>> Any solution
>> to this ? Thanks.
>
> Presuming you change your procedure to a function one way of using
> your select-list alias is:
>
> with perm as (
> select a, b, package_1.func_1(code) code_permission
> from table
> )
> select a, b, code_permission
> from perm
> where code_permission = 1;
>
> However, why are you worried about an index when the 'column' in the
> WHERE clause doesn't exist in the table and, as it is at the moment,
> can't be indexed anyway because you wrote a procedure, not a
> function? You, at the moment, have nothing in the way of indexes to
> ignore. Had you written a function instead of a procedure you could
> have possibly created a function-based index, and then using the
> function in the WHERE clause would be of no concern as an index (your
> function-based index) would be used:
>
> create index table_fbi_code on
> table(package_1.func_1(code));
>
> select a, b, package_1.func_1(code) code_permission
> from table
> where package_1.func_1(code) = 1;
>
> And, voila, you have an indexed access path.
>
> You need to read up on this:
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref928
>
>
>
> David Fitzjarrell

I doubt if the procedure/function on hand will be deterministic, since permission is checked in a pl/sql global table:
>>> I need to call the package procedure because a PL/SQL global table
>>> contains the list of effective permissions to the connected users
so I don't think a fbi will work. If the values in this table change for whatever reason, the fbi won't (or will it?)

Shakespeare Received on Thu Dec 20 2007 - 02:06:27 CST