Select Statement with SubQueries Errorr

[oracle8]

Hi,

I am using Oracle enterprise server 8.04 and encountered this behavioral indifference between a "select statement" and same "select statement" inside a cursor. Actually, the problem starts when I find out that my cursor is not giving me the right output. The select statement inside my cursor contains 3 levels of subqueries with decode functions that i used to manipulate some output. Unfortunately, when I directly run my sql stament in SQL PLUS(w/ no cursor declaration), it did output the correct result! I presumed that this one is an oracle compiler bug. Anybody got a bright suggestion? any database patches that i should apply? Is there a limitation when we use a cursor?

I appreciate very much if you can help.

Best regards.


oracle8

 

[karluk]

I know that PL/SQL has a preprocessor that makes what are usually insignificant changes to the sql before execution. Without seeing your code it's hard to say if this may have made a difference. Why don't you post your queries if you get a chance.

 

[Guest_imported]

Hi,
I want to know how to get formatted ouput for a select statement.
Specifically I want to get the output of a numeric field with prefix zeros.
Can u help me.

Thanks

 

[karluk]

Use a number format with '0' to specify leading zeros. For example

select to_char(123,'099999') from dual;

will return

000123