Usage of sqlplus commands within DBTool of SOAtest

dgoedh

Hi,

Is it in anyway possible to use SQLPLUS commands from the DB Tool within SOAtest (2021.1)?

When I use e.g.

spool ref_stamgegevens.sql
set sqlformat insert
select * from scalaref.ref_stamgegevens;
spool off

with radio button set to Separate Statements by Magic Token I keep getting an

ORA-00900: Invalid SQL-statement: 42000. (same using Separate by semicolon;)

I already checked the .profile of the DB user for the oracle path, but from sql developer I can execute the sql statements above without problems.

Is it more convenient to use a (batch) script to execute SQLPLUS statements using the Exterenal tool?

Any suggestions?

Thanks is advance.

Have a nice day.

Regards, Daniel

benken_parasoft

Here is my understanding:
SQL*Plus is a command line tool for querying Oracle Database. In contrast, java applications like SOAtest use Oracle's JDBC driver to communicate with the Oracle Database. So, certain commands that you can give to SQL*Plus are not necessarily going to be understood or supported by Oracle's JDBC driver. The SPOOL command tells SQL*Plus to store query results to a file. Similarly, SET is used to modify SQL*Plus environment settings. The Oracle JDBC driver doesn't have such features built into itself. The Oracle JDBC driver just passes SQL or PL/SQL statements directly to the Oracle Database.

In SOAtest's DB Tool, simply execute "select * from scalaref.ref_stamgegevens;" The SQL Result Set is represented as XML. You can chain a tool do do something with the XML, like write it to a file, or transform/convert it in some way, or extract values, etc.

dgoedh

Thanks for the reply. Indeed I can opt for hooking up a write-file to the DB Tool to store the Select-results. I hope it is also possible to format these results in an Insert statement, so I am able to restore the table-contents as part of the tear-down. Regards, Daniel