Thursday, 28 January 2016

APEX_ITEM



You can use the APEX_ITEM package to create form elements dynamically based on a SQL query instead of creating individual items page by page.

 It's sometime necessary that you need to use Item(apex_item) in page,like you want show something when query runs.

 Apex_Item will work same as inbuilt option of creating items with Name,Id,Class.

 There are several more apex_item which will be useful.

 https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_item.htm#AEAPI199

 You can also check Demo which will demonstrate how you can create such items.

 If Link Changes in Future then below will be some of the Apex_items you can search and use.

 CHECKBOX2 Function
DATE_POPUP Function
DATE_POPUP2 Function
DISPLAY_AND_SAVE Function
HIDDEN Function
MD5_CHECKSUM Function
MD5_HIDDEN Function
POPUP_FROM_LOV Function
POPUP_FROM_QUERY Function
POPUPKEY_FROM_LOV Function
POPUPKEY_FROM_QUERY Function
RADIOGROUP Function
SELECT_LIST Function
SELECT_LIST_FROM_LOV Function
SELECT_LIST_FROM_LOV_XL Function
SELECT_LIST_FROM_QUERY Function
SELECT_LIST_FROM_QUERY_XL Function
TEXT Function
TEXTAREA Function
TEXT_FROM_LOV Function

TEXT_FROM_LOV_QUERY Function

 Example:

 
SELECT 
    empno, 
    APEX_ITEM.HIDDEN(1,empno)||
    APEX_ITEM.TEXT(2,ename) ename, 
    APEX_ITEM.TEXT(3,job) job, 
    mgr, 
    APEX_ITEM.DATE_POPUP(4,rownum,hiredate,'dd-mon-yyyy') hd,
  FROM emp
ORDER BY 1
In above Example
--.HIDDEN item will be hidden on page(for storing Id you can use this feature)
-- .TEXT with display a text item
-- .DATE_POPUP date popup will be created
By - No comments:

WWV_FLOWS TABLE

If you want to see all workspaces with its applications and parsing schemas, then you can select the WWV_FLOWS table in the Apex owner schema, like apex_040200 (for apex 4.2.x).

Select * from apex_040200.wwv_flows;
You need:
grant select on apex_040200.wwv_flows to parsing_schema;
if you want to see a result set.
An alternative way is to select the Apex view APEX_APPLICATIONS.
To see all workspaces you need thereby the role APEX_ADMINISTRATOR_ROLE .
By - No comments:

Wednesday, 27 January 2016

Creating a Work-space in Oracle Apex 5.0.3

Visit : https://apex.oracle.com/en/

You can see the Below image in-front of your monitor screen if you already have Workspace then insert Credentials if not then you can request it by Clicking on "Request A Workspace".
You will get a link into your mail box(Registered) click on it you will be redirected to above link.


Read more »
By - No comments:
Labels:
Location: Ahmedabad, Gujarat, India

Tuesday, 19 January 2016

Print From Local Instance

if you want to print pdf reports using Xsl-Fo then some of below configurations needs to be make.
1)
Login form internal/admin of apex oracle local instance.
Go to -> Manage Instance


->Instance Settings  
Apply Change.
2) Check Ords installation and configuration had been done or not follow below query.
Check users of ORDS and Apex Listener :
select *
    from dba_users
  WHERE username like '%APEX%'
    order by username;

 if you get below result:
APEX_050000
APEX_PUBLIC_USER
with Locked & Expired  Status then Unlock them first
Unlock Account : ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
Change Password : ALTER USER APEX_050000 IDENTIFIED BY Password;
Password Expiry to Unlimited : ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Now, you need to install/configure ORDS.

 2.1)
Download Oracle REST Data Services
You can download the Oracle REST Data Services (ORDS) distribution from Oracle Technology Network (OTN) by clicking this link.

Unzip the distribution

