Automate your oracle import and export using AutoIt

I always believe that I can do almost everything using AutoIt. Well, since I need to do a lot of import and export job manually, I just wondering why not I just automagically do it using AutoIt.

There are a few steps involve in this process :

1. export data from server to my local pc

2. connect to sqlplus and drop user

3. create user and grant permission

4. import back using dump file created in step 1

Now, here how I convert those steps into AutoIt.

(more…)

Posted in AutoIt, Programming | No Comments »

Domain Transfer and New Hosting

This is my first time changing my domain hosting and also transfer my arejae.com domain. Previously everything is taken care by my hosting provider.

You can read how to transfer your domain name in detail here.

Below is the first step to initiate the domain transfer. The rest, we just need to follow the instruction.

(more…)

Posted in Tips & Tricks | 2 Comments »

Drop all triggers in Oracle

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

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 without TNS Names

In my previous post, I’m showing the way how to connect to oracle database using AutoIt. If you see the connect string, you will find it is very simple since we already define the entry ORCL in tnsname.ora file.

$DSN = "Driver={Microsoft ODBC for Oracle};Server=ORCL;Uid=myID;Pwd=myPwd;"

If for a some reason, you need to connect to oracle database without doing an entry in tnsname.ora file, below is the way how to do it.

$DSN =   "Driver={Microsoft ODBC for Oracle}; " & _
         "CONNECTSTRING=(DESCRIPTION=" & _
         "(ADDRESS=(PROTOCOL=TCP)" & _
         "(HOST=192.168.1.6)(PORT=1521))" & _
         "(CONNECT_DATA=(SERVICE_NAME=orcl))); uid=myID;pwd=myPWD;"

That’s all.

Happy coding !!

p/s: Time is running so fast. do you think so ??

Posted in Database, Programming | 2 Comments »