How to get APEX server details

To Know what is the server your Oracle APEX is running .


Type this in SQL command .



select owa_util.get_cgi_env('SERVER_SOFTWARE') from dual

Interactive report Bug in Oracle APEX Development Tool

I had a  requirement to change the particular column name , In my interactive report i had just used the query

Select * from my_view

My_id,
MY_name,
My_status

now i changed the table and view columns of "My_status"  to "Active_status" .

Select * from my_view ; is not giving me any kind of problem from the SQL Workshop .

but the same query which is there in the Interactive report is given me problem .
 
This was very .

I looked intot he Report source of the Intearctive Report and then typed the same query again .

"Select * from My_view "

when clicked on apply changes the screen shows me a message

My_status got changed to "Active_status" .

I am unable to find , what is the reason that , IR didnot recognize the change in the column unless i touched the source again , as "Select * form my_view" is working perfectly fine in SQL workshop hence i expect the same in IR too .

So strange ..what can be the reason behind it ???? any idea ... is that a bug in ORACLE APEX DEVELOPMENT TOOL ??????

Executing the PLSQL and calling Javascript on the same button

 

TO call a plsql procedure and then call the Javascript to close the current popup in the Oracle APEX.

You can leverage the functionality of Branch with PLSQL in Oracle APEX .
\

Just create a branch after all the plsql process are performed and write the java script inclosed in htp function as shown in the below screen shot .

Mutli Level ( N level ) Tabs in Oracle APEX 4.1 or 4.2

Click Here for demo



 Here is the Multi Level Jquery Tabs in Oracle APEX .

1.Its is Easy to implement .

2.Enhances Look and Feel of the Application .

3.Light Weight.



Steps to be followed :-

1)Create a Application with one level tab , add 4-5 pages

Note the tab Labels should not contain spaces  , in case u want to add spaces use  


2) Create a list entry in the application shared components exactly matching the strings used as the label in the tabs .

Example :- Tabs :- Home , Tab1 ,Tab2 ,Tab3 ,Tab4 points to pages 1,2,3,4,5 correspondingly .

Now create the Sub menu entries like i used in this application

Home > Home child

Tab 1 > Child 1 > Grand Child 1 > Great Grand Child 2 .  soo ooon . 


You need to create the List entry for exactly same with same Label , In case you are including the   in the label , you need to reuse that too here .


 Ex:-  My Home .


3)  Download the Jquery Plugin from the APEX plugin site and upload it into your appliation .

4) Create the Page Zero , Create the Plugin Region with Region Template as
 ( Blank Region or No template ).

Specify the Source as

 

Note as this is the only list entry i have in my application , I took liberty to  use this query else you need to filter it depending on your List Name as an application can contain N Lists . 

5) Now its time to Edit the theme which you have chosen for you application .
       a) Theme can be either Div based.
       b)It can be List based .

You can get to know what is your theme category by clicking on the One Level Template link on any page of your application   .

Got the Standard Attributes Region .

Now change the Code to the corresponding  Values

Once done Just Run the Application and just Try to click or hover on the main tabs  , you will observe the Jquery Menus on the Tabs as follows .





 

Do let me know your comments if this helps you out :)   Happy Learning :) :) :) :)

Generate DDL Bug in Oracle APEX 4.2

Login to http://apex.oracle.com SQL workshop > Utilities > Genereate DDL > CReate Script . I had loads of important R&D tables on Oracle APEX Cloud , I wanted to generate the DDL script for my almost above 50 tables . But I could not find the Export Button , It was really painfull , to copy the table sQL from the Object Broswer and paste it . :( Is this a Bug ???? I guess it is , as I checked the Documentation of 4.2 to know in case the option got shifted to some other path , but i could see the path to generate DDL was still SQL WORKSHOP >> Utilities >> Generate DDL :( I hope it gets resolved soon , I am soo used to DDL Script generation .

Where as it has to be link this , which is there on my local instance APEX 4.1

How important to analyse before you choose a type of chart

All About Reporting and Printing in APEX

Restrictions and enhancements in Oracle APEX 4.1

 Configuration Requirements

The value of the character set portion of PlsqlNLSLanguage in the configuration of the mod_plsql Database Access Descriptor (DAD) must be set to AL32UTF8, regardless of the underlying database character set.

