CASE statement in a procedure 3

[mike777]

Hello all.
I'm trying to use a CASE statement in a SELECT statement. It works fine as long as I just running the SQL from the command prompt. When I try to put the SQL statment in a procedure, I get an error on when it tries to compile the CASE statement.
I read once that the CASE statement doesn't work in PL/SQL, only SQL. So I tried to put the SQL statement in a string variable, and then execute it using EXECUTE IMMEDIATE.
Problem is, the SQL statement is 25,000 characters long. Is there a string variable that can hold that many characters. I tried:

lngSQL longraw;

and

lngSQL varchar2(4000);

(this one obviously won't work!!)
and I keep getting an error about an identifier being too long. I guess that means the variable can't hold the string.
Any suggestions?
Many thanks.
-Mike Kemp

 

[Turkbear]

Hi,
You could try to refine your Sql query - a query of >25000 chars is very long and there must be a way to reduce its size.. (perhaps by pre-building some views, etc..)
Perhaps you could post the reasons for the CASE statements.

 

[mike777]

Thanks, Turkbear.
I'll just rewrite my case statements to be decode's instead. No problem.
The thing, was, though, that those CASE statements really come in handy lots of times, and we're trying to relocate as much code as possible from clients to the backend (we used to write these huge SELECT statements on the client, and then pass it to the server, which would return the results). That means (as far as we know), that we have to create procedures for all of our SELECT statements (to handle query parameters).
We've just learned how to return cursors from procedures, and we are preferring that method right now to the other where you incorporate parameters into CREATE OR REPLACE VIEW statements to create views and then select from them.
Personally, I like the return cursor method because there's no writing done to the data dictionary, and it's a one-step (instead of two-step) process (open cursor, that's it).
Anyway, the whole point of it was (before I went off on a 90-degree tangent), that I would like to be able to keep my CASE statements, and also use them in procedures...
Thanks for the help.
Anybody else have any ideas, please weigh in!!!
-Mike Kemp

 

[SantaMufasa]

Well, yes, I'll weigh in (and if you could see me, you'd know that I weigh in more than just about anyone <smile>). I know this is &quot;weigh&quot; out on a tangent, but you know that in Oracle 9i, the CASE statement is &quot;self-supporting&quot; in that you do not need to embed it in a SELECT. (Is there anything REALLY delaying your upgrading to current version?)

Just my two cents/pence/kopiika/pesetas/centimes/paise worth.

Dave

 

[mike777]

SantaMufasa,
9i: Excellent point. Laziness is always a factor.
I suppose, then, that in 9i, you could also use a SELECT statement that includes a CASE, when the SELECT is inside a procedure?
This is as it is here.
Thanks.
-Mike

 

[SantaMufasa]

Absolutely. PL/SQL in 9i supports the &quot;CASE&quot; construct both with and without SELECT statements. PL/SQL in 8i does not recognize the &quot;CASE&quot; statement in any 'case'. 8i supports &quot;CASE&quot; only within a SELECT that runs outside of PL/SQL.

If you like PL/SQL 'CASE' then it looks like it's time to upgrade to 9i.

Cheers, bud.

Dave

 

[sem]

In pl/sql variables of VARCHAR2 type may be up to 32K contrary to 4000 bytes of sql VARCHAR2.

Regards, Dima

 

[mike777]

Dima,
You don't say!! Now that's good to know!
Many thanks!!
-Mike

 

[sem]

Another solution is to use well forgotten DBMS_SQL package, as since 8 it supports table of varchar2s as a parameter for parse

Regards, Dima

 

[dsanchez2]

The CASE statement does not work in PL/SQL (in Oracle 8) because PL/SQL and the Database-server had different parsers. In Oracle 9 the parsers have been merged into a single parser, so this problem should not occur.

A work around (apart from decode and EXECUTE IMMEDIATE) is to put your query into a VIEW, then select from the VIEW in your PL/SQL code.

 

[dvraggs]

Hello everyone I have a decode question.
My table: item date id
dog 01/01/2004 24
cat 02/04/2003 24
dog 04/08/2004 26
cat 06/04/2003 26
i want it to look like this: id dog cat
24 01/01/2004 02/04/2003
26 04/08/2004 06/04/2003

i do it with a self join but adding more items. How can I use decode to get this. Adding 7 more items, that would be 9 selfjoins. any help?

 

[sem]

select id
, max(decode(item,'dog',date, null)) "dog"
, max(decode(item,'cat',date, null)) "cat"
...
group by id

Regards, Dima

http://seminihin.narod.ru