Archive

Archive for the ‘Application Express’ Category

Automatically Backuping Apex Applications

Automatically Backuping Apex Applications

We can export our apex applications as an sql file in “Home>Application Builder>Application $ID>Export / Import>Export” menu. You can do this as manually, so if you want to backup your apex application(s) you should do this for each time for each application.

As you guess this operation runs a plsql command in background to export application.

“wwv_flow_utilities” public package of apex, includes “export_application_to_clob” function that returns clob variable that containts sql statements. These sql statements those are application metadata that will be imported your workspace. Spec part of function is below :

function export_application_to_clob (
p_application_id in number,
p_export_saved_reports in varchar2 default ‘N’)
return clob
;

Application ID is a number that user specified or automatically got from apex. That is on right of f?p= parameter of url string. You should specify application id to export application. Another parameter is p_export_saved_reports is optional, if you want to export saved report you can set this as ‘Y’.

You can write a dbms_scheduler job that runs this plsql for every specified interval time. Or you can write a unix shell script that produces an sql file, put this shell script to crontab to run this backup operations for every specified interval time. Or anything else…

Also you can export only a page of an application, this function is under wwv_flow_utilities package too, spec part of this :

function export_page_to_clob (
p_application_id in number,
p_page_id in number)
return CLOB
;

Example :

CREATE TABLE EXPORT_CLOB
(
APP_EXPORT CLOB
);

BEGIN

INSERT INTO EXPORT_CLOB
VALUES (WWV_FLOW_UTILITIES.EXPORT_APPLICATION_TO_CLOB(‘107′));

END;

SELECT length(app_export) FROM EXPORT_CLOB;

954956

To backup your all of applications you can get application id from “select * from apex_applications”.

This example was tested on Oracle 11.1.0.6 and Apex 3.2.

Apex Interactive Reports with Dynamic Sql

October 11, 2009 1 comment

Interactive Reports in Apex, presents too many features that will be performed on reports. You can apply specified column filtering, highlighted specified values in a column, use aggregation functions, ajax paginations are some of these features. To use these features you can create a report region as Interactive report ( In a page attributes, Create Region > Report > Interactive Report ).

While creating Interactive Reports, apex wants from you an sql word. It must be pure sql unlike varchar2 variable that stores sql ( in standart reports you can use a varchar2 variable that stores sql. For example lv_dynamic_sql := ‘SELECT EMP_NO FROM EMPLOYEE'; return lv_dynamic_sql; ). So in normally you can’t use dynamic sql in interactive report. I said “in normally” :)

By using PIPELINED FUNCTIONS you can use dynamic sql in Apex Interactive Reports.

In Interactive Reports, you will specify sql as “SELECT * FROM TABLE(table_function);”

After that you should design your “table_function()” function.

First of all you should create an object type that matches a report row fields.

CREATE OR REPLACE TYPE OBJ_STREAM_FILTERED AS OBJECT
(
a_rowid VARCHAR2(100),
ActivationType VARCHAR2(100),
ActivationCellCgi VARCHAR2(100),
TerminationCellCgi VARCHAR2(100),
IMSI VARCHAR2(100),
IMEI_TAC VARCHAR2(100),
IMEI_LAC VARCHAR2(100),
ActivationSec VARCHAR2(100),
OriginationCellCgi VARCHAR2(100),
TargetCellCgi VARCHAR2(100),
ENQ_DATE DATE,
INSERT_DATE DATE,
REMOTE_HOST VARCHAR2(100),
IMEI VARCHAR2(100),
MSISDN_NUMBER VARCHAR2(100),
GENERAL_SEGMENT VARCHAR2(100),
GENERAL_SUB_SEGMENT VARCHAR2(100)
)
/

Your report fields should match these object type fields.

After that you should design your “table_function” function.

CREATE OR REPLACE FUNCTION AQ_ADM_PRTP_V204.get_table_rows2(p_table_name VARCHAR2)
RETURN OBJ_STREAM_FILTERED_ARR
PIPELINED IS
out_rec OBJ_STREAM_FILTERED := OBJ_STREAM_FILTERED(NULL,
null,
NULL,
NULL,
null,
null,
null,
null,
null,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL);
p sys_refcursor;

v0 VARCHAR2(100);
v1 VARCHAR2(100);
v2 VARCHAR2(100);
v3 VARCHAR2(100);
v4 VARCHAR2(100);
v5 VARCHAR2(100);
v6 VARCHAR2(100);
v7 VARCHAR2(100);
v8 VARCHAR2(100);
v9 VARCHAR2(100);
v10 VARCHAR2(100);
v11 VARCHAR2(100);
v12 VARCHAR2(100);
v13 VARCHAR2(100);
v14 VARCHAR2(100);
v15 VARCHAR2(100);
v16 VARCHAR2(100);
t1 TIMESTAMP(6);
t2 TIMESTAMP(6);
BEGIN
OPEN p FOR ‘SELECT
t.rowid,
t.*,
t2.imei,
t2.msisdn_number,
t2.general_Segment,
t2.general_sub_segment
FROM
‘ || p_table_name || ‘ t,
syn_rds_imsi_info t2
WHERE
CONCAT(CONCAT(column15,column16),column17) = t2.IMSI(+)';
LOOP
FETCH p
INTO v0, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, t1, t2, v12, v13, v14, v15, v16;
EXIT WHEN p%NOTFOUND;
out_rec.a_rowid := v0;
out_rec.ActivationType := v1;
Out_rec.ActivationCellCgi := v2;
out_rec.TerminationCellCgi := v3;
out_rec.IMSI := v4 || v5 || v6;
out_rec.IMEI_TAC := v7;
out_rec.IMEI_LAC := v8;
out_rec.OriginationCellCgi := v10;
out_rec.TargetCellCgi := v11;
out_rec.ActivationSec := v9;
out_rec.ENQ_DATE := t1;
out_rec.INSERT_DATE := t2;
out_rec.REMOTE_HOST := v12;
out_rec.IMEI := v13;
out_rec.MSISDN_NUMBER := v14;
out_rec.GENERAL_SEGMENT := v15;
out_rec.GENERAL_SUB_SEGMENT := v16;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_backtrace);
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

