12 Replies Latest reply: Apr 12, 2010 8:13 AM by Siva Ravada RSS

    ORA-00939: too many arguments for function

    393061
      Hi everyone,

      I am trying to insert 20k of x-y coordinates into oracle spatial database using JDBC connection and it complains with "ORA-00939: too many arguments for function" message.

      I understand there is a limit up to 999 arguments would be passed into SQL statement and that's why I am getting above error message.

      Is there anyway I can put in more than 20k of data using JDBC connection? 20K of data is not big enough to hold boundaries or other polygons.

      The following is Java code for inserting my data.

      -----------------------
      String sql_query = "insert into " + table_basin_sdo_write + " (basin_id, geometry) " + "values " + "( " + basin_id + ", mdsys.sdo_geometry" + "( " + "2003, " + "8256, " + "null, " + "mdsys.sdo_elem_info_array(1, 1003, 1), " +
      "mdsys.sdo_ordinate_array( " + xy_coord + " )" + ")" + ")";

      db_stmt_to.executeUpdate(sql_query);
      ---------------------------

      where xy_coord is a string that holds 20k of data.

      Thank you in advance

      Jaeyoung Suh
      PPDM Association
        • 1. Re: ORA-00939: too many arguments for function
          361066
          You need to bind a variable to a PreparedStatement, refer to JDBC Developer's guide or my post:
          Building a variable

          Hope this help you
          • 2. Re: ORA-00939: too many arguments for function
            436323
            Hi,

            I also faced the same problem, I am using JAVA code to insert more than 10000 points for a polygon into SDOGEOMETRY column.

            The above link didn't show any pages.

            Could you please provide me a link or some solution for this problem

            Thanks in Advance
            Senthil






            • 3. Re: ORA-00939: too many arguments for function
              91061
              you need to use a bind variable still. Here is an updated pointer:
              You need to bind a variable to a PreparedStatement, refer to JDBC Developer's guide or my post:
              Building a variable
              • 4. Re: ORA-00939: too many arguments for function
                264534
                Since you are using Java, you should use the JGeometry class provided in the new SDOAPI library to do this.
                double[] coords = new double[10000];
                for(int i=0; i<coords.length; i++)
                  coords[i] = ...;
                
                JGeometry mypoly = JGeometry.createLinearPolygon(coords, 2/*dimensions*/, 8256);
                
                PreparedStatement ps = connection.prepareStatement("insert into states values(?)");
                     //convert JGeometry instance to DB STRUCT
                STRUCT obj = JGeometry.store(mypoly, connection);
                ps.setObject(1, obj);
                ps.execute();
                • 5. Re: ORA-00939: too many arguments for function
                  223152
                  Thanks for sending the example code.
                  Unfortunately, when I try it out I get an exception on this line:
                  STRUCT obj = JGeometry.store(mypoly, connection);

                  Exception:
                  java.lang.ClassCastException
                  at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:128)
                  at oracle.spatial.geometry.JGeometry.createDBDescriptors(JGeometry.java:1321)
                  at oracle.spatial.geometry.JGeometry.store(JGeometry.java:1257)
                  at Test.main(Test.java:43)

                  I don't have the source code for StructDescriptor, so I can't tell why it's throwing a ClassCastException.

                  I am using an sdoapi.jar that I downloaded from the Oracle website a few days ago and am using ojdbc14.jar.
                  I'm running Oracle 9.2, JDK 1.4.2, Windows 2000 OS.
                  • 6. Re: ORA-00939: too many arguments for function
                    436323
                    Hi,

                    I am using a JAVA code and I have used the JGeometry as mentioned in previous message. Basically I am creating a LineString which has more than 999 coordinates.

                    While executing it shows an error message as shown below:

                    ORA-00932: inconsistent datatypes: expected NUMBER got MDSYS.SDO_GEOMETRY

                    Code SnapShot is given below :

                    String strSQL = "INSERT INTO SDOGEOMETRY VALUES ( 1703, 2055, 1704, 2160, 1243,MDSYS.SDO_GEOMETRY(2006,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY ( ? )))";

                    JGeometry Ls= JGeometry.createLinearLineString(dCoords, dCoords.length,0);
                    PreparedStatement PrepStmt = connection.prepareStatement( strSQL );
                    STRUCT GeomObject = JGeometry.store(Ls,connection);
                    PrepStmt.setObject(1,GeomObject );
                    PrepStmt.execute();
                    PrepStmt.close();

                    Could you please let me know any mistake which I have done in my code snapshot which results in error message.

                    Thanks in Advance,
                    Senthil



                    • 7. Re: ORA-00939: too many arguments for function
                      91061
                      my guess is your table definition (sdogeometry) has more than 5 numbers it is expecting before the geometry column.
                      post the results of: describe sdogeometry

                      also, since you are storing a single line you should use sdo_gtype of 2002 rather than 2006.
                      • 8. Re: ORA-00939: too many arguments for function
                        436323
                        Hi,

                        I am really sorry, I have found the reason for this problem. I should NOT given ? for MDSYS.SDO_ORDINATE_ARRAY, but i should have given it for the entire column of MDSYS.SDO_GEOMETRY.

                        String strSQL
                        INSERT INTO SDOGEOMETRY VALUES ( 1703, 2055, 1704, 2160, 1243,?);

                        Sorry for the inconvenience caused

                        The table definition
                        Name Type
                        OBJINSTID NUMBER(38)
                        OBJCLASSID NUMBER(38)
                        COMPID NUMBER(38)
                        COMPCLASSID NUMBER(38)
                        PROJID NUMBER(38)
                        SDODATA MDSYS.SDO_GEOMETRY

                        Thank you very much.
                        Senthil




                        • 9. Re: ORA-00939: too many arguments for function
                          621934
                          Hi,

                          PreparedStatement with JGeometry helps, but now we have a situation where we can't use PreparedStatements (sub-module builds big complex SQL-query from various params).

                          So, is there any other way to solve this issue?

                          Thank you in advance,
                          Bjoern Weitzig

                          PS: We use Java 1.5, JDBC, Oracle 10.2.
                          • 10. Re: ORA-00939: too many arguments for function
                            735320
                            hi daniel,

                            i am using bind variable to load more than 5000 points into sdo_geometry column. i am successfully store the first 2000 points. but when i started to store 5000 points the error of ora-00939 appear again. why is this happened? do you have any idea on this? here is pl/sql code that im using to load the points.

                            declare
                            varr mdsys.sdo_ordinate_array;
                            BEGIN

                            varr := mdsys.sdo_ordinate_array(....here i put the coordinates....);


                            insert into SPHERE values (1,mdsys.sdo_geometry(3007, null, null, mdsys.sdo_elem_info_array( here i put the elem info array....),varr));

                            END;
                            */*


                            regards,
                            sueazri
                            • 11. Re: ORA-00939: too many arguments for function
                              627524
                              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 -

                              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

                              Edited by: Rajesh Barnwal on Apr 12, 2010 4:21 PM
                              • 12. Re: ORA-00939: too many arguments for function
                                Siva Ravada
                                Check this thread:

                                Re: host variable using the SDO_GEOMETRY constructor

                                You need to first build the varray and then pass it into the SDO_GEOMETRY constructor.

                                siva