Ruby with Oracle forward slash error

S

Sam Sang

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.
 
R

Robert Klemme

2008/10/7 Sam Sang said:
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

Maybe you can try this additional line:

type.gsub! %r{^/}, ''
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?

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
 
S

Sam Sang

Robert said:
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,
 
R

Robert Klemme

Robert Klemme wrote:

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
 
K

KUBO Takehiro

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;
 
S

Sam Sang

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!
 
R

Rob Biedenharn

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{^/}, '')}

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

-Rob
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!

Rob Biedenharn http://agileconsultingllc.com
(e-mail address removed)
 
S

Sam Sang

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.
 
S

Sam Sang

############# 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!
 
M

Mark Thomas

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.
 
S

Sam Sang

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top