11 Replies Latest reply on Aug 27, 2009 4:47 PM by alwu-Oracle

    ORA-06502: PL/SQL: numeric or value error: character string buffer too smal

    630782
      Hi,

      I got the "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error when I tried to run some SPARQL query with Oracle Jean adaptor 2.0 against Oracle 11.1.0.6.

      Here is the stacktrace
      ORA-06512: at "MDSYS.RDF_MATCH_IMPL_T", line 176
      ORA-06512: at line 1

           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
           at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
           at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
           at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
           at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
           at oracle.spatial.rdf.client.jena.Oracle.executeQuery(Oracle.java:255)
           at oracle.spatial.rdf.client.jena.OracleSemQueryPlan.executeBindings(OracleSemQueryPlan.java:302)
           ... 31 more

      I saw from some other posts that this is a known bug and it has been fixed in Oracle 11.1.0.7 with patch set. Is there any patch available for Oracle 11.1.0.6? Thanks,

      Weihua
        • 1. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
          JorgeB-Oracle
          Hi,
          This is bug 6802000 and unfortunately this was not backported to 11.1.0.6.0
          It is necessary to apply 11.1.0.7.0 patchset to have this fixed.

          Your query has some condition in the "filter" like this example:

          SELECT x Namex,y Namey
          FROM TABLE(SEM_MATCH('
          (?x :Aheight ?ax) (?x :Bheight ?bx) (?x :Cheight ?cx) (?x :Dheight ?dx)
          (?y :Aheight ?ay) (?y :Bheight ?b1y) (?y :Cheight ?cy) (?y :Dheight ?dy)',
          SEM_Models('family'),
          SEM_Rulebases('RDFS','family_rb'),
          SEM_ALIASES(SEM_ALIAS('','http://www.example.org/family/')),
          '(to_number(bx)+to_number(ax)) <= (to_number(cy)+to_number(dy)) and
          to_number(b1y)+to_number(ay)) <= (to_number(cx)+to_number(dx))'));

          Workaround for now in 11.1.0.6.0 is to have the condition in the where clause instead of as a filter:


          SELECT x Namex,y Namey
          FROM TABLE(SEM_MATCH(
          '(?x :Aheight ?ax) (?x :Bheight ?bx) (?x :Cheight ?cx) (?x :Dheight ?dx)
          (?y :Aheight ?ay) (?y :Bheight ?b1y) (?y :Cheight ?cy) (?y :Dheight ?dy)',
          SEM_Models('family'),
          SEM_Rulebases('RDFS','family_rb'),
          SEM_ALIASES(SEM_ALIAS('','http://www.example.org/family/')),
          null)) where (to_number(bx)+to_number(ax)) <= (to_number(cy)+to_number(dy))
          and
          (to_number(b1y)+to_number(ay)) <= (to_number(cx)+to_number(dx));

          Hope that helps!
          Jorge
          • 2. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too
            630782
            Thanks for the reply. I'm using SPARQL query and I didn't use Filter in the query. The error occurs in my case when I have over 10 match criteria in the SPARQL where clause. What will be the possible work-around for this kind of scenario?

            An interesting observation is that the exact same SPARQL query runs fine in TopBraid composer 3.0, which is also using Oracle Jena adapter to talk to the same Oracle 11.1.0.6. That makes me wonder why the bug didn't affect TopBraid. Any idea?

            Thanks,

            Weihua
            • 3. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too
              alwu-Oracle
              TopBraid Composer tends to break up a query into smaller pieces.

              Could you cut & paste your SPARQL query here? Have you tried to shorten the query? For example, use shorter variable names...
              • 4. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too
                630782
                The query works fine if I remove some criteria. What we are doing is to build ad-hoc SPARQL query tool for BioInformatics scientists to explore their knowledge base. The query they build tend to be pretty large. Thanks,

                Weihua
                • 5. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too
                  alwu-Oracle
                  As you have found out yourself, this problem has been fixed in 11.1.0.7. There is no patch for 11.1.0.6.
                  Please upgrade your database. You can download 11.1.0.7 patchset from metalink.
                  • 6. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                    542526
                    hello

                    I ran into same problem, where did you find out that this is a bug and where can you download the patch please?

                    Edited by: devvvy on Jun 28, 2009 8:56 PM
                    • 7. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                      alwu-Oracle
                      You can download the patch from Metalink.

                      Thanks,

                      Zhe
                      • 8. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                        673499
                        Hi everyone.. im currently developing application for my Final Project, I got this error when i did a long query. Since this error happened i splitted it to several queries but it took a much time...
                        i dont have any oracle metalink account. could anyone give the patchset to my email ? Thanks b4.



                        koharudin.mail07@gmail.com
                        --------------------------------------
                        • 9. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                          alwu-Oracle
                          Can you share with us your long query(s)? Are there many of those? There are a few different approaches assuming you don't have the patch.
                          1) use join of a few sem_match table functions; 2) use a direct SQL to query the underlying public views (tables).

                          Thanks,

                          Zhe
                          • 10. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                            673499
                            Thanks alwu for you hint...

                            This is my long query :

                            SELECT * FROM TABLE(SDO_RDF_MATCH(
                            '
                            (?x rdf:type :Hotel)
                            (?x :punya_lokasi ?l)
                            (?x :punya_nama ?nama)
                            (?x :punya_keterangan ?ket)
                            (?x :punya_kategori_hotel ?kat)
                            (?kat :punya_nama ?kate)
                            (?l :punya_nomor ?lnomor)
                            (?l :punya_kodepos ?kodepos)
                            (?kodepos :punya_nama ?kodeposnama)
                            (?l :punya_kota ?kota)
                            (?kota :punya_nama ?kotanama)
                            (?l :punya_desa ?desa)
                            (?desa :punya_nama ?desanama)
                            (?l :punya_kecamatan ?kec)
                            (?kec :punya_nama ?kecnama)
                            (?l :punya_kabupaten ?kab)
                            (?kab :punya_nama ?kabnama)
                            (?l :punya_jalan ?jalan)
                            (?jalan :punya_nama ?lnama)
                            (?l :punya_latitude ?latitude)
                            (?l :punya_longitude ?longitude)
                            (?l :punya_propinsi ?propinsi)
                            (?propinsi :punya_nama ?propinsinama)
                            (?l :punya_negara ?negara)
                            (?negara :punya_nama ?negaranama)

                            ',SDO_RDF_Models('pariwisata'),
                            SDO_RDF_Rulebases(null),
                            SDO_RDF_Aliases(SDO_RDF_Alias('','http://www.owl-ontologies.com/Ontology1227225596.owl#')), null))

                            ....
                            above query will create error
                            -----------------------------------------------------------------------------------------------
                            Error report:
                            SQL Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                            ORA-06512: at "MDSYS.RDF_MATCH_IMPL_T", line 176
                            ORA-06512: at line 1
                            ---------------------------------------------------------------------------------------------------


                            at first, i create split my long query into some parts and multiple connection to database, but after read your approaches, i change into "join" query...

                            it'll become like this ...


                            SELECT * FROM
                            (
                            SELECT * FROM TABLE(SDO_RDF_MATCH(
                            '
                            (?x rdf:type :Hotel)
                            (?x :punya_lokasi ?l)
                            (?x :punya_nama ?nama)
                            (?x :punya_keterangan ?ket)
                            (?x :punya_kategori_hotel ?kat)
                            (?kat :punya_nama ?kate)
                            (?l :punya_nomor ?lnomor)
                            (?l :punya_kodepos ?kodepos)
                            (?kodepos :punya_nama ?kodeposnama)
                            ',SDO_RDF_Models('pariwisata'),
                            SDO_RDF_Rulebases(null),
                            SDO_RDF_Aliases(SDO_RDF_Alias('','http://www.owl-ontologies.com/Ontology1227225596.owl#')), null))
                            )ITEM
                            JOIN
                            (
                            SELECT * FROM TABLE(SDO_RDF_MATCH(
                            '
                            (?l :punya_kota ?kota)
                            (?kota :punya_nama ?kotanama)
                            (?l :punya_desa ?desa)
                            (?desa :punya_nama ?desanama)
                            (?l :punya_kecamatan ?kec)
                            (?kec :punya_nama ?kecnama)
                            (?l :punya_kabupaten ?kab)
                            (?kab :punya_nama ?kabnama)
                            ',SDO_RDF_Models('pariwisata'),
                            SDO_RDF_Rulebases(null),
                            SDO_RDF_Aliases(SDO_RDF_Alias('','http://www.owl-ontologies.com/Ontology1227225596.owl#')), null))
                            )LOKASI on ITEM.L=LOKASI.L
                            JOIN
                            (
                            SELECT * FROM TABLE(SDO_RDF_MATCH(
                            '
                            (?l :punya_jalan ?jalan)
                            (?jalan :punya_nama ?lnama)
                            (?l :punya_latitude ?latitude)
                            (?l :punya_longitude ?longitude)
                            (?l :punya_propinsi ?propinsi)
                            (?propinsi :punya_nama ?propinsinama)
                            (?l :punya_negara ?negara)
                            (?negara :punya_nama ?negaranama)
                            ',SDO_RDF_Models('pariwisata'),
                            SDO_RDF_Rulebases(null),
                            SDO_RDF_Aliases(SDO_RDF_Alias('','http://www.owl-ontologies.com/Ontology1227225596.owl#')), null))
                            )LOKASI2 on ITEM.L=LOKASI2.L



                            Thanks....