Pages

Monday, September 3, 2012

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 labelvalue 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...





Sunday, September 2, 2012

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;












Oracle APEX Licnesing and Multiple Oracle APEX Instances

Is Oracle APEX licensed ? 

Oracle APEX is absolute free in case you have a licensed Oracle Database (EE)  or with the Oracle DB XE version which has a limited free space .

Well Oracle 11g XE is the free version and comes with the  default Oracle APEX version 4.0 but you cannot use Oracle DB XE for the commerical use when data exceeds more then 4GB .

Oracle 11g EE which is licensed version comes with the Oracle APEX 3.1.2  version . Incase you wanna upgarde the Oracle APEX to higher version you need not have an licnese for it . You can directly download the latest version from OTN and upgrade it .

Can You install Two APEX  instance on the same database 
(with same or different version of apex ) ?
   You cannot install more than one instance of apex with the a database instance , as each database comes with a unique apex schema and its  related module to excute the apex packages   , you cannot install more than one .


Why the need of  Multiple Oracle APEX instance ?

1) A client may have few module which exsists on the different version of apex and still they want to continue with it , for example apex 3.2 .  In case they want to start the new module of the oracle apex with the latest version of apex  , on the same database , they actually cannot go for it as it may require lots of  support work  , which may not be their priortiy right now .

So , in case they want to have a new latest version of the apex instance on the same database they cannot go for it , unless they have a new db instance for it or upgrade the older one .



Can we use the same oracle http server for two different oracle apex instances residing on the two different databases ?

Yes , you can use the same oracle http server for two different instance of the apex residing on the two different databases , but only thing is that you need to create a new dad.conf file for the another extra instance and configure it .


In case you have Oracle Database installed , now you need to do the following  steps to just set up Oracle APEX enabled in your environment or enable it with full functionality .