Archive for December, 2008

Menampilkan Yesterday di Unix

Dec 28th

Posted by gatotkaca in Linux

No comments

Sederhan mungkin berguna, jika kita ingin menampilkan tanggal kemarin di unix script berikut bisa digunakan : 

YESTERDAY=`date '+%y:%m:%d' | awk -F":" '{printf "20%2d%2d%2d\n",$1,$2,($3-1)}' | sed 's/ /0/g'`

script ini pernah saya coba untuk menjalankan crontab yang harus mengambil tanggal kemarin dari system.

Startup dan Shutdown Oracle

Dec 24th

Posted by gatotkaca in Oracle

No comments

Sebelum melakukan startup atau shutdown cek terlebih dahulu apakah service oracle sudah jalan atau belum :

1. Login ke OS dengan user oracle 2. check listener

#lsnrctl stat

jika hasilnya sepertin dibawah ini :

TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=NeuronServer.site)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused</code>

berarti listener oracle tidak jalan.

untuk menjalankan ketik

#lsnrctl start

listener bisa dijalankan jika service oracle sudah hidup.

3. Start Up Oracle

1
2
3

#sqlplus /nolog
SQL>conn / AS sysdba
SQL>startup
ORACLE instance started.
 
Total SYSTEM Global Area  608174080 bytes
Fixed SIZE                  1220820 bytes
Variable SIZE             121638700 bytes
DATABASE Buffers          478150656 bytes
Redo Buffers                7163904 bytes
DATABASE mounted.
DATABASE opened.

Kemudian hidupkan listener dan cek kembali listener

#lsnrctl START
LSNRCTL FOR Linux: Version 10.2.0.1.0 - Production ON 24-DEC-2008 09:36:57
 
Copyright (c) 1991, 2005, Oracle.  ALL rights reserved.
 
Starting /app/oracle/product/10.2/bin/tnslsnr: please wait...
 
TNSLSNR FOR Linux: Version 10.2.0.1.0 - Production
SYSTEM parameter FILE IS /app/oracle/product/10.2/network/admin/listener.ora
LOG messages written TO /app/oracle/product/10.2/network/LOG/listener.LOG
Listening ON: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening ON: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NeuronServer.site)(PORT=1521)))
 
Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS OF the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR FOR Linux: Version 10.2.0.1.0 - Production
START DATE                24-DEC-2008 09:36:59
Uptime                    0 days 0 hr. 0 MIN. 0 sec
Trace LEVEL               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter FILE   /app/oracle/product/10.2/network/admin/listener.ora
Listener LOG FILE         /app/oracle/product/10.2/network/LOG/listener.LOG
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NeuronServer.site)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) FOR this service...
The command completed successfully
More >

Create Tokenize Function in Oracle

Dec 24th

Posted by gatotkaca in Oracle

No comments

Fungsi tokenizer sering sekali digunakan , jiak kita menggunakan java fungsi itu akan relatif mudah di gunakan karena disudah disediakan oleh java, nah bagaimana kita melakukan fungsi tokenizer di oracle. berikut ada sample procedure untuk melakukan tokenizer di oracle

The pl/sql Oracle version for the Tokenizer procedure

CREATE OR REPLACE PROCEDURE tokenizer (
         iStart     IN       NUMBER,
         sPattern   IN       VARCHAR2,
         sBuffer    IN       VARCHAR2,
         sResult    OUT      VARCHAR2,
         iNextPos   OUT      NUMBER
        )
AS
     nPos1   NUMBER;
     nPos2   NUMBER;
BEGIN
     nPos1 := INSTR (sBuffer, sPattern, iStart);
    IF nPos1 = 0
    THEN
            sResult := NULL;
    ELSE
           nPos2 := INSTR (sBuffer, sPattern, nPos1 + 1);
          IF nPos2 = 0
          THEN
                 sResult := RTRIM (LTRIM (SUBSTR (sBuffer, nPos1 + 1)));
                 iNextPos := nPos2;
          ELSE
                sResult := SUBSTR (sBuffer, nPos1 + 1, nPos2 - nPos1 - 1);
                iNextPos := nPos2;
          END IF;
     END IF;
END tokenizer;

untuk test nya gunakan script dibawah ini

– a Procedure to Test the Tokenizer

CREATE OR REPLACE PROCEDURE sp_test_tokenizer
AS
      sepr VARCHAR2(1);
      sbuf VARCHAR2(200);
      sres VARCHAR2(200);
      pos NUMBER;
      istart NUMBER;
BEGIN
 
      sbuf := ‘@0@11@222@3333@44444@555555@6666666@77777777@888888888?;
      sepr := ‘@’;
      istart := 1;
      tokenizer (istart ,sepr,sbuf,sres,pos);
      IF (pos &lt;&gt; 0) THEN
          dbms_output.put_line (sres);
      END IF;
     WHILE (pos &lt;&gt; 0)
     LOOP
             istart := pos;
             tokenizer (istart ,sepr,sbuf,sres,pos );
             dbms_output.put_line (sres);
     END LOOP;
END sp_test_tokenizer;
/

dari SQLPLUS

SQL&gt; SET serveroutput ON
SQL&gt;exec sp_test_tokenizer;

silakan mencoba semoga bermanfaat <sumber dari oracle.com>

More >
«12