Home » SQL & PL/SQL » SQL & PL/SQL » Read Images from DB server (Oracle 10g with aps10g r2)
Read Images from DB server [message #425634] Sun, 11 October 2009 01:34 Go to next message
aabuamara
Messages: 4
Registered: April 2005
Location: Jordan
Junior Member
Hi all,
I'm tring to read images from DB server , with code below,
there is a little problem which is when I get the image name from DB table then go seaching for it at DB server , and when image name not found at the DB server , the loop ended auto.
What i'm looking for is to countine loop run.
------------
PROCEDURE CNV_READ_IMAGE_PROC(P_BRCH_CODE  IN NUMBER,
                                P_PROGRAM_ID IN VARCHAR2) IS
    CURSOR C1 IS
      SELECT T1.ACCNO || LPAD(T1.AUTH_NO, 2, '0') ACC, BRCHCODE
        FROM SIGICBS T1;
    F_LOB   BFILE;
    B_LOB   BLOB;
    V_IMAGE VARCHAR2(25);
  BEGIN
    BEGIN
      DELETE CNV_SIG_IMG WHERE SIG_BRCH = P_BRCH_CODE;
      --  DELETE BBSD_CNV_EXCEPTIONS_D
      --  WHERE  FILE_NAME LIKE 'CNVSIGIMG';
    END;
    FOR I IN C1 LOOP
      V_IMAGE := I.ACC;
      INSERT INTO CNV_SIG_IMG
      VALUES
        (I.ACC, EMPTY_BLOB(), I.BRCHCODE) RETURN SIG_IMAGE INTO B_LOB;
      F_LOB := BFILENAME('CNV_READ_IMAGE_DIR', I.ACC || '.jpg');
      DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY);
      DBMS_LOB.LOADFROMFILE(B_LOB, F_LOB, DBMS_LOB.GETLENGTH(F_LOB));
      DBMS_LOB.FILECLOSE(F_LOB);
      COMMIT;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      BEGIN
        INSERT INTO BBSD_CNV_EXCEPTIONS_D
          (BRCH_CODE, USER_ID, TIME_STAMP, ERROR_DESC, PROGRAM_ID, FILE_NAME)
        VALUES
          (P_BRCH_CODE, USER, SYSDATE, 'COULDN''T FIND THE IMAGE FOR ' || V_IMAGE, P_PROGRAM_ID, 'CNVSIGIMG');
      END;
      commit;

[Updated on: Sun, 11 October 2009 02:11] by Moderator

Report message to a moderator

Re: Read Images from DB server [message #425637 is a reply to message #425634] Sun, 11 October 2009 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your code is hard to read, please read OraFAQ Forum Guide, especially "How to format your post?" section and repost.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Read Images from DB server [message #425639 is a reply to message #425634] Sun, 11 October 2009 02:14 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and when image name not found at the DB server , the loop ended auto.

Include what is inside the loop in a BEGIN/END block and catch the exception.

Regards
Michel
Previous Topic: Firing a Trigger on a time
Next Topic: View with params from user
Goto Forum:
  


Current Time: Fri Dec 09 06:14:55 CST 2016

Total time taken to generate the page: 0.06561 seconds