PL SQL Statement

KNIME Analytics Platform
1

begin

KNM_VALIDATE_STRUC(table_name);

end;

I am trying to execute the PL SQL statement block above to run a procedure in Oracle in the KNIME SQL Database executor node and I keep getting this error:

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

:= . ( % ;

 

I have also tried running this in the Database reader node and get the same error.

 

 

 

2

Hi,

the Database SQL Executor node splits the statement into several statements based on ; and new line. So I guess the problem is that the node sends two statements to the db  begin KNM_VALIDATE_STRUC(table_name);  as first statement and end ; as second statement.

All statements that are send to the db are also logged in the KNIME log file. To view the log file open KNIME and go to View->Open KNIME log.

Bye,

Tobias

3

Hi,

Thanks Tobias for the tip about the KNIME log! Seems like KNIME doesn't like the PL SQL block statement format. I also tried the exec KNM_VALIDATE_STRUC(table_name); to run a stored procedure in Oracle but got an invalid SQL statement error even though the statement runs in Oracle.

So any suggestions on how to run a stored procedure from Oracle in KNIME?

Thanks,

Namitha

4

I'm trying to do the same thing. Can you please give some suggestions on how to run an Oracle procedure from KNIME?

5

Hi

I'm also trying to run a Oracle procedure from KNIME. 

But , I have found this message:

ERROR     Database SQL Executor    -      Execute failed: ORA-00900: Invalid SQL statement

Can you please have some suggestions ?

 

Thank you very much.

Marcus

6

I know it is very cheesy, but as a workaround you could attach a trigger on a 'start_execution' table.

7

Hello,

just add a space after the ; within the procedure body to prevent the split up of the query. Attached you can find an example of a workflow that creates and calls a stored procedure within MySQL which can be adapted to work also with Oracle. The workflow uses the Database Reader node with the option "Run SQL query only during execute, skips configure" enabled (which is the default) to call stored procedures and the Database Executor node to create new ones.

Bye

Tobias

1 Like
8

Wow.  That is a really helpful tip. 

I added the space and now can run PL SQL using the Database SQL Executor node.   The statement block looks like this:

begin

      {statement};{SPACE}

end

 

 Thanks!