Vision: Be a light wherever God sent

Have you need to count all rows on each table in your ORACLE database? If you count one by one, it will take a lot of your time.  So, for the efficient way, you can use steps and syntax below : 1. spool on you query on sqlplus. Syntax: SQL> spool on SQL> spool C:\AllRowCount_all.txt It used to save all your current queries and the result. 2.  Run this query below : SQL> set serveroutput on SQL> declare 2  numrows integer; 3  cursor c1 is select table_name from user_tables order by table_name; 4  function rowcount(tablename in user_tables.table_name%type) 5  return integer  [ Read More ]

As we know, one of softwares that help us to interact with Oracle is TOAD. And for small-medium scale data, there is SQL*Loader feature to be used on TOAD. But how if you’ve around 2Terra or more data to be uploaded ? For my experiences, you will find that TOAD GUI become hang (but in the fact, you can use PL/SQL procedure to check it. sometimes, only TOAD GUI hang, but the process still running). The easiest way that will help you to upload large scale data is via command prompt. Follow this step, and feel the comfortness .. Only  [ Read More ]

Hi, Sometimes as someone who interact with Oracle Database, we need to get total row for all tables in a tablespace, or maybe for multiple tables in a short time. For this purpose, we can use this code below : SQL> set serveroutput on SQL> declare 2 numrows integer; 3 cursor c1 is select table_name from user_tables order by table_name; 4 function rowcount(tablename in user_tables.table_name%type) 5 return integer is 6 cursornum integer; 7 numrows integer; 8 ignore integer; 9 begin 10 cursornum := dbms_sql.open_cursor; 11 dbms_sql.parse(cursornum, 12 ‘select count( * ) from ‘ || tablename, 13 dbms_sql.v7); 14 dbms_sql.define_column(cursornum, 1,  [ Read More ]

ORACLE gives us free for usage, but we must pay for official support from ORACLE. And when company decide to use ORACLE and ask for ORACLE support, they must be given a key called as METALINK. It’s great if you have one when you work with ORACLE products. But how about if the opposite happen? Well, that’s happen to me..The company where I work doesn’t have metalink. Yes, we’re a consulting company, but only clients who decide to use ORACLE support has the metalink. Because of we’re consultants in project, so we didn’t have the metalink. At first, it will  [ Read More ]

For last few days, I need to use SUBSTR to help me analyze data. So, I will give you small tips about SUBSTR. Let’s play ! First, you must know the format of SUBSTR below : substr( string, start_position, [ length ] ) string is the source string. start_position is the position for extraction. The first position in the string is always 1. length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string. So, what’s the interesting part of SUBSTR? If start_position is 0, then substr treats start_position as  [ Read More ]

In ORACLE, we know the data type of : NVARCHAR Vs VARCHAR *) NVARCHAR2 Vs VARCHAR2 *) *) For discussion sample, I took NVARCHAR2 Vs VARCHAR2 as sample. What’s the differences of that? From many sources that I read on internet, I found this facts: 1.) NVARCHAR will return UNICODE data. But it will consume your memory resource almost twice bigger (2n) . For example : When VARCHAR2 (8) only consume 8 bytes, then NVARCHAR2(8) will consume 16 bytes of your memory space. 2.) VARCHAR can’t return the UNICODE data. So,what is UNICODE data? Unicode provides a unique number for  [ Read More ]

RUMUS DASAR Untuk mendapatkan record yang berisi numeric data saja : select  * from namatabel WHERE length(namafield) – Length( TRANSLATE(namafield, CHR(1)||TRANSLATE(namafield, CHR(1)||’1234567890′, CHR(1) ), CHR(1) ) ) = 0 Untuk mendapatkan record yang berisi non-numeric data saja : select  * from namatabel WHERE length(namafield) – Length( TRANSLATE(namafield, CHR(1)||TRANSLATE(namafield, CHR(1)||’1234567890′, CHR(1) ), CHR(1) ) ) > 0 CONTOH KASUS >>> Misalkan ada data sbb di sebuah tabel yang mempunyai nama “NAMATABEL”: ID ISI_DATA D001 3425 D002 6789 D003 Piring D004 Sendok D005 Garpu2 >>> Apabila  ingin mendapatkan numeric data, gunakan sintaks sbb : select * from namatabel WHERE length(isi_data) – Length(  [ Read More ]

Comparison Operators Oracle Microsoft SQL Server = = > > < < >= >= <= <= <>, !=, ^= <> IS NOT NULL IS NOT NULL IS NULL IS NULL Group Functions Oracle Microsoft SQL Server AVG AVG COUNT COUNT MAX MAX MIN MIN SUM SUM Other Functions Oracle Microsoft SQL Server ABS ABS CEIL CEILING COS COS EXP EXP FLOOR FLOOR LN LOG LOG LOG10 MOD % NOT NVL IS NOT NULL NVL IS NULL POWER POWER ROUND ROUND SIN SIN SQRT SQRT TAN TAN