3 Replies Latest reply on Mar 9, 2018 3:05 PM by yenier

    Function 'export_rdfview_model' with the error <PL/SQL: numeric or value error: character string buffer too small>

    yenier

      Dear all,

       

      I'm trying to materialize the triples from a virtual model using the function sem_apis.export_rdfview_model as follow:

       

      BEGIN
      sem_apis.export_rdfview_model(
      model_name => 'MODEL_VIRTUAL',
      rdf_table_owner => 'MyUser',
      rdf_table_name => 'STAGE_TABLE');
      END;
      

       

      But, the function returns the error below:

       

      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at "MDSYS.SDO_RDF", line 451
      ORA-06512: at "MDSYS.SDO_RDF", line 15876
      ORA-06512: at line 1
      ORA-06512: at "MDSYS.SDO_RDF", line 5420
      ORA-06512: at "MDSYS.SDO_RDF", line 5709
      ORA-06512: at "MDSYS.SDO_RDF", line 5761
      ORA-06512: at "MDSYS.RDF_APIS", line 1358
      ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN
       sem_apis.export_rdfview_model(
       model_name => 'MODEL_VIRTUAL',
       rdf_table_owner => 'MyUser',
       rdf_table_name => 'STAGE_TABLE');
      

       

      The virtual model was created via a set of R2RML maps, and the source table has the following structure:

       

      TABLE PERSON_INFO(
      ID NUMBER(10,0) NOT NULL ENABLE, 
      INFO_TYPE_ID NUMBER(10,0) NOT NULL ENABLE, 
      PERSON_ID NUMBER(10,0) NOT NULL ENABLE, 
      INFO VARCHAR2(4000 BYTE), 
      INFO_TYPE VARCHAR2(288 BYTE), 
      META_REPCOL VARCHAR2(4000 BYTE) NOT NULL ENABLE, 
      CONSTRAINT "PERSON_INFO_PK" PRIMARY KEY ("ID")
      );
      

       

      Moreover, the STAGE_TABLE was created as mentioned in Oracle Documentation.

       

      Someone help me, please.

       

      Thank you in advance,

       

      Yenier

        • 1. Re: Function 'export_rdfview_model' with the error <PL/SQL: numeric or value error: character string buffer too small>
          Sdas-Oracle

          Hi Yenier,

           

          This happens when one or more of the values in a column is <= 4000 bytes in the table, but during conversion to RDF term, length of the converted value (due to escaping etc.) becomes more than 4000 bytes and hence needs a CLOB.

          In your case, the column causing this problem is probably either the INFO column and/or the META_REPCOL column.

           

          You could try the use of CLOB type as a workaround as shown in the following example:

           

          conn rdfuser/rdfuser

          create table emp (ename varchar2(10) primary key, bio varchar2(4000));

          insert into emp values ('John', rpad('John''s last name is Smith',4000,'.'));

          commit;

          -- using direct mapping in this example, but this idea can be used for R2RML mapping case also

          exec sem_apis.create_rdfview_model('rv1',sys.odcivarchar2list('EMP'),'http://x/');

          create table stable (rdf$stc_sub varchar2(4000) not null, rdf$stc_pred varchar2(4000) not null, rdf$stc_obj varchar2(4000) not null) compress;

          grant insert on stable to mdsys;

          -- fails with ORA-06502

          exec sem_apis.export_rdfview_model('rv1','RDFUSER','STABLE');

           

          --

          -- workaround: use CLOB type for the input column value (BIO, in this example) and also for the (output) staging table's RDF$STC_OBJ column

          --

          create table stable_clob (rdf$stc_sub varchar2(4000) not null, rdf$stc_pred varchar2(4000) not null, rdf$stc_obj CLOB not null) compress;

          grant insert on stable_clob to mdsys;

          create view emp_clob as select ename, to_clob(bio) bio_clob from emp;

          exec sem_apis.create_rdfview_model('rv1',sys.odcivarchar2list('EMP_CLOB'),'http://x/',options=>' CREATE_ANYWAY=T ');

          exec sem_apis.export_rdfview_model('rv1','RDFUSER','STABLE_CLOB');

           

          Hope this helps. Please feel free to contact me directly: souripriya dot das at oracle dot com

           

          Thanks,

          - Souri.

          1 person found this helpful
          • 2. Re: Function 'export_rdfview_model' with the error <PL/SQL: numeric or value error: character string buffer too small>
            yenier

            Hi Souri:

             

            Thank you very much for your response!

             

            The use of CLOB type for staging table's RDF$STC_OBJ column works when I execute the sem_apis.export_rdfview_model function, but when I run the sem_apis.bulk_load_from_staging_table function, it raises the following error:

             

             

            ORA-13199: Invalid Staging Table reserved column name data types reqd_cc=2 coll_cc=0 user_cc=0 step_num=0

             

             

            What solution can be taken to solve this error?

             

            Thanks!

             

            Yenier

            • 3. Re: Function 'export_rdfview_model' with the error <PL/SQL: numeric or value error: character string buffer too small>
              yenier

              Hi Souri:

               

              By searching here, I found that the error could be solved by running the following statements.

               

              ALTER TABLE STAGE_TABLE ADD (temp varchar2(4000));
              update stage_table set temp=RDF$STC_OBJ;
              alter table  stage_table drop column  RDF$STC_OBJ;
              alter table stage_table rename column temp to RDF$STC_OBJ;
              

               

              Then, I executed the sem_apis.bulk_load_from_staging_table function.

               

              That's right?  Do you recommend doing this?

               

              Thank you,

               

              Yenier

              1 person found this helpful