Re: Basic SQL and package question

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 Received on Wed Dec 19 2007 - 16:06:51 CST