Hi,
My DB is 9i.
I run a script that deletes all records from a table, and then repopulates based on a SQL insert.
This works fine.
I try to run the same combination but with...
BEGIN
END;
...around the code, all other things being equal, and the script errors, complaining that my combination of stragg and distinct is not valid.
"ORA-06550: line 36, column 22:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored"
This being the offending line>
, apps.stragg(distinct substr(cmt_code,1,2)) parent_types
But it does work as pure SQL....
Stragg, being a string aggregation text summary function that I picked up courtesy of 'Ask Tom' - shameless plug!
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
My full code, is below - anyone have any idea why this would fail in an anonymous block run as exactly the same user???
Thanks for your time,
Robert
begin
delete cust.xx_eris_users_all;
insert into cust.xx_eris_users_all
(RAW_ENCRYPTED_KEY,
NAME,
E_MAIL,
COST_CENTRES,
NUM_COST_CENTRES,
PARENT_CODES,
PARENT_TYPES,
NUM_OF_PARENTS,
PERIOD_NAME,
PERIOD_NUM,
PERIOD_YEAR,
QUARTER_NUM
)
(select UTL_RAW.CAST_TO_RAW(isis.inner_text_link_1) raw_encrypted_key
, isis.inner_attribute_1 name
, isis.inner_attribute_2 e_mail
, apps.stragg(eris.cost_centre) cost_centres
, count(eris.cost_centre) num_cost_centres
, inna.parent_codes
, inna.parent_types
, count(inna.parent_codes) num_of_parents
, to_char(add_months(sysdate,-1),'MON-YY') period_name
, prd.period_num
, prd.period_year
, prd.quarter_num
from cust.xx_isis_all isis
, gl.gl_periods prd
, CUST.XX_ERIS_COST_CENTRE_SECURE_MV eris
, (select encrypted_key
, apps.stragg(cmt_code) parent_codes
, apps.stragg(distinct substr(cmt_code,1,2)) parent_types
from CUST.XX_ERIS_CMT_SECURE_MV eriscmt
group by
encrypted_key) inna
where isis.isis_protocol_id = 'XX_ERIS_USER'
and isis.inner_attribute_1 <> 'ALL'
--and isis.inner_attribute_1 = 'Robert Angel'
and eris.encrypted_key(+) = isis.inner_text_link_1
and inna.encrypted_key(+) = isis.inner_text_link_1
and prd.period_name = to_char(add_months(sysdate,-1),'MON-YY')
group by
UTL_RAW.CAST_TO_RAW(isis.inner_text_link_1)
, isis.inner_attribute_1
, isis.inner_attribute_2
, inna.parent_codes
, inna.parent_types
, prd.period_num
, prd.period_year
, prd.quarter_num);
end;
Edited by: Robert Angel on 11-Nov-2010 02:02 - Added Ask Tom link to summary function