DISQUS

Thomas Kyte · 4 years ago

(native) dynamic sql - you can use that to work around this.

Doug Burns · 4 years ago

Good point Eddie. I was well aware of that (it was very frustrating until Oracle sorted out) but thought that it was clear enough that the focus in the paper was on SQL.

I'll think about mentioning it, though.

Cheers,

Doug

Eddie Awad · 4 years ago

Wow! what a coincidence, both Tom and Doug submitted their comments at the same time, but looks like Tom's "transaction" was "committed" before Doug's :)

Thanks Tom for the trick of using dynamic SQL to work around using CASE in PL/SQL prior to 9.0.1. Here is a quick example:

<pre>
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

apps@dev1> declare
2 l_var number;
3 sql_string varchar2(100);
4 begin
5 sql_string := 'select case when 1=1 then 1 else 2 end ' ||
6 'from dual';
7 execute immediate sql_string into l_var;
8 dbms_output.put_line('l_var='||to_char(l_var));
9 end;
10 /
l_var=1

PL/SQL procedure successfully completed.
</pre>

Doug Burns · 4 years ago

"but looks like Tom’s “transaction” was “committed” before Doug’s"

Surprise, surprise ... how could anyone keep up!

;-)

Tom_Fox · 4 years ago

What version did 8i start with? 8.1.5? I'm new to the Oracle world, and the version numbers confuse me.

Eddie Awad · 4 years ago

Here is the Oracle DB version history:

<ul>
<li>Oracle7: 7.0.16 - 7.3.4</li>
<li>Oracle8 Database: 8.0.3 - 8.0.6</li>
<li>Oracle8i Database Release 1: 8.1.5.0 - 8.1.5.1</li>
<li>Oracle8i Database Release 2: 8.1.6.0 - 8.1.6.3</li>
<li>Oracle8i Database Release 3: 8.1.7.0 - 8.1.7.4</li>
<li>Oracle9i Database Release 1:
</li></ul><ul><li>9.0.1.0, May 2002</li>
<li>9.0.1.5</li></ul>
<li>Oracle9i Database Release 2: 9.2.0.1 - 9.2.0.7 (Latest current patchset as of August 2005)</li>
<li>Oracle Database 10g Release 1: 10.1.0.2 - 10.1.0.4 (Latest current patchset as of March 2005)</li>
<li>Oracle Database 10g Release 2: 10.2.0.1</li>

source

Tom_Fox · 4 years ago

Ahhh.. wikipedia... didn't think of that.

"Hierarchy" cursor query · 3 years ago

Hi!
I'm using Oracle's version 8.1.7.
There's a cursor query in a PL/SQL package like the following one:

select "fields",
1 qry_condition
from table
where to_char(Adate, 'YYYYMMDD') = to_char(I_date, 'YYYYMMDD')
and "more_conditions"
union all
select "fields",
2 qry_condition
from table
where to_char(Adate, 'YYYYMM') = to_char(I_date, 'YYYYMM')
and "more_conditions"
union all
select "fields",
3 qry_condition
from table
where to_char(Adate, 'YYYY') = to_char(I_date, 'YYYY')
and "more_conditions"
order by "2" -- qry_condition;

The idea is to execute (only) the first query if results are returned, execute the next query if the first doesn't returned rows, and execute the third query if none of the previous two return data.
But as we can conclude, everytime this cursor is executed it runs
all of the three queries that belong to it, which is impacting negatively the cursor perfomance.

One possible solution would be to divide it in three querys and use,
something like this:

open cursor1(L_date);
fetch cursor1 into ...
if cursor1.notfound
then
open cursor2(L_date);
fetch cursor2 into ...
if cursor2.notfound
then
open cursor3(L_date);
fetch cursor3 into ...
if cursor3.notfound then
(...)

end if;

(...)

