mORMot Open Source

dexter

Member

Registered: 2015-04-24

Posts: 53

Create function in Oracle

Hi,

I'm trying to create a function with OCI with no success.

Code:

DB.ExecuteNoResult(
  'CREATE OR REPLACE FUNCTION ORA_POC(MAIN_TABLE IN VARCHAR2, REC_COUNT IN NUMBER, BATCH_SIZE IN NUMBER) RETURN VARCHAR2' +
  ' AS LANGUAGE JAVA' +
  ' NAME ''OraMain.selectTable(java.lang.String, int, int) return java.lang.String''; ', []);

(DB is TSQLDBOracleConnectionProperties);

Output:

20181107 15232619 SQL   TSQLDBOracleStatement(02985E40) CREATE OR REPLACE FUNCTION ORA_POC(MAIN_TABLE IN VARCHAR2, REC_COUNT IN NUMBER, BATCH_SIZE IN NUMBER) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'OraMain.selectTable(java.lang.String, int, int) return java.lang.String';
20181107 15232619 warn  ORA-24344: success with compilation error

Error from Oracle:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;
The symbol ";" was substituted for "end-of-file" to continue.

Calling same statement using JDBC works fine.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,548

Website

Re: Create function in Oracle

I'm use the same technique to create oracle database without a problem. Try to add LFs inside your string

dexter

Member

Registered: 2015-04-24

Posts: 53

Re: Create function in Oracle

Creating tables is working fine for me as well.

I have noticed something: if I remove the last space in create procedure statement (leaving semicolon as last char) - then semicolon is removed from the statement (or SynLog does not display it?). With space at the end - the semicolon is present (like in my first message).

20181114 08533932 SQL   TSQLDBOracleStatement(00CB5E40) CREATE OR REPLACE PROCEDURE ORA_POC(MAIN_TABLE IN VARCHAR2, REC_COUNT IN NUMBER, BATCH_SIZE IN NUMBER, COMMAND IN VARCHAR2, RESULT OUT VARCHAR2) AS LANGUAGE JAVA NAME 'OraMain.selectTable(java.lang.String, int, int, java.lang.String, java.lang.String[])'
20181114 08533932 warn  ORA-24344: success with compilation error

Edit: adding line feeds inside statement did not help.

Last edited by dexter (2018-11-14 08:58:30)

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,548

Website

Re: Create function in Oracle

Ups. see TSQLDBStatement.StripSemicolon. Should be set to False in your case

dexter

Member

Registered: 2015-04-24

Posts: 53

Re: Create function in Oracle

Thanks mpv, good to know about StripSemicolon.

Anyway, it does help with the issue.

dexter

Member

Registered: 2015-04-24

Posts: 53

Re: Create function in Oracle

The problem still exists and the issue is in ReplaceParamsByNames() function (SynDB.pas), it strips the last semicolon regardless of the TSQLDBStatement.StripSemicolon property.
The workaround:
Change function signature:

function ReplaceParamsByNames(const aSQL: RawUTF8; var aNewSQL: RawUTF8; const aStripSemicolon: boolean = true): integer;

Update function's code (add the aStripSemicolon check)

  if aStripSemicolon then
    while (L>0) and (aSQL[L] in [#1..' ',';']) do
      if (aSQL[L]=';') and (L>5) and IdemPChar(@aSQL[L-3],'END') then
        break else // allows 'END;' at the end of a statement
        dec(L);    // trim ' ' or ';' right (last ';' could be found incorrect)

Update TSQLDBOracleStatement.Prepare (SynDBOracle.pas)  call to ReplaceParamsByNames():

fPreparedParamsCount := ReplaceParamsByNames(aSQL,oSQL,StripSemicolon);

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Create function in Oracle

Did you try with the latest source code?

ReplaceParamsByNames() is not called any more by SynDBOracle.
This unit calls ReplaceParamsByNumbers() - which is faster - with AllowSemicolon=true so it should work as expected.

dexter

Member

Registered: 2015-04-24

Posts: 53

Re: Create function in Oracle

Just downloaded the latest code and it does not work, because I need semicolon not to be trimmed, but it is by below code (SynDBOracle.pas, line 3416):

        L := Length(fSQLPrepared);
        while (L>0) and (fSQLPrepared[L] in [#1..' ',';']) do
        if (fSQLPrepared[L]=';') and (L>5) and IdemPChar(@fSQLPrepared[L-3],'END') then
          break else // allows 'END;' at the end of a statement
          dec(L);    // trim ' ' or ';' right (last ';' could be found incorrect)
        if L <> Length(fSQLPrepared) then
          fSQLPrepared := copy(fSQLPrepared,1,L); // trim right ';' if any

My workaround is to check StripSemicolon flag before trimming, otherwise leave fSQLPrepared as is.

      if StripSemicolon then
      begin
        L := Length(fSQLPrepared);
        while (L>0) and (fSQLPrepared[L] in [#1..' ',';']) do
        if (fSQLPrepared[L]=';') and (L>5) and IdemPChar(@fSQLPrepared[L-3],'END') then
          break else // allows 'END;' at the end of a statement
          dec(L);    // trim ' ' or ';' right (last ';' could be found incorrect)
        if L <> Length(fSQLPrepared) then
          fSQLPrepared := copy(fSQLPrepared,1,L); // trim right ';' if any
      end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Create function in Oracle

I am afraid it may break some ORM-generated statements, or existing code.
If a user has ';' at the end of its SQL, and expect it to be trimmed, then he/she will get an error.

dexter

Member

Registered: 2015-04-24

Posts: 53

Re: Create function in Oracle

StripSemicolon by default is true.
When user does not want to trim semicolon for some specific statements (like me) - it sets it to false.

Edit: wasn't this the purpose of StripSemicolon property? To trim or not the semicolon depending on the case?

Last edited by dexter (2020-07-31 09:33:56)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Create function in Oracle

It was the purpose within the SQL statement itself.
The trailing ';' was always trimmed.

So in order not to break backward compatibility, I propose the following trick:
https://synopse.info/fossil/info/67f2363425

dexter

Member

Registered: 2015-04-24

Posts: 53

Re: Create function in Oracle

Looks good to me. Thanks!

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,548

Website

Re: Create function in Oracle

I fix an infinity loop in ; removal implementation for statements what ends with space chars, like:   '....END;'#10
Also simplify logic a bit - please, merge #341

Last edited by mpv (2020-08-08 18:01:00)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Create function in Oracle

Merged.

Sorry for the bug.

dexter

Member

Registered: 2015-04-24

Posts: 53

Re: Create function in Oracle

I fix an infinity loop in ; removal implementation for statements what ends with space chars, like:   '....END;'#10
Also simplify logic a bit - please, merge #341

I think because of this change now it fails again.
I mean I have updated to latest code and the trick with ';;' does not work anymore.
I have reverted back to commit 82b4176 and works again.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Create function in Oracle

Indeed.

Please try https://synopse.info/fossil/info/914dd25726

dexter

Member

Registered: 2015-04-24

Posts: 53

Re: Create function in Oracle

Works ok now, thanks!

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,548

Website

Re: Create function in Oracle

Interesting issue with trailing ";". For statements like

ALTER TABLE BLA_BLA DROP COLUMN STR_PREPEND;

OR

SELECT SOMETHING FROM PERIODICALEND;

Trailing semicolon is not removed, because statement ends with END. But should be.

Should be fixed by #369  @ab - please, do the same in mORMot2

Last edited by mpv (2020-12-11 17:53:40)