9 Replies Latest reply on Jul 1, 2008 1:27 PM by 640284

    Getting FRM-40734 when reading a BLOB column

    640284
      Hi All,
      When running my Oracle Form (Version 9.0.4), I get a “FRM-40734: Internal Error: PL/SQL error occurred.” message.

      I traced the error to the following select statement in one of the program units in my form. In this select statement, I am trying to read a BLOB column into a variable that is also defined as a BLOB.

      select template_data into vblob from vol_templates
      where template_name = 'Training';

      In this select statement, the template_data is defined as a BLOB and the template_name is a primary key on the vol_templates table. I also have defined vblob as BLOB.

      Does anyone know why I am getting this error and what must be done to correct it?

      Thanks in advance,

      Seyed
        • 1. Re: Getting FRM-40734 when reading a BLOB column
          Gerd Volberg
          is the select statement written in forms or can you encapsulate it in a db-procedure and test this ?
          • 2. Re: Getting FRM-40734 when reading a BLOB column
            640284
            This select statement is written in Forms. I tested the select statement in TOAD after taking the 'INTO' out and it works.
            • 3. Re: Getting FRM-40734 when reading a BLOB column
              574682
              have u handled exception for this select statement?

              Does it return multiple values?
              • 4. Re: Getting FRM-40734 when reading a BLOB column
                640284
                The template_name column is the primary key to the vol_templates table, when testing the script in TOAD, it returns one row. At this point, this table has only one row. By the time, I am finished with this project, there should be a maximum of six rows in this table containing six MS Word documents.

                select template_data from vol_templates
                where template_name = 'Training';

                I don't have code for hablding exception for this script.

                Thanks a lot
                • 5. Re: Getting FRM-40734 when reading a BLOB column
                  640284
                  I added the following exception handling routine, I still get FRM-40734 error message, it looks like the error caused by the select statement is fatal.

                  Again, here is the select statement
                  select template_data into vblob from vol_templates
                  where template_name = 'Training';

                  Here is the exception handling routine that I added,

                  EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                  v_error_code := SQLCODE;
                  v_error_message := substr(SQLERRM,1,200);
                  Message('No data found...!'|| v_error_code||' '||v_error_message);
                  WHEN OTHERS THEN
                  -- Consider logging the error and then re-raise
                  v_error_code := SQLCODE;
                  v_error_message := substr(SQLERRM,1,200);
                  Message('Other Error..!'|| v_error_code||' '||v_error_message);
                  RAISE;
                  • 6. Re: Getting FRM-40734 when reading a BLOB column
                    Gerd Volberg
                    that's half the way to solve the problem. Put your code in a stored package-function or procedure and start this from your forms.
                    • 7. Re: Getting FRM-40734 when reading a BLOB column
                      640284
                      Hi Gerd,
                      At the moment, Form A, calls Form B and Form B calls Form C. This problem that I am having is in a program unit in Form C. I copy and paste the entire script below. For my testing purposes, I put in MESSAGEs in the script to debug the script. If I comment out the select statement beteen MESSAGE('BBBBBB..') and MESSAGE('CCCCCC..'), the form runs fine, it just does not build the entire MS Word document that I would like. It only builds the volunteer name and address and today's date and writes it to the Word document on the C:\ drive. At that point the missing part would be the content of a Word Document that I had stored in a Blob column in the Oracle table. Anyway, below is the entire script and thank you for input.

                      PROCEDURE OLE_WRITE IS

                      --DECLARE
                      app CLIENT_OLE2.OBJ_TYPE;
                      docs CLIENT_OLE2.OBJ_TYPE;
                      doc CLIENT_OLE2.OBJ_TYPE;
                      selection CLIENT_OLE2.OBJ_TYPE;
                      args CLIENT_OLE2.LIST_TYPE;
                      file1 TEXT_IO.FILE_TYPE;
                      --str                 VARCHAR2(80);
                      str VARCHAR2(90);
                      --prev_str            VARCHAR2(80);
                      --prev_str            VARCHAR2(90);
                      v_vol_id NUMBER(10);
                      v_last_name VARCHAR2(30);
                      v_first_name VARCHAR2(30);
                      v_address_line1 VARCHAR2(50);
                      v_address_line2 VARCHAR2(50);
                      v_city_code VARCHAR2(40);
                      V_state_code VARCHAR2(2);
                      v_zip_code VARCHAR2(9);
                      v_word_data varchar2(2000);
                      v_template_data varchar2(2000);
                      v_date varchar2(12);
                      v_test varchar2(4);
                      v_hold varchar2(3000);

                      v_error_code number;
                      v_error_message varchar2(200);

                      src_file BFILE := bfilename('VOLUNTEER_DIR', 'vol_template.doc');

                      dst_file BLOB;
                      lgh_file BINARY_INTEGER;
                      l_buffer RAW(20000);
                      l_text_buffer VARCHAR2(32767);
                      destination_file BLOB;

                      vblob BLOB;
                      vclob CLOB;
                      vstart NUMBER :=1;
                      bytelen NUMBER;
                      len NUMBER;
                      my_vr RAW(32000);
                      X NUMBER;
                      l_output UTL_FILE.FILE_TYPE;

                      CURSOR get_volunteer_info (v_vol_id NUMBER )
                      IS
                      SELECT vol_id,
                      INITCAP(last_name) last_name,
                      INITCAP(first_name) first_name,
                      INITCAP(address_line1) address_line1,
                      INITCAP(address_line2) address_line2,
                      INITCAP(reference_codes.description) city,
                      state_code,
                      zip_code
                      FROM off_vol.volunteers,
                      oms_owner.reference_codes
                      WHERE volunteers.city_code = oms_owner.reference_codes.code
                      AND reference_codes.domain = 'CITY'
                      AND vol_id = v_vol_id;

                      BEGIN

                      v_vol_id := 0;
                      v_last_name := ' ';
                      v_first_name := ' ';
                      v_address_line1 := ' ';
                      v_address_line2 := ' ';
                      v_city_code := ' ';
                      v_state_code := ' ';
                      v_zip_code := ' ';
                      v_date := ' ';
                      v_test := ' ';
                      v_template_data := NULL;
                      v_hold := ' ';
                      vblob := NULL;
                      vclob := NULL;

                      -- define output directory
                      l_output := utl_file.fopen('VOLUNTEER_DIR','vol_template.doc', 'wb', 32760);
                      vstart := 1;
                      bytelen := 32000;
                      MESSAGE('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
                      -- get length of the blob
                      SELECT dbms_lob.getlength(template_data) into len
                      from vol_templates
                      where template_name = 'Training';
                      MESSAGE('BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');
                      -- save blob length
                      X := len;

                      -- select blob into variable
                      select template_data into vblob from vol_templates
                      where template_name = 'Training';
                      --v_hold := DBMS_LOB.SUBSTR(vblob, 3000,1);
                      --MESSAGE ('v_hold = '||v_hold);
                      MESSAGE('CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC');
                      -- is it small enough for a single write?
                      IF len < 32760 THEN
                           MESSAGE ('%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% LENGTH < 32760 *************************');
                           utl_file.put_raw(l_output,vblob);
                           utl_file.fflush(l_output);
                      ELSE -- write in pieces
                           vstart := 1;
                           WHILE vstart < len and bytelen > 0
                           LOOP
                                MESSAGE ('=============================== vstart = '||vstart);
                                dbms_lob.read(vblob,bytelen,vstart,my_vr);
                                utl_file.put_raw(l_output,my_vr);
                                
                                -- set the start position for the next cut
                                vstart := vstart + bytelen;
                                
                                -- set the end position if less thatn 32000 bytes
                           X := X - bytelen;
                           IF X < 32000 THEN
                                     bytelen := X;
                           END IF;

                           utl_file.fclose(l_output);
                           END LOOP;
                      END IF;
                      MESSAGE ('%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%');


                      select to_char(sysdate,'Mon DD, YYYY') into v_date from dual;

                      v_word_data := NULL;
                      --
                           Open get_volunteer_info (:letter_vol_id);
                           FETCH get_volunteer_info
                      INTO v_vol_id, v_last_name, v_first_name,
                      v_address_line1, v_address_line2,
                      v_city_code,
                      v_state_code,
                      v_zip_code;

                      for idx in 1..60 loop
                      v_word_data := v_word_data||chr(32);
                      end loop;
                      v_word_data := v_word_data||v_date||chr(13)||chr(10);
                      v_word_data := v_word_data||chr(13)||chr(10)||v_first_name||chr(32)||v_last_name||
                      chr(13) || chr(10)|| v_address_line1;
                      if NVL(v_address_line2,'X') = 'X' then
                           v_word_data := v_word_data||chr(13) || chr(10)||
                      v_city_code||', '||v_state_code||chr(32)||v_zip_code;
                      else
                      v_word_data := v_word_data||chr(13) || chr(10)||
                                v_address_line2||chr(13) || chr(10)||
                                v_city_code||', '||v_state_code||v_zip_code;
                      end if;
                      for idx in 1..6 loop
                      v_word_data := v_word_data||chr(13)||chr(10);
                      end loop;

                      Message('v_word_data = '||v_word_data);
                      v_template_data := f_read_blob(vblob);
                      v_template_data := v_template_data||v_word_data;

                      ---- create a new document
                      app := CLIENT_OLE2.CREATE_OBJ('Word.Application');
                      CLIENT_OLE2.SET_PROPERTY(app,'Visible',1);
                      docs := CLIENT_OLE2.GET_OBJ_PROPERTY(app, 'Documents');
                      doc := CLIENT_OLE2.INVOKE_OBJ(docs, 'add');
                      selection := CLIENT_OLE2.GET_OBJ_PROPERTY(app, 'Selection');
                      --Message('Writing Volunteer Name and Address to "c:\example.doc" ');
                      -- insert data into new document from long item
                      --CLIENT_OLE2.SET_PROPERTY(selection, 'Text', 'this is a test message');
                      --CLIENT_OLE2.SET_PROPERTY(selection, 'Text', v_word_data);
                      CLIENT_OLE2.SET_PROPERTY(selection, 'Text', v_template_data);
                      -- save document as example.tmp
                      args := CLIENT_OLE2.CREATE_ARGLIST;
                      CLIENT_OLE2.ADD_ARG(args, 'c:\example.doc');
                      --CLIENT_OLE2.ADD_ARG(args, 'C:\Volunteer1.dot');
                      CLIENT_OLE2.INVOKE(doc, 'SaveAs', args);
                      CLIENT_OLE2.DESTROY_ARGLIST(args);
                      -- close example.tmp
                      args := CLIENT_OLE2.CREATE_ARGLIST;
                      CLIENT_OLE2.ADD_ARG(args, 0);
                      CLIENT_OLE2.INVOKE(doc, 'Close', args);
                      CLIENT_OLE2.DESTROY_ARGLIST(args);
                      CLIENT_OLE2.RELEASE_OBJ(selection);
                      CLIENT_OLE2.RELEASE_OBJ(doc);
                      CLIENT_OLE2.RELEASE_OBJ(docs);
                      -- exit MSWord
                      CLIENT_OLE2.INVOKE(app,'Quit');

                           EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                      v_error_code := SQLCODE;
                      v_error_message := substr(SQLERRM,1,200);
                      Message('No data found...!'|| v_error_code||' '||v_error_message);
                      WHEN OTHERS THEN
                      -- Consider logging the error and then re-raise
                      v_error_code := SQLCODE;
                      v_error_message := substr(SQLERRM,1,200);
                      Message('Other Error..!'|| v_error_code||' '||v_error_message);
                      RAISE;

                      END;
                      • 8. Re: Getting FRM-40734 when reading a BLOB column
                        Gerd Volberg
                        is it possible to debug this problem instead of using messages? That's normally x-times faster
                        • 9. Re: Getting FRM-40734 when reading a BLOB column
                          640284
                          Hi Gerd,
                          I just tried debugging the form in Forms Builder 9.0.4. I set several break points in the program unit. When it got to the following line

                          select template_data into vblob from vol_templates
                          where template_name = 'Training';

                          form displayed FRM-40734 Internal Error: PL/SQL error occured message and then quit running.

                          At this point, I am considering, writing a stored procedure to build the MS Word file and write it to a BLOB column in a table on the database. Then have the program unit in this form, read it in and write it to a Word file on the client's PC.

                          Thanks for your help,

                          Seyed