New Checkbox Plug-in Attribute Type

The new plugin has been added to the apex 4.1 version , where the select list is listed with the check box beside it . 

New Region SQL Statement Column Plug-in Attribute Type

With the new Region SQL Statement Column custom plug-in attribute (only available for region type plug-ins), plug-in developers can create more flexible region type plug-ins which are based on an SQL statement (plug-in configuration has checked Region Source is SQL Statement in Standard Attributes). This new custom plug-in attribute type will show the column names of the SQL statement specified in the region source.
An example is a region type plug-in which displays a chart. To populate the chart a developer has to specify an SQL statement in the region source. In 4.0 the plug-in developer would have to exactly specify how many columns the SQL statement has to have and which column position is mapped to which feature. For example an SQL statement format could look like:

select label,
       value,
       [link],
       [color],
       [tooltip]
  from table

As you can see in the above example, only label and value are required. All the other columns are optional. When using this chart type plug-in, you first have to know the format of the SQL statement and you have to write odd SQL statements if, for example, you want to have a statement where only the label, value and tooltip is set. Such a statement would look like:

select label,
       value,
       null as link,
       null as color,
       tooltip
  from mytable

The above SQL looks odd and gets more complicated if the SQL statement provides even more options.
In 4.1, the SQL statement specified in the region source is greatly simplified and the plug-in developer can now make implementation of the plug-in much more declarative for the average developer who is using the plug-in. The plug-in developer would define five new custom plug-in attributes of type Region SQL Statement Column. These would, for example, be called Label Column, Value Column, Link Column, Color Column and Tooltip Column. Only Label Column and Value Column are required.
If a developer is using the chart plug-in, in 4.1 the developer can now just enter the following into the Region Source:

select *
  from my_table

or

select dname,
       sum(sal) as total_sal,
       'Total Employees: '||count(employee_id) as total_employees
  from my_table

The developer is then prompted to actually map the different columns of the SQL statement to the Label Column, Value Column, Link Column, Color Column and Tooltip Column custom attributes of the plug-in. In our example, the developer would enter dname for the Label Column, total_sal for Value Column and total_employees for Tooltip Column. This is much more declarative, because the developer doesn't have to know how the SQL statement is formatted. This also makes it a lot easier if columns are optional.

Application Attributes to Control Browser Security

1)   In Application Express 4.1,
        there are two new application attributes to control Browser Security: 
        1)Cache  -- to save page content  on the disk and memory of the browser . 
         2) Embed in Frames.. browser is allowed to display your application's pages within a frame. 

 "SET_COMPATIBILITY_MODE Procedure" in the Oracle Application Express API Reference.

Bugs in Oracle APEX 4.2

Now Oracle APEX 4.2 is ready for use since Spetember 2nd 2012 .


SQL Workshop >>> SQL command


My observation about SQL Workshop and SQL command is as below ,  I found that its quite slow when compatively to the 4.1 , may be because of the ajax calls .

The image icon of the SQL workshop are toggling between the images of its in 4.1 and  4.2 , i found it a bit strange , may be some issue with the installation .

I found the results from sql command are not correct  , a normal desc tables is returning its column name twice .

UI made easier in Oracle APEX 4.2 , GRID EDIT

There are many awesome features included in the Oracle APEX 4.2 version , the most important thing is the whole  APEX UI based environment is now converted to a very light Jquery based environment .

Well this makes the developer feel lucky and lighter ,  expecially when there are  hard dead line  :)

UI is one of the major change in the Oracle APEX 4.2 release , to notice the big change just drag and drop few regions and items on ur screem and then in the development environment  , go to the development tool bar , you will find a new  icon called show grid edit :) ...


wow thats really awesome ... was my first experssion :)

Now once you click on show Grid edit , you will be swtiched to a grid mode of the whole page , which is really wowwwwwwwww...

Unable to Open Help Link in ORacle APEX

We need to give an access of the Oracle TEXT URL  datastore to the APEX_030200 user such .

create a role, assign it to the APEX_030200 database user and grant the required permission to the role.


SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';

PAR_VALUE
--------------------------------------------------------------------------------

SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;

Role created.

SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_030200;

Grant succeeded.

SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

 If the role exists, then just grant this role to the APEX_030200 user. 

Error while access network services in Oracle APEX

