Please follow below steps to
export an application from PROD Instance :
1. Login to PROD APEX
Workspace.
2. Click on Application
builder icon as shown in below screenshot.
3. Click on Edit
button of the application that you want to export.We have to export Application No. 103 from PROD instance.
4. Below screen will appear. Now,Click on Export/Import icon.
5.Select Export
option and click Next.
5.Cick on Export Application
6.You will get f103.sql
export file for that application.
Please follow below steps to import
APEX Application successfully on another instance:
1. Check whether correct apex version is configured same as
PROD.
2. Login to Apex Environment(New Apex Instance).
3. After login, Run below query from SQL workshop to get
current workspace id.
4. Check whether above workspace id is matching with
workspace id in f103.sql file
(Application Exported from PROD) , If not then update the f103.sql file with above workspace id (new workspace id of the
instance workspace on which we are installing application). And save the
changes.
5. Now, Go to application builder and click on Import button as shown below
6. Below screen will appear, choose options as given below
and click on Next button :
·
Import File : Click on choose file and select f103.sql from directory location where
this file is kept.
·
File Type : Select option “Database Application, Page or Component Export”.
·
File Character Set : keep “Unicode UTF-8”.
7. It may take few minutes to import file.
8. Apex will show successful import message, just click on Next Button:
9. Below screen will appear, choose options as given below
and click on Install Application
button :
·
Parsing Schema : choose database schema
·
Build Status : Run and Build Application
·
Install As Application : select 2nd
option “Reuse Application ID from Export File”
10. Installation in progress, It may take few minutes to
install.
11. It will show successful message if application is
installed correctly.
Integrating APEX with Oracle E-Business Suite :
In order to connect from Oracle E-Business Suite to your new Oracle Application Express
Applications, a mechanism is required to hand control from Oracle E-Business Suite to Oracle Application Express. The following actions need to be performed:
We are going to use the cookie settings in the icx_sessions table of Oracle E-Business Suite.
Because, when checking the session variables I store more relevant Oracle E-Business Suite session information in Oracle Application Express Application Items.
Application Items (Shared Components > Logic)
Scope : Application
Session State Protection : Restricted - May not be set from browser
The Application Items are used in queries or when setting the ‘environment’ (apps_initialize).
Authentication (Shared Components > Security)
The Oracle Application Express pages are directly launched from the E-Business Suite. Additional login is not desirable, so no Authentication Scheme.
Authorization (Shared Components > Security)
I created an Authorization Scheme 'Check EBS Credentials' that will check whether the user has a valid E-Business Suite session. If so, then session attributes are copied into the Application Items. If not, then an error message will be displayed that access is not allowed. The E-Business Suite function icx_sec.getsessioncookie is used to determine the session_id. This session_id is the key to retrieve additional information from the E-Business Suite tables icx_sessions and icx_session_attributes.
Authorization Schemes: Create>
Next>
Name: Check EBS Credentials
Scheme Type: PL/SQL Function Returning Boolean
PL/SQL Function Body:
BEGIN
RETURN apps.apex_global.check_ebs_credentials;
END;
Error message displayed when scheme violated: "Access not allowed: No valid E-Business Suite session."
Evaluation point: once per page view
Create Authorization Scheme
Code of function check_ebs_credentials (from package body apps.apex_global):
FUNCTION check_ebs_credentials
RETURN BOOLEAN
IS
c_ebs VARCHAR2(240) := 'E-Business Suite';
--
l_authorized BOOLEAN;
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_sec_group_id NUMBER;
l_org_id NUMBER;
l_time_out NUMBER;
l_ebs_url VARCHAR2(100);
l_appl_name VARCHAR2(240);
--
CURSOR get_apps_credentials
IS
SELECT iss.user_id
, iss.responsibility_id
, iss.responsibility_application_id
, iss.security_group_id
, iss.org_id
, iss.time_out
, isa.value
FROM apps.icx_sessions iss
, apps.icx_session_attributes isa
WHERE iss.session_id = apps.icx_sec.getsessioncookie
AND isa.session_id = iss.session_id
AND isa.name = '_USERORSSWAPORTALURL';
--
CURSOR get_appl_name (b_appl_id NUMBER)
IS
SELECT application_name
FROM apps.fnd_application_tl
WHERE application_id = b_appl_id
AND language = USERENV('LANG');
--
BEGIN
OPEN get_apps_credentials;
FETCH get_apps_credentials
INTO l_user_id
, l_resp_id
, l_resp_appl_id
, l_sec_group_id
, l_org_id
, l_time_out
, l_ebs_url;
--
IF get_apps_credentials%NOTFOUND THEN
l_authorized := FALSE;
ELSE
l_authorized := TRUE;
--
OPEN get_appl_name(l_resp_appl_id);
FETCH get_appl_name INTO l_appl_name;
IF get_appl_name%NOTFOUND THEN
l_appl_name := c_ebs;
END IF;
CLOSE get_appl_name;
--
apex_util.set_session_state('EBS_USER_ID',TO_CHAR(l_user_id));
apex_util.set_session_state('EBS_RESP_ID',TO_CHAR(l_resp_id));
apex_util.set_session_state('EBS_RESP_APPL_ID',TO_CHAR(l_resp_appl_id));
apex_util.set_session_state('EBS_SEC_GROUP_ID',TO_CHAR(l_sec_group_id));
apex_util.set_session_state('EBS_ORG_ID',TO_CHAR(l_org_id));
-- apex_util.set_session_state('EBS_TIME_OUT',TO_CHAR(l_time_out));
apex_util.set_session_state('EBS_URL',l_ebs_url);
apex_util.set_session_state('EBS_APPLICATION_NAME',l_appl_name);
--
apex_util.set_session_max_idle_seconds(l_time_out*60,'APPLICATION');
END IF;
--
CLOSE get_apps_credentials;
RETURN l_authorized;
--
EXCEPTION
WHEN OTHERS THEN
IF get_apps_credentials%ISOPEN THEN CLOSE get_apps_credentials; END IF;
RETURN FALSE;
END;
Security Attributes (Shared Components > Security)
Access to any page in the APEX application is not allowed when no E-Business Suite session is active. This is arranged by setting the Authorization Scheme as a Security Attribute. However, it is also possible to manage authorization per page. In the latter case don't set the authorization scheme as shared component.
Security > Security Attributes: Authorization
Authorization Scheme: Check EBS Credentials
Apply Changes.
In order to connect from Oracle E-Business Suite to your new Oracle Application Express
Applications, a mechanism is required to hand control from Oracle E-Business Suite to Oracle Application Express. The following actions need to be performed:
1. Create an Oracle Application Express application
2. Configure the Oracle Application Express login process if you want to authenticate user before accessing the application.
3. Define Oracle Application Express authorizations
4. Define Oracle E-Business Suite profile and functions
5. Link functions to menus and responsibility
Perform below activities from APEX side :
Creating an Oracle Application Express ApplicationWe are going to use the cookie settings in the icx_sessions table of Oracle E-Business Suite.
Because, when checking the session variables I store more relevant Oracle E-Business Suite session information in Oracle Application Express Application Items.
Application Items (Shared Components > Logic)
Scope : Application
Session State Protection : Restricted - May not be set from browser
| Application Item Name | Comments |
| EBS_USER_ID | Key to User; To check EBS authorization and to set EBS context (icx_sessions) |
| EBS_RESP_ID | Key to Responsibility; To check EBS authorization and to set EBS context (icx_sessions) |
| EBS_RESP_APPL_ID | Key to Responsibility Application; To check EBS authorization and to set EBS context (icx_sessions) |
| EBS_SEC_GROUP_ID | Key to Security Group; To check EBS authorization and to set EBS context (icx_sessions) |
| EBS_TIME_OUT | Session Time Out in Oracle E-Business Suite (icx_sessions) |
| EBS_URL | URL to return to EBS Homepage from APEX (icx_session_attributes) |
| EBS_ORG_ID | EBS ORG_ID (icx_sessions) - MO: Operating Unit from Responsibility |
| EBS_APPLICATION_NAME | To be displayed at the left tophand corner (application_name from fnd_application_tl using EBS_RESP_APPL_ID) |
Authentication (Shared Components > Security)
The Oracle Application Express pages are directly launched from the E-Business Suite. Additional login is not desirable, so no Authentication Scheme.
Authorization (Shared Components > Security)
I created an Authorization Scheme 'Check EBS Credentials' that will check whether the user has a valid E-Business Suite session. If so, then session attributes are copied into the Application Items. If not, then an error message will be displayed that access is not allowed. The E-Business Suite function icx_sec.getsessioncookie is used to determine the session_id. This session_id is the key to retrieve additional information from the E-Business Suite tables icx_sessions and icx_session_attributes.
Authorization Schemes: Create>
Next>
Name: Check EBS Credentials
Scheme Type: PL/SQL Function Returning Boolean
PL/SQL Function Body:
BEGIN
RETURN apps.apex_global.check_ebs_credentials;
END;
Error message displayed when scheme violated: "Access not allowed: No valid E-Business Suite session."
Evaluation point: once per page view
Create Authorization Scheme
FUNCTION check_ebs_credentials
RETURN BOOLEAN
IS
c_ebs VARCHAR2(240) := 'E-Business Suite';
--
l_authorized BOOLEAN;
l_user_id NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_sec_group_id NUMBER;
l_org_id NUMBER;
l_time_out NUMBER;
l_ebs_url VARCHAR2(100);
l_appl_name VARCHAR2(240);
--
CURSOR get_apps_credentials
IS
SELECT iss.user_id
, iss.responsibility_id
, iss.responsibility_application_id
, iss.security_group_id
, iss.org_id
, iss.time_out
, isa.value
FROM apps.icx_sessions iss
, apps.icx_session_attributes isa
WHERE iss.session_id = apps.icx_sec.getsessioncookie
AND isa.session_id = iss.session_id
AND isa.name = '_USERORSSWAPORTALURL';
--
CURSOR get_appl_name (b_appl_id NUMBER)
IS
SELECT application_name
FROM apps.fnd_application_tl
WHERE application_id = b_appl_id
AND language = USERENV('LANG');
--
BEGIN
OPEN get_apps_credentials;
FETCH get_apps_credentials
INTO l_user_id
, l_resp_id
, l_resp_appl_id
, l_sec_group_id
, l_org_id
, l_time_out
, l_ebs_url;
--
IF get_apps_credentials%NOTFOUND THEN
l_authorized := FALSE;
ELSE
l_authorized := TRUE;
--
OPEN get_appl_name(l_resp_appl_id);
FETCH get_appl_name INTO l_appl_name;
IF get_appl_name%NOTFOUND THEN
l_appl_name := c_ebs;
END IF;
CLOSE get_appl_name;
--
apex_util.set_session_state('EBS_USER_ID',TO_CHAR(l_user_id));
apex_util.set_session_state('EBS_RESP_ID',TO_CHAR(l_resp_id));
apex_util.set_session_state('EBS_RESP_APPL_ID',TO_CHAR(l_resp_appl_id));
apex_util.set_session_state('EBS_SEC_GROUP_ID',TO_CHAR(l_sec_group_id));
apex_util.set_session_state('EBS_ORG_ID',TO_CHAR(l_org_id));
-- apex_util.set_session_state('EBS_TIME_OUT',TO_CHAR(l_time_out));
apex_util.set_session_state('EBS_URL',l_ebs_url);
apex_util.set_session_state('EBS_APPLICATION_NAME',l_appl_name);
--
apex_util.set_session_max_idle_seconds(l_time_out*60,'APPLICATION');
END IF;
--
CLOSE get_apps_credentials;
RETURN l_authorized;
--
EXCEPTION
WHEN OTHERS THEN
IF get_apps_credentials%ISOPEN THEN CLOSE get_apps_credentials; END IF;
RETURN FALSE;
END;
Security Attributes (Shared Components > Security)
Access to any page in the APEX application is not allowed when no E-Business Suite session is active. This is arranged by setting the Authorization Scheme as a Security Attribute. However, it is also possible to manage authorization per page. In the latter case don't set the authorization scheme as shared component.
Security > Security Attributes: Authorization
Authorization Scheme: Check EBS Credentials
Apply Changes.
Perform below activities from EBS side :
Update the FND: APEX URL profile option with the correct setting at the site level using the following steps:
1. Log in to Oracle E-Business Suite with
the SYSADMIN user
(http ://<EBS_Hostname>:8074/OA_HTML/AppsLogin)
2. Navigate to the System Administrator
responsibility > Profile > System menu option
3. Search for Profile %APEX%, click Find
4. for profile FND: APEX URL enter Site http://<EBS_Hostname>:8080/apex
5.
Save the profile
Perform the following steps to define the
Oracle E-Business Suite functions:
1. Navigate to the System Administrator responsibility
> Application > Function menu option
2. For calls to the page without
responsibility, create a function with the following details:
Description -
Function: APEX_FUN
User
Function Name: APEX Extension
Properties
–
Type:
JSP
Web HTML –
HTML Call: GWY.jsp?targetAppType=APEX&
p=<APEX Application Id>:
<APEX
Page>:<Session>:<Request>:<Debug>:<Clear Cache>:
<Parameter Pairs>
{ For example, to call Oracle Application
Express application 104, Page 1
use GWY.jsp?targetAppType=APEX&p=104:1:0::NO:1:
, all other parameters are
optional}
Add menu component under required Menu as
shown below-
Menu
Name:
User
Menu Name : [Specify User Menu Name here]
Menu
Type: Standard
3.
Create menu component –
Sequence:
1
Prompt:
Launch APEX Page
Function: APEX Extension
4.
Save the menu.
Following is the table detail which I want to populate from data in excel sheet.
Table Name : UPLOAD_DATA
Please follow below steps :
1) Created a “File Browse” item on a page and having internal name it P35_UPLOAD.
2) Created a button having internal name as “Upload“. This button is used to upload and process the data in the excel sheet.
3) Created a conditional branch when upload button is pressed, it should branch to the same page (35 in my case)
4) When we select an excel sheet using browse button and click on submit button, it will branch to same page (indirectly we are saying that its going to refresh the page). In such case even if there is no code written in the backend, APEX is going to load the excel sheet in a BLOB format into a table wwv_flow_files.
This is the internal table used by APEX. Our task is to read this table and get the required row which got inserted into this table. With each upload 1 row will get inserted into the table. One of the column of the table (BLOB_CONTENT) is BLOB where the actual excel sheet is uploaded. All other columns are metadata about excel.
When we upload an excel sheet, a random name will get generated for the file in the form FXXXXX/<file_name_you_uploaded>. You can use this to get the file details. Also when you refresh the page, it will upload the file to this table wwv_flow_files, but as soon as refresh completed and it displays the page back again, that row will get deleted again. So you have to get that row processed immediately using the code that you will write when submit button is pressed.
In my case, I want to upload the rows into UPLOAD_DATA table. My excel sheet content looks as shown below.
create or replace procedure "INSERT_ROW"
(
P_RECORD IN VARCHAR2
) AS
v_col_1 varchar2(100);
v_col_2 varchar2(100);
v_col_3 varchar2(100);
BEGIN
v_col_1 := substr(P_RECORD,1,instr(P_RECORD,',',1,1)-1);
v_col_2 := substr(P_RECORD,instr(P_RECORD,',',1,1)+1,instr(P_RECORD,',',1,2)-instr(P_RECORD,',',1,1)-1);
v_col_3 := substr(P_RECORD,instr(P_RECORD,',',1,2)+1,length(P_RECORD)-instr(P_RECORD,',',1,2));
--dbms_output.put_line('rECORD - ' ||v_col_1||' , '||v_col_2||' , '||v_col_3);
insert into UTR_OUTCOME(DESCRIPTION,STATUS,END_ON) values(v_col_1,v_col_2,v_col_3);
commit;
--dbms_output.put_line(v_col_5);
exception
when others then
--dbms_output.put_line('Error : '||SQLERRM);
insert into UTR_OUTCOME(DESCRIPTION) values('Error');
end;
Below is the code for uploading the excel sheet.
declare
v_temp BLOB;
v_record varchar2(3276);
v_len number := 0;
v_count number:=0;
v_data varchar2(32767);
begin
-- When an data is uploaded it is stored in the
-- FLOW_FILES.WWV_FLOW_FILE_OBJECTS$ table.
-- The wwv_flow_file_objects$ table is identified in the dads.conf
-- file in the PlsqlDocumentTablename parameter.
-- WWV_FLOW_FILES is a view to this table.
--File is uploaded in BLOB format so we need to extract data from it and insert into our table.
select blob_content into v_temp
from wwv_flow_files
where
created_on= (select max(created_on) from wwv_flow_files where UPDATED_BY = :app_user)
and id = (select max(id) from wwv_flow_files where updated_by = :app_user);
v_data := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(v_temp,DBMS_LOB.GETLENGTH(v_temp), 1));
DBMS_OUTPUT.PUT_LINE('THE LENGTH IS : ' || DBMS_LOB.GETLENGTH(v_temp));
DBMS_OUTPUT.PUT_LINE('THE BLOB IS READ : ' || v_data );
v_data := substr(v_data,instr(v_data,chr(10),1)+1,length(v_data));
v_len := length(v_data);
DBMS_OUTPUT.PUT_LINE(v_len);
delete from utr_outcome;
while v_count <= v_len-1
loop
v_count := v_count+1;
v_record := substr(v_data,v_count,instr(v_data,chr(10),v_count)-v_count);
DBMS_OUTPUT.PUT_LINE(v_record);
INSERT_ROW(v_record);
v_record :='';
v_count :=instr(v_data,chr(10),v_count);
DBMS_OUTPUT.PUT_LINE(v_count);
end loop;
Exception
When others then
DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
end;
Create a new PLSQL process under Processes section in APEX and put this code under the same. Make the process run when upload button is pressed.
Data in excel sheet will get loaded into table.
Hope this helps !!
Following PL/SQL block will help you to export or download table data directly in your browser with .xsl format (workbook with multiple sheets or tabs).
As shown below :
declare
l_clob clob;
l_blob blob;
l_sql_delimiter varchar2(30);
l_lang_context integer := DBMS_LOB.DEFAULT_LANG_CTX;
l_warning integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
l_dest_offset integer := 1;
l_source_offset integer := 1;
PROCEDURE run_query(p_sql IN VARCHAR2) IS
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
-- parse the SQL statement
DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
-- start execution of the SQL statement
d := DBMS_SQL.EXECUTE(c);
-- get a description of the returned columns
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
-- bind variables to columns
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
ELSE
DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
END CASE;
END LOOP;
-- Output the column headers
l_clob := l_clob||'<ss:Row>';
FOR j in 1..col_cnt
LOOP
l_clob := l_clob||'<ss:Cell>';
l_clob := l_clob||'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
END LOOP;
l_clob := l_clob||'</ss:Row>';
-- Output the data
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
l_clob := l_clob||'<ss:Row>';
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
l_clob := l_clob||'<ss:Cell>';
l_clob := l_clob||'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
l_clob := l_clob||'<ss:Cell>';
l_clob := l_clob||'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
l_clob := l_clob||'<ss:Cell ss:StyleID="OracleDate">';
l_clob := l_clob||'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
ELSE
DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
l_clob := l_clob||'<ss:Cell>';
l_clob := l_clob||'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
END CASE;
END LOOP;
l_clob := l_clob||'</ss:Row>';
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
END;
--
PROCEDURE start_workbook IS
BEGIN
l_clob := l_clob||'<?xml version="1.0"?>';
l_clob := l_clob||'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">';
END;
PROCEDURE end_workbook IS
BEGIN
l_clob := l_clob||'</ss:Workbook>';
END;
--
PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
BEGIN
l_clob := l_clob||'<ss:Worksheet ss:Name="'||p_sheetname||'">';
l_clob := l_clob||'<ss:Table>';
END;
PROCEDURE end_worksheet IS
BEGIN
l_clob := l_clob||'</ss:Table>';
l_clob := l_clob||'</ss:Worksheet>';
END;
--
PROCEDURE set_date_style IS
BEGIN
l_clob := l_clob||'<ss:Styles>';
l_clob := l_clob||'<ss:Style ss:ID="OracleDate">';
l_clob := l_clob||'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>';
l_clob := l_clob||'</ss:Style>';
l_clob := l_clob||'</ss:Styles>';
END;
begin
l_clob := null;
dbms_lob.createtemporary(l_blob, true);
--l_clob :='akshay patil';
-- Generate your file content here in l_clob.
htp.flush;
htp.init;
sys.OWA_UTIL.mime_header ('application/octet', FALSE);
-- sys.owa_util.mime_header( 'application/octet-stream', FALSE,'UTF-8' );
sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_clob ));
-- sys.htp.p('Content-Disposition: inline; filename="FILENAME.TXT"' );
sys.HTP.p('Content-Disposition: attachment; filename="myfile.xls"');
sys.owa_util.http_header_close;
start_workbook;
set_date_style;
Start_Worksheet('Customers');
run_query('select * from u_customer where rownum<10');
end_worksheet;
Start_Worksheet('Employees');
Run_Query('select * from u_user where rownum<10');
end_worksheet;
end_workbook;
dbms_lob.converttoblob (
dest_lob => l_blob,
src_clob => l_clob,
amount => DBMS_LOB.LOBMAXSIZE,
dest_offset => l_dest_offset,
src_offset => l_source_offset,
blob_csid => DBMS_LOB.DEFAULT_CSID,
lang_context=> l_lang_context,
warning => l_warning
);
sys.wpg_docload.download_file(l_blob);
apex_application.stop_apex_engine;
exception when others then
sys.htp.prn('error: '||sqlerrm);
apex_application.stop_apex_engine;
end;
As shown below :
declare
l_clob clob;
l_blob blob;
l_sql_delimiter varchar2(30);
l_lang_context integer := DBMS_LOB.DEFAULT_LANG_CTX;
l_warning integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
l_dest_offset integer := 1;
l_source_offset integer := 1;
PROCEDURE run_query(p_sql IN VARCHAR2) IS
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
-- parse the SQL statement
DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
-- start execution of the SQL statement
d := DBMS_SQL.EXECUTE(c);
-- get a description of the returned columns
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
-- bind variables to columns
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
ELSE
DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
END CASE;
END LOOP;
-- Output the column headers
l_clob := l_clob||'<ss:Row>';
FOR j in 1..col_cnt
LOOP
l_clob := l_clob||'<ss:Cell>';
l_clob := l_clob||'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
END LOOP;
l_clob := l_clob||'</ss:Row>';
-- Output the data
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
l_clob := l_clob||'<ss:Row>';
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
l_clob := l_clob||'<ss:Cell>';
l_clob := l_clob||'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
l_clob := l_clob||'<ss:Cell>';
l_clob := l_clob||'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
l_clob := l_clob||'<ss:Cell ss:StyleID="OracleDate">';
l_clob := l_clob||'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
ELSE
DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
l_clob := l_clob||'<ss:Cell>';
l_clob := l_clob||'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>';
l_clob := l_clob||'</ss:Cell>';
END CASE;
END LOOP;
l_clob := l_clob||'</ss:Row>';
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
END;
--
PROCEDURE start_workbook IS
BEGIN
l_clob := l_clob||'<?xml version="1.0"?>';
l_clob := l_clob||'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">';
END;
PROCEDURE end_workbook IS
BEGIN
l_clob := l_clob||'</ss:Workbook>';
END;
--
PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
BEGIN
l_clob := l_clob||'<ss:Worksheet ss:Name="'||p_sheetname||'">';
l_clob := l_clob||'<ss:Table>';
END;
PROCEDURE end_worksheet IS
BEGIN
l_clob := l_clob||'</ss:Table>';
l_clob := l_clob||'</ss:Worksheet>';
END;
--
PROCEDURE set_date_style IS
BEGIN
l_clob := l_clob||'<ss:Styles>';
l_clob := l_clob||'<ss:Style ss:ID="OracleDate">';
l_clob := l_clob||'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>';
l_clob := l_clob||'</ss:Style>';
l_clob := l_clob||'</ss:Styles>';
END;
begin
l_clob := null;
dbms_lob.createtemporary(l_blob, true);
--l_clob :='akshay patil';
-- Generate your file content here in l_clob.
htp.flush;
htp.init;
sys.OWA_UTIL.mime_header ('application/octet', FALSE);
-- sys.owa_util.mime_header( 'application/octet-stream', FALSE,'UTF-8' );
sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_clob ));
-- sys.htp.p('Content-Disposition: inline; filename="FILENAME.TXT"' );
sys.HTP.p('Content-Disposition: attachment; filename="myfile.xls"');
sys.owa_util.http_header_close;
start_workbook;
set_date_style;
Start_Worksheet('Customers');
run_query('select * from u_customer where rownum<10');
end_worksheet;
Start_Worksheet('Employees');
Run_Query('select * from u_user where rownum<10');
end_worksheet;
end_workbook;
dbms_lob.converttoblob (
dest_lob => l_blob,
src_clob => l_clob,
amount => DBMS_LOB.LOBMAXSIZE,
dest_offset => l_dest_offset,
src_offset => l_source_offset,
blob_csid => DBMS_LOB.DEFAULT_CSID,
lang_context=> l_lang_context,
warning => l_warning
);
sys.wpg_docload.download_file(l_blob);
apex_application.stop_apex_engine;
exception when others then
sys.htp.prn('error: '||sqlerrm);
apex_application.stop_apex_engine;
end;
Below PL/SQL Block will help you to download file from specific directory path to your browser.
First you need to register the directory path of file with oracle using below syntax.
DECLARE
V_Amount Integer;
V_Src_Loc Bfile;
v_b BLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(V_B,true);
v_src_loc := BFILENAME('MY_DIR', 'myfile.xls');
--MY_DIR is the directory registered with a path where file is present.
Sys.Dbms_Lob.Open(V_Src_Loc, Dbms_Lob.Lob_Readonly);
V_Amount := SYS.Dbms_Lob.Getlength(V_Src_Loc);
htp.flush;
htp.init;
-- Set the MIME type
OWA_UTIL.mime_header ('application/octet', FALSE);
htp.p('Content-length: ' || V_Amount );
HTP.p('Content-Disposition: attachment; filename="myfile.xls"');
OWA_UTIL.http_header_close;
Sys.Dbms_Lob.Loadfromfile(V_B, V_Src_Loc, V_Amount);
Sys.Dbms_Lob.Close(V_Src_Loc);
UTL_FILE.FREMOVE(location=>'MY_DIR',filename=>'myfile.xls');
wpg_docload.download_file(V_B);
apex_application.stop_apex_engine;
exception
when others then
htp.p('Error '||SQLERRM);
END;
First you need to register the directory path of file with oracle using below syntax.
CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';
where:
directory_name: Database-unique directory name.
path_name: Operating system directory path (note that if the OS is case-sensitive, the name must be exact)
Now,give read,write access to database user for created directory.
grant read, write on directory directory_name to database_schema
DECLARE
V_Amount Integer;
V_Src_Loc Bfile;
v_b BLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(V_B,true);
v_src_loc := BFILENAME('MY_DIR', 'myfile.xls');
--MY_DIR is the directory registered with a path where file is present.
Sys.Dbms_Lob.Open(V_Src_Loc, Dbms_Lob.Lob_Readonly);
V_Amount := SYS.Dbms_Lob.Getlength(V_Src_Loc);
htp.flush;
htp.init;
-- Set the MIME type
OWA_UTIL.mime_header ('application/octet', FALSE);
htp.p('Content-length: ' || V_Amount );
HTP.p('Content-Disposition: attachment; filename="myfile.xls"');
OWA_UTIL.http_header_close;
Sys.Dbms_Lob.Loadfromfile(V_B, V_Src_Loc, V_Amount);
Sys.Dbms_Lob.Close(V_Src_Loc);
UTL_FILE.FREMOVE(location=>'MY_DIR',filename=>'myfile.xls');
wpg_docload.download_file(V_B);
apex_application.stop_apex_engine;
exception
when others then
htp.p('Error '||SQLERRM);
END;
Following PL/SQL Block generates workbook (.xsl) file with two worksheet's named as Employees and Customers containing detail level data about employee and customer.
By using below code,you can directly export the various sql queries output with multiple sheets into single workbook.
To store generated workbook at specific path, first you need to register that path with oracle using below syntax.
DECLARE
v_fh UTL_FILE.FILE_TYPE;
v_dir VARCHAR2(30) := 'MY_DIR'; --Directory Name
V_File Varchar2(30) := 'myfile.xls';
V_Amount Integer;
V_Src_Loc Bfile;
v_b BLOB;
PROCEDURE run_query(p_sql IN VARCHAR2) IS
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
-- parse the SQL statement
DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
-- start execution of the SQL statement
d := DBMS_SQL.EXECUTE(c);
-- get a description of the returned columns
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
-- bind variables to columns
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
ELSE
DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
END CASE;
END LOOP;
-- Output the column headers
UTL_FILE.PUT_LINE(v_fh,'<ss:Row>');
FOR j in 1..col_cnt
LOOP
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
END LOOP;
UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
-- Output the data
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
UTL_FILE.PUT_LINE(v_fh,'<ss:Row>');
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell ss:StyleID="OracleDate">');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
ELSE
DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
END CASE;
END LOOP;
UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
END;
--
PROCEDURE start_workbook IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'<?xml version="1.0"?>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">');
END;
PROCEDURE end_workbook IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'</ss:Workbook>');
END;
--
PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'<ss:Worksheet ss:Name="'||p_sheetname||'">');
UTL_FILE.PUT_LINE(v_fh,'<ss:Table>');
END;
PROCEDURE end_worksheet IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'</ss:Table>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Worksheet>');
END;
--
PROCEDURE set_date_style IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'<ss:Styles>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Style ss:ID="OracleDate">');
UTL_FILE.PUT_LINE(v_fh,'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Style>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Styles>');
END;
BEGIN
v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w',32767);
start_workbook;
set_date_style;
Start_Worksheet('Customers');
run_query('select * from customer where rownum<10');
end_worksheet;
Start_Worksheet('Employees');
Run_Query('select * from user where rownum<10');
end_worksheet;
end_workbook;
UTL_FILE.FCLOSE(v_fh);
END;
By using below code,you can directly export the various sql queries output with multiple sheets into single workbook.
To store generated workbook at specific path, first you need to register that path with oracle using below syntax.
CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';
where:
directory_name: Database-unique directory name.
path_name: Operating system directory path (note that if the OS is case-sensitive, the name must be exact)
Now,give read,write access to database user for created directory.
grant read, write on directory directory_name to database_schema
DECLARE
v_fh UTL_FILE.FILE_TYPE;
v_dir VARCHAR2(30) := 'MY_DIR'; --Directory Name
V_File Varchar2(30) := 'myfile.xls';
V_Amount Integer;
V_Src_Loc Bfile;
v_b BLOB;
PROCEDURE run_query(p_sql IN VARCHAR2) IS
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
-- parse the SQL statement
DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
-- start execution of the SQL statement
d := DBMS_SQL.EXECUTE(c);
-- get a description of the returned columns
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
-- bind variables to columns
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
ELSE
DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
END CASE;
END LOOP;
-- Output the column headers
UTL_FILE.PUT_LINE(v_fh,'<ss:Row>');
FOR j in 1..col_cnt
LOOP
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
END LOOP;
UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
-- Output the data
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
UTL_FILE.PUT_LINE(v_fh,'<ss:Row>');
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell ss:StyleID="OracleDate">');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
ELSE
DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
UTL_FILE.PUT_LINE(v_fh,'<ss:Cell>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Cell>');
END CASE;
END LOOP;
UTL_FILE.PUT_LINE(v_fh,'</ss:Row>');
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
END;
--
PROCEDURE start_workbook IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'<?xml version="1.0"?>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">');
END;
PROCEDURE end_workbook IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'</ss:Workbook>');
END;
--
PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'<ss:Worksheet ss:Name="'||p_sheetname||'">');
UTL_FILE.PUT_LINE(v_fh,'<ss:Table>');
END;
PROCEDURE end_worksheet IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'</ss:Table>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Worksheet>');
END;
--
PROCEDURE set_date_style IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'<ss:Styles>');
UTL_FILE.PUT_LINE(v_fh,'<ss:Style ss:ID="OracleDate">');
UTL_FILE.PUT_LINE(v_fh,'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Style>');
UTL_FILE.PUT_LINE(v_fh,'</ss:Styles>');
END;
BEGIN
v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w',32767);
start_workbook;
set_date_style;
Start_Worksheet('Customers');
run_query('select * from customer where rownum<10');
end_worksheet;
Start_Worksheet('Employees');
Run_Query('select * from user where rownum<10');
end_worksheet;
end_workbook;
UTL_FILE.FCLOSE(v_fh);
END;
Start with iReport :
1.Download and install iReport version 4.6.0
2.Load required .jar file (ex. ojdbc14 driver for oracle database) using below navigation.
From Menu bar: Tools => Options
3.Click on Add Jar Button to load library files.
4. If you do not load Oracle JDBC driver it will show below Error Message.
5. To make database connection go through below image.
6. Below window will appear. Click on New button to add new connection.
7. Select type of data source and click on Next button.
8. Enter required details in given fields and click on Test button.
JDBC Driver: For Oracle Database choose Oracle Driver.
JDBC URL: localhost is the name of the machine where database reside.
Database Port: 1521
Database: XE
Use Database user credential as database username and password.
9. It will show successful message if you have entered correct information.
