0 Replies Latest reply: Jan 13, 2014 4:27 AM by T. Morton RSS

    FRM-41072 with CREATE_GROUP_FROM_QUERY + VARCHAR2(4000) + UTF8

    T. Morton

      Hello,

       

      I recently migrated my application in Forms 10g, which was previously connected to a DB 10g with single-byte charset, to a DB 11g in UTF8.

       

      In my DB I have a view named PERSONNEL which uses function to return data as VARCHAR2(4000 CHAR):

       

      CREATE VIEW PERSONNEL AS

        (NAME, FIRST_NAME, ADDRESS, ZIP_CODE, BIRTH_NAME)

      SELECT F_DECRYPT(NAME) NAME, F_DECRYPT(FIRST_NAME) FIRST_NAME, F_DECRYPT(ADDRESS) ADDRESS, F_DECRYPT(ZIP_CODE) ZIP_CODE, F_DECRYPT(BIRTH_NAME) BIRTH_NAME

      FROM ENC_PERSONNEL;

       

      Of course all these 5 columns return VARCHAR2(4000) because it's a funtion call.

       

      In my Forms, I use the function CREATE_GROUP_FROM_QUERY to query this view:

      rg_id := create_group_from_query('MyRG', 'SELECT NAME, FIRST_NAME, ADDRESS, ZIP_CODE, BIRTH_NAME FROM PERSONNEL' ) ;

       

      When connecting to my DB 10g everything works fine.

      But when connected to DB 11g in UTF8, the CREATE_GROUP_FROM_QUERY raise an FRM-41072 error.

       

      I suspect Forms to have a size limitation on column.

       

      Further tests I've made:

      - Removing one column in the select statement avoid the error:

      rg_id := create_group_from_query('MyRG', 'SELECT NAME, FIRST_NAME, ADDRESS, ZIP_CODE FROM PERSONNEL' ) ; --> OK

      or

      rg_id := create_group_from_query('MyRG', 'SELECT NAME, ADDRESS, ZIP_CODE, BIRTH_NAME FROM PERSONNEL' ) ; --> OK

       

      - Adapting the view PERSONNEL to do a SUBSTR on column avoid the error:

      CREATE VIEW PERSONNEL AS

        (NAME, FIRST_NAME, ADDRESS, ZIP_CODE, BIRTH_NAME)

      SELECT SUBSTR(F_DECRYPT(NAME), 1, 100) NAME, F_DECRYPT(FIRST_NAME) FIRST_NAME, F_DECRYPT(ADDRESS) ADDRESS, F_DECRYPT(ZIP_CODE) ZIP_CODE, F_DECRYPT(BIRTH_NAME) BIRTH_NAME

      FROM ENC_PERSONNEL; --> Column definition for NAME passed from VARCHAR2(4000) to VARCHAR2(100).

       

      rg_id := create_group_from_query('MyRG', 'SELECT NAME, FIRST_NAME, ADDRESS, ZIP_CODE, BIRTH_NAME FROM PERSONNEL' ) ; --> OK

       

      Thanks in advance for help.

      Regards,