Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » File Upload and download into ORacle DB Thro APEX (Oracle 10G/Linux)
File Upload and download into ORacle DB Thro APEX [message #419309] Fri, 21 August 2009 02:09 Go to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
I have a form based on the table below. On the forrm, the filename(all type of documents)
is defined as 'File Browser'. I need to manually insert into the table (instead of using the automated row insert). What is the code needed to insert into the BLOB with SQL?.


Thanks,
Dhanalakshmi.
Re: File Upload and download into ORacle DB Thro APEX [message #419326 is a reply to message #419309] Fri, 21 August 2009 04:58 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
The files uploaded trough a file browse item can be found in the apex_application_files view. Find the blob from there (blob_content column) and insert it into your table. (The file you uploaded is the one where the name column equals the value of your file browse item)

http://download.oracle.com/docs/cd/E10513_01/doc/appdev.310/e10497/up_dn_files.htm#CIHHEHCJ
Re: File Upload and download into ORacle DB Thro APEX [message #419331 is a reply to message #419326] Fri, 21 August 2009 05:39 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
while uploading into table,its throwing some HTTP 404 File Not Found Error.I couldn't able to insert the same into my table.


Thanks,
Dhanalakshmi.
Re: File Upload and download into ORacle DB Thro APEX [message #419333 is a reply to message #419331] Fri, 21 August 2009 05:50 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Then it would seem that there might be something wrong in your branch. Which page are you branching to? Is it a page in your application (which doesn't seem likely) or have you just given in a value in the URL field? Have you checked your table, and is the blob content inserted? (A SQL insert would not be able to throw a HTTP 404 file not found error...)
Re: File Upload and download into ORacle DB Thro APEX [message #419534 is a reply to message #419309] Mon, 24 August 2009 01:33 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Thanks for your guidelines.Now i could able to insert the docs into the table.But while downloading the doc from the custom table it is throwing an ORA:065012 PL/SQL numaeric or value error.

For your reference,i have attached the stored proc that i used to download the file and the error message screen shot.


Please help me!!!!!!!!!!!!!


Thanks,
Dhanalakshmi.
  • Attachment: download.sql
    (Size: 1.37KB, Downloaded 2073 times)

[Updated on: Mon, 24 August 2009 01:34]

Report message to a moderator

Re: File Upload and download into ORacle DB Thro APEX [message #419535 is a reply to message #419309] Mon, 24 August 2009 01:35 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
here with i have attached the screen shot of the error message..


Thanks,
Dhanalakhmi.
Re: File Upload and download into ORacle DB Thro APEX [message #419553 is a reply to message #419535] Mon, 24 August 2009 04:12 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
It seems correct to me. Can you please give an example of the data you've used when calling the procedure?
Re: File Upload and download into ORacle DB Thro APEX [message #419555 is a reply to message #419309] Mon, 24 August 2009 04:17 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
The input param used is 19913908666520409 as p_file.


Re: File Upload and download into ORacle DB Thro APEX [message #419560 is a reply to message #419555] Mon, 24 August 2009 04:38 Go to previous messageGo to next message
gauravdua
Messages: 9
Registered: January 2008
Junior Member
Hi,

It seems correct.
just try two things,i hope it will work

1.In procedure take input parameter as Varchar2 (i.e download_my_file(p_file in varchar2).
2.Add "owa_util.http_header_close;" which you have comment in yours procedure

Please let me know...

Regards
Gaurav Dua
Re: File Upload and download into ORacle DB Thro APEX [message #419565 is a reply to message #419309] Mon, 24 August 2009 05:03 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
It is not working ...Agina getting the same error.I think problem starts here.Actually i had tested with some dbms_output statements on step by step process.The error throwing at this point of time.i.e while coming into the below step,
owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
throwing error.



Thanks,
Dhanalakshmi.
Re: File Upload and download into ORacle DB Thro APEX [message #419574 is a reply to message #419565] Mon, 24 August 2009 05:26 Go to previous messageGo to next message
gauravdua
Messages: 9
Registered: January 2008
Junior Member
Hi,
Just try attached procedure.
And make this procedure in some package and call package.download_my_file(:P10_DOC_ID) from the branch action in apex and branch type : Branch to pl/sql Procedure.

Regards
Gaurav Dua
  • Attachment: download.sql
    (Size: 1.07KB, Downloaded 1520 times)
Re: File Upload and download into ORacle DB Thro APEX [message #419585 is a reply to message #419309] Mon, 24 August 2009 06:38 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
oops..This is also not working fine ..


Re: File Upload and download into ORacle DB Thro APEX [message #419596 is a reply to message #419585] Mon, 24 August 2009 07:37 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Which error message do you get? Try granting execute on the procedure to apex_public_user
Re: File Upload and download into ORacle DB Thro APEX [message #419616 is a reply to message #419574] Mon, 24 August 2009 08:34 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Oh, I see now that gauravdua's procedure won't even compile. Please test your code before publishing it. (And to both of you, please, the next time publish your code in the actual post, using code tags. It will be a lot easier to read without having to go trough the step of downloading some file. Especially since your code is quite short)

And also, adding a loop around the fetching of the file is of no use. I assume that the id given in is unique, and the error if it wasn't would be too many rows, not numeric or value error. Use the code you originally had. That one will compile at least. And if kb_id is a number you should keep p_file as a number rather than a varchar2. As I said earlier, try granting execute on the procedure to apex_public_user. Call your procedure by using dbsupport.download_my_file?p_file=somefilenumber in the link in your application
Re: File Upload and download into ORacle DB Thro APEX [message #419670 is a reply to message #419309] Tue, 25 August 2009 00:23 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Stenerson,

Thanks a lot for your reply...You are absolutely correct..The problem is with Grant to the apex_application_user.Once granted, everything is working fine...


But i couldn't able to understand why we need this grant.Since anyway we are creating the procedure in the same workspace only.Then what is the need of grant.
Please clarify this doubt.


Thanks,
Dhanalakshmi.
Re: File Upload and download into ORacle DB Thro APEX [message #419683 is a reply to message #419670] Tue, 25 August 2009 02:36 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Now, this I don't know for sure, but I would assume it's because the function can't be called from your workspace only. E.g. in our company, we have several workspaces residing on the same server. But the applications would all have the same url structure, no matter which workspace they reside in. www.someserver.com/somename/f?p=appnumber.
But when the procedure you defined is called you do it trough www.someserver.com/somename/dbsupport.download_my_file?p_file=somefilenumber
So trough this, which application you are using is not known, and no authentication will be done. By granting access to apex_public_user though, you're letting apex call the procedure regardless of the user. If you want some extra access control you should probably add that to your procedure. (You could just send a HTML document with an error message using htp.p if the person shouldn't have access.) If not, anyone who knows the url will be able to access it.
Previous Topic: Generate XML with additional Nodes.
Next Topic: auto login from one application to anothor
Goto Forum:
  


Current Time: Thu Mar 28 21:04:41 CDT 2024