tag:blogger.com,1999:blog-56675398134694064452024-03-13T16:21:16.643-07:00Tutorial OracleSolusi Mengenal Oraclerio.oraclehttp://www.blogger.com/profile/05432548246611534828noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-5667539813469406445.post-40105025610776463272009-07-03T22:05:00.000-07:002009-07-03T22:06:58.395-07:00CREATE Table from another table with consitionsTutorial kali ini membahas mengenai cara membuat table baru hasil dari select, atau semacam table temporary. Saya biasa menggunakan script ini untuk mengambil data dari hasil select * from [view]. Jadi data-data dari Oracle View dapat saya export ke database lain.<br /><br />Kelebihan script ini antara lain :<br />1. dapat membuat table hasil dari select table/view<br />2. tidak perlu menentukan jenis field/coloum parameter<br />3. dapat di masukkan di dalam procedure.<br /><br />Perhatikan step-step ny , semoga bermanfaat.<br /><br />SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,<br /> 2 ENAME VARCHAR2(10),<br /> 3 JOB VARCHAR2(9),<br /> 4 MGR NUMBER(4),<br /> 5 HIREDATE DATE,<br /> 6 SAL NUMBER(7, 2),<br /> 7 COMM NUMBER(7, 2),<br /> 8 DEPTNO NUMBER(2));<br />SQL><br />SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);<br />SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);<br />SQL> INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);<br />SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);<br />SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);<br />SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);<br />SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);<br />SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);<br />SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);<br />SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);<br />SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);<br />SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);<br />SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);<br />SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);<br />SQL><br />SQL><br />SQL> -- CREATE Table from another table with consitions<br />SQL><br />SQL> CREATE TABLE newTable AS (SELECT * FROM emp WHERE sal > 2000);<br />SQL><br />SQL> select * from newTable;<br /><br /> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO<br />---------- ---------- --------- ---------- --------- ---------- ---------- ----------<br /> 7566 JONES MANAGER 7839 02-APR-81 2975 20<br /> 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30<br /> 7782 CLARK MANAGER 7839 09-JUN-81 2450 10<br /> 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20<br /> 7839 KING PRESIDENT 17-NOV-81 5000 10<br /> 7902 FORD ANALYST 7566 03-DEC-81 3000 20<br />SQL><br />SQL> drop table newTable;<br />SQL><br />SQL> drop table emp;<br />SQL><br />SQL><br />SQL>rio.oraclehttp://www.blogger.com/profile/05432548246611534828noreply@blogger.com0tag:blogger.com,1999:blog-5667539813469406445.post-9950227318462994132009-07-03T21:05:00.000-07:002009-07-03T21:08:14.730-07:00Oracle Job Schedule<div class="PostContent"> <div class="article"> <p>Tutorial membuat schedule dari Procedure.<br />Oracle Procudure bisa di jalankan otomatis pada saat tertentu dengan cara menggunakan oracle job schedule. Biasanya hal ini di lakukan untuk memanipulasi data-data yg cukup besar, atau perhitungan-perhitungan khusus yang agak rumit dan membutuhkan waktu eksekusi yang cukup lama. Dengan menggunakan job schedule ini procedure akan dijalankan oleh system oracle sendiri sehingga crash data atau crash system dapat di hindari.<br /><br />Interval Parameter :<br />Calendar expresions can have one of these values:<br />'Yearly','Monthly','Weekly','Daily','Hourly','Minutely','Secondely'</p> <p><br /><br /><br /><span style="font-family: lucida grande;">BEGIN</span><br /><span style="font-family: lucida grande;">DBMS_SCHEDULER.CREATE_JOB(</span><br /><span style="font-family: lucida grande;"> job_name => 'bt_auto_schedule',</span><br /><span style="font-family: lucida grande;"> job_type => 'STORED_PROCEDURE',</span><br /><span style="font-family: lucida grande;"> job_action => 'bt_job',--procedure name</span><br /><span style="font-family: lucida grande;"> start_date => TO_TIMESTAMP_TZ('30-DEC-2008 </span><br /><span style="font-family: lucida grande;">00:00:00','DD-MM-YYYY HH24:MI:SS'),</span><br /><span style="font-family: lucida grande;"> repeat_interval => 'FREQ = DAILY; INTERVAL = 1');</span><br /><span style="font-family: lucida grande;">END;</span><br /><span style="font-family: lucida grande;">/</span><br /><br /><br /><span style="font-family: lucida grande;">BEGIN</span><br /><span style="font-family: lucida grande;"> dbms_scheduler.enable('bt_auto_schedule');</span><br /><span style="font-family: lucida grande;">END;</span><br /><span style="font-family: lucida grande;">/</span><br /><br /><span style="font-family: lucida grande;">--</span><br /><span style="font-family: lucida grande;">BEGIN</span><br /><span style="font-family: lucida grande;"> dbms_scheduler.disable('CLEAR_REPORT_BT', TRUE);</span><br /><span style="font-family: lucida grande;">END;</span><br /><span style="font-family: lucida grande;">/</span></p> </div> </div>rio.oraclehttp://www.blogger.com/profile/05432548246611534828noreply@blogger.com0tag:blogger.com,1999:blog-5667539813469406445.post-79467303054949446922009-07-03T20:41:00.000-07:002009-07-03T20:46:39.458-07:00NLS parameter on Oracle<pre>Hal-hal yang membuat NLS Orcle menjadi penting :<br />1. format Date / time<br /> oracle memang agak-agak rewel di datetime function , jika kita tidak melakukan<br />query dengan parameter date dan kita tidak tahu format dasar sysdate kita bakal<br />kerepotan.<br />2. currecy<br /> disini kita akan tahu format currency yg kita butuhkan<br /><br />Mengetahui setting NLS LANG pada oracle sangat penting sekali.<br />Dengan mengaetahui NLS Lang dalam setup/setting Oracle mempermudah kita dalam<br />menquery sebuah data. Setting NLS Lang antara lain :<br /><br /><span style="font-weight: bold;">NLS_LANGUAGE AMERICAN</span><br /><span style="font-weight: bold;">NLS_TERRITORY AMERICA</span><br /><span style="font-weight: bold;">NLS_CURRENCY $</span><br /><span style="font-weight: bold;">NLS_ISO_CURRENCY AMERICA</span><br /><span style="font-weight: bold;">NLS_NUMERIC_CHARACTERS .,</span><br /><span style="font-weight: bold;">NLS_CALENDAR GREGORIAN</span><br /><span style="font-weight: bold;">NLS_DATE_FORMAT DD-MON-RR</span><br /><span style="font-weight: bold;">NLS_DATE_LANGUAGE AMERICAN</span><br /><span style="font-weight: bold;">NLS_CHARACTERSET WE8MSWIN1252</span><br /><span style="font-weight: bold;">NLS_SORT BINARY</span><br /><span style="font-weight: bold;">NLS_TIME_FORMAT HH.MI.SSXFF AM</span><br /><span style="font-weight: bold;">NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM</span><br /><span style="font-weight: bold;">NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR</span><br /><span style="font-weight: bold;">NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR</span><br /><span style="font-weight: bold;">NLS_DUAL_CURRENCY $</span><br /><span style="font-weight: bold;">NLS_NCHAR_CHARACTERSET AL16UTF16</span><br /><span style="font-weight: bold;">NLS_COMP BINARY</span><br /><span style="font-weight: bold;">NLS_LENGTH_SEMANTICS BYTE</span><br /><span style="font-weight: bold;">NLS_NCHAR_CONV_EXCP FALSE</span><br /><br />melihat data diatas, kita semakin tahu pentingnya setting NLS Lang di dalam Oracle.<br /> Tanpa kita tahu bagaimana cara kita insert date/manupulasi data bentuk date,<br />itulah salah satu pentingnya kita tahu setting NLS Oracle.<br />Cara melihat setting yaitu menggunakan query SQL :<br /><br /><span style="font-weight: bold;">SELECT *</span><br /><span style="font-weight: bold;">FROM v$nls_parameters<br /><br /></span>data akan keluar seperti diatas, sekitar 17 setting. Untuk mengubah setting NLS<br />dapat kita lakukan dengan script sebagai berikut :<br /><br /><span style="font-weight: bold;">ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';</span><br /><span style="font-weight: bold;">ALTER SESSION SET NLS_ISO_CURRENCY = 'AMERICA';</span><br /><span style="font-weight: bold;">ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MM-RR HH.MI.SSXFF AM';</span><br /><span style="font-weight: bold;">ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'DD-MM-RR HH.MI.SSXFF AM TZR';</span><br /><span style="font-weight: bold;">ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RR';</span><br /><span style="font-weight: bold;">commit;</span><br /><br />pada script diatas saya mencoba mengubah setting date format dari DD-MON-RR menjadi<br />DD-MM-RR.<br /><br />Semoga bisa membantu.</pre>rio.oraclehttp://www.blogger.com/profile/05432548246611534828noreply@blogger.com0tag:blogger.com,1999:blog-5667539813469406445.post-31490951729991118552009-07-03T18:51:00.000-07:002009-07-03T20:03:25.361-07:00Oracle Tutorial<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrPcxgCIelIao0E_gb6Giv93vd6uomin4XsAllqbRWz6VFNJ_kX3O_lU5v8zGUcQ0mvazXJ7SfgN51LuJo77mrqVL9dR0o-vx3FvfCPV_0mAqrq8CBVDzMNmYren_hGqubT7rEaPUeVzqk/s1600-h/oracle_logo.jpg"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer; width: 159px; height: 106px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrPcxgCIelIao0E_gb6Giv93vd6uomin4XsAllqbRWz6VFNJ_kX3O_lU5v8zGUcQ0mvazXJ7SfgN51LuJo77mrqVL9dR0o-vx3FvfCPV_0mAqrq8CBVDzMNmYren_hGqubT7rEaPUeVzqk/s320/oracle_logo.jpg" alt="" id="BLOGGER_PHOTO_ID_5354419626338966114" border="0" /></a><span style="font-weight: bold;">Selamat datang di Oracle Tutorial</span>,<br /><br />Situs ini saya dedikasikan buat para pencari Ilmu , untuk para praktisi IT yang membutuhkan tutorial Oracle, dan siapa saja yang mengenal Oracle.<br /><br /><br />Materi yang akan saya berikan antara lain :<br />1. Oracle Installation<br />2. Create Database<br />3. User Role<br />4. Penanganan Trouble Service (ORA-Error)<br />5. Oracle DB Administrator<br />6. SQL Query<ul><li>Oracle String</li><li> Oracle Aritmatic</li><li> Oracle Date Time Function</li><li> Oracle Convertion</li><li> Oracle Logical Expression</li></ul>7.PL/SQL (stored procedure, function, view)<br />8.NLS<br />9.Oracle LinkOracle<br />Backup exp/imp, expdp/impdp<br /><br />referenci :<br /><ul><li><a href="http://oracle.com/">oracle.com</a></li><li><a href="http://djomblomedia.com/index.php?q=taxonomy/term/16">djomblomedia.com</a></li></ul>rio.oraclehttp://www.blogger.com/profile/05432548246611534828noreply@blogger.com0