Hi Guys,
I'm trying to BULK COLLECT into a PL/SQL table but I keep getting the ORA-00947: not enough values error message, even though the table has 4 values and the select has 4 values. Am I missing something?
Do I need to add something to this?
I've included the database object types I've created on the database.
I've commented out the Original code and used the table DUAL just to make it simple to workout.
/*
CREATE OR REPLACE TYPE Usage_Groups_for_coda_rec AS
object
(Usage_Group_ID NUMBER(10),
Coda_comment VARCHAR2(45),
Amount NUMBER,
Deduction_amount NUMBER);
CREATE OR REPLACE
TYPE USAGE_GROUPS_FOR_CODA_TAB AS
TABLE OF Usage_Groups_for_coda_rec;
*/
declare
-- CURSOR c_adj_roy_trans
-- IS
-- SELECT DISTINCT rotr.on_behalf_of_soc_nbr, rotr.right_type
-- FROM royalty_transaction rotr
-- WHERE rotr.ps_adjust_royalty_flg = cm_default.get_yes;
CURSOR c_adj_roy_trans
IS
SELECT '052', 'P'
FROM dual;
t_uge_Grp_for_coda_tab USAGE_GROUPS_FOR_CODA_TAB; -- the table type has been created on the database
begin
FOR r_adj_roy_trans IN c_adj_roy_trans LOOP
-- SELECT rotr.usage_group_id as Usage_Group_ID,
-- cm_coda_account_default.get_canc_adj_coda_comment || ' - CAE ' as Coda_comment,
-- SUM(NVL(rotr.gross_amt, 0) + NVL(rotr.reciprocal_deduction_amt, 0)) as Amount,
-- SUM(rotr.reciprocal_deduction_amt) as Deduction_amount
-- BULK COLLECT INTO t_uge_Grp_for_coda_tab
-- FROM royalty_transaction rotr
-- WHERE rotr.ps_adjust_royalty_flg = cm_default.get_yes
-- AND rotr.on_behalf_of_soc_nbr = r_adj_roy_trans.on_behalf_of_soc_nbr
-- AND rotr.right_type = r_adj_roy_trans.right_type
-- group by rotr.usage_group_id;
SELECT 6874534 as Usage_Group_ID,
'This is a test - CAE ' as Coda_comment,
100 as Amount,
50 as Deduction_amount
BULK COLLECT INTO t_uge_Grp_for_coda_tab
FROM dual;
/*
IF l_uge_Grp_for_coda_tab.COUNT > 0 THEN
cm002p.std_coda_post_cashing_out_bulk( p_on_behalf_of_society_number => r_adj_roy_trans.on_behalf_of_soc_nbr,
p_right_type => r_adj_roy_trans.right_type,
p_Usage_Groups_for_coda_tab => t_uge_Grp_for_coda_tab,
p_reverse_posting_direction => FALSE,
p_posting_override_direction => NULL,
p_cohi_id => NULL
);
END IF;
*/
END LOOP;
end;