How to install Oracle Application Express 4.1 with Database XE 11.2g

Download and install Oracle Database XE 11.2g

Download Oracle Application Express 4.1 (APEX)

Tip: In case you get an ORA-12560 error, your database listener has not started.
Either start it by running “c:\oraclexe\app\oracle\product\11.2.0\server\bin\StartDB.bat” or use  the “Start DB” shortcut from the start menu.

Connect as SYS to the database console
[bash]conn sys/passw as sysdba[/bash]

Run the following sql command to setup APEX
[sql] @c:\apex\apexins myapp myapp  temp /i/
exit[/sql]

How to set PL/SQL Developer or TOAD to use a different oracle instance

Whether you have an oracle home setup or not:

* copy/setup PL/SQL Developer or TOAD to your system
* copy/setup Oracle Instantclient to your system
* add a System PATH Variable for Instantclient i.e.

[text]c:\users\oracle\instantclient

-if this is a 2nd instance add it after 1st instance:
c:\Oracle\Middleware\as_1\bin;c:\users\oracle\instantclient[/text]

* Run PL/SQL Developer or TOAD and configure to use the new instance and tns names

PLSQL: Tools > Preferences > set oracle_home and oracle_oci dll paths
TOAD: If set correctly, it will find the new instance

Note: recommended to use the same tnsnames.ora path for all applications

 

How to set PL/SQL Developer to use a different HOME and OCI library

From within PL/SQL Developer:

Tools > Preferences, Under Oracle > Connection

* Oracle Home… (point to where tnsnames.ora file is, under the Oracle Client Installation)

[text]c:\Users\<username>\oracle\instantclient\network\admin\[/text]

* OCI Library… (point to where the OCI library is placed of the same Oracle Installation)

[text]c:\Users\<username>\oracle\instantclient\bin\OCI.dll[/text]

 

Trace and Alert log files on Oracle Database Server (11g)

Trace and Alert log files

Root ‘log dump’ location:
[sql]select value from v$parameter where name = ‘background_dump_dest’;[/sql]
[text]/u01/app/oracle/diag/rdbms/db_name/trace[/text]

Find the trace file path of current session use the following PL/SQL snippet:
[sql]select u_dump.value || ‘/’ || db_name.value || ‘_ora_’ || v$process.spid ||
nvl2(v$process.traceid, ‘_’ || v$process.traceid, null) || ‘.trc’ “Trace File”
from v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session on v$process.addr = v$session.paddr
where u_dump.name = ‘user_dump_dest’
and db_name.name = ‘db_name’
and v$session.audsid = sys_context(‘userenv’, ‘sessionid’);[/sql]

This would output something similar to:
[text]/u01/app/oracle/diag/rdbms/db_name/trace[/text]

The ALERT file would be:
[text]/u01/app/oracle/diag/rdbms/db_name/trace/alert_<db_name>.log[/text]

You could also create a DB table that would keep the alert log file and you do a select
to view a list of the logged items.

 

Credits to: Rene Nyffenegger

 

How to build a Java app and load it into an Oracle DB

Create the Java class

[java]public class Hello2 {

public static String hello() {
return “Hello World.”;
}

public static String hello(String name) {
return “Hello ” + name + “.”;
}

public static void main(String[] args) {
System.out.println(Hello2.hello());
System.out.println(Hello2.hello(“Java”));
}
}[/java]

Test the output (console):

[bash]java Hello[/bash]
Output:
[text]Hello World.
Hello Java.[/text]
After compilation, run the following (via puntty on DB server) to load it into the Database (console)

[bash]loadjava -r -f -o -user dbuser/dbpass HelloWorld2.class
# run dropjava to unload any loaded java app/class[/bash]
Calling it from JDeveloper:
[sql]–Create a db connection in JDev as SYS
–R-click on this new connection and select “SQL Worksheet”
–Enter the following in command window:
call dbms_java.LOADJAVA(‘/home/oracle/myapp.jar -verbose -force -grant SCOTT -resolve’);
–to Drop it:
call dbms_java.DROPJAVA(‘/home/oracle/myapp.jar -verbose’);[/sql]

Using a Package (see for Function further down):

[sql]–Create PL/SQL wrapper package bind to a Java class file
CREATE OR REPLACE PACKAGE hello2 AS
–Null argument function header
FUNCTION hello RETURN VARCHAR2;
–One argument function header
FUNCTION hello(who VARCHAR2) RETURN VARCHAR2;
END hello2;
/
–Package body
CREATE OR REPLACE PACKAGE BODY hello2 AS
–Null argument function body
FUNCTION hello RETURN VARCHAR2 IS
LANGUAGE JAVA NAME ‘Hello2.hello() return String’;
–One argument function body
FUNCTION hello(who VARCHAR2) RETURN VARCHAR2 IS
LANGUAGE JAVA NAME ‘Hello2.hello(java.lang.String) return String’;
END hello2;
/[/sql]

Now check that the objects are present (sql):

[sql]SELECT object_name, object_type, status
FROM user_objects
WHERE object_name IN (‘Hello2’, ‘HELLO2’);[/sql]

The output should be something like:

[text]OBJECT_NAME  OBJECT_TYPE   STATUS
———–  ————  ——
HELLO2 PACKAGE VALID
HELLO2 PACKAGE BODY VALID
Hello2 JAVA CLASS VALID[/text]

Finally, test the package by calling:

[sql]SELECT  hello2.hello(‘Mr Java’) FROM dual;[/sql]

Output:

[text]HELLO2.HELLO(‘MRJAVA’)
———————————–
Hello Mr Java.[/text]

Using a PL/SQL Function rather than a Package

[sql]create or replace function hello2 return varchar2 as
language java name ‘Hello2.hello() return java.lang.String’;[/sql]

To call the function, from sqlplus set a variable and dump the string in there:
[sql]variable tmp varchar2(20);
call hello2.hello() into :tmp;[/sql]
Output:
[text]tmp
———
Hello World.[/text]

How to set an Oracle DB 10g to start automatically

Login to where the Oracle DB is installed and vi as root

[bash]vi /etc/oratab[/bash]

Edit the file, find and set the lines as follows:

[text]orcl:/u01/app/oracle/product/10.2.0/db_1:N[/text]
vi cmd to type Insert key
[text]orcl:/u01/app/oracle/product/10.2.0/db_1:Y[/text]
vi cmd Esc to exit editing
vi cmd to write/save and exit :wq

Edit dbora, which is mainly to set the ORA home and owner and execute the listener to start or stop

[bash]vi /etc/init.d/dbora #file will be created if it does not exist[/bash]

Edit the file and enter the following as specific to your ORA environment vars:

[text]#!/bin/sh
# description: Oracle auto start-stop script.
# # Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
# # Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME PATH

ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ] then
echo “Oracle startup: cannot start”
exit fi

case “$1” in
‘start’)
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c “$ORA_HOME/bin/dbstart $ORA_HOME” ;
;

‘stop’)
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c “$ORA_HOME/bin/dbshut $ORA_HOME” ;
;
esac[/text]

Set the appropriate privileges to the file:
[bash]chmod 750 /etc/init.d/dbora[/bash]

Notes:
May need to set in /u01/app/oracle/product/10.2.0/db_1/bin/dbstart
[text]ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle/bin/tnslsnr
to:
ORACLE_HOME_LISTNER=$ORACLE_HOME[/text]

…in some cases where the listener won’t start.