by Andreas Mastichis | Apr 25, 2012 | Databases, Oracle, oracle forms
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]
by Andreas Mastichis | Apr 9, 2012 | Databases, Oracle, Windows
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
by Andreas Mastichis | Mar 20, 2012 | Databases, oracle pl/sql
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]
by Andreas Mastichis | Mar 12, 2012 | Databases, Oracle
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
by Andreas Mastichis | Mar 8, 2012 | Databases, java, JDeveloper, Oracle, oracle pl/sql
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]
by Andreas Mastichis | Feb 23, 2012 | Databases, Oracle
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.
Comments