[feature] enhance Execute script KW to support more statement blocks

[oferdan]

Hi,

Currently, Execute Script keyword is dividing statements based on the semicolon sign (;) - and executes them separately later on.

Unless the semicolon is inside a statements block - it is treated as an exception and all the statements inside are executed together, even if they contain a semicolon (and they must contain one, otherwise it’s an invalid script for Oracle).

But not all possible statements blocks are currently supported.
In fact, it’s only a simple BEGIN - END block which is supported.
Here is an example of a simple script which does work. So the IF block is split just in the middle - which resulted in a wrong statement.
The „END ONDREJ;“ line won’t also work by the way - the current version of the library won’t understand the procedure name after the „END“ statement.

Feature request: support more statement blocks, including IF/ELSE. Or to find some more universal way to divide the statements.

Example script that currently doesn't work:

create or replace procedure ondrej (udate in varchar2, errcode out number,errmsg out varchar2)
is

voperation varchar2(50);

begin

if length(udate) = 1 then
    voperation := 'EOD Operation';
else
    voperation := 'POD Operation';
end if;

end ondrej;

Example Robot script to execute above:

Test Plsql
    Connect To Database 	dbapiModuleName=oracledb  dbName=${db_SID}  dbUsername=${db_USERNAME}  dbPassword=${db_PASSWORD}  dbHost=${db_IP}  dbPort=${db_PORT}  dbCharset=UTF-8
    Execute Sql Script    ${EXECDIR}//data//ondrej.sql
    Disconnect From Database

Based on our Slack discussion: https://app.slack.com/client/T07PJQ9S7/C5M9S3482/thread/C5M9S3482-1693825842.922829

[oferdan]

Another example related to the parsing change (?), which was working fine with DatabaseLibrary 1.2.x:

Robot code:

*** Settings ***
Variables    ../resources/sql/hsexport.py

*** Variables ***
${HSEXPORT}    ${HSEXPORT_PY}    # variable loaded from ../resources/sql/hsexport.py

*** Tests ***
Test DB
    DatabaseLibrary.Connect To Database 	dbapiModuleName=oracledb  dbName=${db_SID}  dbUsername=${db_USERNAME}  dbPassword=${db_PASSWORD}  dbHost=${db_IP}  dbPort=${db_PORT}  dbCharset=UTF-8
    DatabaseLibrary.Execute SQL String    ${HSEXPORT}
    DatabaseLibrary.Disconnect from Database

hsexport.py

HSEXPORT_PY="""DECLARE
  ERRCODE NUMBER;
  ERRMSG VARCHAR2(200);
BEGIN
  HSEXPORT(
    ERRCODE => ERRCODE,
    ERRMSG => ERRMSG
  );
END;"""

getting:

DatabaseError: ORA-06550: line 9, column 3:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.

[oferdan]

I think it would be good to have one KW that would run everything in the file as it is without any parsing.

[amochin]

The issue is more complicated than I suggested.

I slightly improved splitting the SQL script files into statements - now some more statements blocks of a PL/SQL script for Oracle are processed correctly, like the one in the issue description.

However, I found out that I was just scratching on the surface.
There should be a better, more universal solution - see #205.

[amochin]

The keyword Execute SQL Script has a new parameter split now - setting it to False disables splitting the SQL script into statements. In this case the entire script content will be passed to the database module for execution.

[amochin]

Also, I've added a new parameter omitTrailingSemicolon to the keyword Execute SQL String - for explicit instruction, if the trailing semicolon (;) at the SQL string end should be removed or not. Setting it to False will fix the error provided above.

Some explanation:

• Some database modules (e.g. Oracle) throw an exception, if you leave a semicolon at the string end
• However, there are exceptional cases, when you need it even for Oracle - e.g. at the end of a PL/SQL block.
• If not specified, it's decided based on the current database module in use. For Oracle, the semicolon is removed by default.

[amochin]

The version 1.4.2 with the fix will be released till the end of the week.