1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00939 too many arguments for function - Inserting a Row with large arguments

Discussion in 'SQL PL/SQL' started by rajesh_bu2001, Apr 12, 2010.

  1. rajesh_bu2001

    rajesh_bu2001 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,
    I am trying to execute this insert stat into a type table (nested table) but am finding the mesage -

    "ORA-00939 too many arguments for function"

    The script is -
    Code (SQL):

    INSERT INTO confsys.cnfgtr_tde_dist_rate
    (num_rate_desc_indx, num_dist_rate_indx, yn_float_rate,
    typ_dist_rate_fac,
    typ_dist_rate_value,
    yn_dyn_rate
    )
    VALUES (90, 2, 0,
    cnfgtr_tde_factor_nt
    (cnfgtr_tde_factor_ty ('1', 'Product Code'),
    cnfgtr_tde_factor_ty ('2',
    'Paid up Capital(Cr.)'
    ),
    cnfgtr_tde_factor_ty ('3', 'Type of Client')
    ),
    cnfgtr_tde_value_nt (cnfgtr_tde_value_ty ('598', '10'),
    cnfgtr_tde_value_ty ('599', '6.25'),
    cnfgtr_tde_value_ty ('345', '0'),
    cnfgtr_tde_value_ty ('346', '15'),
    cnfgtr_tde_value_ty ('156', '0'),
    cnfgtr_tde_value_ty ('157', '15'),
    cnfgtr_tde_value_ty ('158', '15'),
    ---
    ---
    ---
    --- argument line no 1586 ),
    0 );
    I understand tht the limit here is 999. but I have to give a solution or way around to my developers. The script is executed with a host of other scripts through a batch file. Pls help.

    Thnx in Advance,
    Rajesh
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You fail to provide any version information for the Oracle database in use, any DDL for the table in question and any reason why you 'need' to try to violate the size of this nested table. You understand the physical limits of the operation yet you 'need' to fly in the face of them to force an insert which cannot occur. Refusing to provide any tools (create table script, the complete insert statement so others can possibly test this and arrive at a solution) doesn't help your case any.

    Provide more information (Oracle version, create table script, complete insert statement) if you want anyone to be able to make an attempt to assist you.
     
  3. rajesh_bu2001

    rajesh_bu2001 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Zargon,
    Here are the details tht u r looking for -

    Its oracle 10gR2.
    The DDL for the table is as follows -
    CREATE TABLE CNFGTR_TDE_DIST_RATE
    (
    NUM_RATE_DESC_INDX NUMBER,
    NUM_DIST_RATE_INDX NUMBER,
    YN_FLOAT_RATE NUMBER(1),
    TYP_DIST_RATE_FAC CNFGTR_TDE_FACTOR_NT,
    TYP_DIST_RATE_VALUE CNFGTR_TDE_VALUE_NT,
    YN_DYN_RATE NUMBER(1) DEFAULT 0 NOT NULL
    )
    NESTED TABLE TYP_DIST_RATE_FAC STORE AS CNFGTR_TDE_TYP_DIST_RATE_FAC,
    NESTED TABLE TYP_DIST_RATE_VALUE STORE AS CNFGTR_TDE_TYP_DIST_RATE_VALUE;

    An Insert statement that is working properly with the table is -

    INSERT INTO confsys.cnfgtr_tde_dist_rate
    (num_rate_desc_indx, num_dist_rate_indx, yn_float_rate,
    typ_dist_rate_fac,
    typ_dist_rate_value,
    yn_dyn_rate)

    VALUES (90, 2, 0,
    cnfgtr_tde_factor_nt(
    cnfgtr_tde_factor_ty ('1', 'Product Code'),
    cnfgtr_tde_factor_ty ('2','Paid up Capital-Cr.'),
    cnfgtr_tde_factor_ty ('3', 'Type of Client')
    ),
    cnfgtr_tde_value_nt (
    cnfgtr_tde_value_ty ('598', '10'),
    cnfgtr_tde_value_ty ('599', '6.25'),
    cnfgtr_tde_value_ty ('345', '0'),
    cnfgtr_tde_value_ty ('346', '15'),
    cnfgtr_tde_value_ty ('156', '0'),
    cnfgtr_tde_value_ty ('157', '15'),
    cnfgtr_tde_value_ty ('158', '15')
    ),
    0 );

    But when the no of arguments in cnfgtr_tde_value_nt increases to more than 999,
    i.e. cnfgtr_tde_value_ty ('598', '10') is having repeated more than 999 times, the system is throwing the said error.

    This table is basically a master table, efforts are on to decouple the nested tables into a group of normal tables but till then we need to insert the data thru SQL Plus and sql query only. So pls suggest.

    Thnx,
    Rajesh
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "10gR2" covers 10.2.0.1.0 through 10.2.0.5.0 with MANY bug fixes and possible enhancements between them. Also you're attempting to exceed physical limits for nested tables imposed by the databsae itself. You posted a create table statement but did not provide the type declarations to build the nested tables; how can you expect anyone to test this without supplying complete information? Do these need to be nested tables or could they be declared as varrays?