Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-06550: line 3, column 10: PLS-00172: string literal too long

prakashJan 5 2012 — edited Jan 5 2012
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 2 2012
Added on Jan 5 2012
3 comments
2,736 views