Home » Developer & Programmer » Forms » Unable to retrieve an image (Windows 10 Pro. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0, Forms 11g 32 bit)
Unable to retrieve an image [message #680071] Mon, 20 April 2020 17:37
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Hi all,

I am running into an issue where in I am unable to retrieve a saved image. Here's the background:
a) The image is stored in IMAGES table (which I am accessing through a synonym) and this table is in a different schema.
b) When I load and save the image on the form, I do not even get a message that says "record saved and transaction applied". However when I look into a few tables including IMAGES I see that the data is present in those tables. You can see the screenshot attached:
b.1) After the data is saved and when I query on the form, all the details are retrieved excepting the image.
c) where as when I create a new record and do not attach the image, the form says FRM-40400: Transaction complete: Record saved and applied.

What is that I am doing wrong? My code is present in POST-INSERT trigger and here it is:
DECLARE
 lv_color_id    STYLE_COLORS.color_id%TYPE;
 lv_NbrImageSeq NUMBER(5);
BEGIN	
 IF :GLOBAL.v_file IS NOT NULL THEN 
   	insert_in_images(:STLS.DESCRIPTION,
 								  SUBSTR(:GLOBAL.v_file, instr(:GLOBAL.v_file, '\', -1) + 1),
 								  SUBSTR(:GLOBAL.v_file,1,(INSTR(:GLOBAL.v_file,'\',-1,1)-1)),
 								  'SCREEN',
 								  'Y',
 								  'ACTIVE',
								   USER,
							  	SYSDATE,
							   	NULL,
							   	NULL,
							   	NULL,
							   	NULL,
							   	NULL,
							  	'DEFAULT',
							   	lv_NbrImageSeq
							    );
	    
	    
	ELSIF :GLOBAL.v_file IS NULL THEN
		     insert_in_images(:STLS.DESCRIPTION,
 								  NULL,
 								  NULL,
 								  'SCREEN',
 								  'Y',
 								  'ACTIVE',
								   USER,
							  	SYSDATE,
							   	NULL,
							   	NULL,
							   	NULL,
							   	NULL,
							   	NULL,
							  	'DEFAULT',
							   	lv_NbrImageSeq
							    );
				    
	
  END IF;
  					    

END;
Code for insert_in_images is as follows:
CREATE OR REPLACE PROCEDURE insert_in_images (p_CharStyleDesc IN VARCHAR2,
                                            p_CharFileName  IN VARCHAR2,
                                            p_CharFilePathName IN VARCHAR2,
                                            p_CharSource IN VARCHAR2,
                                            p_CharPublicInd IN VARCHAR2,
                                            p_CharStatus IN VARCHAR2,
                                            p_charCreatedBy IN VARCHAR2,
                                            p_DtCreatedDate IN DATE,
                                            p_CharModBy IN VARCHAR2,
                                            p_DtModDate IN DATE,
                                            p_BlobImgFile IN ORDSYS.ORDIMAGE,
                                            p_CharSessionId IN VARCHAR2,
                                            p_BlobImgThmb IN ORDSYS.ORDIMAGE,
                                            p_CharType IN VARCHAR2,
                      p_lv_NbrImageSeq OUT NUMBER
                                            )



  IS
    lv_count NUMBER(3);
    lv_NbrImage_id NUMBER(5);
    --lv_NbrImageSeq  NUMBER(5);
    lv_NbrSeqCapture NUMBER(5);
  BEGIN
      --Checking to see if the image was already created today (Sysdate)
             SELECT COUNT(*)
             INTO lv_count
             FROM images
             WHERE description = p_CharStyleDesc
             AND created_date > trunc(sysdate);

            IF lv_count > 0 THEN
               SELECT image_id
               INTO lv_NbrImage_id
               FROM images
               WHERE description = p_CharStyleDesc
               AND created_date > trunc(sysdate);

           p_lv_NbrImageSeq:= lv_NbrImage_id;

              UPDATE images
              SET original_name = p_CharFileName,
                  description = p_CharStyleDesc,
                  image_id = lv_NbrImage_id,
                  modified_by =  p_charCreatedBy,
                  modified_date = SYSDATE,
                  source =p_CharSource,
                  public_ind =p_CharPublicInd,
                  status =p_CharStatus,
                  original_path = p_CharFilePathName
              WHERE description = p_CharStyleDesc
              AND created_date > trunc(sysdate);

            ELSIF lv_count = 0 THEN
                   p_lv_NbrImageSeq:=image_id.nextval;
              INSERT INTO images(image_id,
                 description,
                 original_name,
                 original_path,
                 source,
                 public_ind,
                 status,
                 created_by,
                 created_date,
                 modified_by,
                 modified_date,
                 image_file,
                 session_id,
                 image_thumbnail,
                 type
                )
              VALUES
                (p_lv_NbrImageSeq,
                 p_CharStyleDesc,
                 p_CharFileName,
                 p_CharFilePathName,
                 p_CharSource,
                 p_CharPublicInd,
                 p_CharStatus,
                 p_charCreatedBy,
                 p_DtCreatedDate,
                 p_CharModBy,
                 p_DtModDate,
                 p_BlobImgFile,
                 p_CharSessionId,
                 p_BlobImgThmb,
                 p_CharType
                );
              END IF;
     COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
     log_output('Error in insertinimages procedure'||'-'||SQLERRM||'-'||SQLCODE);
     Raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
  END insert_in_images;
Structure of IMAGES table (which resides in a different schema) is as follows: I am accessing it via a synonym.
SQL> desc images  
Name            Type            Nullable Default   Comments   
--------------- --------------- -------- --------- --------   
IMAGE_ID        NUMBER(10)                                    
DESCRIPTION     VARCHAR2(120)   Y                             
ORIGINAL_NAME   VARCHAR2(250)   Y                             
ORIGINAL_PATH   VARCHAR2(200)   Y                             
SOURCE          VARCHAR2(20)    Y                             
PUBLIC_IND      VARCHAR2(1)                                   
STATUS          VARCHAR2(10)                                  
CREATED_BY      VARCHAR2(30)                                  
CREATED_DATE    DATE                                          
MODIFIED_BY     VARCHAR2(30)    Y                             
MODIFIED_DATE   DATE            Y                             
IMAGE_FILE      ORDSYS.ORDIMAGE Y                             
SESSION_ID      VARCHAR2(256)   Y                             
IMAGE_THUMBNAIL ORDSYS.ORDIMAGE Y                             
TYPE            VARCHAR2(10)             'DEFAULT'
Any assistance on this would be highly appreciated.

[Updated on: Mon, 20 April 2020 17:39]

Report message to a moderator

Previous Topic: Coupan calculation in pos
Next Topic: get report name
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jan 22 08:03:48 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.