Running Oracle 11g SQL*plus script generates ORA-06650 "end-of-file"

[GoogleCodeExporter]

I have the following SQL*Plus script:

DECLARE
  -- --------------------------------------------------------------------------
  -- Proc to do inserts into AOR Table
  -- --------------------------------------------------------------------------
  PROCEDURE sync_aor (
            i_aor_id      casm_owner.aor.aor_id%TYPE
          , i_aor_name    casm_owner.aor.aor_name%TYPE
  ) IS
  BEGIN
      MERGE INTO casm_owner.aor        aor
            USING (SELECT i_aor_id    AS aor_id
                        , i_aor_name  AS aor_name
                     FROM dual) sq
               ON (sq.aor_id = aor.aor_id)
             WHEN MATCHED THEN
                  -- basically a dummy statement here.  Don't need to do
anything
                  -- if we have a match.  Just leave this as it is.
                  UPDATE SET aor.aor_name = sq.aor_name
             WHEN NOT MATCHED THEN
                  -- Record doesn't exists so add it to the table
                  INSERT (aor_id, aor_name, created_dt, created_by)
                    VALUES(sq.air_id, sq.aor_name, SYSTIMESTAMP, USER);
  END;
BEGIN
  --AOR's from the casm development client
  sync_aor('E85E6CEB1AF28590092FBADAAC4C8FB0','Work Flow Test');
END;
/

Somehow, the script fails when I try to run it using this utility.  It
thinks the semi-colon(;) at the end of the INSERT is not there.  Therefore
it generates the error:  

Caused by: java.sql.SQLException: ORA-06550: line 1, column 568:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:

Original issue reported on code.google.com by globobla...@gmail.com on 28 Jan 2010 at 7:40

[GoogleCodeExporter]

Thanks for the bug submission.

At this point in time, Oracle isn't really supported by the db-migration 
framework.  We've had some 
success with it on simple projects, but we haven't implemented support for 
functions/procedures.

I'm not sure when or if I'll have time to add Oracle support.  I'll keep this 
issue around and update it 
when I have the time to spend time on Oracle support.

There are two things you can do to help if you're up for the task:

1) Attach a few other examples of valid oracle procedures, ideally each 
exercising some variations on 
syntax, etc.  These will become part of the test suite and will make it easier 
for me to enhance the sql 
parser logic for oracle.

2) Take a look at the source code and try adding support for Oracle.  I welcome 
patches.

Take care,
Christian

Original comment by christia...@gmail.com on 28 Jan 2010 at 8:36

[GoogleCodeExporter]

I will work on this and keep you posted.

Is there a way you can attach my e-mail address so I can get notifications when
updates are available.  It is:  loboblanco@earthlink.net

Thanks.

Original comment by globobla...@gmail.com on 28 Jan 2010 at 11:39

[GoogleCodeExporter]

Hello Chris,

I think I found the problem with the plugin.  Line 86 of the file
ScriptRunnerImpl.java is stripping all the lines of their delimiter ";"  Oracle
really needs to have that delimiter as it is the only way Oracle can determine 
it
found the end of a command.  This is particularly true when we are assembling
procedures and functions.  The solution I propose is to change the line to add 
a 1 to
that line to read something like this: lastIndexOf(...)+1

Please, let me know what you think and if you can add this to the code.
Thanks.

Original comment by globobla...@gmail.com on 29 Jan 2010 at 11:55

[GoogleCodeExporter]

According to oracle docs, there's a special case when semicolon is not a 
delimiter:

> SQL*Plus treats PL/SQL subprograms in the same manner as SQL commands, except 
that a semicolon (;) or a blank line does not terminate and execute a block. 
Terminate PL/SQL subprograms by entering a period (.) by itself on a new line. 
You can also terminate and execute a PL/SQL subprogram by entering a slash (/) 
by itself on a new line.
>
> You enter the mode for entering PL/SQL statements when:
>
> * You type DECLARE or BEGIN. After you enter PL/SQL mode in this way, type 
the remainder of your PL/SQL subprogram.
>
> * You type a SQL command (such as CREATE PROCEDURE) that creates a stored 
procedure. After you enter PL/SQL mode in this way, type the stored procedure 
you want to create.

The details are at 
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch4.htm#i1039663 

I solved it using a patched OracleScriptRunner, 
https://github.com/gxa/gxa/blob/updates/atlas-updates/src/main/java/uk/ac/ebi/gx
a/db/OracleScriptRunner.java — hope it helps.

If it makes sense to pack this hack into a patch and submit it to you, just let 
me know, and I will.

Original comment by alexey.f...@gmail.com on 15 Sep 2011 at 2:18

[GoogleCodeExporter]

The above mentioned runner fails on declarations, though.

E.g. the following declaration will be split:

CREATE OR REPLACE PACKAGE ATLASMGR IS
  PROCEDURE DisableConstraints;
  PROCEDURE EnableConstraints;
  PROCEDURE DisableTriggers;
  PROCEDURE EnableTriggers;
  PROCEDURE RebuildSequence(seq_name varchar2);
  PROCEDURE RebuildSequences;
  PROCEDURE RebuildIndex;
  PROCEDURE fix_sequence(tbl VARCHAR2, field VARCHAR2, seq VARCHAR2);
END ATLASMGR;
/

Original comment by alexey.f...@gmail.com on 15 Sep 2011 at 3:51

[GoogleCodeExporter]

Fixed now. Please find test samples attached.

Original comment by alexey.f...@gmail.com on 15 Sep 2011 at 7:24

Attachments:

• TestOracleScriptSplitter-package_with_body.sql
• TestOracleScriptSplitter-sample.sql
• TestOracleScriptSplitter-simple_statements.sql
• TestOracleScriptSplitter-trigger.sql

[GoogleCodeExporter]

The updated script runner is available at 
https://github.com/gxa/gxa/tree/updates/atlas-updates/src/main/java/uk/ac/ebi/gx
a/db (it's not a single file any more, sorry)

Original comment by alexey.f...@gmail.com on 15 Sep 2011 at 7:26