Incase you wanna use the pdf printing functionality or email fucntionality then you need to run this package , the package exists in the installation document , kindly! go thru it once . 

DECLARE
 ACL_PATH VARCHAR2(4000);
 ACL_ID RAW(16);
BEGIN
 -- Look for the ACL currently assigned to '*' and give APEX_030200
 -- the "connect" privilege if APEX_030200 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, ensure that the ACL is valid
 -- (for example, does not contain stale references to dropped users).
 -- If it does, the following exception will be raised:
 --
 -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_030200'
 -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
 --
 SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
 FROM XDB.XDB$ACL A, PATH_VIEW P
 WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
 EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
 IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200',
 'connect') IS NULL THEN
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
 'APEX_030200', TRUE, 'connect');
 END IF;

EXCEPTION
 -- When no ACL has been assigned to '*'.
 WHEN NO_DATA_FOUND THEN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
 'ACL that lets power users to connect to everywhere',
 'APEX_030200', TRUE, 'connect');
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Setting up Oracle APEX for the first time

Once you are done with installing your Oracle DB instance  ( 11g or 10g )  , the next step is to make sure your Oracle APEX is kick started .


1) Make sure your Oracle Database is up and running .

2) Make sure your default port for the webserver that is (8080) is not occupied by any other product .

3) As the Oracle Database already comes with oracle XML DB and Oracle embedded plsql gateway server , you actually need not bother about configuring the apex  , as APEX uses the embedded plsql gateway as the default Application server .


4) There are many default scripts which comes with oracle apex which can be used for installing , configuring or adminsitrting or customizing the settings of the oracle apex .

       C:\Oracle_home\APEX\    ---- you will find many sql scripts which starts with the apex_version_no_script_description.sql
 EX:-  apxldimg.sql  this is used for loading the images of the application developer of the apex during the apex installation from scratch or it upgradation .


5) One the files is apxconf.sql  , this is used for configuring the APEX   .

When you run this particular command , you will be shown the default port no which is being used or available :- 8080 .

Then  you will be asked to enter the password for the APEX ADMIN :- " enter the password".
Then you need to reneter the password for the confirmation again :  " renter the password " .

Note this password will be the admin password for you default workspace of apex thats nothing but ( Internal ) .

 You will be asked to enter the port , incase you wanna continue with the same default port then u can enter (8080) as you port no ,else you can change for any other .




[oracle@db11gr2 apex]$ cd $ORACLE_HOME/apex
[oracle@db11gr2 apex]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 11 00:48:32 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> @apxconf

PORT
----------
 8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.
Enter a password for the ADMIN user []
Enter a port for the XDB HTTP listener [ 8080]
...changing HTTP Port

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.
Commit complete.

 6) Using the Following Comand cross verify whether your  web server is up and running .

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT
-----------
 8080

 You can mak apex down , by runnnig the comman

SQL>EXEC DBMS_XDB.SETHTTPPORT(0);
SQL>EXEC DBMS_XDB.SETHTTPPORT(8080);
again makes the Oracle APEX Up .

7)  Now login into oracle apex instance as

http://localhost:8080/apex/apex_admin

or 

http://localhost:8080/apex 

Workspace:-  Internal 

  User:-  Admin 

Password :- enter admin password .

Now create your development  workspace ,schemas and  users,



8)Incase you wanna use the pdf printing functionality or email fucntionality then you need to run this package , the package exists in the installation document , kindly! go thru it once .



DECLARE
 ACL_PATH VARCHAR2(4000);
 ACL_ID RAW(16);
BEGIN
 -- Look for the ACL currently assigned to '*' and give APEX_030200
 -- the "connect" privilege if APEX_030200 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, ensure that the ACL is valid
 -- (for example, does not contain stale references to dropped users).
 -- If it does, the following exception will be raised:
 --
 -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_030200'
 -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
 --
 SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
 FROM XDB.XDB$ACL A, PATH_VIEW P
 WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
 EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
 IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200',
 'connect') IS NULL THEN
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
 'APEX_030200', TRUE, 'connect');
 END IF;

EXCEPTION
 -- When no ACL has been assigned to '*'.
 WHEN NO_DATA_FOUND THEN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
 'ACL that lets power users to connect to everywhere',
 'APEX_030200', TRUE, 'connect');
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;