hi all,
When i try to excute the below script iam getting ORA-06550: line 3, column 10: PLS-00172: string literal too long
DECLARE
v_input CLOB
:= '0001F,0001T,0002F,0002T,0003F,0003T,0004F,0005F,0005T,0006F,0006T...........................................'; --here i am trying to asign length of 1376213 bytes
v_rule_name CLOB;
v_engine_name application.NAME%TYPE := 'CLAIMS';
v_knwldgebase_name knowledge_base.NAME%TYPE := 'CLAIMS';
BEGIN
DBMS_OUTPUT.put_line ('Input_value,Rule Name');
DBMS_OUTPUT.ENABLE (1000000);
FOR indx IN (SELECT REGEXP_SUBSTR (v_input, '[^,]+', 1,
LEVEL) AS input
FROM DUAL
CONNECT BY LEVEL <=
( LENGTH (v_input)
- LENGTH (REPLACE (v_input, ','))
)
+ 1)
LOOP
v_rule_name := NULL;
/* RULEIT 3.x - Keyword search (All Group) */
FOR rname IN (SELECT DISTINCT rh.NAME
FROM rule_header rh,
knowledge_base kb,
application app
WHERE REGEXP_LIKE (rh.definition,
indx.input,
'i'
)
/* INPUT - 'age' is the keyword to be searched , */
/* 'i' for case - insensitive (replace 'i' with null for case-sensitive search */
AND app.application_id = kb.application_id
AND rh.knowledge_id = kb.knowledge_base_id
AND app.NAME = v_engine_name
/* INPUT - engine name*/
AND kb.NAME = v_knwldgebase_name
/* INPUT - knowledge base name*/
)
LOOP
v_rule_name := v_rule_name || rname.NAME || ' \';
END LOOP;
v_rule_name := SUBSTR (v_rule_name, 1, LENGTH (v_rule_name) - 1);
DBMS_OUTPUT.put_line (indx.input || ',' || v_rule_name);
-- DBMS_OUTPUT.put_line (indx.input);
END LOOP;
END;
As per the documentation it says clob can accommodate (4 gigabytes - 1) * (database block size).
I dont understand what is (database block size)
Please help me..
Thanks,
P Prakash