What Am I missing?

From: Dave <oracle_at_1001111.com>
Date: Mon, 7 Sep 2020 12:58:44 -0600
Message-ID: <e265fab5-ea37-d9c8-fb18-b7c14d404223_at_1001111.com>

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.

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

TIA
Dave

-- 
Dave Morgan
Senior Consultant, 1001111 Alberta Limited
dave.morgan_at_1001111.com
403 399 2442

-- There are only 10 kinds of people in the world
-- Those who understand binary, and those who don't. 
[rec.humor.funny] 
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 07 2020 - 20:58:44 CEST