0

I would need to process the content of a script that typically would be processed by Oracle SQLPlus within an application.

The scripts contains blocks like:

  • comments (-- this is a comment)
  • SQLPlus commandos (set echo off)
  • plain SQL statements (select * from dual;)
  • PL/SQL statements like (create function foo returning number as begin return 1; end;/)

and I would need to execute the individual blocks individually.

Is there a reasonably simple way to tokenize the statements in JavaScript or would I need a full blown tokenizer that fully understand the SQLPlus syntax?

Improve this question
2
  • if some of the commands or SQL syntax are specific to sqlplus (e.g. set echo off, or variable substitution using &), they won't translate to anything else. Commented Apr 25, 2021 at 11:57
  • I understand that the sqlplus specific syntax needs to be processed separately but my main problem is how to "separate" them into individual tokens without a full blown parser. Commented Apr 25, 2021 at 12:06

1 Answer 1

Reset to default
1

The simplest method is to assume that the SQL script was written in a reasonably readable manner and that there is either 1 statement-per-line or that statements are split over multiple lines and you will not have the case where there are multiple statements on a single line.

The majority of cases can be handled by:

Read the next line and trim any leading white-space:

  • If it starts with SET or COLUMN then you have a SQL/Plus directive which will (typically) consist of a single line; that line is your statement.
  • If it starts with CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, DECLARE or BEGIN (and a following white-space character) then you will have a PL/SQL block that will be terminated with a / on a separate line and you can read lines until you have found it and that is your complete statement.
  • If it starts with -- then it is a comment and you can skip the rest of the line and prepend it to the next statement (since comments are part of the next statement).
  • If it starts with /* then it is an inline comment and you can read until the next */ and then repeat by processing the rest of the line (the comment will be prepended to the next statement).
  • Otherwise, read until the next ; that is at the end of a line (or followed by a comment) (or / on a separate line) and that is your complete statement.

Then repeat, reading the next line.

This is not guaranteed to work on 100% of cases (i.e. if you have multi-line string literals which happen to have a ; at then end of a line within the literal). You should review the output to see if there are any additional cases that need to be handled but for relatively simple scripts it should work.

If you have developers that like writing (less readable) scripts with multiple statements on the same line then you may need to implement a parser.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.