Database Application

8:20 PM database, oracle No Comments

Database Application
Almost perangakat in all aspects of computer use in an organization is always associated with the database. Computing devices are usually used to perform the function of Management Information Systems, which today has become a necessity, in order to improve efficiency, competitiveness, kekuratan, organizations operating speed. And the database is one of the main components in each system information. There is no information system can be created / executed without any database.
In more technical areas of common yan has used the database for efficiency, accuracy and speed of operation include:

�� Officer, for a variety of companies that have many employees.
�� warehousing (inventory), for manufacturing firms (manufacturers), wholesalers (resellers), pharmacies, etc..
�� Accounting for various companies.
�� Reservation, for hotels, planes, trains, etc. APAI
�� customer service (customer care), to companies associated with bnayaknya customers (banks, consultants, etc.)
Are forms of organization that uses the database (as a component information system) can be:
�� Banking, in conducting customer data management / data saving / borrowing, making accounting reports, accounting, customer information services / prospective customers.
�� Insurance, in conducting customer data management / data premium payments, processing insurance claims filing, etc..
�� Hospital, in managing the disease history / treatment pasen, handling maintenance payments.
�� Goods Manufacturers, in managing the data in and out, of goods (inventory).
�� manufacturing industry, in order to help the management of goods, managing employee data.
�� Education / schools, in helping students pengelola0an data, scheduling.
�� Telecommunications, in doing administration data cable management / customer data, etc. to handle noise.

Read More

select (oracle)

8:18 PM database, oracle No Comments

One of the most important syntax in SQL is select. Select function to display the data in the tables contained in the database. This is where there are many differences between regular SQL and Oracle SQL. Oracle lot summarizes syntax into simpler syntax although also remain memerima oracle SQL strandar.

SELECT
[tb1].[nama_kolom1],
[tb2].[nama_kolom2], . . .
FROM
[nama_tabel1] [tb1],
[nama_tabel2] [tb2]
WHERE
[kondisi_join]
[operator_boolean] [kondisi_select]
ORDER BY [nama_kolom_order]
GROUP BY [nama_kolom_group];

Description:
tb: Alias of [nama_tabel1]
nama_kolom: Name columns to display
kondisi_join: If the FORM clause there are two or more tables then there should be this condition is menjoinkan table-table in clause
operator_boolean: operator-like kondisi_join operator for AND and OR
kondisi_select: Conditions addition to perfecting this SELECT operation. Usually using comparison operations, such as:>, <,> =, <=, BETWEEN, <>
nama_kolom_order: SELECT results to the sort by the nama_kolom.
nama_kolom_group: SELECT Results to nama_kolom classified based. So all the records of the same value will be issued only one record only. Noteworthy is that all fields must be in the SELECT GROUP BY a.

Before clarified with an example is better for us to fill the data for the tables we have made in the article Create Table "Teaching-Learning System" - (Analysis & Design). Script to fill in the data please download it here.

1. Displaying all students with grade I sorted by student name.

SELECT
NAMA,
ALAMAT
FROM
TEST.MURID
WHERE
KELAS_ID = ‘I’ /*[kondisi_select]*/
ORDER BY NAMA, NIS; /*[nama_kolom_order]*/

2. Displaying the teachers who teach biology.

SELECT
g.NIG,
b.NAMA
FROM
TEST.GURU g,
TEST.BELAJAR b
WHERE
g.NIG = b.NIG /*[kondisi_join]*/
AND b.KODE_MP = ‘DA0007′ /*[kondisi_select]*/
GROUP BY g.NIG, g.NAMA; /*[nama_kolom_group]*/

Read More

Insert, Update, Delete (Oracle)

6:35 PM database, oracle No Comments

Standard operation is a database insert, delete and update. The three so-called DML stands for Data Manipulation Language. SQL is a standard language for database processing. Of course, any use Oracle-SQL, only a few characteristics that differ from standard SQL.

Once we started discussing the "Try-try" Oracle, then we have a separate user is a user named TEST. Please go to your page or in Oracle XE to SQL + (if you install another version of the oracle, for example: 8i, 9i, 10g, and you also must enter hostring / tnsnames your database) and login with the user, namely:

User TEST
Password test

INSERT

Insert is used to enter data into a table in the user (schema) specific. Insert syntax can be written as follows:

INSERT INTO [nama_user].[nama_table]

([nama_kolom1], [nama_kolom2], . . .)

VALUES

([nilai1], [nilai2], . . .);

Description:
username: user name or schema name when logged in
nama_tabel: Name of the table contained in the user (schema) is
nama_kolom: Name of the column that will contain the data in [nama_tabel]
value: The value that will be filled in [nama_kolom], eg: [value1] will be populated into the [nama_kolom1], [value2] will be populated into the [nama_kolom2]
UPDATE

