Passing SQL to an Oracle bind variable

I need to execute someth开发者_如何学运维ing like:

select  
[very big SQL]  
where phone_number in(:SQL2)  

Is it possible to use bind variable for SQL2?

I want to save the execution plan of the major SQL.

Thanks.

Create a temporary table and save SQL2's results there prior to executing SQL1:

CREATE GLOBAL TEMPORARY TABLE mytemptable (id INT NOT NULL)

CREATE OUTLINE ol_sql1
ON
SELECT  *
FROM    sql1
WHERE   id IN
        (
        SELECT  id
        FROM    mytemptable
        )

INSERT
INTO    mytemptable
SELECT  *
FROM    sql2

SELECT  *
FROM    sql1
WHERE   id IN
        (
        SELECT  id
        FROM    mytemptable
        )

If this query gets executed a lot of times, I wouldn't use a temporary table for it.

There is a 'trick' to bind an inlist, which Tom Kyte describes on his blog:

http://tkyte.blogspot.com/2006/06/varying-in-lists.html

I would bet on that being much more efficient. It should be easy to prove with a SQL Trace.

Further to Quassanoi's point. It sounds like you may not be familiar with temporary tables. This is a good introduction.

You only create the table once. Then within a given session you first:

1. populate the temporary table
2. execute your query pulling from the temporary table
3. rollback.

There's no risk of conflicting/overlapping with another session's data.