Oracle Errors ORA-01034 and ORA-27101

Writing by on Tuesday, 7 of August , 2012 at 9:37 pm

Sometimes your disk maybe full and your Oracle might not start up or your just having a bad day with Oracle, your IT dept, the build is broken etc. etc.

Well ./lsnrctl start works fine, but Oracle is still coming back with ORA-01034 and ORA-27101 errors. Apparently Oracle was shutdown and never came up.

$ sqlplus
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Now things should be better. You do know that with Oracle/Linux development work happening, chaos etc. , it won’t be long before you hit some more Oracle related issues. But for now, we’re good!

Leave a comment

Category: SQL DB

Get the Schema/Metadata out of a SQL Query without executing it

Writing by on Thursday, 7 of June , 2012 at 5:06 pm

You can use PreparedStatement to compile the SQL and then get the ResultSetMetaData from it.

// Get the Connection
Connection conn = getConnection();

// Get the MetaData
PreparedStatement ps = conn.prepareStatement(sql);
ResultSetMetaData rsmd = ps.getMetaData();

// Get the alias & type
for (int i=0; i<rsmd.getColumnCount(); i++) {
   String alias = rsmd.getColumnName(i+1);
   String strType = rsmd.getColumnTypeName(i+1);
   int type = rsmd.getColumnType(i+1)
}

Leave a comment

Category: Java,SQL DB

MySQL converting MAC Address to a Number

Writing by on Friday, 2 of December , 2011 at 12:36 am

Here’s an example of converting a Mac Address 01:23:45:67:89:ab into a decimal number (1250999896491), using the MySQL conv() function.

The idea is select CONV(‘0123456789ab’, 16, 10); 01:23:45:67:89:ab without the :

select CONV(REPLACE(SUBSTR(’01:23:45:67:89:ab’, INSTR(’01:23:45:67:89:ab’, ‘(‘)+1, 17),’:’,”),16,10);

Results In: 1250999896491

Leave a comment

Category: SQL DB

MySQL: Run SQL Queries From Command Line

Writing by on Thursday, 10 of March , 2011 at 5:51 pm

Here’s the syntax:

mysql -u user -p password database -e ‘SQL Query’

Here are a few examples:

mysql -u root -p mypass world -e ‘select * from city;’
mysql -u root -p mypass world -e ‘desc city;’

Leave a comment

Category: SQL DB

Sample Database for MySQL

Writing by on Wednesday, 9 of July , 2008 at 8:45 am

If you need a sample test database for personal testing and don’t want to create a database, MySQL provides you with these instructions to Set up the sample World database.

It comes with these three tables with enough data to get you started.

Country: Information about countries of the world.
City: Information about some of the cities in those countries.
CountryLanguage: Languages spoken in each country.

Leave a comment

Category: SQL DB

SQL Server First, Last Aggregate Functions

Writing by on Tuesday, 20 of May , 2008 at 4:01 pm

Are you getting a SQL Server error saying that FIRST or LAST are not recognized or unsupported?

Error:’FIRST’ is not a recognized built-in function name. Number:195 Severity:15 State:10

Thats because they’re not.

SELECT LotFk, FIRST(RedemptionPeriodDate) as RedemptionPeriodDate -- ERROR 
FROM InvRegister
GROUP BY LotFk

Workaround is to use MIN or MAX

SELECT LotFk, MIN(RedemptionPeriodDate) as RedemptionPeriodDate -- VALID
FROM InvRegister
GROUP BY LotFk

Leave a comment

Category: SQL DB

Shivdev Kalambi's Blog

Shivdev Kalambi is a Software Development Manager, previously a Principal Software Engineer at ArcSight/HP. With over 16 years' experience in software development, he's worked on several technologies and played different roles and contributed to all phases of projects. Non-tech activies include Ping-pong, Rock Climbing and Yoga at PG, Golf, Skiing, Swimming & a beer enthusiast.