Forum: Ruby Ruby with Oracle forward slash error

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
A2d122882fe5203d772a38f4c2231a0d?d=identicon&s=25 Sam Sang (duffman)
on 2008-10-07 23:21
Hi,

I am a complete newbie to Ruby, so if this is not the right forum for
this, please let me know.  We're trying to do some migration using ruby
(on Oracle) and I get an error that I don't really understand.

One of the components is the migration of a file that defines Oracle
Types.  Below is a snippet of the sql defined (the real sql is more
complicated -- I stripped it down to this simplified version that
outputs the same error).


-- RUBY file

class CrosstabReportsObjectType < ActiveRecord::Migration
  def self.up
        type = File.open(File.join(File.dirname(__FILE__), 'type',
'testSQL.sql')).read
        execute(type)

  end

  def self.down
        execute('drop type crosstab_report_tbl')
    execute('drop type crosstab_report_obj')
  end
end


-- SQL File (testSQL.sql)

BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE crosstab_report_tbl';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/
create table tester2 (counter int);
/


-- ERROR Message
rake aborted!
OCIError: ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "/" : BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE crosstab_report_tbl';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/
create table arisdw.tester2 ( counter int);
/

I get the above error when I try to run it.  The forward slash seems to
bother the script when its run -- any ideas on how I can make this run?

Thanks in advance.
E0d864d9677f3c1482a20152b7cac0e2?d=identicon&s=25 Robert Klemme (Guest)
on 2008-10-09 09:37
(Received via mailing list)
2008/10/7 Sam Sang <younoeme@hotmail.com>:
>
>
> -- RUBY file
>
> class CrosstabReportsObjectType < ActiveRecord::Migration
>  def self.up
>        type = File.open(File.join(File.dirname(__FILE__), 'type',
> 'testSQL.sql')).read

Maybe you can try this additional line:

type.gsub! %r{^/}, ''

>
>
> create table arisdw.tester2 ( counter int);
> /
>
> I get the above error when I try to run it.  The forward slash seems to
> bother the script when its run -- any ideas on how I can make this run?

See above. But normally I'd parse the text in chunks using the slash
at the beginning of the line as delimiter and submit statements
separately.

Kind regards

robert
A2d122882fe5203d772a38f4c2231a0d?d=identicon&s=25 Sam Sang (duffman)
on 2008-10-16 00:10


Robert Klemme wrote:
> 2008/10/7 Sam Sang <younoeme@hotmail.com>:
>>
>>
>> -- RUBY file
>>
>> class CrosstabReportsObjectType < ActiveRecord::Migration
>>  def self.up
>>        type = File.open(File.join(File.dirname(__FILE__), 'type',
>> 'testSQL.sql')).read
>
> Maybe you can try this additional line:
>
> type.gsub! %r{^/}, ''

Many thanks for the reply Robert.  I suppose this command tries to
replace the slash in the sql file.  When executed, I now get an error

PLS-00103: Encountered the symbol "CREATE" : BEGIN.



> See above. But normally I'd parse the text in chunks using the slash
> at the beginning of the line as delimiter and submit statements
> separately.

Not sure what you mean.. could you please elaborate a bit?

Thanks again,
E0d864d9677f3c1482a20152b7cac0e2?d=identicon&s=25 Robert Klemme (Guest)
on 2008-10-16 09:05
(Received via mailing list)
On 16.10.2008 00:10, Sam Sang wrote:
> Robert Klemme wrote:

>> See above. But normally I'd parse the text in chunks using the slash
>> at the beginning of the line as delimiter and submit statements
>> separately.
>
> Not sure what you mean.. could you please elaborate a bit?

Instead of submitting the whole file as a single statement (string) to
the database, parse (or split) it into chunks (using "^/" as delimiter)
and submit those chunks one by one.

Kind regards

  robert
6ece05caebbfa91944047629101bc5ea?d=identicon&s=25 Takehiro Kubo (kubo)
on 2008-10-16 13:43
(Received via mailing list)
On Thu, Oct 16, 2008 at 4:04 PM, Robert Klemme
<shortcutter@googlemail.com> wrote:
>>> See above. But normally I'd parse the text in chunks using the slash
>>> at the beginning of the line as delimiter and submit statements
>>> separately.
>>
>> Not sure what you mean.. could you please elaborate a bit?
>
> Instead of submitting the whole file as a single statement (string) to the
> database, parse (or split) it into chunks (using "^/" as delimiter) and
> submit those chunks one by one.

It is what sqlplus and other Oracle tools do. Oracle cannot run more
than
one bare SQL statement or PL/SQL block at once.

Another (unusual) way is submitting a PL/SQL block which contains SQL
statements as follows:

BEGIN
   BEGIN
      EXECUTE IMMEDIATE 'DROP TYPE crosstab_report_tbl';
   EXCEPTION
      WHEN OTHERS THEN NULL;
   END;
   EXECUTE IMMEDIATE 'create table tester2 (counter int)';
END;
A2d122882fe5203d772a38f4c2231a0d?d=identicon&s=25 Sam Sang (duffman)
on 2008-10-16 18:59
Ok -- see what you mean, and doing that does make it a lot better!  Just
one last question in this regard..

Executing chunks works fine -- an issue is that if I have a few extra
empty lines (\n's) at the end of the file it will also try to execute
them and end up getting an

-- execute(nil)
rake aborted!
OCIError: ORA-24373: invalid length specified for statement:


This is what I am currently using:

string.each('/') {|s| execute(s.gsub! %r{^/}, '')}

My two thoughts are:

1) in between put in a if statement that checks for nils and only
execute if not nil.  something like
string.each('/') {|s| if s != nil execute(s.gsub! %r{^/}, '')}


