Pages

Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Monday, June 16, 2014

ORA-00904: "%text%": invalid identifier 00904. 00000 - "%s: invalid identifier"

A was trying a pretty basic command in SQL Developer and for some reason I got the following response that irritated me for almost an hour

 

ORA-00904: "%text%": invalid identifier

00904. 00000 -  "%s: invalid identifier"

*Cause:   

*Action:

Error at Line: 2 Column: 62

 

My query was pretty much similar to the following.

 

select * from <<table_name>> where <<column_name>> like “text”;

 

After a few trial and errors I identified the error. It was because of the double quotes around the text pattern that I was using. I replaced it with single quotes and things worked like charm. So as end result my query was like

 

select * from <<table_name>> where <<column_name>> like ‘text’;

 

Hope this helps some one J

 

Saturday, November 12, 2011

JDBC Driver URL Format for connection with Oracle


I got the idea of testing my connectivity through JDBC with Oracle, when my attempt to connect through Hibernate in a Struts application failed.
Really I wonder like any one else about what one’s mind can do to one. In my case here it was constructive. Atleast I got to learn something J

jdbc:oracle:thin:[user/password]@[host][:port]:SID
jdbc:oracle:thin:[user/password]@//[host][:port]/SID

  user - The login user name defined in the Oracle server.

  password - The password for the login user.

  host - The host name where Oracle server is running.
         Default is 127.0.0.1 - the IP address of localhost.

  port - The port number where Oracle is listening for connection.
         Default is 1521.

  SID  - System ID of the Oracle server database instance.
         SID is a required value. By default, Oracle Database 10g Express
         Edition creates one database instance called XE.

Here are some example connection URLs:

jdbc:oracle:thin: Herong/TopSecret @localhost:1521:XE
jdbc:oracle:thin:Herong/TopSecret@:1521:XE

jdbc:oracle:thin:Herong/TopSecret@//localhost:1521/XE
jdbc:oracle:thin:Herong/TopSecret@//:1521/XE
jdbc:oracle:thin:Herong/TopSecret@//localhost/XE
jdbc:oracle:thin:Herong/TopSecret@///XE

You can find more details about the same in http://www.herongyang.com/JDBC/Oracle-JDBC-Driver-Connection-URL.html. Seems to be a real nice site

The host, port and SID details can be found in tnsNames.ora file. Following is an illustration of the same


Hence the URL formats for me were like

jdbc:oracle:thin:Karthick/Password@10.106.50.250:1521:AGH
jdbc:oracle:thin: Karthick/Password @//10.106.50.250:1521/AGH


Hope this helps some one seeking for this out there :)

"java.sql.SQLException: Io exception: The Network Adapter could not establish the connection" - A simple way to handle it


Following is a most common exception that one would face if connection to Oracle server is denied for some reason.

java.sql.SQLException: Io exception: The Network Adapter could not establish the connection


All you got to do is ensure that you add classes12.jar to the Java build path of you Java project. You can navigate to this point by right clicking on your project and clicking on properties



Following is a sample class that I used to connect to Oracle

import java.sql.*;

public class OracleConnectionUrl {
       public static void main(String[] args) {
              Connection con = null;

              System.out.println("about to load");
              // Load the Oracle JDBC driver
              try {
                     Class.forName("oracle.jdbc.OracleDriver");
              } catch (ClassNotFoundException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
              }
              System.out.println("Oracle JDBC driver loaded ok.");

              try {
                     con = DriverManager
                                  .getConnection("jdbc:oracle:thin:karthick/password@10.116.51.215:1521:XHF");
                     con.close();
              } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
              }
}
}

Hope this helps :)

Saturday, October 29, 2011

Configuring ODBC Connection for Oracle in Windows 7

1. In order to configure a ODBC connection for Oracle in windows 7 follow the following steps

2. Goto Start->Control Panel->Administrative Tools->Data Sources (ODBC)

3. The following screen titled “ODBC Data Source Administrator” will be opened up


4. Now choose the System DSN tab and click on add.

5. This will open up a new window titled “Create New Data Source”

6. Scroll down to “Microsoft ODBC for Oracle” and click Finish



7. This will result in the following window. Provide the credentials as required and click ok

About TNSNames.ora

This is in continuation to my previous article where I mentioned about TNSNames.ora.
Again this is my understanding and am a beginner1. The Location where you would find tnsnames.ora file inside a oracle client directory is typically inside the NETWORK\ADMIN folder

2. In My case it is inside d:\oracle\product\11.2.0\client_1\NETWORK\ADMIN

3. This is one file that you should know to create if you want to create connections in SQL Developer client to databases in sever.

4. A sample copy of tnsnames.ora file will be available in the SAMPLE Directory. When in doubt copy paste it and make necessary modification to it



5. The various elements that should be understood in this file are as follows

Hello World Oracle SQL Developer

Well by Oracle SQL Developer, I mean a developer tool and not a programmer/developer. :)








Following are purely my own understanding as a beginner to work with Oracle SQL Developer.
  1. When you install a oracle server 2008, you will automatically obtain a Oracle SQL Developer client with it.
  2. You cannot create a new database with Oracle SQL Developer client. In order to do that you would require an Admin Client for the same.
  3. So in order to work with databases you have to create a connection to the oracle database residing in a server from the developer client.Following section illustrates the same
  4. A typical Oracle SQL Developer 1.5.5 looks like the following


5. Right Click on Connections and click New Connection

    6. This will result in the following window

      7. First choose the Network Alias. The list of Network Aliases that you see here comes from tnsnames.ora. This is one Important file that would be found in Network/Admin folder.Since this is very important I have documented my understanding on the same in a separately. So refer to the same after go through this one.

      8. The network alias that I have chosen here corresponds to a particular database that has been configured in tnsname.ora
      9. Now key in a connection name of your choice and provide a valid username and password. I know it sounds irritating. Yes you got to have them before trying this one. Speak to some one who can help you on this if you don’t know how to get it. Also as always you can google it.
      10. Now click on test. If the info "Status: Success" appears on the left side of the window then you are ready to go. If not, hmmm… I am not sure. God bless you. Seek help some where.
      11. Now click on Connect. This will result in a new connection as shown in the following image

        12. Now there are two ways by which I can open up the sql console for this connection. They are as follows
          i. First way –using the quick launch icon


          ii. Using the tools menu
          13. Thus you can arrive at the following sql console

            14. Here is my hello word to SQL