but the blob content after insert does not show any link to the attachment
Are you talking about showing a link in a Report?
The value used for the 'link' column in the report should be the size of the BLOB. (hint: DBMS_LOB.GETLENGTH( blob_column) AS link )
The 'link' column then needs to be "formatted" -- specifically, you need to set the format to: BLOB
When you do that, a section dedicated for 'filling in the blanks' will appear. (APEX uses information in that section to create the URL link)
You'll need to fill out "table name", "pk column name", "file name" and "blob column name"
(you may have to create a synonym in your parsing schema that points to that table since you can't include "schema name" in that section.)
(choose "attachment" if you want a link. use "embedded" if you are trying to display a thumbnail images in the report.)
From there, APEX will magically create a link that will work.
Oracle provides documentation on how to handle LOBs in APEX.
PS - this line gives me the shivers..
select COALESCE (MAX(CUSTOM_REVISED_ID), 0) + 1 INTO REVISION_ID from RTA.RTA_CUSTOM_REPORT_REVISION;
It is logically flawed because you assume only one person will be using this application at any given time.
Error codes usually come with a line number and some other text that helps determine the exact location where the error occurred.
I don't know the location of the error, so I can't help you.
It will also help if you should show the relevant information of the DEBUG page. (ie run it in "DEBUG")
Also, feel free to place a few APEX_DEBUG.MESSAGE() lines within your process to assist you in finding the precise error.
(eg show a debug message of what values you are going to use before you use them.)
As always, if problems become too complicated to address in the forums, try to reproduce the problem on apex.oracle.com using non-confidential information.
(don't forget to create a 'developer' account so you don't have to post up your email address for the spam-bots to collect)
I don't think the error is from the code because as soon as I changed the blob column attribute in report page it appears and when I remove that it will go away. When I change the format to blob and fill out the blob column attribute, it will give me this error and it does not have any line number associated with. The error is just:
report error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error