Hi Dave,
This problem is not in oracle, but in a client tools, usually in sqlplus
(I'm not sure about SQLcl) where / means 'execute' command:
SQL> help /
/ (slash)
---------
Executes the most recently executed SQL command or PL/SQL block
which is stored in the SQL buffer. Use slash (/) at the command
prompt or line number prompt in SQL*Plus command line. The buffer
has no command history and does not record SQL*Plus commands.
/
If you are using sqlplus you save you ddl command as a separate file and
use 'get' command:
SQL> ho cat tests/slash-2.sql
select '
/
' x
from dual
SQL> @tests/slash-2.sql
ERROR:
ORA-01756: quoted string not properly terminated
SP2-0042: unknown command "' x" - rest of line ignored.
SP2-0042: unknown command "from dual" - rest of line ignored.
SQL> get tests/slash-2.sql
1 select '
2 /
3 ' x
4* from dual
SQL> /
X
---
/
Or you can use Oracle SQL Developer or 'Program window' in Allround PL/SQL
developer.
пн, 7 сент. 2020 г., 22:05 Dave <oracle@xxxxxxxxxxx>:
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:ignored.
ORA-00933: SQL command not properly ended
SQL> RETURN vPercentage;
SP2-0734: unknown command beginning "RETURN vPe..." - rest of line
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@xxxxxxxxxxx
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]
--
//www.freelists.org/webpage/oracle-l
