Re: What Am I missing?
Date: Mon, 7 Sep 2020 22:23:17 +0300
Message-ID: <CAOVevU7kbxLUGc0KMFwn+uQSSQjd63rAp2FQsySxLktcv=MAvw_at_mail.gmail.com>
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> _at_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_at_1001111.com>:Received on Mon Sep 07 2020 - 21:23:17 CEST
> 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
> > > -- http://www.freelists.org/webpage/oracle-l