dynamic SQL

[NJMrBig]

Hi all,

I am using Oracle 8i and writing a stored procedure to select data from multiple
tables given an array of IDs. I would like to use PL/SQL's dynamic SQL capability
in order to build the select statement in a stored procedure and execute
it putting the results in a REF CURSOR.

Anyone have any idea on how to do this? My current code is below (I have
changed the names to protect the innocent):

....
TYPE somecur IS REF CURSOR;
....
PROCEDURE retrieveDisplayData(p_ContractData IN VARCHAR2,
p_errorcode OUT NUMBER,
p_cursor OUT somecur)
IS
....
BEGIN
vSQL := 'SELECT * FROM contracts' ||
'WHERE client = ' || vNumber || ' AND number = ' || vNumber || ' AND year
= ' || vYear || 'ORDER BY ID';

/* What I want to do something like this */
OPEN p_cursor FOR ... EXECUTE dynamic SQL in vSQL

....
END;

[Boris Milrud]

Hi NJMrBig,

Here is your code with some modifications:

TYPE somecur IS REF CURSOR;
....
PROCEDURE retrieveDisplayData(
p_ContractData IN VARCHAR2,
p_errorcode OUT NUMBER,
p_cursor OUT somecur)
IS
....
BEGIN

vSQL := 'SELECT * FROM contracts ' ||
'WHERE client = :bClient AND ' ||
'number = :bNumber AND ' ||
'year = :bYear ' ||
'ORDER BY ID';

OPEN p_cursor FOR vSQL USING vClient, vNumber, vYear;
....

END;

Hope it helps.
Boris Milrud.


"NJMrBig" <NJMrBig@aol.com> wrote:
>
>Hi all,
>
>I am using Oracle 8i and writing a stored procedure to select data from
multiple
>tables given an array of IDs. I would like to use PL/SQL's dynamic SQL capability
>in order to build the select statement in a stored procedure and execute
>it putting the results in a REF CURSOR.
>
>Anyone have any idea on how to do this? My current code is below (I have
>changed the names to protect the innocent):
>
>....
>TYPE somecur IS REF CURSOR;
>....
>PROCEDURE retrieveDisplayData(p_ContractData IN VARCHAR2,
>p_errorcode OUT NUMBER,
>p_cursor OUT somecur)
>IS
>....
>BEGIN
>vSQL := 'SELECT * FROM contracts' ||
>'WHERE client = ' || vNumber || ' AND number = ' || vNumber || ' AND year
>= ' || vYear || 'ORDER BY ID';
>
>/* What I want to do something like this */
>OPEN p_cursor FOR ... EXECUTE dynamic SQL in vSQL
>
>....
>END;

[NJMrBig]

Hi Boris,
Thank you for the code suggestions. After posting my original question I
went out and bought PL/SQL for Dummies (just kidding) and figured it out
on my own.
One question you may be able to answer is that my WHERE clause is being set
by the inputs fed into the stored procedure. Therefore, it looks more like
this:

