This discussion is archived
4 Replies Latest reply: Jan 25, 2013 2:25 PM by kiranss RSS

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

915922 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    http://www.techonthenet.com/oracle/errors/ora01727.php
  • 2. Re: - ORA-01727: numeric precision specifier is out of range (1 to 38)
    915922 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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