PL/SQL Tip of the Month
Date: 1999/10/06
Message-ID: <7tg8gn$9u0$1_at_nnrp1.deja.com>#1/1
Join the "PL/SQL Pipeline" - a free internet community for Oracle developers worldwide. You will find monthly tips, free white papers and software utilities, discussion forums and more. The PL/SQL Pipeline is hosted by best-selling PL/SQL author Steven Feuerstein and sponsored by RevealNet. http://www.revealnet.com/pipeline.htm
Querying from an Index-By Table
So you've defined an index-by table (formerly known as PL/SQL tables), and you want to be able use SQL to query the results. Sadly, index-by tables are 100% programmatic PL/SQL constructs and cannot be referenced natively from within SQL. There is, however, a workaround, one that was suggested by several people on the PL/SQL Pipeline (most notably and with content for this tip drawn from Susanna Batson, at smbatson_at_technologist.com):
Instead of referencing the index-by table contents directly, you can call a function that returns the value in a row.
Here is one such example:
SELECT Col1, Col2
FROM My_DB_Table
WHERE Col3 IN (
SELECT ibtable_row (ColId)
FROM Place_holder_table);
where Place_holder_table is a table that simply provides a context in
which to reference the ibtable_row function, which would look something
like this..
CREATE OR REPLACE FUNCTION ibtable_row (colid IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
RETURN mypkg.ibtable (colid);
END;
Where mypkg is the package that contains the declaration of ibtable. If
the function is defined in the same package, you do not have to qualify
the reference to the index-by table.
The place_holder_table might be dual, so that it only returns one value/row. You could also correlate the row number passed to ibtable_row with information in that table.
NEWS FROM REVEALNET
* Quest Software Integrates RevealNet's Knowledge Bases
with SQL Navigator 3.1.D.1
* PCWEEK LABS gives PL/Generator Rave Reviews
http://www.revealnet.com
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Oct 06 1999 - 00:00:00 CEST