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 »

AutoIT – Connecting to Oracle.

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.

Till then..

Happy coding..!

Posted in Database, SQL/ETL, Tips & Tricks | 3 Comments »

Generate DROP TABLE Statement in ORACLE.

I’ve come to this site while gooling around for generating drop tables statement in Oracle. Thanks.

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

Posted in Database, My Life | No Comments »

MSSQL – Useful query for my reference

This will me my reference of SQL statement. I will update this post if I find anything that I feel useful for me..and for you.

1. Check for temporary table and Drop table if exist.

IF object_id('tempdb..#myTempTable') IS NOT NULL drop table #myTempTable

2. Convert SQL string to HEX

select CONVERT(varbinary(100),'select getdate() as MyDateInHex')

(more…)

Posted in Database, SQL/ETL | No Comments »

Download Malaysia Postcode

While everyone is talking about fatwa on yoga , I’m here to share Malaysia postcode that I use to do some of my analysis work. (which I also get it from someone else. )

You will be expecting the file to be in this CSV format.

This is the only postcode list I have. If you are expecting postcode to be tag by City, I’m sorry for that. I don’t have any. If you do have it, kindly share it with me

(more…)

Posted in Database, SQL/ETL | 6 Comments »