SQL & PL/SQL

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