But personally I dont like this solution. I was thinking in use the CASE keyword to help me solving it, continuing to have only a single cursor. Something like this (I don't know if this is possible):

select "fields",
1 qry_condition
from table
where (
CASE when to_char(Adate, 'YYYYMMDD') = to_char(I_date, 'YYYYMMDD')
and "more_conditions"
then 1
----
when to_char(Adate, 'YYYYMM') = to_char(I_date, 'YYYYMM')
and "more_conditions"
then 2
----
when to_char(Adate, 'YYYY') = to_char(I_date, 'YYYY')
and "more_conditions"
then 3
end IN (1, 2, 3)
;

I know that this Oracle version of the PL/SQL doesn't support the CASE, so I was thinking in using dynamic Sql to do it.
What's your opinion, does the CASE solves my request or do you have a better solution?

Thanks in advance

SASantos

Eddie Awad · 3 years ago

SASantos, it looks like you are selecting the same fields from the same table in all three queries, So, why don't you use one query like this:

select "fields"
from table
where
(to_char(Adate, 'YYYYMMDD') = to_char(I_date, 'YYYYMMDD')
and "more_conditions")
OR
(to_char(Adate, 'YYYYMM') = to_char(I_date, 'YYYYMM')
and "more_conditions")
OR
(to_char(Adate, 'YYYY') = to_char(I_date, 'YYYY')
and "more_conditions")

Moreover, if Adate and I_date are of a date datatype, why converting to to_char? you can just use TRUNC.

SaSantos · 3 years ago

Eddie,
the ideal would be to only run the first OR if there weren't any rows returned for the "first conditions":
(to_char(Adate, 'YYYYMMDD') = to_char(I_date, 'YYYYMMDD')
and "more_conditions")

And run the second OR if the:
(to_char(Adate, 'YYYYMM') = to_char(I_date, 'YYYYMM')
and "more_conditions")
Didn´t returned rows.

But with the OR's or with the UNION's the query is to "heavy".
Thats why the ideal would be run the query (based on three small queries) from the start to end, and "stop" at the first that returned rows.
I don't know if thats possible. Besides the optional solution I refered before.

TIA,
SaSantos

Eddie Awad · 3 years ago

SaSantos, what do you mean by "heavy"? Have you measured the performance of your query and found it to be slow? Have you run your query through an explain plan? a tkprof?

I suggest you post your question on forums.oracle.com. I also suggest you read:

http://oraclesponge.blogspot.com/2005/04/writing-good-sql.html

STEVE · 3 years ago

so how would you code something that returns a cursor nested in a table based on a case statment in 9i?

select *,
(case
when tb1.item=100 then
cursor(select * from tb2 where id=5 from mytable2 tb2)
when tb2.item=200 then
cursor(select * from tb3 where id=6 from mytable3 tb3)
else null
end ) curval
from mytable1

or would you have to used "decode"

Eddie Awad · 3 years ago

Steve,
Your SQL syntax does not look correct. However, I tried the following in 10gXE:

<pre>
SELECT (CASE
WHEN 1 = 1
THEN CURSOR (SELECT 1
FROM DUAL)
WHEN 1 = 2
THEN CURSOR (SELECT 1
FROM DUAL)
ELSE NULL
END
) x
FROM DUAL
/
</pre>

It returned an error:
ORA-22902: CURSOR expression not allowed
Cause: CURSOR on a subquery is allowed only in the top-level SELECT list of a query.

That means you cannot use cursor expressions in a CASE or even DECODE.

Another solution may be the use of Dynamic SQL.

Oracle Functions · 2 years ago

Excuse me, i don't understand...
How i can you DECODE functions?

santhosh · 1 year ago

apps@dev1> declare
2 l_var number;
3 sql_string varchar2(100);
4 begin
5 sql_string := 'select case when 1=1 then 1 else 2 end ' ||
6 'from dual';
7 execute immediate sql_string into l_var;
8 dbms_output.put_line('l_var='||to_char(l_var));
9 end;
10 /
l_var=1

The above is applicable, when the sql returns only one value (1 row, 1 column)

How do I implement the same using curser,
ie, if the sql statement returns a tabular data (M rows, N columns:: where M & N >2)?

Eddie Awad · 1 year ago

@santhosh: You can try something like the following, or use dbms_sql.

<pre>
DECLARE
l_my_cursor sys_refcursor;
l_stmt_str VARCHAR2(32767);
l_var NUMBER;
BEGIN
l_stmt_str :=
'SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END FROM dual
UNION
SELECT CASE WHEN 1=2 THEN 1 ELSE 2 END FROM dual';
OPEN l_my_cursor FOR l_stmt_str;
LOOP
FETCH l_my_cursor
INTO l_var;
EXIT WHEN l_my_cursor%NOTFOUND;
dbms_output.put_line('l_var='||to_char(l_var));
END LOOP;
CLOSE l_my_cursor;
END;
</pre>