Archive for the ‘Database’ Category
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 »
Tuesday, March 2nd, 2010
In MySQL, we can easily drop table and check whether it is exist or not using this statement.
DROP TABLE IF EXISTS MyTable
In Oracle, you can’t do that.
Solution that I found is to create a procedure and execute it.
As for my reference and others (if any) here is the script to create that procedure.
(more…)
Posted in Database, Tips & Tricks | No Comments »
Saturday, September 19th, 2009
Everything goes fine with my Oracle 11G but out of the sudden it give me this error. ORA-12560: TNS:protocol adapter error.
I then look at the Event Viewer logs and give me another error message.
ACTION : ‘CONNECT’ DATABASE USER: ‘/’ PRIVILEGE : SYSDBA CLIENT USER: NT AUTHORITY\SYSTEM CLIENT TERMINAL: MYAREJAE STATUS: 0 .
Googling around give me below solution. Just run the command and everything goes back to normal.
oradim -edit -sid orcl -startmode auto
Depending on the situation, I think maybe this solution is not necessary true for everyone but at least it works for me.
Till then…
Happy Coding…
Posted in Database, Programming, Tips & Tricks | No Comments »
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 »
Monday, March 2nd, 2009
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 »