Re: Help with running PL/SQL block through Bering
Rhett Sutphin
On Jan 6, 2010, at 11:28 AM, ramakrishna gundala wrote:
> Hi Rhett,
>
> I have this code in a .sql file.
>
> drop sequence consents_id_seq;
> drop sequence study_versions_id_seq;
> drop sequence study_subject_consents_id_seq;
> drop sequence study_site_versions_id_seq;
> drop sequence stu_site_status_history_id_seq;
> drop sequence study_subject_versions_id_seq;
>
> declare
> l_cnt NUMBER(10,0);
> begin
> execute immediate 'select max(id) from consents' into l_cnt;
> execute immediate'CREATE SEQUENCE consents_id_seq INCREMENT BY 1
> START WITH ' || NVL( l_cnt, 1 ) || 'NOMAXVALUE MINVALUE 1 NOCYCLE
> NOCACHE NOORDER';
> execute immediate 'select max(id) from study_versions' into l_cnt;
> execute immediate'CREATE SEQUENCE study_versions_id_seq INCREMENT
> BY 1 START WITH ' || NVL( l_cnt, 1 ) || 'NOMAXVALUE MINVALUE 1
> NOCYCLE NOCACHE NOORDER';
> execute immediate 'select max(id) from study_subject_consents' into
> l_cnt;
> execute immediate'CREATE SEQUENCE study_subject_consents_id_seq
> INCREMENT BY 1 START WITH ' || NVL( l_cnt, 1 ) || 'NOMAXVALUE
> MINVALUE 1 NOCYCLE NOCACHE NOORDER';
> execute immediate 'select max(id) from study_site_versions' into
> l_cnt;
> execute immediate'CREATE SEQUENCE study_site_versions_id_seq
> INCREMENT BY 1 START WITH ' || NVL( l_cnt, 1 ) || 'NOMAXVALUE
> MINVALUE 1 NOCYCLE NOCACHE NOORDER';
> execute immediate 'select max(id) from stu_site_status_history'
> into l_cnt;
> execute immediate'CREATE SEQUENCE stu_site_status_history_id_seq
> INCREMENT BY 1 START WITH ' || NVL( l_cnt, 1 ) || 'NOMAXVALUE
> MINVALUE 1 NOCYCLE NOCACHE NOORDER';
> execute immediate 'select max(id) from study_subject_versions' into
> l_cnt;
> execute immediate'CREATE SEQUENCE study_subject_versions_id_seq
> INCREMENT BY 1 START WITH ' || NVL( l_cnt, 1 ) || 'NOMAXVALUE
> MINVALUE 1 NOCYCLE NOCACHE NOORDER';
> end
>
> commit;
Side note: you shouldn't need to commit inside a bering migration.
Bering will automatically commit if the migration is successful. This
is somewhat less important with oracle since it automatically commits
after every DDL statement.
> I am trying to run this as a whole via the external (fileName..)
> function from a .groovy file.
> external("../../oracle/set_study_versioning_tables_sequences.sql")
>
> I haven't tried splitting it up. I got stuck in the variable
> declaration line itself ( l_cnt NUMBER(10,0);)
I didn't mean that you should split it up -- in order to execute the
code using JDBC, Bering has to split it up in separate statements.
(SQLDeveloper has to do this internally also.) In Bering's log
output, you can see how it split it up.
In any case, I see the problem. In an attempt to avoid having to
write a full PL/SQL parser in Bering's oracle dialect, I implemented
some basic heuristics to extract PL/SQL blocks from scripts. Those
heuristics don't/won't work on your code for a few reasons:
A) The dialect expects a script with PL/SQL to contain several PL/SQL
blocks and that each block will be separated from the next by a
forward slash on a line by itself. Without this lone forward slash,
it won't detect that there's any PL/SQL. You could work around this
by ending your script with a forward slash. (Actually, I'm noticing
now that the split regex is a bit overspecified -- you'll need to end
with a forward slash followed by a blank line.)
B) The dialect also implicitly assumes that PL/SQL blocks start with
CREATE and don't otherwise include CREATE. Yours breaks both of those
assumptions, so even if you work around (A), it still won't work.
(B) is a substantially harder problem to address. I can think of two
ways to fix it:
1) Find / implement a java PL/SQL parser. Extend the oracle dialect
to use it. Upsides: you could do this without modifying bering itself
since the dialect is configurable -- write your own that extends the
built-in one and overrides #separateStatements(String). Downsides:
obviously, PL/SQL is not a trivial thing to parse.
2) Add another method (or an option to #execute) to Migration which
executes a single statement without trying to split it up. Upsides:
this would provide a general workaround for future script parsing
issues. Downsides: would not be compatible with sourcing the script
from an external file.
(2) is a pretty small code change. If you'd like to give it a shot,
I'd be happy to answer questions / accept patches (with tests).
Rhett
(CC'ing the bering discussion group for future reference.)
>
> What I am trying to do is to set the sequence of a table to a value
> returned by a query. Looks like there is no easy way to do this in
> Oracle.
>
> Thanks,
> Ramakrishna
>
>
>
> On Wed, Jan 6, 2010 at 12:13 PM, Rhett Sutphin <rhett....@gmail.com
> > wrote:
> Hi Ramakrishna,
>
>
> On Jan 6, 2010, at 10:01 AM, ramakrishna gundala wrote:
>
> Hi Rhett,
>
> I am trying to run a PL/SQL block through Bering, but I am
> constantly running into this error.
>
> PLS-00103: Encountered the symbol "end-of-file" when expecting one
> of the
> following:
>
>
> I am able to run the same piece of code directly from sqldeveloper
> client. The issue is coming when it encounters a ; (semi-colon).
>
> Can you please let me know if you ever faced this problem ?
>
> I haven't seen this error in particular. Bering's oracle dialect
> has some code to handle splitting up PL/SQL into separate statements
> -- it attempts to follow the syntax that works with sqlplus, but I
> am not an expert in this area and so it probably needs improvement.
> What are you trying to run, specifically? How does bering split it
> up and how would you expect it to split it up?
>
> Rhett
>
>
> Thanks,
> Ramakrishna
>
>
>
>
>
> Ramakrishna Gundala
> C3PR Developer
> SemanticBits LLC
> ramakrish...@semanticbits.com
>
> Office: 703-787-9656 x227
> Mobile: 803-240-6163
> www.semanticbits.com
>
>