Re: What Am I missing?

Hi William!My knowledge here might be slightly out of date. .the last time I used lex and yacc was when I was writing a parser which was converting Oracle*Forms 2.3 INP file into Oracle*Forms 3.0 INP file, which was a few decades ago. The reason for writing this was that the commercial tool that did the same thing was licensed by the trigger. It was charging the end user for every trigger converted from the version 2.3 --> 3.0. As a consultant, I invented my own tool. So, from that little experience I know that the program can set a flag when it encounters the first BEGIN and unset it when it encounters the last END. While this flag is set, the "/" should be interpreted as a division rather than the end of block. I would definitely call it a "parser bug". The problem is in the yacc parser which doesn't behave correctly and uses "/" to terminate the block prematurely. As a digression, I was considering writing a parser for the .trc files but there already is a great publicly available alternative on CPAN.Regards On Tue, 2020-09-08 at 10:33 +0100, William Robertson wrote:
> 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 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; RET
> > URN 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; RET
> > URN 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; RET
> > URN 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