14 Replies Latest reply: Feb 9, 2011 9:00 AM by JorgeB-Oracle RSS

    APEX or SQL Developer?

    814061
      Hello,

      This is my first time posting here, I hope I have put it in the right section.

      I was told that in order to have SPARQL with Oracle, I should use either APEX or SQL Developer, and not to install Oracle Enterprise. (I have never worked with it, and am a bit lost).

      Is it possible to write SPARQL queries in this manner? Which is recommended for a beginner that only knows basics of SQL? Which has a more user-friendly interface?

      I would very much appreciate the help.

      Thank you :)

      Edited by: user13098869 on 14-Nov-2010 02:39
        • 1. Re: APEX or SQL Developer?
          JorgeB-Oracle
          Hi

          APEX - Oracle Application Express is a no-cost option of the Oracle database.
          SQL Developer is a tool that connects to the database to execute queries and PLSQL code

          I do not know how APEX or SQL Developer would work with SPARQL.

          One way to work with SPARQL with Oracle is using the Jena Adaptor. This is a set of tasks you would need to perform:

          1. Install Oracle Database Enterprise Edition
          2. Configure the database to use Semantic Technologies
          3. Install JDK
          4. Install Jena
          5. Install Jena Adaptor
          6. Review Jena Adaptor Demos that include SPARQL examples
          Jena Adaptor Tutorial:
          http://download.oracle.com/otndocs/tech/semantic_web/pdf/semtech11gr2_jenaadapt-tl.pdf
          From Test8.java on you can see some SPARQL examples


          This is the Oracle Semantic Technologies Web Page:
          http://www.oracle.com/technetwork/database/options/semantic-tech/index.html

          This is my scratch pad that I used when doing these tasks in case it helps:

          ---------------------------------------------------
          1. Install 11.2.0.2.0
          and
          2. Create database at time of installation.

          3. Create tablespace for Semantics:

          create tablespace RDF_TABLESPACE
          datafile '/.../rdf01.dbf' size 2000M
          autoextend on next 2000M maxsize 16000M;

          4. Setup Semantic Technologies:

          sqlplus / as sysdba
          @?/md/admin/catsem
          exec sem_apis.create_sem_network('RDF_TABLESPACE');

          5. Place files in working directory:

          JDK: jdk-6u22-linux-x64.bin
          Jena: jena-2.6.3.zip
          Jena Adaptor ./JenaAdaptor/jena_adaptor_for_release11.2.zip

          6. Unzip files:
          unzip jena-2.6.3.zip
          cd JenaAdaptor
          unzip jena_adaptor_for_release11.2.zip

          7. Install JDK:
          chmod u+x jdk-6u22-linux-x64.bin
          ./jdk-6u22-linux-x64.bin
          answer: yes

          8. You should have in your working directory:

          Wdir> ls -l
          total 70388
          drwxr-xr-x 8 ... Jena-2.6.2/
          drwxr-xr-x 7 ... JenaAdaptor/
          -rwxr--r-- 1 ... jdk-1_5_0_21-linux-i586.bin*
          drwxr-xr-x 9 ... jdk1.5.0_21/
          -rw-r--r-- 1 ... jena-2.6.2.zip

          9. Copy files:

          a. Copy ojdbc5.jar into <Jena_DIR>/lib (Linux) or <Jena_DIR>\lib (Windows). (ojdbc5.jar is in

          $ORACLE_HOME/jdbc/lib or %ORACLE_HOME%\jdbc\lib.)

          cp $ORACLE_HOME/jdbc/lib/ojdbc5.jar .../Jena-2.6.2/lib

          b. Copy sdordf.jar into <Jena_DIR>/lib (Linux) or <Jena_DIR>\lib (Windows). (sdordf.jar is in

          $ORACLE_HOME/md/jlib or %ORACLE_HOME%\md\jlib.)

          cp $ORACLE_HOME/md/jlib/sdordf.jar .../Jena-2.6.2/lib

          c. Copy sdordfclient.jar. It is in Work_dir/JenaAdpator/jar

          cp .../JenaAdaptor/jar/sdordfclient.jar .../user/Jena-2.6.2/lib


          10. Environmental variables:

          export JAVA_HOME=.../user/jdk1.5.0_21/bin
          export PATH=.../user/jdk1.5.0_21/bin:$PATH

          11. To compile the examples:

          copy the *.java examples to JENA_DIR/lib

          cp .../user/JenaAdaptor/examples/*.java .../user/Jena-2.6.2/lib

          -- To compile:
          cd .../user/Jena-2.6.2/lib
          javac -classpath ./:./jena-2.6.2.jar:./sdordfclient.jar:./ojdbc5.jar:slf4j-api-1.5.6.jar:log4j-

          1.2.13.jar:slf4j-log4j12-1.5.6.jar:arq-2.8.1.jar:xercesImpl-2.7.1.jar Test.java

          -- To run:
          cd .../user/JenaAdaptor/examples
          java -classpath ./:./jena-2.6.2.jar:./sdordfclient.jar:./ojdbc5.jar:slf4j-api-1.5.6.jar:log4j-

          1.2.13.jar:slf4j-log4j12-1.5.6.jar:arq-2.8.1.jar:xercesImpl-2.7.1.jar:iri-0.7.jar:icu4j-3.4.4.jar

          Test jdbc:oracle:thin:@caosspc46.ca.oracle.com:1521:orcl1102 rdfusr rdfusr Family

          You should see:

          ---------------------------------------------------------
          | f | k |
          =========================================================
          | <http://example.com/John> | <http://example.com/Mary> |
          ---------------------------------------------------------

          Regards!
          Jorge
          • 2. Re: APEX or SQL Developer?
            814061
            Hi,

            Thank you very much for your detailed reply. :) I think it will prove to be very helpful in the process. :)

            Yes, it seems I was wrong, I hadn't understood the subject properly. I need Jena Adaptor after all.

            I have never used Oracle before, and I feel at a loss. How do I create the option 3 that you wrote? <Create tablespace for Semantics> I don't know where to write the data you gave.

            It might be an obvious answer, but at the moment my mind feels a bit blank.

            Thank you very much again! :)
            • 3. Re: APEX or SQL Developer?
              JorgeB-Oracle
              Hi,

              Steps 3 and 4 you execute from the sqlplus prompt

              In order to get the sqlplus prompt type the following from the operating system prompt:

              sqlplus / as sysdba

              The SQL> prompt will appear:

              SQL>

              Here is where you type the CREATE TABLESPACE command

              Regards!
              Jorge
              • 4. Re: APEX or SQL Developer?
                814061
                Uffff, ok I got this far...

                But I made the mistake and put the rdf01.dbf directly into C:\. Is this now my working directory? When I tried to do ls -l, it said "Access Denied".

                (Btw, why is it that I can use the System username in the command prompt, but not in the localhost page? )
                • 5. Re: APEX or SQL Developer?
                  JorgeB-Oracle
                  Hi,

                  No, your working directory is not where the datafiles are.

                  If you are on windows (c:\) why using ls -l that is a unix command?

                  Regards!
                  Jorge
                  • 6. Re: APEX or SQL Developer?
                    814061
                    Erm... yes, you are right. Whoops. I wasn't thinking clearly, panicking about Oracle and Jena. I managed to reach till the last part and compile the files successfully :D It doesn't run though, and it gives me:

                    "ORA-12505, TNS:listener does not currently know of any SID given in connect descriptor"

                    I also used localhost... Could it be something in the connection settings when the database was created?

                    Btw, you saved me a lot of time by writing it so analytical. I appreciate it :)
                    • 7. Re: APEX or SQL Developer?
                      JorgeB-Oracle
                      Hi,

                      Not sure localhost will work here, use the machine name:

                      change this:
                      jdbc:oracle:thin:@caosspc46.ca.oracle.com:1521:orcl1102

                      to your database connection:
                      jdbc:oracle:thin:@<your_complete_machine_name>:<port_of_the_listener>:<SID>

                      You need to know your:

                      <your_complete_machine_name>
                      <port_of_the_listener>
                      <SID>


                      1. For the machine name check it in "My Computer" icon, right click -> Properties -> Computer Name
                      See Full computer name: xxx.xxx.xxxx.xxx

                      2. For the port
                      From the command prompt type:

                      c:\> lsnrctl stat

                      Check line:
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<machine_name>)(PORT=1521)))

                      The port in this example is 1521

                      3. For the SID, enter to sqlplus:

                      sqlplus / as sysdba

                      -- Then execute:
                      select instance_name
                      from v$instance;

                      you can also verify the host name with

                      select host_name
                      from v$instance;

                      Regards!
                      Jorge
                      • 8. Re: APEX or SQL Developer?
                        814061
                        Hi,

                        I did those steps and it still doesn't work for some reason.... I don't know if it helps, but this is what it tells me:


                        Exception in thread "main" com.hp.hpl.jena.shared.JenaException: java.sql.SQLExc
                        eption: The Network Adapter could not establish the connection
                        at oracle.spatial.rdf.client.jena.Oracle.<init>(Oracle.java:199)
                        at Test.main(Test.java:16)
                        Caused by: java.sql.SQLException: The Network Adapter could not establish the co
                        nnection
                        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:412)
                        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:
                        531)
                        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
                        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio
                        n.java:32)
                        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
                        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSou
                        rce.java:280)
                        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java
                        :207)
                        at oracle.spatial.rdf.client.jena.Oracle.getConnection(Oracle.java:904)
                        at oracle.spatial.rdf.client.jena.Oracle.<init>(Oracle.java:195)
                        ... 1 more
                        Caused by: oracle.net.ns.NetException: The Network Adapter could not establish t
                        he connection
                        at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:359)
                        at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.j
                        ava:422)
                        at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:672)
                        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:237)
                        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1042)
                        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:301)
                        ... 9 more
                        Caused by: java.net.ConnectException: Connection refused: connect
                        at java.net.PlainSocketImpl.socketConnect(Native Method)
                        at java.net.PlainSocketImpl.doConnect(Unknown Source)
                        at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
                        at java.net.PlainSocketImpl.connect(Unknown Source)
                        at java.net.SocksSocketImpl.connect(Unknown Source)
                        at java.net.Socket.connect(Unknown Source)
                        at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:141)
                        at oracle.net.nt.ConnOption.connect(ConnOption.java:123)
                        at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:337)
                        ... 14 more


                        Could it be about this Spatial option I've read that should be installed with Oracle? I installed the Desktop Class.
                        • 9. Re: APEX or SQL Developer?
                          JorgeB-Oracle
                          Hi,

                          You can verify if Spatial is installed executing:

                          col comp_name format a35
                          col version format a15
                          col status format a20
                          select comp_name, version, status
                          from dba_registry;

                          I assume Spatial is installed otherwise you would not have been able to create the sem network with exec sem_apis.create_sem_network as in step 4.

                          The problem is the connection.

                          1. What is the outout of:

                          lsnrctl stat

                          2. What is your complete connect string for the jdbc thin driver?

                          Regards!
                          Jorge
                          • 10. Re: APEX or SQL Developer?
                            814061
                            Hi,

                            Ok, Spatial is installed.

                            This is the output:

                            1.

                            LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 27-JAN-2011 18:31
                            :06

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

                            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
                            STATUS of the LISTENER
                            ------------------------
                            Alias LISTENER
                            Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
                            ction
                            Start Date 27-JAN-2011 14:57:59
                            Uptime 0 days 3 hr. 33 min. 13 sec
                            Trace Level off
                            Security ON: Local OS Authentication
                            SNMP OFF
                            Listener Parameter File C:\Users\D\Documents\Oracle\product\11.2.0\dbh
                            ome_1\network\admin\listener.ora
                            Listener Log File c:\users\d\documents\oracle\diag\tnslsnr\D
                            -P\listener\alert\log.xml
                            Listening Endpoints Summary...
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
                            Services Summary...
                            Service "CLRExtProc" has 1 instance(s).
                            Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
                            Service "orcl" has 1 instance(s).
                            Instance "orcl", status READY, has 1 handler(s) for this service...
                            Service "orclXDB" has 1 instance(s).
                            Instance "orcl", status READY, has 1 handler(s) for this service...
                            The command completed successfully


                            2. Connect string:

                            jdbc:oracle:thin:@D-P:1521:orcl
                            • 11. Re: APEX or SQL Developer?
                              JorgeB-Oracle
                              Hi,


                              1. Make sure you now your hostname

                              2. The line

                              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))


                              should tell the name of your server machine instead of the loopback address 127.0.0.1

                              to change this:

                              a. Stop the listener

                              lsnrctl stop

                              b. Edit listener.ora

                              In the server machine go to the $ORACLE_HOME/network/amin directory
                              find file listener.ora and edit:

                              LISTENER =
                              (DESCRIPTION_LIST =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                              (ADDRESS = (PROTOCOL = TCP)(HOST = <complete.machine.name_here>)(PORT = 1521))
                              )
                              )

                              example:

                              LISTENER =
                              (DESCRIPTION_LIST =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                              (ADDRESS = (PROTOCOL = TCP)(HOST = tetrapak.uk.sectra.com)(PORT = 1521))
                              )
                              )

                              c. start the listener and check status

                              lsnrctl start
                              lsnrctl stat

                              3. Is your machine name really D-P?

                              jdbc:oracle:thin:@D-P:1521:orcl

                              please change D-P with the name of the host.

                              Regards!
                              Jorge
                              • 12. Re: APEX or SQL Developer?
                                JorgeB-Oracle
                                Hi,

                                I hope you were able to connect to the database after changing the things in the previous recommendation, if not, please let me know.

                                I wanted to point out that we have a newer release of the Jena Adapter

                                Jena Adapter for Oracle Database 11g Release 2 (11.2.0.2 ) 23-November-2010

                                This release of the Jena Adapter is compatible with ARQ 2.8.5, Jena 2.6.3, and Joseki 3.4.2. It includes performance enhancements, such as compact mid tier caching, faster and more robust SPARQL to SQL interface, binding variable support for faster query executions, better international character handling and optimized join methods for federated queries. The Jena Adapter also has new capabilities for bulk loading of RDF documents and direct loading of RDFa from any Web site into the Oracle Database RDF store.

                                To download use the following link:

                                http://www.oracle.com/technetwork/database/options/semantic-tech/downloads/index-156999.html

                                click on Accept radio button

                                click on Download the Jena Adaptor for Oracle Database 11g Release 2 (ZIP - 772KB)

                                Regards!
                                Jorge
                                • 13. Re: APEX or SQL Developer?
                                  814061
                                  Hi,

                                  I tried also the previous recommendation, but it hadn't worked again. I also tried to change my hostname, but then it wasn't sure that I would use Oracle anymore.

                                  Now it seems that I won't be using it. But thank you for the new update and your help !

                                  Best regards,
                                  Dru
                                  • 14. Re: APEX or SQL Developer?
                                    JorgeB-Oracle
                                    The problem is in the Listener. If you are later required to use Oracle, please let me know. My email is jorge.barba@oracle.com in case you need to contact me directly.

                                    Regards!
                                    Jorge