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?
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.
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;
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
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{^/}, ‘’)}
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.
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
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.
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