11 Replies Latest reply: Feb 12, 2013 7:19 AM by yoonas RSS

    JDBC in Connecting to the database

    prog
      I have the database ( Oracle 11g express) on my desktop and I am trying to use JDBC..I am not sure if I am filling the fields correctly
      I already unlocked the HR schema .
      please check the error in Jasper ireport.
      http://www.2shared.com/photo/-mlGjs6H/error.html

      Edited by: Beena-IT on Feb 10, 2013 1:19 AM
        • 1. Re: JDBC in Connecting to the database
          yoonas
          Hi,

          I think you will have to download jdbc for oracle then add using Tools>options>Class path

          http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

          Then you use oracle jdbc driver

          Select Oracle (oracle.jdbc.driver.OracleDriver) from JDBC Driver list

          jdbc:oracle:thin:@localhost:1521:DatabaseName - (Change the local host to your hostname if you have xe running on same pc where you are running ireport you can just change the DatabaseName to your database name leaving localhost unchanged, on xe normally name is 'xe'.

          example: jdbc:oracle:thin:@localhost:1521:xe


          To see your listener details (ports,host) - you can start a command prompt and run 'lsnrctl status'

          Regards
          Yoonas

          Edited by: yoonus on Feb 10, 2013 3:15 AM
          • 2. Re: JDBC in Connecting to the database
            prog
            I've done as you mentioned
            now I am getting this error:

            SQL problems: Listener refused the connection with the following error:
            ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
            ...

            http://www.2shared.com/photo/5ClpHb2x/error2.html
            • 3. Re: JDBC in Connecting to the database
              yoonas
              star a command prompt and type 'lsnrctl status', post the output.

              Replace hr with xe

              try this in jdbc url jdbc:oracle:thin:@localhost:1521:xe
              • 4. Re: JDBC in Connecting to the database
                prog
                Microsoft Windows [Version 6.1.7601]
                (c) 2013 AG Corporation. All rights reserved.

                C:\Users\Bayan>lsnrctl status

                LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 10-FEB-2013 17:19
                :36

                Copyright (c) 1991, 2010, Oracle. All rights reserved.

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                STATUS of the LISTENER
                ------------------------
                Alias LISTENER
                Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
                ction
                Start Date 10-FEB-2013 08:33:33
                Uptime 0 days 8 hr. 46 min. 3 sec
                Trace Level off
                Security ON: Local OS Authentication
                SNMP OFF
                Default Service XE
                Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\a
                dmin\listener.ora
                Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\Bayan-PC\listener\
                alert\log.xml
                Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Bayan-PC)(PORT=1521)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Bayan-PC)(PORT=8080))(Presentation=H
                TTP)(Session=RAW))
                Services Summary...
                Service "CLRExtProc" has 1 instance(s).
                Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                Service "PLSExtProc" has 1 instance(s).
                Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                Service "XEXDB" has 1 instance(s).
                Instance "xe", status READY, has 1 handler(s) for this service...
                Service "xe" has 1 instance(s).
                Instance "xe", status READY, has 1 handler(s) for this service...
                The command completed successfully

                C:\Users\Bayan>
                • 5. Re: JDBC in Connecting to the database
                  yoonas
                  I should have asked to post the output only if the given solution does not work

                  >
                  try this in jdbc url jdbc:oracle:thin:@localhost:1521:xe
                  >

                  Please mark the thread as answered if its resolved else please update the status
                  • 6. Re: JDBC in Connecting to the database
                    prog
                    I am not sure what's going wrong with me but let me summarize my steps:
                    1- downloaded ireport designer in my server which contains Oracle Database 11g Express.
                    2-added ojdbc5 to the classpath in ireport
                    3-configure the data source as below :
                    Name: HIS@xe (HIS is the name of my schema in application express)
                    JDBC Driver: Oracle (oracle.jdbc.driver.OracleDriver)
                    JDBC URL: jdbc:oracle:thin:@localhost:1521:XE
                    username: admin (the name I am using to login to my workspace admin)
                    pswd: *******


                    Testing Result:
                    SQL problems: ORA-01017: invalid username/password; logon denied

                    jdbc:oracle:thin:@localhost:1521:XE

                    ========================================================
                    The output from lsnrctl status :

                    'lsnrctrl' is not recognized as an internal or external command,
                    operable program or batch file.


                    LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 11-FEB-2013 11:58
                    :49

                    Copyright (c) 1991, 2010, Oracle. All rights reserved.

                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                    STATUS of the LISTENER
                    ------------------------
                    Alias LISTENER
                    Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
                    ction
                    Start Date 11-FEB-2013 03:19:55
                    Uptime 0 days 8 hr. 38 min. 54 sec
                    Trace Level off
                    Security ON: Local OS Authentication
                    SNMP OFF
                    Default Service XE
                    Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\a
                    dmin\listener.ora
                    Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\Lolo\listener\aler
                    t\log.xml
                    Listening Endpoints Summary...
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Lolo)(PORT=1521)))
                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Lolo)(PORT=8080))(Presentation=HTTP)
                    (Session=RAW))
                    Services Summary...
                    Service "CLRExtProc" has 1 instance(s).
                    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                    Service "PLSExtProc" has 1 instance(s).
                    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                    Service "XEXDB" has 1 instance(s).
                    Instance "xe", status READY, has 1 handler(s) for this service...
                    Service "xe" has 1 instance(s).
                    Instance "xe", status READY, has 1 handler(s) for this service...
                    The command completed successfully
                    • 7. Re: JDBC in Connecting to the database
                      yoonas
                      >
                      username: admin (the name I am using to login to my workspace admin)
                      pswd: *******
                      >

                      Your workspace is mapped to a schema name in database.

                      On Database JDBC connection Wizard provide your schema name and password Under credentials

                      username: HIS
                      password:<password>

                      Edited by: yoonus on Feb 11, 2013 2:06 AM
                      • 8. Re: JDBC in Connecting to the database
                        prog
                        I read that 10g is sending all the password as an uppercase so I changed the pswd in the schema to 123456
                        useing it gave me :

                        SQL problems: ORA-28000: the account is locked

                        jdbc:oracle:thin:@localhost:1521:XE

                        even when I used the main password for the database it gave me the same thing ..
                        what's wrong with my connection !
                        • 9. Re: JDBC in Connecting to the database
                          yoonas
                          Unlock the account you are trying to connect with

                          start sqlplus session and check if you can connect
                          start>cmd>sqlplus
                          <username>/<password>
                          To unlock
                          sqlplus
                          / as sysdba
                          Alter user <username> account unlock
                          Edited by: yoonus on Feb 11, 2013 3:02 AM
                          • 10. Re: JDBC in Connecting to the database
                            prog
                            CONNECTION TEST SUCCESSFUL!
                            THANK YOU THANK YOU THANK YOU
                            • 11. Re: JDBC in Connecting to the database
                              yoonas
                              You are welcome. Very glad to know it was helpful, especially when i see how happy you are :)