Home » SQL & PL/SQL » SQL & PL/SQL » Procedure image load problem (2 Merged) (Oracle sqldeveloper)
icon9.gif  Procedure image load problem (2 Merged) [message #571837] Sat, 01 December 2012 07:35 Go to next message
katspn
Messages: 2
Registered: December 2012
Location: Plymouth
Junior Member
I am creating a image database and have hit a snag.
I have created this procedure below which is suppose to take the filename (saved in the filename coloum) and upload the image to the image coloumn which is and ORDSYS.ORDImage.

when I run the procedure in sqldeveloper it creates the procedure without any errors however when I look in the procedures folder in the sidebar it has a red X over the procedure which suggests that I have done something wrong.

also when I place the procedure under a button it does not pull the image from the file as it is ment to Confused
I am now well and truly stuck, if anyone can help I would be very very grateful

create or replace
PROCEDURE upload_new_image
(p_filename IN VARCHAR2)
IS

l_upload_size INTEGER;
l_upload_blob BLOB;
l_park_id NUMBER;
l_image ORDSYS.ORDImage;

BEGIN

-- Get the length, MIME type and the BLOB of the new image from the
-- upload table. apex_application_files is a synonym for WWV_FLOW_FILES

SELECT doc_size,
blob_content
INTO l_upload_size,
l_upload_blob
FROM apex_application_files
WHERE name = p_filename;

-- Insert a new row into the table, returning the newly allocated sequence
-- number from seq_park_id

INSERT INTO Parks
(
park_id,
filename,
image
)
VALUES
(
seq_park_id.nextval,
p_filename,
ORDSYS.ORDIMAGE.INIT()
)
RETURNING park_id
INTO l_park_id;

-- lock the row
SELECT image
INTO l_image
FROM Parks
WHERE park_id = l_park_id
FOR UPDATE;

-- copy the blob into the ORDImage BLOB container
DBMS_LOB.COPY( l_image.source.localData, l_upload_blob, l_upload_size );
l_image.setProperties(); -- just in case

UPDATE Parks
SET image = l_image
WHERE park_id = l_park_id;

-- clear from upload table
DELETE FROM apex_application_files
WHERE name = p_filename;

COMMIT;

EXCEPTION
WHEN others
THEN htp.p(SQLERRM);

upload_new_image;
END;
icon9.gif  Procedure image load problem [message #571838 is a reply to message #571837] Sat, 01 December 2012 07:36 Go to previous messageGo to next message
katspn
Messages: 2
Registered: December 2012
Location: Plymouth
Junior Member
Sad

[Updated on: Sat, 01 December 2012 08:26]

Report message to a moderator

Re: Procedure image load problem [message #571841 is a reply to message #571838] Sat, 01 December 2012 08:40 Go to previous message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's compiled with errors. You can query user_errors to see what they are.
Previous Topic: Unable to send Email through Google-mail to private domain
Next Topic: invalid number
Goto Forum:
  


Current Time: Sat Apr 19 09:01:02 CDT 2014

Total time taken to generate the page: 0.09725 seconds