4 Replies Latest reply: Jan 25, 2013 4:25 PM by kiranss RSS

    - ORA-01727: numeric precision specifier is out of range (1 to 38)

    915922
      What is cause of above error??

      SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

      DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_1')
      ----------------------------------------------------------------------------------------------------
      GENERAL INFORMATION SECTION
      -------------------------------------------------------------------------------
      Tuning Task Name : my_sql_tuning_task_1
      Tuning Task Owner : SYS
      Workload Type : Single SQL Statement
      Scope : COMPREHENSIVE
      Time Limit(seconds): 3600
      Completion Status : COMPLETED
      Started at : 09/27/2012 13:24:45
      Completed at : 09/27/2012 13:24:45

      -------------------------------------------------------------------------------
      Schema Name: PDS
      SQL ID : 4wv8x1b10dvk0
      SQL Text : INSERT INTO dlg_participant
      (dp_dg_id, dp_dlg_id, dp_customer_id, dp_context,
      dp_inserted_timestamp, dp_moved_timestamp, dp_active,
      dp_internal_id)
      (SELECT
      cast(:1 as number(:"SYS_B_0")),
      cast(:2 as number(:"SYS_B_1")),
      mh_customer_id,
      mh_context,
      SYSDATE,
      SYSDATE,
      cast(:3 as number(:"SYS_B_2")),
      cast(:4 as number(:"SYS_B_3"))
      FROM
      (SELECT

      Relations.MARE_ID as mh_customer_id,
      :"SYS_B_4" as mh_context,
      null as mh_participant_id
      FROM

      (select *
      from Testuser.RelationsEnriched_10M
      ) Relations
      WHERE
      (
      ((:5 >= Relations.MARE_ID))) AND
      NOT EXISTS (SELECT
      dp_id
      FROM
      dlg_participant,
      dlg_group
      WHERE
      (dp_dg_id = dg_id) AND
      (dg_dlg_id = :6) AND
      (dp_active = :7) AND
      (dp_customer_id = Relations.MARE_ID))) mh_container)
      Bind Variables :
      2 - (NUMBER):9
      4 - (NUMBER):9
      6 - (NUMBER):18
      8 - (NUMBER):18
      10 - (NUMBER):1500000
      11 - (NUMBER):1053
      12 - (NUMBER):0

      -------------------------------------------------------------------------------
      ERRORS SECTION
      -------------------------------------------------------------------------------
      - ORA-01727: numeric precision specifier is out of range (1 to 38)

      -------------------------------------------------------------------------------
        • 1. Re: - ORA-01727: numeric precision specifier is out of range (1 to 38)
          Ora
          http://www.techonthenet.com/oracle/errors/ora01727.php
          • 2. Re: - ORA-01727: numeric precision specifier is out of range (1 to 38)
            915922
            Strange as none of the number datatype is more than 38

            SQL> desc testuser.relationsenriched_10m
            Name Null? Type
            ----------------------------------------------------- -------- ------------------------------------
            MAEX_VALUE_CODE_002 NUMBER(38)
            MAEX_PHONE_IND_DATE DATE
            MAEX_TOTAL_SWITCH_DATE DATE
            MAEX_VALUE_CODE_002_DATE DATE
            MARE_COMP_FS_PC VARCHAR2(199)
            MARE_DEALER_RETAIL VARCHAR2(7)
            MARE_CONSODATA_IND VARCHAR2(2)
            MAEX_VALUE_CODE_003 NUMBER(38)
            MARE_EXT_KEY1_NR VARCHAR2(199)
            MARE_GM_CARD_CODE VARCHAR2(199)
            MAEX_LAST_CHANGE_DATE DATE
            MAAD_MARE_ID NUMBER(38)
            MAEX_MAIL_IND_DATE DATE
            MARE_PERS_TITLE VARCHAR2(6)
            MARE_EXT_KEY3_NR VARCHAR2(199)
            MARE_BSM_DATE VARCHAR2(199)
            MARE_ID NUMBER(38)
            MARE_GM_CARD_STATUS VARCHAR2(199)
            MAAD_PREFERED_IND VARCHAR2(2)
            MAEX_SMS_IND_DATE DATE
            MARE_RELATED_CODE VARCHAR2(199)
            MARE_FAX FLOAT(126)
            MACA_MARE_ID NUMBER(38)
            MACA_REGDATE_OWNER_YEAR NUMBER(38)
            MAEX_EMAIL_IND_DATE DATE
            MAEX_MAILABLE_IND6 VARCHAR2(2)
            MARE_PERS_LAST_NAME VARCHAR2(42)
            MARE_PERS_SALUTATION VARCHAR2(9)
            MARE_PERS_FIRST_NAMES VARCHAR2(51)
            MARE_SUB_TYPE VARCHAR2(2)
            MACA_PREFERED_IND VARCHAR2(2)
            MAEX_CONTROL_GROUP_DATE DATE
            MARE_MOBILE_PHONE VARCHAR2(21)
            MARE_COMP_ID NUMBER(38)
            MARE_EXT_KEY1_CHAR VARCHAR2(16)
            MARE_FIRST_CONTACT_DATE DATE
            MARE_MOI1_DESCR VARCHAR2(21)
            MAEX_MAILABLE_IND9 VARCHAR2(2)
            MARE_MOI1 VARCHAR2(11)
            MARE_MAAD_ID_T NUMBER(38)
            MARE_COMP_SIZE_CAT NUMBER(38)
            MAEX_SMS_IND VARCHAR2(2)
            MARE_PERS_LAST_NAME_SECOND VARCHAR2(15)
            MAEX_VALUE_CODE_001_DATE DATE
            MARE_ORDER_T NUMBER(38)
            MARE_BSM_IND VARCHAR2(2)
            MACA_BRAND VARCHAR2(12)
            MACA_DEALER_RETAIL VARCHAR2(7)
            MARE_HOME_PHONE VARCHAR2(21)
            MAEX_MAIL_IND VARCHAR2(2)
            MARE_PERS_BIRTH_DATE DATE
            MARE_CNSMR_TYPE VARCHAR2(2)
            MAEX_MAILABLE_IND5 VARCHAR2(2)
            MARE_PERS_AGE NUMBER(38)
            MAEX_MAILABLE_IND1 VARCHAR2(2)
            MARE_INSRC_RNWL_DATE DATE
            MACA_NEW_USED VARCHAR2(5)
            MARE_LOAD_DATE DATE
            MARE_PERS_MAR_STATUS VARCHAR2(199)
            MARE_PERS_PREFIX VARCHAR2(7)
            MARE_COMP_SIZE NUMBER(38)
            MAEX_VALUE_CODE_100_DATE DATE
            MARE_HISTORY_FILE VARCHAR2(26)
            MARE_COMP_NAME VARCHAR2(108)
            MARE_IDX_T VARCHAR2(51)
            MARE_DEALER_RETAIL_NAME VARCHAR2(41)
            MACA_OWNER_TYPE VARCHAR2(2)
            MARE_CREATION_DATE DATE
            MARE_MACA_ID_T NUMBER(38)
            MARE_EXT_KEY3_CHAR VARCHAR2(199)
            MAAD_POSTAL_CODE VARCHAR2(8)
            MAEX_MARE_ID NUMBER(38)
            MARE_CNSMR_TYPE_CODE NUMBER(38)
            MARE_SOURCE VARCHAR2(5)
            MARE_LAST_CONTACT_DATE DATE
            MARE_EXT_KEY2_CHAR VARCHAR2(199)
            MAEX_ID NUMBER(38)
            MAEX_TOTAL_SWITCH VARCHAR2(2)
            MARE_EMAIL VARCHAR2(53)
            MARE_DEALER_SERV_NAME VARCHAR2(41)
            MARE_WORK_PHONE VARCHAR2(21)
            MAEX_VALUE_CODE_100 NUMBER(38)
            MAEX_PHONE_IND VARCHAR2(2)
            MARE_AERD VARCHAR2(199)
            MARE_SPS_FILE_ID NUMBER(38)
            MAEX_LOAD_DATE DATE
            MARE_MOI5 VARCHAR2(11)
            MAEX_MAILABLE_IND7 VARCHAR2(2)
            MAEX_VALUE_CODE_001 NUMBER(38)
            MARE_COMP_NAME_2 VARCHAR2(172)
            MARE_SOFI_NR VARCHAR2(17)
            MARE_COMP_CODE VARCHAR2(199)
            MARE_COMP_FS_CC VARCHAR2(199)
            MAEX_MAILABLE_IND4 VARCHAR2(2)
            MARE_DEALER_PREF VARCHAR2(7)
            MARE_LANG_CODE VARCHAR2(3)
            MACA_REGDATE_CAR_YEAR NUMBER(38)
            MARE_PERS_INITIALS VARCHAR2(7)
            MARE_DEAR_SALUTATION VARCHAR2(50)
            MAEX_MAILABLE_IND8 VARCHAR2(2)
            MAEX_TITLE_IND VARCHAR2(2)
            MARE_MATCHING_IDX VARCHAR2(199)
            MARE_MOI3 VARCHAR2(11)
            MARE_DEALER_PREF_NAME VARCHAR2(2)
            MARE_ENVELOPE_NAME VARCHAR2(199)
            MARE_PERS_CHILD_UNDER_18 VARCHAR2(199)
            MARE_MOI2 VARCHAR2(11)
            MARE_RELATED_DESCR VARCHAR2(199)
            MARE_EXT_KEY2_NR VARCHAR2(199)
            MARE_PERS_GENDER VARCHAR2(2)
            MACA_MODEL VARCHAR2(16)
            MARE_PERS_HH_SIZE VARCHAR2(199)
            MAEX_TITLE_IND_DATE DATE
            MARE_LAST_CHANGE_DATE DATE
            MARE_MOI4 VARCHAR2(11)
            MAEX_MAILABLE_IND2 VARCHAR2(2)
            MARE_DEALER_SERV VARCHAR2(7)
            MAEX_VALUE_CODES VARCHAR2(25)
            MARE_NAME_T VARCHAR2(51)
            MAEX_VALUE_CODE_003_DATE DATE
            MARE_PERS_HH_CARS VARCHAR2(199)
            MAEX_MAILABLE_IND3 VARCHAR2(2)
            MARE_ERD NUMBER(38)
            MAEX_CREATION_DATE DATE
            MARE_IERD VARCHAR2(199)
            MAEX_CONTROL_GROUP NUMBER(38)
            MAEX_EMAIL_IND VARCHAR2(2)
            MARE_COMP_FS_TOT VARCHAR2(199)
            MARE_RELT_ID NUMBER(38)
            MARE_PERS_ID NUMBER(38)

            SQL> desc pds.dlg_participant
            Name Null? Type
            ----------------------------------------------------- -------- ------------------------------------
            DP_ID NOT NULL NUMBER(18)
            DP_DG_ID NOT NULL NUMBER(9)
            DP_DLG_ID NOT NULL NUMBER(9)
            DP_CUSTOMER_ID NOT NULL NUMBER(38)
            DP_CONTEXT NOT NULL NVARCHAR2(128)
            DP_INSERTED_TIMESTAMP NOT NULL DATE
            DP_MOVED_TIMESTAMP NOT NULL DATE
            DP_PART_STAMP NVARCHAR2(3)
            DP_ACTIVE NOT NULL NUMBER(18)
            DP_INTERNAL_ID NUMBER(18)
            DP_CUSTOM_VALUES NCLOB
            • 3. Re: - ORA-01727: numeric precision specifier is out of range (1 to 38)
              kiranss
              I am running into the same issue, were you able to resolve ?
              • 4. Re: - ORA-01727: numeric precision specifier is out of range (1 to 38)
                rp0428
                If you have a question of issue create your own thread and post your 4 digit Oracle version, the code and the problem you are having.