Re: What Am I missing?

Hi Dave,It's a PL/SQL parser bug. Boh SQL*Developer and SQL*Plus report an error on thee "/" line, Here is the version that works: SQL> create or replace function test_fn return integer as 2 vPercentage NUMBER; 3 cursor csr is select round(((select sum(vsize(ename)) from emp)/(SELECT SUM(bytes) FROM user_free_space WHERE tablespace_name = 'USERS')) *100, 2) from dual; 4 begin 5 open csr; 6 fetch csr into vPercentage; 7 close csr; 8 RETURN vPercentage; 9 END test_fn; 10 /

Function TEST_FN compiled
Obviously, i don't have your tables so I cannot replicate exactly the same code but this should be close enough. And yes, explicit cursor handling is a drag which would normally require some explicit language but this works. This is the best I was able to come up with in 5 min.Regards
On Mon, 2020-09-07 at 12:58 -0600, Dave wrote:
> Hello All, When the '/' (divide) is on a line by itself the
> function does not compile.However, the following 2 versions compile:
> CREATE OR REPLACE FUNCTION test RETURN NUMBERIS vPercentage
> NUMBER;BEGIN SELECT ROUND(( (SELECT NVL(SUM(
> LENGTH(ds.document_binary) ),0) FROM doc_store ds) / (SELECT
> SUM(bytes) FROM user_free_space WHERE tablespace_name = 'LOB_DATA'))
> *100, 2) INTO vPercentage FROM DUAL; RETURN
> vPercentage;END test;/
> CREATE OR REPLACE FUNCTION test RETURN NUMBERISvPercentage
> NUMBER;BEGIN SELECT ROUND(( (SELECT NVL(SUM(
> LENGTH(ds.document_binary) ),0) FROM doc_store ds) / (SELECT
> SUM(bytes) FROM user_free_space WHERE tablespace_name = 'LOB_DATA'))
> *100, 2) INTO vPercentage FROM DUAL; RETURN
> vPercentage;END test;/
> This version with the slash on it's own line (for readability sake)
> does not does not compile:CREATE OR REPLACE FUNCTION test RETURN
> NUMBERISvPercentage NUMBER;BEGIN SELECT ROUND(( (SELECT
> NVL(SUM( LENGTH(ds.document_binary) ),0) FROM doc_store ds) /
> (SELECT SUM(bytes) FROM user_free_space WHERE tablespace_name =
> 'LOB_DATA')) *100, 2) INTO vPercentage FROM DUAL; RETUR
> N vPercentage;END test;/
> > (SELECT SUM(bytes) FROM user_free_space WHERE tablespace_name =
> > 'LOB_DATA')) *100, 2) INTO
> > vPercentage
> > *ERROR at line 1:ORA-
> > 00933: SQL command not properly endedSQL> RETURN
> > vPercentage;SP2-0734: unknown command beginning "RETURN vPe..." -
> > rest of line ignored.SQL> END test;SP2-0042: unknown command "END
> > test" - rest of line ignored.
>
> It appears that it is treating a single slash as a comment?Does not
> seem right to me. 11.2.0.3 and 12.1.x on MSWIN
> TIADave

-- 
Mladen Gogala

Database Consultant

Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l