If it’s good, it’s wonderful

If it’s bad, it’s experience

p/s : who feel time is running too fast…?

Searching that software online bring me no luck, so I ask my friend to email it to me. Now, im here to share the software in case someone need it.

You can download it here.

p/s: I’m very very busy nowdays….sorry if my response is so slow.

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.

Opt("WinTitleMatchMode",2)
$cmdPID = Run("cmd", "", @SW_SHOWNORMAL)
WinWaitActive("cmd.exe")
$hDos = WingetHandle("cmd.exe")
SendKeepActive($hDos)

Send("d:")
Send("{ENTER}")
Send("cd\backup")
Send("{ENTER}")

$filename = @YEAR & @MON & @MDAY & ".dmp"
;First part
$parameter = "arejae/arejae@mydevelopmentserver file=" & $filename
Send('exp.exe ' & $parameter)
Send ("{ENTER}") 

;Second part
Send('sqlplus "/as sysdba"')
Send ("{ENTER}")
Send('drop user arejae cascade;')
Send ("{ENTER}")
;assuming no errors..well..just to let you know that assuming is killing...

;3rd part
Send('create user arejae identified by arejae;')
Send ("{ENTER}")
Send('grant DBA to arejae;')
Send ("{ENTER}") 

;exit SQLPlus and proceed to import file
Send("exit")
Send("{ENTER}")

;4th part
Send('imp arejae/arejae file='& $filename & ' full=y ')
Send ("{ENTER}") 

;done..exit from command prompt
Send("exit")
Send("{ENTER}")

That’s all.. now i can combine all those steps with only one single click or..i can schedule it to run at anytime.

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.

Make sure we know the EPP key. This is basically a password for a domain name and can be requested from our current provider.

Once everything is done, you will receive an email to approve the domain transfer.

ermm…a litte bit confuse here right?? yes..currently you are the owner of the domain name but someone else is take care for you. Now you want to take care by yourself

It take me for one week++ to complete the overall process.

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...

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.

$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 ??

$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.

Till then..

Happy coding..!

As for my future reference, I will write those SQL statement in my post.

select 'drop table '||table_name||' CASCADE CONSTRAINTS;' from USER_TABLES where table_name like '%_XXX%'

Happy SQL-ing !

p/s: Is there anyone know about replication in oracle ?? it gives me headache !! grrrrrr

Previously I upload the csv file containing malaysia postcode here hence I will use this file as my sample for this tutorial.

1. Create new transformation (I’m assuming that you know how to open up this application)

2. Drag CSV file input to Transformation area

3. Double click CSV file input and Browse the source file.

Once the source file is in place, click the Get Fields button.

Name,Type,Length will auto populate based on the file contain. Change it accordingly.

4. Once the source file is ready, drag the Table Output to the transformation area.

Click both the file input and table output at the same time right click to create New Hop

5. Double click table output and create new connection.

6. Once the connection is define properly,click the SQL button

and Execute the SQL Statement to create the output table

7. Run the transformation.

8. Execution results.

9. Check the result from mysql console.

I’ve convert this tutorial into movie format. You can have a look it here. Sorry for the quality of the movie is not good.

Till then,Adioss.

ps :

This is my first post in year 2009. I’m expecting my work life is getting much more busier in this few months.