Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for the ‘APEX 4.0’ Category

Delete Specific application from the workspace

Posted by Hendry chinnapparaj on July 1, 2010

Login as Developer user who created the Applications

Home –> Application Builder –> Application 104

Home à application builder à application 104

On the rt side of the page à delete application

you have requested the permanent deletion of application SS ORACLE ENVIRONMENT (104). Please confirm your delete request.

à permanentlyu delete now

Advertisements

Posted in APEX 4.0 | Leave a Comment »

Create Application from Excel Sheet

Posted by Hendry chinnapparaj on July 1, 2010

Create Application from Excel Sheet

——————————————-

Pre-requisite

Save the .XLS file as .CSV file or Tab Limited text in my case.

 

Login as Developer user “Hendry”

Go to Home à Application Builder à Create Application à Database à next à from spreadsheet à next à

Copy and paste à  tab delimited à next à

Rename columns if required

Schema à oracledba

Table name à ORACLE_ENVIRONMENT_TAB

SINGULAR NAME: Oracle Environment Tab

PLURAL  NAME: Oracle Environment Tab

Applcation name : ORACLE ENVIRONMENT

Create mode : readn and write

Report Implementation à interactive

Select Theme à Theme 1

Create

The application has been successfully created.

Run Application or Edit Application

End user Login

http://cpuu026.greatwest.com:8080/apex/f?p=108:1

Developer

http://cpuu026.greatwest.com:8080/apex/apex

Posted in APEX 4.0 | Leave a Comment »

Create Workspace and add Apex users

Posted by Hendry chinnapparaj on July 1, 2010

Create a Workspace and Add Oracle Application Express Users

You access the Oracle Application Express home page by logging in to workspace using a Web browser. Your Web browser must support JavaScript and the HTML 4.0

and CSS 1.0 standards. See “Browser Requirements” on page 2-2.

A workspace is a virtual private database allowing multiple users to work within the same Oracle Application Express installation while keeping their objects, data and

applications private. Each workspace has a unique ID and name. An instance administrator can create a workspace manually within Oracle Application Express Administration Services or have users submit requests. Oracle Application Express Administration Services is a separate application for managing an entire Oracle Application Express instance. See “Creating Workspaces” and “Managing Workspace Requests” in Oracle Application Express Administration Guide.

http://pcu026.shalom.com:8080/apex/apex_admin

userid: admin

password: ****

Click Manage Workspaces.

3. Under Manage Workspaces, click Create Workspace.

The Create Workspace Wizard appears.

4. For Identify Workspace, enter a workspace name and description and click Next. à ORACLEDBA

5. For Identify Schema, select the Oracle Forms application schema. à reuse schema:no;schema:oracledba; schema pwd:oracledba;space:100mb

Admin username: admin

Password: ***

First name: Hendry

Last name: Dasan

Email: hendry.oracle@shalom.com.au

Confirm request à create workspace

Workspace successfully provisioned.

Workspace ORACLEDBA provisioned with administrator ADMIN.

Database user ORACLEDBA created with default tablespace APEX_1284401228498898 using datafile /u02/oradata/apex4/APEX_1284401228498898.dbf

Creating Oracle Application Express Users

To create an Oracle Application Express user account:

1. Log in to Oracle Application Express Administration Services as described in the previous section. See “Logging in to Oracle Application Express Administration

Services” in Oracle Application Express Administration Guide.

Manage workspaces à manage developers and users à create user

User Attributes

Username
Email Address
First Name
Last Name
Description

Workspace à oracledba

Default schema à oracledba

User is an administrator à no

Account availability à unlocked

Create end user

——————–

Password: xx15908

Confirm password: xx15908

Require change of password:no

Create’

Create Developer user

Username:ndry

Email: Hendry.oracle@shalom.com.au

First name : Hendry

Last name: Dasan

Description: developer

Workspace: oracledba

Default schema: oracledba

User is administrator: no

User is developer: yes

Application builder access: yes

Sql workshop access: yes

Team development access: yes

Account availability: unlocked

Password: hendry

Confirm: hendry

Require change of password on first use: no

Login to your workspace as end user and test

http://cpu026.shalom.com:8080/apex/

Enter Application Express workspace and credentials.

Workspace
Username
Password xx15908

Login to your workspace as Developer  and test

http://cpu026.shalom.com:8080/apex/

Enter Application Express workspace and credentials.

Workspace
Username
Password hendry

Posted in APEX 4.0 | Leave a Comment »

Install and Configure Apex 4.0 in Oracle Database 11gR2

Posted by Hendry chinnapparaj on July 1, 2010

Install Apex 4.0 in Oracle Database 11gR2 in Aix Env

Disk Space Requirement

Oracle Application Express disk space requirements are as follows:

■ Free space for Oracle Application Express software files on the file system: 450 MB

if using English only download (apex_4_0_en.zip) and 1 GB if using full download (apex_4_0.zip).

■ Free space in Oracle Application Express tablespace: 185 MB

■ Free space in SYSTEM tablespace: 100 MB

■ Free space in Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB

Oracle XML DB Requirement

