Ruby with Oracle forward slash error


#1

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.


#2

2008/10/7 Sam S. removed_email_address@domain.invalid:

– 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


#3

Robert K. wrote:

2008/10/7 Sam S. removed_email_address@domain.invalid:

– 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,


#4

On 16.10.2008 00:10, Sam S. wrote:

Robert K. 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


#5

On Thu, Oct 16, 2008 at 4:04 PM, Robert K.
removed_email_address@domain.invalid 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;


#6

On Oct 16, 2008, at 12:59 PM, Sam S. 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 B. http://agileconsultingllc.com
removed_email_address@domain.invalid


#7

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


#8

show an example of what type might contain and you’ll get more help.

-Rob


#9

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 B. http://agileconsultingllc.com
removed_email_address@domain.invalid


#10

############# 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 B. wrote:

show an example of what type might contain and you’ll get more help.

-Rob


#11

On Oct 16, 2:06 pm, Sam S. removed_email_address@domain.invalid 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.


#12

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.