This discussion is archived
1 Reply Latest reply: Dec 5, 2012 5:25 AM by Christian Erlinger RSS

Does CHAR data type work with Forms 11g

960075 Newbie
Currently Being Moderated
We have an application in Forms 11g (32bit - version 11.1.2.0.0). DB is a Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.

When our Forms calls a stored procedure which has parameters defined using column name type (i.e. like p_customer_code customers.code%TYPE ) and if the column data type is CHAR we get a run time error when executing the package (ORA-06502: PL/SQL: numeric or value error string).

When we change the data type of the tables column to varchar2, it works??

Problem is we have dozens of tables with CHAR data type columns and we can't do all the changes manually. For one thing, if they are part of a primary or unique key or even foreign key, we can't change the data type of the column without dropping the constraint.

What is the solution to this pls??
  • 1. Re: Does CHAR data type work with Forms 11g
    Christian Erlinger Guru
    Currently Being Moderated
    When our Forms calls a stored procedure which has parameters defined using column name type (i.e. like p_customer_code customers.code%TYPE ) and if the column data type is CHAR we get a run time error when executing the package (ORA-06502: PL/SQL: numeric or value error string).
    this might also be related to compile settings, check your NLS settings (NLS_LANG, NLS_LENGTH_SEMANTICS) both times when you compile forms AND your stored procedures.

    As for the CHAR datatype: CHAR internally is treated like a blank padded varchar:

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1542606219593

    Speaking of that it might be time to use varchar2 instead your char columns. You'd have to drop and recreate your constraints, but with the help of the data dictionary (+user_tables+, user_tab_cols, user_cons_columns, user_constraints) and dbms_metadata this shouldn't be that hard.

    Also you'd of course need to recompile all your forms after you are finished, but I guess you are aware of that ;)

    cheers

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points