File Upload Download in Apex

FILE UPLOADING AND DOWNLOADING:

CREATE TABLE  "XXP_UPLOAD1"("ID" NUMBER(10,0),"FILENAME" VARCHAR2(400),     "MIMETYPE" VARCHAR2(400),"FILECONTENT" BLOB,"DOC_SIZE" NUMBER(10,0),

        "NAME" VARCHAR2(400),"DESCRIPTION" VARCHAR2(400),

        "FNAME" VARCHAR2(400),

         CONSTRAINT "XXP_UPLOAD1_PK" PRIMARY KEY ("ID") ENABLE  )

-------Table created-----------

1>create blank page in apex, add html region, add two page item to accept name and description, one page item for browsing, create one submit/upload Button,in the same region create classic report. then run the application. when you run it look like this

 3>create one blank page with no tabs to assigned to it, create one page item button on it (to accept id )

4>in branching processing create download process (download_my_file) and pass id to it.

Right click->create process->select process name->select BRANCH TYPE=BRANCH TO PLSQL PROCEDURE

->WRITE PROCEDURE NAME  ie download_my_file(:P1_ID) (in my application)

5> run the application

ie download_my_file code is given below:

CREATE OR REPLACE PROCEDURE download_my_file(p_file IN NUMBER) AS

v_mime VARCHAR2(48);

v_length NUMBER;

v_file_name VARCHAR2(2000);

Lob_loc BLOB;

BEGIN

SELECT MIMETYPE, FILECONTENT, fname,DBMS_LOB.GETLENGTH(FILECONTENT)

INTO v_mime,lob_loc,v_file_name,v_length

FROM xxp_upload1

WHERE id = p_file;

---- set up HTTP header

--

-- use an NVL around the mime type and

-- if it is a null set it to application/octect

-- application/octect may launch a download window from windows

owa_util.mime_header( nvl(v_mime,'application/octet'),FALSE );

-- set the size so the browser knows how much to download

htp.p('Content-length: ' || v_length);

-- the filename will be used by the browser if the users does a save as

htp.p('Content-Disposition: attachment;

filename="'||replace(REPLACE(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),NULL),chr(13),NULL)|| '"');

-- close the headers

owa_util.http_header_close;

-- download the BLOB

wpg_docload.download_file( Lob_loc );

END download_my_file;

Apex Welcome Page Creation

Download Data to CSV/Excel

TO  FETCH  DATA  FROM  DATABASE &  SAVE IT INTO EXCEL FILE

Step 1: Write a procedure to get data from the database using HTML codes and insert it into a variable of type Clob. As the Example shown below.

PROCEDURE DOWNLOAD_CSV(p_batch_id number) AS

v_download clob;

v_insert varchar2(32000) := '';

v_length  NUMBER;

l_filename VARCHAR2(100);

v_blob  blob;

v_clob clob;

v_mime  VARCHAR2(4000) := 'application/octet-stream';

v_name varchar2(150) := 'Student Names';

v_lang1 varchar2(50) := 'I Lang';

v_lang2 varchar2(50) := 'II Lang';

v_lang3 varchar2(50) := 'III Lang';

v_lang4 varchar2(50) := 'Subject';

v_lang5 varchar2(50) := 'Subject';

v_lang6 varchar2(50) := 'Subject';

v_sub1 varchar2(50);

v_sub2 number;

v_sub3 number;

v_batch varchar2(50) := 'Batchid';

cursor Cur is

select a.firstname,a.lastname

from ct_admission_info a,ct_stud_batch_tbl s

where a.id = s.studid

and s.batchid = p_batch_id;

cursor cur1 is select * from ct_dummy;

begin

/*select subject_id into v_sub1 from CT_BATCH_SUBJECT_TBL

where subject_id = 61

and  batch_id = p_batch_id;

select shortname into v_sub1 from ct_subject_master a, CT_BATCH_SUBJECT_TBL b

where a.id = b.subject_id

and b.batch_id = p_batch_id

and b.subject_id = 61;

select subject_id into v_sub2 from CT_BATCH_SUBJECT_TBL

where subject_id = 62

and  batch_id = p_batch_id;

select subject_id into v_sub3 from CT_BATCH_SUBJECT_TBL

where subject_id = 63

and  batch_id = p_batch_id;*/

v_insert :='<table border ="1" width = "100%">';

v_insert := v_insert||'<tr>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_batch;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_name;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang1;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang2;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang3;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang4;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang5;

v_insert := v_insert||'</th>';

v_insert := v_insert||'<th bgcolor="yellow">';

v_insert := v_insert||v_lang6;

v_insert := v_insert||'</th>';

v_insert := v_insert||'</tr>';

v_insert := v_insert||'<tr>';

v_insert := v_insert||'<td bgcolor="orange">';

v_insert := v_insert||p_batch_id;

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="orange">';

v_insert := v_insert||'';

v_insert := v_insert||'</td>';

 insert into ct_dummy (select shortname from ct_subject_master a, CT_BATCH_SUBJECT_TBL b

where a.id = b.subject_id and b.batch_id = p_batch_id);

for j in cur1

loop

v_insert := v_insert||'<td bgcolor="orange">';

v_insert := v_insert||j.subject;

v_insert := v_insert||'</td>';

end loop;

v_insert := v_insert||'</tr>';

/*v_insert := v_insert||'<td>';

v_insert := v_insert||v_sub2;

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td>';

v_insert := v_insert||v_sub3;

v_insert := v_insert||'</td>';

v_insert := v_insert||'</tr>';*/

for i in cur

loop

v_insert := v_insert||'<tr>';

v_insert := v_insert||'<td>';

v_insert := v_insert||'';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td>';

v_insert := v_insert||i.firstname||i.lastname;

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

v_insert := v_insert||'<td bgcolor="yellow">';

v_insert := v_insert||'</td>';

/*

for j in c_sub

loop

v_insert := v_insert||'<td>';

v_insert := v_insert||j.subject_id;

v_insert := v_insert||'</td>';

end loop;*/

v_insert := v_insert||'</tr>';

end loop;

v_insert :=v_insert||'</table>';

v_download := v_insert;

v_blob := clob_to_blob_func(v_download); ***

l_filename := 'Attendance.xls';

 ** owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );

                 -- set the size so the browser knows how much to download

   v_length := DBMS_LOB.GETLENGTH(v_blob);

   htp.p('Content-length: ' || v_length);

               -- the filename will be used by the browser if the users does a save as

   htp.p('Content-Disposition:  attachment;filename="'||l_filename|| '"');

                -- close the headers           

   owa_util.http_header_close;

                -- download the BLOB

   wpg_docload.download_file(v_blob);**

  

   delete from ct_dummy;

   commit;

     