Update is used to change the data in a table in the user (schema) based on certain specific conditions. Update syntax can be written as follows:

UPDATE [nama_user].[nama_table]SET

[nama_kolom1] = [nilai1],

[nama_kolom2] = [nilai2],

[nama_kolom3] = [nilai3],

. . .

WHERE

[kondisi_update];

Description:
username: user name or schema name when logged in
nama_tabel: Name of the table contained in the user (schema) is
nama_kolom: Name of the column that will contain the data in [nama_tabel]
value: The value that will be filled in [nama_kolom]
kondisi_update: A condition that the filter (filter) which records all that will be updated
DELETE

Delete is used to delete data on a table in the user (schema) based on certain specific conditions. Delete syntax can be written as follows:

DELETE [nama_user].[nama_table]WHERE

[kondisi_delete];

Description:
username: user name or schema name when logged in
nama_tabel: Name of the table contained in the user (schema) is
kondisi_delete: A condition that the filter (filter) the records to be anywhere in the delete

Before reading the example below, a good idea to read the article first Create Table-Teaching and Learning System - (Analysis & Design). The article will help you to understand the examples given, because the tables used are based on case studies.

Insert:

INSERT INTO TEST.MURID (

NIS,
NAMA,
TGL_LAHIR,
JENIS_KELAMIN,
ALAMAT,
ORTU)

VALUES (

‘000001′,
‘MUKHTARUL UMAM’,
TO_DATE(‘23-04-1993′,‘DD-MM-YYYY’),
‘L’,
‘JL. P. DIPENOGORO, TEGAL’,
‘SULAIMAN’);

Update :


UPDATE TEST.MURID SET

NAMA = ‘MUKHTARUL UMAM SHOLEH’,
TGL_LAHIR = TO_DATE(‘25-04-1993′,‘DD-MM-YYYY’)

WHERE

NIS = ‘000001′;

Delete :


DELETE TEST.MURID

WHERE

NIS = ‘000001′;

NB:

* If you want to change the database kept by the end of each DML syntax to execute COMMIT;
* If you do not want to change the alias stored restored to the end with the execution of a ROLLBACK;
* Any DML syntax in COMMIT that it will not be in

Read More

Install Oracle Express on Ubuntu

6:21 PM database, oracle No Comments

As a step to prepare theses, today I accidentally leave to install Oracle Express on Ubuntu I am. Since yesterday trying to install it but failed because the package must connect to the Internet to install it.

From the results of the download at www.oracle.com installer package [oracle-xe-universal_10.2.1-1.0_i386.deb] and then right-click the Open With GDebi Package Installer.

Then the menu below



Installer package ready to run.



Get ready to walk Installing package file ....



Installing oracle-xe-universal

Read More

TRIGGER ORACLE

6:18 PM database, oracle No Comments

Ok yesterday, yesterday I was writing an article about Oracle. And this happened again today belajat about his Oracle trigger. Let trigger more applicable here would not talk about his theory, but what I've tried to make it n cases like this nie .... We had to make three tables of goods, transaction history ma. Now each of us to make transactions in the amount of data tables to update things as well. So let check this out ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

CREATE OR REPLACE TRIGGER barang

BEFORE INSERT OR UPDATE OR DELETE ON transaksi

FOR EACH ROW

DECLARE

vJum NUMBER(10);

vJum2 NUMBER(10);

vSelisih NUMBER(10);

BEGIN

SELECT jumlah INTO vJum FROM alat WHERE id_barang = :NEW.id_barang;

IF INSERTING THEN

UPDATE alat

SET jumlah = vJum – :NEW.jumlah

WHERE id_barang = :NEW.id_barang;

SELECT jumlah INTO vJum2 FROM alat WHERE id_barang = :NEW.id_barang;

INSERT INTO history (id_barang,tanggal,stock,tipe_transaksi) VALUES(:NEW.id_barang,:NEW.tanggal,vJum2,’Tambah Data’);

ELSIF UPDATING(‘jumlah’) THEN

IF :OLD.jumlah > :NEW.jumlah THEN

vSelisih := :OLD.jumlah – :NEW.jumlah;

UPDATE alat

SET jumlah = vJum + vSelisih

WHERE id_barang = :NEW.id_barang;

SELECT jumlah INTO vJum2 FROM alat WHERE id_barang = :NEW.id_barang;

INSERT INTO history (id_barang,tanggal,stock,tipe_transaksi) VALUES(:NEW.id_barang,:NEW.tanggal,vJum2,’Update Data’);

ELSIF :OLD.jumlah < :NEW.jumlah THEN

vSelisih := :NEW.jumlah – :OLD.jumlah;

UPDATE alat

SET jumlah = vJum – vSelisih

WHERE id_barang = :NEW.id_barang;

SELECT jumlah INTO vJum2 FROM alat WHERE id_barang = :NEW.id_barang;