2) do something like (string.gsub! %r{^/}, '').gsub! %r{^nil}, '')


I know that both of these syntax's are definitely incorrect but I
couldn't find material with which I could rectify it.

Could you point me towards how to do an if statement inside the each
function in ruby?  Or do another search-replace for nil's?

Thanks!


>
> Instead of submitting the whole file as a single statement (string) to
> the database, parse (or split) it into chunks (using "^/" as delimiter)
> and submit those chunks one by one.
>
> Kind regards
>
>   robert
Ef3aa7f7e577ea8cd620462724ddf73b?d=identicon&s=25 Rob Biedenharn (Guest)
on 2008-10-16 19:09
(Received via mailing list)
On Oct 16, 2008, at 12:59 PM, Sam Sang wrote:

> OCIError: ORA-24373: invalid length specified for statement:
>
>
> This is what I am currently using:
>
> string.each('/') {|s| execute(s.gsub! %r{^/}, '')}

Don't use gsub! in this case.  You just want gsub (read about what
gsub! returns if it makes no change in string).

-Rob

>
>> Instead of submitting the whole file as a single statement (string)
> Posted via http://www.ruby-forum.com/.
>

Rob Biedenharn    http://agileconsultingllc.com
Rob@AgileConsultingLLC.com
A2d122882fe5203d772a38f4c2231a0d?d=identicon&s=25 Sam Sang (duffman)
on 2008-10-16 20:07
Ah -- of course!  Ok, that one is resolved.  More generically how do we
handle chunks of code in Ruby?  Like in this case I have a lot of sql
statements seperated by "/".  After the last "/" there might be some
empty lines, or some text which I want to ignore.

I tried using,
type.each('/') {|s| execute( ((s.gsub %r{^/}, '')).gsub %r{^\n}, '')}
but that gave me an error

-- execute("")
rake aborted!
OCIError: ORA-24373: invalid length specified for statement:


I want to ensure that anything that doesn't end in a "/" should never be
executed.

Thanks much for your help again -- have been programming server side
Java and am new to this stuff.

> Don't use gsub! in this case.  You just want gsub (read about what
> gsub! returns if it makes no change in string).
>
> -Rob
>
>>
>>> Instead of submitting the whole file as a single statement (string)
>> Posted via http://www.ruby-forum.com/.
>>
>
> Rob Biedenharn    http://agileconsultingllc.com
> Rob@AgileConsultingLLC.com
Ef3aa7f7e577ea8cd620462724ddf73b?d=identicon&s=25 Rob Biedenharn (Guest)
on 2008-10-16 20:51
(Received via mailing list)
show an example of what type might contain and you'll get more help.

-Rob
A2d122882fe5203d772a38f4c2231a0d?d=identicon&s=25 Sam Sang (duffman)
on 2008-10-16 21:28
############# BEGINNING OF SQL FILE

BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE crosstab_report_tbl';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/
CREATE OR REPLACE TYPE agg_obj AS OBJECT
(
        state_sid                 NUMBER(22),
        state_code                VARCHAR2(32),
);
/

CREATE OR REPLACE TYPE agg_tbl AS TABLE OF agg_obj;
/

              # 2 empty lines at the end of the file
############ ENDOF SQL FILE


################# BEGINNING OF RUBY FILE
class ArisGrowthReportObjectType < ActiveRecord::Migration
  def self.up


          type =
File.open(File.join(File.dirname(__FILE__),'test.sql')).read
          type.each('/') {|s| execute( (s.gsub %r{^/}, '')) }
  end

  def self.down
  end
end


################# END OF RUBY FILE


This throws the error:
-- execute("\n\nCREATE OR REPLACE TYPE agg_tbl AS TABLE OF agg_obj;\n")
   -> 0.0310s
   -> 0 rows
-- execute("\n\n")
rake aborted!
OCIError: ORA-00900: invalid SQL statement:


(See full trace by running task with --trace)


Thanks!



Rob Biedenharn wrote:
> show an example of what type might contain and you'll get more help.
>
> -Rob
134ea397777886d6f0aa992672a50eaa?d=identicon&s=25 Mark Thomas (Guest)
on 2008-10-16 21:35
(Received via mailing list)
On Oct 16, 2:06 pm, Sam Sang <youno...@hotmail.com> wrote:
> Ah -- of course!  Ok, that one is resolved.  More generically how do we
> handle chunks of code in Ruby?  Like in this case I have a lot of sql
> statements seperated by "/".  After the last "/" there might be some
> empty lines, or some text which I want to ignore.

Maybe something like this?

statements = type.split(%r{^/})

statements.each do |s|
  execute(s) unless s.strip == ""
end

-- Mark.
A2d122882fe5203d772a38f4c2231a0d?d=identicon&s=25 Sam Sang (duffman)
on 2008-10-16 21:43
Perfect..!  Was looking for something exactly like this.  Many thanks
for all your help Robert, and thanks for this Mark.

Much appreciated.  Just getting started with Ruby, but reminds me a lot
of perl.

Cheers,

> Maybe something like this?
>
> statements = type.split(%r{^/})
>
> statements.each do |s|
>   execute(s) unless s.strip == ""
> end
>
> -- Mark.
This topic is locked and can not be replied to.