I read some forum posts and did more googling. Here are some additional details
1: I’m now running it as they “system” user which is at least getting me connected to oracle just fine.
2: I’m calling my script like this
declare
l_SQL varchar2(4000);
begin
for cur in (
select * from dba_tables where owner = ‘TEST_MYAPP’)
loop
l_sql := ‘grant select, insert, update, delete on ’ || cur.owner || ‘.’ || cur.table_name || ’ to MYAPP_RW_ROLE’;
--dbms_output.put_line(l_SQL || ‘;’);
execute immediate l_SQL;
end loop;
end;
declare
l_SQL varchar2(4000);
begin
for cur in (
select * from dba_sequences where sequence_owner = ‘TEST_MYAPP’)
loop
l_sql := ‘grant select on ’ || cur.sequence_owner || ‘.’ || cur.sequence_name || ’ to MYAPP_RW_ROLE’;
--dbms_output.put_line(l_SQL || ‘;’);
execute immediate l_SQL;
end loop;
end;
3: and this is the error i’m getting
Liquibase update Failed: Migration failed for change set dba_liquibase.xml::1::jeffm:
Reason: liquibase.exception.DatabaseException: Error executing SQL declare
l_SQL varchar2(4000);
begin
for cur in (
select * from dba_tables where owner = ‘TEST_MYAPP’)
loop
l_sql := ‘grant select, insert, update, delete on ’ || cur.owner || ‘.’ || cur.table_name || ’ to MYAPP_RW_ROLE’;
--dbms_output.put_line(l_SQL || ‘;’);
execute immediate l_SQL;
end loop;
end;
declare
l_SQL varchar2(4000);
begin
for cur in (
select * from dba_sequences where sequence_owner = ‘TEST_MYAPP’)
loop
l_sql := ‘grant select on ’ || cur.sequence_owner || ‘.’ || cur.sequence_name || ’ to MYAPP_RW_ROLE’;
--dbms_output.put_line(l_SQL || ‘;’);
execute immediate l_SQL;
end loop;
end;: ORA-06550: line 13, column 1:
PLS-00103: Encountered the symbol “DECLARE”
ORA-06550: line 23, column 4:
PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<a double-quoted