Unzip the file you just downloaded to a folder of your choosing. The name of the file will change depending on what the latest released version of ORDS is. It will start with ords, followed by a version number and end with .zip.
mkdir ~/ords
unzip ords.3.0.1.176.03.18.zip -d ~/ords

Run the installer

Change to the folder where you unzipped the distribution and launch ORDS. It will automatically run the installer if necessary.
cd ~/ords
java -jar ords.war
The following output should be displayed:
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Complete all the prompts to connect to your database, and you are done!

Frequently asked questions

What’s the ORDS_PUBLIC_USER for?

ORDS_PUBLIC_USER is the database user that ORDS uses to communicate with the database. TheORDS_PUBLIC_USER user is created during the first run of the installer.

Do I want to use the PL/SQL Gateway?

The PL/SQL Gateway provides a means for stored PL/SQL procedures to be invoked via HTTP.
  • If you use Oracle Application Express (APEX), then the answer is yes, the PL/SQL Gateway manages the communication between APEX and HTTP.
  • If you use Oracle Web Agent (OWA) stored procedures, or if you are migrating from mod_plsql, then the answer is yes.
  • If you don’t use either of the above and you just want to expose data in your Oracle database via REST, then the answer is no.

What’s APEX_PUBLIC_USER for?

  • If you use the PL/SQL Gateway, then APEX_PUBLIC_USER is the database user that ORDS uses to communicate with the database.
  • Note that ORDS does not create the APEX_PUBLIC_USER user, this user is created as part of theApplication Express install process.

What’s APEX_LISTENER and APEX_REST_PUBLIC_USER for?

  • If you’ve created RESTful Services using APEX 4.2.0+ (or using ORDS 2.0.x and SQL Developer), then you need to configure both these users.
  • If you haven’t used RESTful Services before or don’t intend to (you just want to use the PL/SQL Gateway), then you should not configure these users.
  • Note that ORDS does not create either of these users, they are created as a party of the Application Express install process.

What’s Standalone Mode?

Standalone Mode is a way to run ORDS without needing an application server like Oracle WebLogic orApache Tomcat. It’s a quick and handy way to get ORDS up and running, but it’s not supported for use in production, you’ll need to deploy ORDS to a supported Application Server when it comes time to go production.

Advanced Mode

By default the installer runs in ‘simple’ mode. This mode is optimized to ask you the least amount of questions and get ORDS up and running as simply as possible. If you need it, there is also an ‘advanced’ mode that has more prompts and will help you configure more advanced deployments.
You can read all about it in the Installation Guide

Read the Install Guide

Want more information? Read the Installation Guide by clicking here.

Installation Example

Here’s an example of completing all prompts in the installation process:
$ java -jar ords.war
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/Users/cdivilly/ords/confs
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:orcl
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:

