SQL & PL/SQL

Hi,

i had some sql who were working fine in sql/plus with Rdbms 10g. The same query give me a ORA-00910 in sql/plus with Rdbms 11Gr2.

WITH tab1 AS (
SELECT owner, table_name, action_name,
RANK() OVER(PARTITION BY table_name ORDER BY action_name) AS rang from (
SELECT DISTINCT owner, table_name, action_name
FROM TABLE_AUDIT_TAB_COLUMN
WHERE owner LIKE UPPER('&1%')
ORDER BY owner, table_name, action_name)
),
tab2 AS (
SELECT owner, table_name, action_name, STRAGG(column_name) column_name, policy_name
FROM TABLE_AUDIT_TAB_COLUMN
WHERE owner LIKE UPPER('&1%')
GROUP BY owner, table_name, action_name, policy_name
ORDER BY owner, table_name, action_name, policy_name
)
SELECT substr(
'BEGIN '||chr(13)||chr(10)
||'DBMS_FGA.Add_Policy (OBJECT_NAME => '' '||A.table_name||''','||chr(13)||chr(10)
||' OBJECT_SCHEMA => '''||A.owner||''''||', '||chr(13)||chr(10)
--||' POLICY_NAME => ''AUD_'||A.table_NAME||''','||chr(13)||chr(10)
||' POLICY_NAME => '''||NVL(B.POLICY_NAME, A.table_name||'_'||TO_CHAR(A.rang))||''','||chr(13)||chr(10)
||' AUDIT_CONDITION => ''1=1'','||chr(13)||chr(10)
||DECODE(TRIM(B.column_name),NULL,'',' AUDIT_COLUMN => '''||TRIM(B.column_name)||''','||chr(13)||chr(10))
||' ENABLE => TRUE,'||chr(13)||chr(10)
||' statement_types=> '''||A.action_name||''','||chr(13)||chr(10)
||' HANDLER_SCHEMA=>''AGRT997'',HANDLER_MODULE=>''PROC_INSERT_FGA_JNLJOURNAL_REC''); '||chr(13)||chr(10)
||'END;'||chr(13)||chr(10)
||'/ ',1,718) ligne_commande
FROM tab1 A, tab2 B, dba_objects C
WHERE B.owner = A.owner
AND B.table_name = A.table_name
AND B.action_name = A.action_name
AND C.owner = A.owner
AND C.object_type IN('TABLE','VIEW')
AND C.object_name = A.table_name
ORDER BY 1;

If i change the substr from a lenght 718 to 720, i will get ORA-00910.


Why it was working in 10G and no more in 11G, is it a bug?

Is there a parameter i sould change so the same sql will work in 11G?