INSERT INTO history (id_barang,tanggal,stock,tipe_transaksi) VALUES(:NEW.id_barang,:NEW.tanggal,vJum2,’Update Data’);

END IF;

ELSIF DELETING THEN

UPDATE alat

SET jumlah = vJum + :NEW.jumlah

WHERE id_barang = :NEW.id_barang;

INSERT INTO history (id_barang,tanggal,stock,tipe_transaksi) VALUES(:NEW.id_barang,:NEW.tanggal,vJum,’Hapus Data’);

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘Data tidak ada’);

END;

/

Read More

Step Install Oracle 10g R2 on Solaris 10 x86

6:15 PM database, oracle No Comments

Checking Requirement:

—————————-

#/usr/sbin/prtconf | grep “Memory size” [Check RAM size]
# /usr/sbin/swap -s [check swap]
# df -k /tmp [check /tmp size (>400mb)]
# uname -r [check solaris version]

# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

# cat /etc/nsswitch.conf | grep hosts
# hostname
# domainname

RUN INSTALL:
—————-
A. create group name “dba”, oracle inventory group “oinstall” and “oracle” user

# /usr/sbin/groupadd oinstall
# /usr/sbin/groupadd dba

{Determine oracle user exist or not
# id -a oracle
{if exist, should be look like this=
uid=440(oracle) gid=200(oinstall) groups=201(dba),202(oper)

{create oracle user=
# useradd -d /export/home/oracle -g dba -G oinstall -m -s /bin/ksh oracle

#mkdir /export/home/oracle
#chown oracle:dba /export/home/oracle

{set password=
# passwd -r files oracle

{to determine nobody user=
# id nobody
# /usr/sbin/useradd nobody >>run if does not exist

B. EDIT FILE /export/home/oracle/.profile
————————————–
umask 022
TMP=/tmp
TMPDIR=$TMP
DISPLAY=localhost:0.0
export TMP TMPDIR DISPLAY
ORACLE_BASE=/u01/app/oracle [replace with ur Oracle base Directory]
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 [replace with ur Oracle home Directory]

ORACLE_SID=jktdb [replace with your database]
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

C. Configure Kernel Parameter
—————————–
Note: Do not follow the official installation instruction, they contain misleading and out errors of fact!

#projadd oracle [This command will create a new 'resource project']
edit the /etc/user_attr file:
adm::::profiles=Log Management
lp::::profiles=Printer Management
root::::auths=solaris.*,solaris.grant;profiles=Web Console Management,All;lock_after_retries=no

oracle::::project=oracle [add this line]

then:

#su – oracle
$ id -p
$ prctl -n project.max-shm-memory -i project oracle

The display look like this:
project: 100: oracle
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-shm-memory
privileged 126MB – deny -
system 16.0EB max deny -

leaving the oracle user still connected in the original one Then, as root in the new terminal, you can issue this command:

#prctl -n project.max-shm-memory -v 4gb -r -i project oracle [create max memory to 4GB]

As soon as you’ve issued that command, switch back to the oracle user’s session and re-issue the earlier command:

$ prctl -n project.max-shm-memory -i project oracle

Note:
#prctl -n project.max-shm-memory -v 4gb -r -i project oracle [this setting will lost after reboot]
to set permanently, run this: #projmod -s -K “project.max-shm-memory=(priv,4gb,deny)” oracle

D. Performing the Oracle Installation
————————————-
#su – oracle
$xhost +
$export DISPLAY=localhost;0.0
$ xhost + >>run this if you install from remote PC
$ cd /export/home/database/ [the source unzipped here]

./runInstaller

FOR SOLARIS SPARC:
====================
$ gunzip ship_rel10_sol64_db.cpio.gz
$ cpio -idm < ship_rel10_sol64_db.cpio

$./runInstaller

If you found unsufficient SWAP disk space on your disk, create folder under / then run this command:
—————————————————
$ TMP=/directory
$ TMPDIR=/directory
$ export TMP TMPDIR

Follow the screen>>NEXT>>NEXT

last, run this as root user:
—————————-
/u01/app/oracle/oraInventory/orainstRoot.sh
/u01/app/oracle/product/10.2.0/db_1/root.sh

Create db:
———-
orc1
jktdb

E. 6.0 On-going Administration
——————————–
Finally, it’s time to get the web-based Enterprise Manager database administration tool up and running.
Since we’re using 10g Release 2, you should be able to launch a browser (Launch -> Web Browser) and simply navigate to : http://localhost:1158/em

If you do not know the correct port number to use, look for the following line in the $ORACLE_HOME/install/portlist.ini file.

in order to be able to log on as SYS with a password of whatever you supplied to the first screen of the Oracle installation wizard. In fact, getting a meaningful result at this point relies on three things having been performed successfully:

1. starting a listener (lsnrctl start)
2. opening the database (sqlplus / as sysdba then startup)
3. starting the Enterprise Manager agent (emctl start dbconsole)

F. Automating Database Startup
———————————————–
edit file “/var/opt/oracle/oratab” script to find lines with ‘Y’ at their ends
Create file “/etc/init.d/dbora”
——-
#!/bin/sh
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’)
su – $ORA_OWNER -c “$ORA_HOME/bin/dbstart”
;;
’stop’)
su – $ORA_OWNER -c “$ORA_HOME/bin/dbshut”
;;
esac

———
#chmod 777 /etc/init.d/dbora
#/etc/init.d/dbora stop

To integrate dbora file to standart Solaris startup and shutdown process:
————————————————————————
#ln -s /etc/init.d/dbora /etc/rc0.d/K01dbora
#ln -s /etc/init.d/dbora /etc/rc2.d/S99dbora

IF u found error this:
———————–
ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener

edit file “dbstart” & “dbshut”, find line $ORACLE_HOME_LISTNER=$1

and change to = $ORACLE_HOME_LISTNER=/u01/app/oracle/product/10.2.0/db_1

RECOMMENDED DIRECTORY STRUCTURE:
———————————————-

[Oracle Base Directory:]
/u01/app/oracle
/u01/app/orauser
/opt/oracle/app/oracle

[Oracle Inventory Directory:]
ORACLE_BASE/oraInventory

[Oracle Home Directory:]

ORACLE_BASE/product/10.2.0/db_1

[Identify an existing oracle base directory:]
#more /var/opt/oracle/oraInst.loc
[the output should be:]

inventory_loc=/u01/app/oracle/oraInventory
inst_group=oinstall

# more /var/opt/oracle/oratab

*:/u03/app/oracle/product/10.2.0/db_1:N

*:/opt/orauser/infra_904:N

*:/oracle/9.2.0:N

COMMON INSTALLATION ERROR:
===========================
Unable to convert from “UTF-8″ to “646″ for NLS!
Solution: Install SUNWuiu8 package.

error adduser:
———————
UX: useradd: ERROR: Inconsistent password files. See pwconv(1M)

This is because the /etc/passwd and /etc/shadow files are out of synchronization on your machine. [CSCdi74894]
To fix this, run the pwconv command, and then rerun cwconfigure.

try to run:

wc -l /etc/passwd /etc/shadow

————–
ERROR Checking monitor: must be configured to display at least 256 colors >>> Could not execute auto check for
display colors using command /usr/openwin/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before continuing with theinstallation, at which time they will be rechecked.

Solution(s):
1. Install SUNWxwplt package
2. Set DISPLAY variable
3. Execute xhost + on target (set in DISPLAY) computer

———————————————————-
Exception in thread “main” java.lang.UnsatisfiedLinkError:
… libmawt.so: ld.so.1: java: fatal: libXm.so.4: open failed: No such file or directory

Solution: Install the SUNWmfrun package.

—————————————————————————————————-
Can’t load ‘/usr/perl5/5.8.4/lib/i86pc-solaris-64int/auto/Sun/Solaris/Project/Project.so’ for module
Sun::Solaris::Project: ld.so.1: perl: fatal: libpool.so.1: open failed: No such file or directory at
/usr/perl5/5.8.4/lib/i86pc-solaris-64int/DynaLoader.pm line 230. at /usr/sbin/projadd line 19 Compilation
failed in require at /usr/sbin/projadd line 19. BEGIN failed–compilation aborted at /usr/sbin/projadd line 19.

Solution: Install the SUNWpool SUNWpoolr packages.

———————————————————————–
bash-3.00$ /u01/app/oracle/product/10.2.0/db_1/bin/./emctl start dbconsole
Exception in getting local host
java.net.UnknownHostException: -a: -a
at java.net.InetAddress.getLocalHost(InetAddress.java:1191)
at oracle.sysman.emSDK.conf.TargetInstaller.getLocalHost(TargetInstaller.java:4977)
at oracle.sysman.emSDK.conf.TargetInstaller.main(TargetInstaller.java:3758)
Exception in getting local host

Solution : check server hostname and /etc/hosts
————————————————————————-

UNINSTALL ORACLE 10G:
———————
1. remove all database, by running $dbca
2. stop any oracle process running:
Database Control : $ORACLE_HOME/bin/emctl stop dbconsole
Oracle Net listener : $ORACLE_HOME/bin/lsnrctl stop
iSQL*Plus : $ORACLE_HOME/bin/isqlplusctl stop
Ultra Search : $ORACLE_HOME/bin/searchctl stop

3. Start Oracle Universal installer:

$ORACLE_HOME/oui/bin/runInstaller

4. In the Welcome window, click Deinstall Products.
5. In the Inventory screen, select the Oracle home and the products that you want to remove,
then click Remove.

Read More