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

    APEX or SQL Developer?


      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?

          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:
          From Test8.java on you can see some SPARQL examples

          This is the Oracle Semantic Technologies Web Page:

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

          1. Install
          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
          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
          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-


          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> |

          • 2. Re: APEX or SQL Developer?

            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?

              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:


              Here is where you type the CREATE TABLESPACE command

              • 4. Re: APEX or SQL Developer?
                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?

                  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?

                  • 6. Re: APEX or SQL Developer?
                    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?

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

                      change this:

                      to your database connection:

                      You need to know your:


                      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:

                      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;

                      • 8. Re: APEX or SQL Developer?

                        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
                        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:412)
                        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:
                        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
                        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio
                        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
                        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSou
                        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java
                        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
                        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?

                          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?

                          • 10. Re: APEX or SQL Developer?

                            Ok, Spatial is installed.

                            This is the output:


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

                            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 - Produ
                            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
                            Listener Log File c:\users\d\documents\oracle\diag\tnslsnr\D
                            Listening Endpoints Summary...
                            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:

                            • 11. Re: APEX or SQL Developer?

                              1. Make sure you now your hostname

                              2. The line


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

                              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))


                              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?


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

                              • 12. Re: APEX or SQL Developer?

                                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 ( ) 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:


                                click on Accept radio button

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

                                • 13. Re: APEX or SQL Developer?

                                  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,
                                  • 14. Re: APEX or SQL Developer?
                                    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.