[Liquibase-user] Oracle execute procedure problem

[Pradnya Gawade]

Hi,

I am trying to create a procedure and run it on oracle 10g database.
The procedure gets created successfully but never gets executed.
Because I can see the procedure listed under Procedures through the
SQL developer but do not see the expected tables updated which is done
inside procedure. If I compile and run the procedure manually through
SQL developer, it does the correct job.
Initially the create procedure and execute procedure was placed inside
the same changeset within <sql>..</sql> block. But because procedure
do not get exeuted I am tries to place them in separate changesets. In
this case I get "ORA-00900: invalid SQL statement" exception. Could
any body please suggest the solution. Following is the liqiobase code
I tried in the 2 cases cases I mentioned:

Approach 1:

<changeSet author="kkrumlian" id="2011-03-01-4575-2" dbms="oracle">
<comment>Add a row to rule_action_run and update rule_action
row</comment>
<sql splitStatements="false">
CREATE OR REPLACE Procedure add_row_to_rule_action_run
IS
cursor c1 is select id from rule_action where
rule_action_run_id is null;
BEGIN
FOR cc in c1
LOOP
INSERT INTO RULE_ACTION_RUN
(administrative_data_entry,initial_data_entry,double_data_entry,import_data_entry,batch,version)
VALUES (1, 1, 1, 1, 1,0);
UPDATE rule_action set rule_action_run_id =
RULE_ACTION_RUN_ID_SEQ.currval where id = cc.id;
END LOOP;
RETURN;
END;
EXECUTE add_row_to_rule_action_run();
DROP PROCEDURE add_row_to_rule_action_run;
</sql>
</changeSet>

Approach 2:

<changeSet author="kkrumlian" id="2011-03-01-4575-2" dbms="oracle">
<comment>Add a row to rule_action_run and update rule_action
row</comment>
<sql splitStatements="false">
CREATE OR REPLACE Procedure add_row_to_rule_action_run
IS
cursor c1 is select id from rule_action where
rule_action_run_id is null;
BEGIN
FOR cc in c1
LOOP
INSERT INTO RULE_ACTION_RUN
(administrative_data_entry,initial_data_entry,double_data_entry,import_data_entry,batch,version)
VALUES (1, 1, 1, 1, 1,0);
UPDATE rule_action set rule_action_run_id =
RULE_ACTION_RUN_ID_SEQ.currval where id = cc.id;
END LOOP;
RETURN;
END;

</sql>
</changeSet>
<changeSet author="kkrumlian" id="2011-03-01-4575-2-test123" dbms="oracle">
<comment>Execute procedure add_row_to_rule_action_run</comment>
<sql splitStatements="false">
EXECUTE add_row_to_rule_action_run();
</sql>
</changeSet>

Thank you,
Pradnya

[Pradnya Gawade]

Hi,

Please excuse me if I am sending it twice as because of the network
issue, I am not sure the one I sent couple of seconds back got posted
or not.

I am trying to create a procedure and run it on oracle 10g database.
The procedure gets created successfully but never gets executed.
Because I can see the procedure listed under Procedures through the
SQL developer but do not see the expected tables updated which is done
inside procedure. If I compile and run the procedure manually through
SQL developer, it does the correct job.
Initially the create procedure and execute procedure was placed inside
the same changeset within <sql>..</sql> block. But because procedure
do not get exeuted I am tries to place them in separate changesets. In
this case I get "ORA-00900: invalid SQL statement" exception. Could
any body please suggest the solution. Following is the liqiobase code
I tried in the 2 cases cases I mentioned:

Approach 1:

<changeSet author="kkrumlian" id="2011-03-01-4575-2" dbms="oracle">
<comment>Add a row to rule_action_run and update rule_action
row</comment>
<sql splitStatements="false">
CREATE OR REPLACE Procedure add_row_to_rule_action_run
IS
cursor c1 is select id from rule_action where
rule_action_run_id is null;
BEGIN
FOR cc in c1
LOOP
INSERT INTO RULE_ACTION_RUN
(administrative_data_entry,initial_data_entry,double_data_entry,import_data_entry,batch,version)
VALUES (1, 1, 1, 1, 1,0);
UPDATE rule_action set rule_action_run_id =
RULE_ACTION_RUN_ID_SEQ.currval where id = cc.id;
END LOOP;
RETURN;
END;
EXECUTE add_row_to_rule_action_run();
DROP PROCEDURE add_row_to_rule_action_run;
</sql>
</changeSet>

Approach 2:

<changeSet author="kkrumlian" id="2011-03-01-4575-2" dbms="oracle">
<comment>Add a row to rule_action_run and update rule_action
row</comment>
<sql splitStatements="false">
CREATE OR REPLACE Procedure add_row_to_rule_action_run
IS
cursor c1 is select id from rule_action where
rule_action_run_id is null;
BEGIN
FOR cc in c1
LOOP
INSERT INTO RULE_ACTION_RUN
(administrative_data_entry,initial_data_entry,double_data_entry,import_data_entry,batch,version)
VALUES (1, 1, 1, 1, 1,0);
UPDATE rule_action set rule_action_run_id =
RULE_ACTION_RUN_ID_SEQ.currval where id = cc.id;
END LOOP;
RETURN;
END;

</sql>
</changeSet>
<changeSet author="kkrumlian" id="2011-03-01-4575-2-test123" dbms="oracle">
<comment>Execute procedure add_row_to_rule_action_run</comment>
<sql splitStatements="false">
EXECUTE add_row_to_rule_action_run();
</sql>
</changeSet>

Thank you,
Pradnya