> vSQL := 'SELECT * FROM contracts ' ||
> 'WHERE (client = 0001 AND ' ||
> 'number = 10 AND ' ||
> 'year = 1994) ' ||
OR (client = 0002 AND ' ||
> 'number = 11 AND ' ||
> 'year = 1995) ' ||
> 'ORDER BY ID';

Could potentially OR up to 100 different ANDs if you know what I mean. When
I parse my inputs, create my SQL and execute it in-line dynamically using
the technique you describe below, it is unacceptably slow. My expected output
was about 2600 rows of data.

Is there anyway to optimize this? or am I using the wrong technique to solve
this particular problem?

Thanks,
Big

"Boris Milrud" <milrud@hotmail.com> wrote:
>
>Hi NJMrBig,
>
>Here is your code with some modifications:
>
>TYPE somecur IS REF CURSOR;
>....
>PROCEDURE retrieveDisplayData(
> p_ContractData IN VARCHAR2,
> p_errorcode OUT NUMBER,
> p_cursor OUT somecur)
>IS
>....
>BEGIN
>
> vSQL := 'SELECT * FROM contracts ' ||
> 'WHERE client = :bClient AND ' ||
> 'number = :bNumber AND ' ||
> 'year = :bYear ' ||
> 'ORDER BY ID';
>
> OPEN p_cursor FOR vSQL USING vClient, vNumber, vYear;
> ....
>
>END;
>
>Hope it helps.
>Boris Milrud.
>
>
>"NJMrBig" <NJMrBig@aol.com> wrote:
>>
>>Hi all,
>>
>>I am using Oracle 8i and writing a stored procedure to select data from
>multiple
>>tables given an array of IDs. I would like to use PL/SQL's dynamic SQL
capability
>>in order to build the select statement in a stored procedure and execute
>>it putting the results in a REF CURSOR.
>>
>>Anyone have any idea on how to do this? My current code is below (I have
>>changed the names to protect the innocent):
>>
>>....
>>TYPE somecur IS REF CURSOR;
>>....
>>PROCEDURE retrieveDisplayData(p_ContractData IN VARCHAR2,
>>p_errorcode OUT NUMBER,
>>p_cursor OUT somecur)
>>IS
>>....
>>BEGIN
>>vSQL := 'SELECT * FROM contracts' ||
>>'WHERE client = ' || vNumber || ' AND number = ' || vNumber || ' AND year
>>= ' || vYear || 'ORDER BY ID';
>>
>>/* What I want to do something like this */
>>OPEN p_cursor FOR ... EXECUTE dynamic SQL in vSQL
>>
>>....
>>END;
>

[Boris Milrud]

NJMrBig,

1. Check if you have a composite index on (client, number, year).
2. Analyze the execution path for that SQL. It would help if CBO uses INLIST
iterator instead of CONCATENATION one.
3. Another way to form this query would be with IN clause:
SELECT *
FROM contracts
WHERE (client, number, year)
IN ((0001,10,1994), (0002,11,1995))

I think it looks better. But the real question is if the execution path would
get better.

See if it helps, though I doubt: you mentioned that you have up to 100 sets
of (client, number, year). Another factor is the selectivity of your data.

I think you may need to rethink the whole strategy for this task. It's hard
for me to suggest anything beyond that since I don't know anything about
your data set.

Boris Milrud.

"NJMrBig" <NJMrBig@aol.com> wrote:
>
>Hi Boris,
>Thank you for the code suggestions. After posting my original question I
>went out and bought PL/SQL for Dummies (just kidding) and figured it out
>on my own.
>One question you may be able to answer is that my WHERE clause is being
set
>by the inputs fed into the stored procedure. Therefore, it looks more like
>this:
>
>> vSQL := 'SELECT * FROM contracts ' ||
>> 'WHERE (client = 0001 AND ' ||
>> 'number = 10 AND ' ||
>> 'year = 1994) ' ||
>OR (client = 0002 AND ' ||
>> 'number = 11 AND ' ||
>> 'year = 1995) ' ||
>> 'ORDER BY ID';
>
>Could potentially OR up to 100 different ANDs if you know what I mean. When
>I parse my inputs, create my SQL and execute it in-line dynamically using
>the technique you describe below, it is unacceptably slow. My expected output
>was about 2600 rows of data.
>
>Is there anyway to optimize this? or am I using the wrong technique to solve
>this particular problem?
>
>Thanks,
>Big
>
>"Boris Milrud" <milrud@hotmail.com> wrote:
>>
>>Hi NJMrBig,
>>
>>Here is your code with some modifications:
>>
>>TYPE somecur IS REF CURSOR;
>>....
>>PROCEDURE retrieveDisplayData(
>> p_ContractData IN VARCHAR2,
>> p_errorcode OUT NUMBER,
>> p_cursor OUT somecur)
>>IS
>>....
>>BEGIN
>>
>> vSQL := 'SELECT * FROM contracts ' ||
>> 'WHERE client = :bClient AND ' ||
>> 'number = :bNumber AND ' ||
>> 'year = :bYear ' ||
>> 'ORDER BY ID';
>>
>> OPEN p_cursor FOR vSQL USING vClient, vNumber, vYear;
>> ....
>>
>>END;
>>
>>Hope it helps.
>>Boris Milrud.
>>
>>
>>"NJMrBig" <NJMrBig@aol.com> wrote:
>>>
>>>Hi all,
>>>
>>>I am using Oracle 8i and writing a stored procedure to select data from
>>multiple
>>>tables given an array of IDs. I would like to use PL/SQL's dynamic SQL
>capability
>>>in order to build the select statement in a stored procedure and execute
>>>it putting the results in a REF CURSOR.
>>>
>>>Anyone have any idea on how to do this? My current code is below (I have
>>>changed the names to protect the innocent):
>>>
>>>....
>>>TYPE somecur IS REF CURSOR;
>>>....
>>>PROCEDURE retrieveDisplayData(p_ContractData IN VARCHAR2,
>>>p_errorcode OUT NUMBER,
>>>p_cursor OUT somecur)
>>>IS
>>>....
>>>BEGIN
>>>vSQL := 'SELECT * FROM contracts' ||
>>>'WHERE client = ' || vNumber || ' AND number = ' || vNumber || ' AND year
>>>= ' || vYear || 'ORDER BY ID';
>>>
>>>/* What I want to do something like this */
>>>OPEN p_cursor FOR ... EXECUTE dynamic SQL in vSQL
>>>
>>>....
>>>END;
>>
>