Please login with SYSDBA privileges to verify Oracle REST Data Services schema. Installation may be required.


Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:
Confirm password:
Mar 11, 2015 2:30:31 PM oracle.dbtools.rt.config.setup.SchemaSetup addSchemaParams
INFO:
Oracle REST Data Services schema does not exist and will be created.


Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step [1]:1
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:1
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Mar 11, 2015 2:30:58 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex_pu, apex, apex_al, apex_rt
Mar 11, 2015 2:30:58 PM oracle.dbtools.installer.Installer installORDS
INFO:
Installing Oracle REST Data Services version 3.0.0.65.09.31
... Log file written to /Users/cdivilly/ords/logs/ordsinstall_2015-03-11_143058_00465.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Created Oracle REST Data Services proxy user
Mar 11, 2015 2:31:09 PM oracle.dbtools.installer.Installer installORDS
INFO: Completed installation for Oracle REST Data Services version 3.0.0.65.09.31. Elapsed time: 00:00:10.899

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter the APEX static resources location:/Users/cdivilly/work/apex/4.2.4/images
2015-03-11 14:31:37.643:INFO::main: Logging initialized @106799ms
2015-03-11 14:31:38.492:INFO:oejs.Server:main: jetty-9.2.z-SNAPSHOT
2015-03-11 14:31:38.504:INFO:oejsh.ContextHandler:main: Started o.e.j.s.h.ContextHandler@36574ac1{/i,null,AVAILABLE}
Mar 11, 2015 2:31:39 PM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No encryption key found in configuration, generating key
Mar 11, 2015 2:31:39 PM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No mac key found in configuration, generating key
Mar 11, 2015 2:31:39 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex, apex_al, apex_pu, apex_rt
Mar 11, 2015 2:31:39 PM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: Updated configuration with generated keys
Mar 11, 2015 2:31:39 PM oracle.json.ords.SodaShared startup
INFO: Starting up ORDS JSON service
2015-03-11 14:31:39.220:INFO:/ords:main: INFO: Using configuration folder: /Users/cdivilly/ords/confs/ords
2015-03-11 14:31:39.220:INFO:/ords:main: FINEST: |ApplicationContext [configurationFolder=/Users/cdivilly/ords/confs/ords, services=Application Scope]|
Mar 11, 2015 2:31:39 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: apex
Mar 11, 2015 2:31:39 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex is correctly configured
Mar 11, 2015 2:31:39 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: apex_al
Mar 11, 2015 2:31:39 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex_al is correctly configured
Mar 11, 2015 2:31:39 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: apex_pu
Mar 11, 2015 2:31:39 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex_pu is correctly configured
Mar 11, 2015 2:31:39 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: apex_rt
Mar 11, 2015 2:31:39 PM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: apex_rt is correctly configured
2015-03-11 14:31:39.894:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version : 3.0.0.65.09.31|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2015-03-11 14:31:39.895:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@398f600{/ords,null,AVAILABLE}
2015-03-11 14:31:39.914:INFO:oejs.ServerConnector:main: Started ServerConnector@34766cda{HTTP/1.1}{0.0.0.0:8080}
2015-03-11 14:31:39.915:INFO:oejs.Server:main: Started @109074ms
link:
3) Run this Query:
select *
    from dba_users
  WHERE username like '%APEX%'
    order by username;
Result :


APEX_050000
APEX_PUBLIC_USER
APEX_REST_PUBLIC_USER
APEX_LISTENER


Account _Status :  Open
Now, provide privileges using below query.
4) ACL privileges :


DECLARE
 ACL_PATH  VARCHAR2(4000);
BEGIN
 -- Look for the ACL currently assigned to 'localhost' and give APEX_050000
 -- the "connect" privilege if APEX_050000 does not have the privilege yet.
 SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
  WHERE HOST = 'APEX_050000' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
  
 IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050000',
    'connect') IS NULL THEN
     DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
    'APEX_050000', TRUE, 'connect');
 END IF;
 
EXCEPTION
 -- When no ACL has been assigned to 'localhost'.
 WHEN NO_DATA_FOUND THEN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users1.xml',
   'ACL that lets users to connect to localhost',
   'APEX_050000', TRUE, 'connect');
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users1.xml','APEX_050000');
END;
/
COMMIT;
Check Privileges :
SELECT DECODE(
 DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(
      'httpcon.xml', 'APEX_050000', 'resolve'),
 1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
FROM DUAL;
Result : Granted
Still if you get below error when you try to print :
The connection pool named: apex_rt is not correctly configured, due to the following error(s): ORA-28150: proxy not authorized to connect as client
Grant Access :
ALTER USER ECUBE GRANT CONNECT THROUGH apex_rest_public_user;
……...

If you are using APACHE Tom cat then do check for <entry key="misc.enableOldFOP">true</entry>
 in default.xml file if you do not have this then update it and restart tomcat.

YouTube : https://youtu.be/BkQVrjXPj74

By - 1 comment:
Subscribe to: Posts (Atom)

Show values in right side of shuttle

While working with select list and shuttle, when we want to display values into right side of shuttle depending upon selection from select ...