Re: What Am I missing?

I wouldn't call it a PL/SQL parser bug. The client tool doesn't know it's PL/SQL and splits the code at the / character, producing an incomplete function followed by some unparsable lines which never reach the PL/SQL compiler. I'm amazed that I've never seen this before but I can reproduce it in SQL*Plus 19.0.0.0.0.

I'm not sure what else SQL*Plus could do, to be honest, as / means run and there is no way to change it.

William

On 7 Sep 2020, at 20:47, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

Hi Dave,
It's a PL/SQL parser bug. Boh SQL*Developer and SQL*Plus report an error on the "/" 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 NUMBER
> IS
> 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 NUMBER
> IS
> 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;
> /
>
> This version with the slash on it's own line (for readability sake) does not does not compile:
> CREATE OR REPLACE FUNCTION test RETURN NUMBER
> IS
> 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;
> /
>

>> (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 ended
>> SQL>    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.

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