Using Splice*Plus

Splice*Plus extends Splice Machine SQL with a procedural language you can use to perform operations that you would otherwise write as SQL queries.

Splice*Plus is a command line utility program that provides an interpreter for running PL/SQL programs interactively. You can run PL/SQL programs stored in text files, or you can enter PL/SQL statements and blocks interactively, similarly to the way you use the splice> prompt to run SQL commands.

If you are already familiar with PL/SQL from using it with Oracle or another database, Splice Machine PL/SQL is almost exactly the same. The Splice*Plus language topic provides a quick review of the supported language structures.

The remainder of this topic includes these sections:

Invoking Splice*Plus

Splice*Plus is automatically installed for you when you install the Enterprise version of Splice Machine. You can invoke Splice*Plus to run one or more PL/SQL programs, or you can use it interactively by invoking it without any program files on the command line. The basic syntax is:

spliceplus [--output outputFile] [cmdlineOptions*] sqlFile*

For example, this command line runs the PL/SQL program in the file myExample1.sql:

spliceplus myExample1.sql

You can add the --output option to send any program output to a file; for example:

spliceplus --output myExample1.out myExample1.sql

You can also invoke Splice*Plus to display help or version information from the command line:

spliceplus --help
spliceplus --version

Command Line Options

You can specify zero or more of the following options on the Splice*Plus command line, each of which have default values that are applied if the option is not specified on the command line. Here’s the syntax:

Option Description Default Value
debugOptions

Specifies debugging options. You can specify any number of these options:

  • SCRIPT
  • LOCAL
  • GLOBAL
  • OPCODE
  • FUNCALL
  • FUNARGS
  • FUNAST
  • SQL
  • SQLDATA
  • SQLANALYZE
  • SQLCURSOR
  • JDBC
  • JDBCAUTH
  • DISABLE_PERSISTENCE
  • PERSISTENCE
OFF
jdbcString The JDBC connection string to use, in this form: //hostname:1527/splicedb jdbc://localhost:1527/splicedb
schemaName The name of the database schema to use. SPLICE

The Splice*Plus Analyzer

You can use the spliceplus-analyzer program to profile which features your PL/SQL file uses; this allows you to analyze which features are supported in Splice*Plus.

The basic syntax for running the analyzer is:

spliceplus-analyzer [--output outputFile] [--verbose] sqlFile+

You can use the --output option to save the analyzer output to a CSV file. The --verbose option increases the amount of information the analyzer displays.

You can also invoke the analyzer to display help or version information from the command line:

spliceplus-analyzer --help
spliceplus-analyzer --version

Analyzer Example

You can analyze the newtroot.sql example program with this command line:

spliceplus-analyzer ./examples/newtroot.sql

You’ll see output like this:

Analyzing ./examples/newtroot.sql
Feature count is:
PlConstruct:ArgumentPositional:Implemented x 3
PlConstruct:AssignmentStatement:Implemented
PlConstruct:BlockStatement:Implemented
PlConstruct:LoopWhile:Implemented
PlType:SimpleDouble:Implemented x 3
PlExpression:Add:Implemented
PlExpression:Constant:Implemented x 4
PlExpression:Div:Implemented x 2
PlExpression:Gt:Implemented x 2
PlExpression:Identifier:Implemented x 5
PlExpression:LogicalAnd:Implemented
PlExpression:Lt:Implemented
PlExpression:Mul:Implemented
PlExpression:PlsqlFunction:Implemented x 3
PlExpression:Sub:Implemented
PlBuiltinFunctionCall:ABS:PartiallyImplemented
PlBuiltinFunctionCall:DBMS_OUTPUT.PUT_LINE:PartiallyImplemented
PlUserFunctionCall:CW.ASSERT_TRUE:Implemented

Hello World in PL/SQL

Here’s a basic PL/SQL version of “Hello World”:

DECLARE
   message  varchar2(20):= 'Hello, World!';
BEGIN
   dbms_output.put_line(message);
EXCEPTION
   WHEN PROGRAM_ERROR THEN
      dbms_output.put_line('Uh-oh; something went wrong');
END;
/

The single / at the end of the program block is required to tell the PL/SQL interpreter that it should run the code block; if you leave the / out, the interpreter will simply wait for more input after loading the block.

Storing Your PL/SQL Code

All PL/SQL procedures that you create with Splice*Plus are stored in the SYS.SYSSOURCECODE table in your database.

You can specify that your code should not be stored in your database with the --debug DISABLE_PERSISTENCE command line option.