END DOWNLOAD_CSV;

Note *** : It is a Standard Function which converts CLOB into BLOB, this is then called inside the procedure as shown above.

Standard Function to converts CLOB into BLOB is given below.

create or replace

FUNCTION clob_to_blob_func(v_clob Clob) RETURN BLOB  AS

v_blob Blob;

v_in Pls_Integer := 1;

v_out Pls_Integer := 1;

v_lang Pls_Integer := 0;

v_warning Pls_Integer := 0;

v_id number(10);

begin

dbms_lob.createtemporary(v_blob,TRUE);

DBMS_LOB.convertToBlob(v_blob,v_clob,DBMS_lob.getlength(v_clob),

v_in,v_out,DBMS_LOB.default_csid,v_lang,v_warning);

RETURN v_blob;

END  clob_to_blob_func;

Note ** : The code with two stars is the standard codes used to give filename.

Adding Breadcrumb in Apex

Progress Bar in Apex

Put in html header and body attribute

<script type="text/javascript">

<!--

function html_Submit_Progress(pThis){

$x_Show('AjaxLoading');

window.setTimeout('$s("AjaxLoading",$x("AjaxLoading").innerHTML)', 100);

doSubmit('GO');

}

function submit_HideAll(pThis){

$x_Hide('wwvFlowForm');

doSubmit('GO');

}

function submit_ButtonRegion(pThis){

$x_Hide('button_region');

doSubmit('GO');

}

//-->

</script>

Put in html footer text

<style> #AjaxLoading{padding:5px;font-size:18px;width:500px;text-align:center;left:20%;top:20%;position:absolute;border:2px solid #666;background-color:#fff;}

</style>

<div id="AjaxLoading" style="display:none;"><div>..Compliance Service Running..</div></br><div align="center"><img src="/i/fusioncharts/spinner4-bluey.gif" id="wheel" /></div></br>Please do not press Go again until you have been notified that the compliance run has completed</br></br><img src="/i/fusioncharts/pleasewait.gif" id="wait" /></div>

Action when button clicked

javascript:html_Submit_Progress(this);

Oracle Silver Partner

Agilets Pvt Ltd became Partner with Oracle - (Silver Partner in the month of  July - 2012)...

JQuery Tab in APEX

 

1)   Goto  Shared Components

2)   Select  template

3)   Create a new template of type "Region" (create one from scratch)

4)   Name: jQuery Tabs

5)   Template Class: Custom 1

6)   Place the below code in the “Definition” section:

<div id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>

#BODY##SUB_REGION_HEADERS##SUB_REGIONS#

<div style="clear:both;"></div>

</div>

<link rel="stylesheet" href="#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/jquery.ui.tabs.css" type="text/css" />

<script src="#IMAGE_PREFIX#libraries/jquery-ui/1.8/ui/minified/jquery.ui.tabs.min.js" type="text/javascript"></script>

7)    Header Template

<ul style="height: auto;">#ENTRIES#</ul>

8)    Header Entry Template

<li><a href="##REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION_TITLE#</a></li>

9)    Template

<div id="#REGION_STATIC_ID#-tab-#SUB_REGION_ID#">#SUB_REGION#</div>

10)After this, goto page and create a html region.

10)            Select  Template of html region as jQuery Tabs.

11)              Place the below code in region footer.

<script type="text/javascript">

 apex.jQuery(function() {

 apex.jQuery("##REGION_STATIC_ID#").tabs();

 });

</script>

12)              Now select the regions that should be transferred into a tab and change the parent region as html region created above.