topic Executing Native SQL query for oracle in Application Development and Automation Discussions

Executing Native SQL query for oracle

Former Member — Tue, 13 Sep 2005 12:55:13 GMT

Hi,

I want to run following native sql query but it is giving me error ora:933,

DATA: BEGIN OF WA,

TSP_NAME(255) TYPE C,

PER_USAGE(10) TYPE C,

END OF WA.

EXEC SQL PERFORMING loop_output.

select t.tablespace_name,'(' || TO_CHAR(ROUND(100*(NVL(b.bytes,0)/NVL(a

.bytes,0)))) || '%)' "TSUsed%" from dba_tablespaces t,

( select tablespace_name, sum(bytes)/1024/1024 bytes

from dba_data_files group by tablespace_name) a,

( select e.tablespace_name, sum(e.bytes)/1024/1024 bytes

from dba_extents e group by e.tablespace_name ) b,

( select f.tablespace_name, sum(f.bytes)/1024/1024 bytes

from dba_free_space f group by f.tablespace_name ) c

where t.tablespace_name = a.tablespace_name(+) and

t.tablespace_name = b.tablespace_name(+) and

t.tablespace_name = c.tablespace_name(+) into :wa.

ENDEXEC.

Please provide me the soln

Regards,

Bharat Mistry

Re: Executing Native SQL query for oracle

Former Member — Tue, 13 Sep 2005 14:10:29 GMT

ORA-00933: SQL command not properly ended.

Try:


EXEC SQL PERFORMING loop_output.

select 

...

into :wa

ENDEXEC.

(No "." at the end). If that doesn't work, try ending it with a ";"

Rob

Re: Executing Native SQL query for oracle

Former Member — Wed, 21 Sep 2005 10:14:03 GMT

Hi Rob,

Thanks for your reply but i am getting new error

Database error text........: "ORA-01476: divisor is equal to zero"

I Think there is something wrong in the query.

Regards

Bharat Mistry

Re: Executing Native SQL query for oracle

Former Member — Wed, 21 Sep 2005 13:53:13 GMT

I think there is too.

It's a pretty complex select statement. For debugging purposses, break it down into simpler ones. And evaluate your aritmetic expressions manually in a break-point.

Rob