Arejae.Com

me,myself,english and programming..

Archive for the ‘SQL/ETL’ Category

Error: ORA-01033 ORACLE initialization or shutdown in progress

Thursday, March 11th, 2010

For some reason which I don’t know why, my oracle give me that kind of error.

Googling give me for below solution. And here I post for my future reference, and also for you if you got this same problem .

Run this command first in console.

sqlplus /nolog

And after that, use these commands :

SQL> connect / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down
SQL> startup nomount
ORACLE instance started.
SQL> alter database mount;
SQL> alter database open;

(more…)

Posted in Database, SQL/ETL | No Comments »

Drop all triggers in Oracle

Friday, March 20th, 2009

If you need to drop all the triggers in oracle for a split second, here how you can do it. Below is the sample how to drop all triggers in myTesting schema with trigger name like ‘%_BI’

begin
for i in (select trigger_name,owner from dba_triggers where  trigger_name like '%_BI%' and owner = 'myTesting' ) LOOP
execute immediate 'DROP TRIGGER '||i.owner||'.'||i.trigger_name;
END LOOP;
END;

Make sure you know what you are doing.

Till then..

Happy coding...

Posted in SQL/ETL | No Comments »

Check for NULL Value in Oracle and MySQL

Thursday, March 5th, 2009

If you are familiar with MS-SQL, you will know that you can use ISNULL function to convert NULL value to something else. In below case, it is an example how to convert NULL value to zero.

ISNULL(myField,0)

In Oracle, you can use NVL to get the same result.

NVL(myField,0)

Same thing for MySQL, you can use IFNULL to get the same result.

IFNULL(myField,0)

Don’t under estimate with this function. I’m debugging my program for almost 1 hours just to notice that some values in my Oracle tables having NULL values and causing errors while inserting to MySQL tables.

That’s my quick update for today. Till then…Happy Coding.

Posted in Database, SQL/ETL | 2 Comments »

AutoIT – Connecting to Oracle.

Wednesday, February 18th, 2009

Previously I wrote about how to connect to SQL Server using AutoIt. Since that I’m using Oracle as my main database in my new workplace, I will show how to do the same thing for Oracle.

$conn = ObjCreate( "ADODB.Connection" )
$DSN = "Driver={Microsoft ODBC for Oracle};Server=ORCL;Uid=myID;Pwd=myPwd;"
$conn.Open($DSN)
$rs = ObjCreate( "ADODB.RecordSet" )
$rs.Open( "select BANNER from v$version where banner like 'Oracle%'", $conn )
MsgBox(0, "AutoIT-SQL Result", "Value = " & $rs.Fields( "BANNER" ).Value )
$conn.close

The codes will produce something like below.

autoit-oracle