You can check this function accuracy:

SELECT * FROM TABLE(get_table_rows2(‘galata_report.FUAT123′));

Anymore, there is no need to do anything to use this sql in interactive report.

If you get an uniquely related error you can specify a unique column ( a_rowid in this example ) in Report Attributes > Link Column section.

Edited on 05.10.2010 :
There is a better solution to handle more dynamic sql. Which means that you can prepare dynamic interactive report for any sql. Those sqls do not need to has same number of columns or same column names. It is demonstrated at http://www.oracleapplicationexpress.com/tutorials/71 .

Installing jQuery to Application Express ( APEX )

Sometimes, you want to add different things your page such as different buttons, images, motions. JQuery API provides these wishes by using JavaScript and CSS.

For detail information, examples and to do download this api, visit http://jquery.com.

If you want to import and use this api in your Apex Application you will have to do some steps.

1 ) Download latest version of jQuery Api from http://jquery.com or customizable version at : http://jqueryui.com/download
2 ) Install those files to $ORACLE_HOME/apex/images/jquery/ folder. ( jQuery folder must be created ).
3 ) Then, you should edit the $ORACLE_HOME/apex/images/imagelist.xml file.
This file an xml file and you should add those under the directories node.



jquery
jquery/theme
jquery/theme/images

And, you should add file paths under the files node. ( Those file names should be changed if you downloaded another version instead of 1.6 Personalized JQuery )

/jquery/jquery-1.3.1.js
/jquery/jquery-ui-personalized-1.6rc6.min.js
/jquery/jquery-ui-personalized-1.6rc6.js
/jquery/theme/ui.accordion.css
/jquery/theme/ui.all.css
/jquery/theme/ui.base.css
/jquery/theme/ui.core.css
/jquery/theme/ui.datepicker.css
/jquery/theme/ui.dialog.css
/jquery/theme/ui.progressbar.css
/jquery/theme/ui.resizable.css
/jquery/theme/ui.slider.css
/jquery/theme/ui.tabs.css
/jquery/theme/ui.theme.css
/jquery/theme/images/ui-bg_diagonals-thick_18_b81900_40x40.png
/jquery/theme/images/ui-bg_diagonals-thick_20_666666_40x40.png
/jquery/theme/images/ui-bg_flat_10_000000_40x100.png
/jquery/theme/images/ui-bg_glass_65_ffffff_1x400.png
/jquery/theme/images/ui-bg_glass_100_f6f6f6_1x400.png
/jquery/theme/images/ui-bg_glass_100_fdf5ce_1x400.png
/jquery/theme/images/ui-bg_gloss-wave_35_f6a828_500x100.png
/jquery/theme/images/ui-bg_highlight-soft_75_ffe45c_1x100.png
/jquery/theme/images/ui-bg_highlight-soft_100_eeeeee_1x100.png
/jquery/theme/images/ui-icons_228ef1_256x240.png
/jquery/theme/images/ui-icons_222222_256x240.png
/jquery/theme/images/ui-icons_ef8c08_256x240.png
/jquery/theme/images/ui-icons_ffd27a_256x240.png
/jquery/theme/images/ui-icons_ffffff_256x240.png

4 ) After that, you should run apex_epg_config.sql ( in $ORACLE_HOME/apex directory ) and enter $ORACLE_HOME whichever it is, to refresh file paths.

SQL> @apex_epg_config

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Enter value for 1: D:\app\TTASUNGUR\product\11.1.0\db_1\
old 1: create directory APEX_IMAGES as ‘&1/apex/images’
new 1: create directory APEX_IMAGES as ‘D:\app\TTASUNGUR\product\11.1.0\db_1\/
apex/images’

Directory created.

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:00:59.68

Session altered.

PL/SQL procedure successfully completed.

Commit complete.

Session altered.

Directory dropped.

5 ) So far, you imported and installed the jquery to your Apex environment. Now, you can use jquery in your apex pages. Let’s do an example, create a page and create a region and we will create a draggable div place.

$(document).ready(function()
{
$(“#tasinabilir”).draggable( { revert: true } );

}
);

Bura tasinabilir bir bolge

( There are many examples at http://jquery.com )
6 ) Then test your page, click div place and drag it to anywhere and release, it will return its first position.

Follow

Get every new post delivered to your Inbox.

Join 86 other followers