Oracle XML DB must be installed in the Oracle database that you want to use. If you are using a preconfigured database created either during an installation or by

Database Configuration Assistant (DBCA), Oracle XML DB is already installed and configured.

apex4:/u01/oracle/product/11.2> pwd

/u01/oracle/product/11.2

/u01/oracle/product/11.2> unzip apex_4.0_en.zip

cd apex

apex4:/u01/oracle/product/11.2/apex> pwd

/u01/oracle/product/11.2/apex

SQL> show parameter db_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_name                              string      apex4

SQL> @apexins SYSAUX SYSAUX TEMP /i/

Configure APEX_PUBLIC_USER Account

It is important to correctly configure the APEX_PUBLIC_USER account to enable proper operation of Oracle Application Express.

Unlocking the APEX_PUBLIC_USER Account

The APEX_PUBLIC_USER account is locked at the end of a new installation of Oracle Application Express. You must unlock this account before configuring the database

access descriptor (DAD) in a new installation.

To unlock the APEX_PUBLIC_USER account:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On UNIX and Linux:

$ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

2. Run the following statement:

ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK

Changing the Password for the APEX_PUBLIC_USER Account

The APEX_PUBLIC_USER account is created with a random password in a new

installation of Oracle Application Express. You must change the password for this

account before configuring the database access descriptor (DAD) in a new installation.

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY apex_public_user;

Where new_password is the new password you are setting for APEX_PUBLIC_USER. You will use this password when creating the DAD

About Password Expiration in Oracle Database 11g In the default profile in Oracle Database 11g, the parameter PASSWORD_LIFE_TIME is set to 180. If you are using Oracle Database 11g with Oracle Application Express, this causes the password for APEX_PUBLIC_USER to expire in 180 days. As a result, your Oracle Application Express

instance will become unusable until you change the password.

To prevent this behavior, create another profile in which the PASSWORD_LIFE_TIME parameter is set to unlimited and alter the APEX_PUBLIC_USER account and assign it

the new profile.

Otherwise you can do this

alter profile default limit password_life_time unlimited;

alter profile default limit PASSWORD_LOCK_TIME 7;

Configure the Embedded PL/SQL Gateway

The embedded PL/SQL gateway installs with the Oracle Database 11g. However, you must configure it before you can use it with Oracle Application Express. To accomplish

this, you run a configuration file and unlock the ANONYMOUS account.

Running the apex_epg_config.sql Configuration Script

In a new installation, you configure the embedded PL/SQL gateway by running the configuration script apex_epg_config.sql. Then, you unlock the ANONYMOUS

account.

To run the apex_epg_config.sql configuration script:

1. Change your working directory to the apex directory where you unzipped the Oracle Application Express software.

2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

■ On UNIX and Linux:

$ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

3. Run apex_epg_config.sql passing the file system path to the base directory where the Oracle Application Express software was unzipped as shown in the

following example:

On UNIX and Linux:

@apex_epg_config /tmp

or

SQL> !pwd

/u01/oracle/product/11.2/apex

SQL> @/u01/oracle/product/11.2/apex/apex_epg_config_core.sql /u01/oracle/product/11.2

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as ‘&1/apex/images’

new   1: create directory APEX_IMAGES as ‘/u01/oracle/product/11.2/apex/images’

Directory created.

Enter value for imgupg:

old  47:     if ‘&IMGUPG’ != ” then

new  47:     if ” != ” then

Enter value for imgupg:

old  48:         l_mv_folder := ‘&IMGUPG’;

new  48:         l_mv_folder := ”;

— note here is seems like a hang, but really it takes a lot of time to run this if your server is not a rocket.

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

timing for: Load Images

Elapsed: 00:39:20.80

Session altered.

PL/SQL procedure successfully completed.

Commit complete.

Session altered.

Directory dropped.

4. Enter the following statement to unlock the ANONYMOUS account:

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.

Verifying the Oracle XML DB HTTP Server Port

The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database. You can determine if the Oracle XML DB HTTP server is enabled by

verifying the associated port number.

To verify the port number where the Oracle XML DB HTTP Server is running:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

Enter the following statement to verify the port number:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If the port number returns 0, the Oracle XML DB HTTP Server is disabled.

Enabling Oracle XML DB HTTP Server

The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database.

To enable Oracle XML DB HTTP server:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

GETHTTPPORT

———–

8080

SQL> show parameter job_queue_

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     1000

Change the Password for the ADMIN Account

In a new installation of Oracle Application Express, or if you are converting a runtime environment to a development environment, you must change the password of the

internal ADMIN account. In an upgrade scenario, the password will be preserved and carried over from the prior release.

To change the password for the ADMIN account:

1. Change your working directory to the apex directory where you unzipped the installation software.

2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

Run apxchpwd.sql. For example:

@apxchpwd

When prompted enter a password for the ADMIN account.

pwd: ****

————————————

Verifying the Validity of an Oracle Application Express Installation

You can verify the validity of an Oracle Application Express installation by running the following query:

SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';

If the result is VALID, you can assume the installation was successful.

SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = ‘APEX’;

STATUS

———–

VALID

1 row selected.

——————–

Posted in APEX 4.0 | 4 Comments »