Running PL/SQL query through SQL+

SoupyNorman

Hi,

I'm currently batching up alot of scripts, modifying and formatting the output through SQL+ spooling into formats required by the users.

One script is of the plsql flavour and although my vanilla SQL skills are pretty proficient my plsql is in need of attention.

When I run the script through sql+ nothing happens, it does not seem to recogneise the query.

Is there a way to run plsql this way?



Code:


CLEAR COLUMNS
CLEAR BREAKS
SET PAGESIZE 20
SET LINESIZE 500
SET ECHO OFF
SET FEEDBACK ON

BEGIN
select xxxxxxxxxxxxxxxxxxxxxxxxx,sysdate as "Time

Now",xxxxxxx as xxxx_xxx_xxxx,



floor(to_number(to_char(xxxxx))-to_number(to_char(xxxxx,'dd')))*60*60*24

+

abs(to_number(to_char(xxxx,'hh'))-to_number(to_char(xxxxx,'hh')))*60*60

+

abs(to_number(to_char(xxxx,'mi'))-to_number(to_char(xxxxx,'mi')))* 60

as xxxxxxxxxxx,

case when xxxxxxxx like '%999%' or

to_number(to_char(next_day,'yy'))> 10

then ‘xxxxxxx ‘

when sysdate > next_day then 'Potentially Something Wrong'

Else

(case

when xxxxx <= 86400 AND



abs(to_number(xxxx(xxxx))-to_numberxx(last_day,'dd')))*60*60*24

+

abs(xxx(to_char(xxxx'hh'))-to_number(xxx(xxx'hh')))*60*60

+

abs(to_number(xxxx(xxxx,'mi'))-to_number(to_char(xxxxx'mi')))* 60 +

100<= xxxxx_1

then 'xxxxRunningxxxx

ELSE 'Running on Time'

END)

END as "xxx OF xx TASK",xxxx_kz, cxx_id,

decode(b.ERROR_CODE,'0',null,error_code) "Error?", c.Txxxx_USER, c.*, B.*

from xxxx_tasks b,

xxxx_tasks c

where 1=1

and c.xx=b.xx

and b.xxxxxx='1'

order by c.xxxxx

END;

Tom Dunne

Can you give more info on how you are running it, and what exact error you are seeing?

Also, did you SET SERVEROUTPUT ON?

And yes, you can run PL/SQL that way, it is called an Anonymous Block.

SoupyNorman

Tom Dunne wrote: »

Can you give more info on how you are running it, and what exact error you are seeing?

Also, did you SET SERVEROUTPUT ON?

And yes, you can run PL/SQL that way, it is called an Anonymous Block.

Hi Tom, cheers for the response. The problem turned out to be the formatting of the code itself. I ran the code through an SQL beautifier and then it worked.


Oh and I was also missing the '/' and '.' at the end of the statement. I suppose these are hiccups you encounter with code that is not your own (and not being too hot on PLSQL didnt help either!!)


Cheers again.