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
------------------------------------------------------------------------------
Special Offer -- Download ArcSight Logger for FREE!
Finally, a world-class log management solution at an even better
price-free! And you'll get a free "Love Thy Logs" t-shirt when you
download Logger. Secure your free ArcSight Logger TODAY!
http://p.sf.net/sfu/arcsisghtdev2dev
_______________________________________________
Liquibase-user mailing list
Liquibase-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/liquibase-user
