10 Replies Latest reply on Aug 24, 2010 9:58 AM by 668457

    Default Schema in connection URL

    704077

      I'd like to set default database schema in Connection URL

      jdbc:oracle:thin:@<server>:<port1521>:<sid>

      I need to query database without schema prefix
      *select monkey_name from animals.monkey*

      ie. When I run
      select monkey_name from monkey

      it will use animals schema by default.

      Anyone with solution?

      Thanks

        • 1. Re: Default Schema in connection URL
          456474
          Hi,
          I'm supposing that you are connecting with user A and that your application/security/etc requirements do no t allow to connect as user animals.
          As far as I know you have (at least) two choices both based on the "ALTER SESSION SET CURRENT SCHEMA = schema" command
          1)ask your dba for a logon trigger which executes "ALTER SESSION SET CURRENT SCHEMA = ANIMALS"
          2)issue a "ALTER SESSION SET CURRENT SCHEMA = ANIMALS" after you get a connection from the datasource (if you have a pooled DatasSource like DBCP you can try the trick of setting the sql as validation query, I did not tested but it could work)

          ciao,
          Giovanni
          1 person found this helpful
          • 2. Re: Default Schema in connection URL
            castorp
            >
            if you have a pooled DatasSource like DBCP you can try the trick of setting the sql as validation query, I did not tested but it could work
            >
            That will not work.

            DBCP only allows statements that return a result as a validation statement.

            I have always wondered why none of the popular connection pools (DBCP, C3P0) allow an "initialization SQL" to be provided as a configuration parameter...
            • 3. Re: Default Schema in connection URL
              518325
              >
              I have always wondered why none of the popular connection pools (DBCP, C3P0) allow an "initialization SQL" to be provided as a configuration parameter...
              >
              According to [http://commons.apache.org/dbcp/configuration.html]...
              *connectionInitSqls:* (default null) A Collection of SQL statements that will be used to initialize physical connections when they are first created. These statements are executed only once - when the configured connection factory creates the connection.
              I haven't tried this myself, but it seems to fit the bill. Have not personally used C3P0.

              -Rick
              • 4. Re: Default Schema in connection URL
                518325
                Oh, and I almost forgot...you could set up a LOGON trigger to fire as soon as the pool's user connects to do anything you want to your session.

                -Rick
                • 5. Re: Default Schema in connection URL
                  456474
                  Hi,
                  aftre reading the response of tarpaw and castorp I build a simple test case that demonstrates the DBCP feature:

                  package org.jpadbf.jdbc;

                  import java.sql.Connection;
                  import java.sql.ResultSet;
                  import java.sql.SQLException;
                  import java.sql.Statement;
                  import java.util.ArrayList;
                  import java.util.List;

                  import javax.sql.DataSource;

                  import org.apache.commons.dbcp.BasicDataSource;

                  public class TestDBCPConnection {
                       private static final String THIN_DRIVER="jdbc:oracle:thin:@";
                       private static String databaseUrl="localhost:1521:ORCL";
                       private static String username="test1";
                       private static String password="test1";     
                  /*
                  create table TEST_INSERT(ID NUMBER not null, DESCRIPTION VARCHAR2(100));
                  grant select on test_insert to test1;
                  */
                       /**
                       * @param args
                       * @throws SQLException
                       */
                       public static void main(String[] args) throws SQLException {
                            DataSource dataSource = getDBCPDataSource(databaseUrl, username, password,1,20);
                            Connection conn=dataSource.getConnection();
                            try {
                                 testQuery(conn);
                            } finally {
                                 conn.close();
                            }
                            

                       }
                       
                       public static void testQuery(Connection conn) throws SQLException {
                            Statement statement =conn.createStatement();
                            try {
                                 ResultSet resultSet=statement.executeQuery("select 1 from test_insert");
                                 try {
                                      if (resultSet.next()) {
                                           if (resultSet.getInt(1)!=1) {
                                                throw new RuntimeException ("Invalid query result");
                                           }
                                      }
                                 } finally {
                                      resultSet.close();
                                 }
                            } finally {
                                 statement.close();
                            }
                            
                       }     
                       
                       public static DataSource getDBCPDataSource(String jdbcUrl, String username, String password, int minConncetions, int maxConnections) throws SQLException {
                            BasicDataSource dataSource= new BasicDataSource();
                            dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
                            dataSource.setUrl(THIN_DRIVER + jdbcUrl);
                            dataSource.setUsername(username);
                            dataSource.setPassword(password);
                            dataSource.setInitialSize(minConncetions);
                            dataSource.setMaxActive(maxConnections);
                            dataSource.setMaxWait(1000);
                            List<String> sqls=new ArrayList<String>();
                            sqls.add("ALTER SESSION SET CURRENT_SCHEMA = TEST");
                            dataSource.setConnectionInitSqls(sqls);
                            return dataSource;
                       }     

                  }

                  And it seems work nicely.
                  the initSQLS property is added in version 1.3 (On my pc, before running this test case I had version 1.2).

                  Runnign the test case showed that I give you a wrong sql the correct is:
                  ALTER SESSION SET CURRENT_SCHEMA = ANIMALS

                  note the _ before CURRENT and SCHEMA

                  ciao,
                  Giovanni
                  • 6. Re: Default Schema in connection URL
                    castorp
                    >
                    connectionInitSqls: (default null) A Collection of SQL statements that will be used to initialize physical connections when they are first created. These statements are executed only once - when the configured connection factory creates the connection.

                    I haven't tried this myself, but it seems to fit the bill. Have not personally used C3P0.
                    >
                    Ah, great. That must be new, I haven't seen that before...

                    But very good to know, thanks for posting the link
                    • 7. Re: Default Schema in connection URL
                      704077
                      After read through all responses, I now come to the conclusion that creating logon trigger with "ALTER SESSION SET CURRENT SCHEMA = schema" is my possible solution.

                      This will means, every times my app connect to the database with user A, this trigger will switch default schema to "animals".

                      I personally think, this solution is good in a way that all generic SQLs are still portable.

                      Thanks guys. Appreciated your time :)

                      Manet
                      • 8. Re: Default Schema in connection URL
                        704077
                        Not yet implemented, but it is obvious enough that this solution will work.
                        • 9. Re: Default Schema in connection URL
                          773242
                          I wanted to use a connection pool to an oracle database with has multiple schema, but have the connections use a specific schema. I was experiencing the same problem noted here, meaning there seems to be no way to pass the schema in the url. In searching for a solution I found this thread. Unfortunately, it seems that if I use "oracle.ucp.jdbc.PoolDataSourceFactory" there does not seem to be a method that operates on the PoolDataSource that sets the datasource.setConnectionInitSqls() so the connections in the pool will run the SQL to get the correct schema. Moreover, the oracle datasource methods do not have a "setConnectionInitSqls" method.

                          At least I think that is the case. Does anayone know how to implement the setConnectionInitSqls() mthod on the connections from an Oracle PoolDataSourceFactory or oracle DataSource, not apache?
                          • 10. Re: Default Schema in connection URL
                            668457
                            An alternative is to use synonyms. You can either add a synonym to the user connecting by jdbc or you can add a public synonym. So for example if you connected as the user zoo you could add a local synonym as follows:

                            create synonym monkey for animals.monkey;

                            Obviously you would need the create synonym privilege or alternatively if you are running the sql under a system account prefix the synonym name with the schema you want it created under:

                            create synonym zoo.monkey for animals.monkey;

                            Alternatively but less preferred is to create a public synonym as follows:

                            create public synonym monkey for animals.monkey;

                            This is less preferred because the public synonym names are unique to the entire database. So if you had two tables called monkey in different schema you could only create one public synonym called monkey referencing one of them.

                            Here is a script that will create the SQL statements required to generate all the local synonyms for all the objects in a particular schema:

                            select DISTINCT 'CREATE SYNONYM YOUR_USER.' || object_name || ' FOR ' || owner||'.'||object_name||';' from dba_objects where owner = 'YOUR_SCHEMA_NAME' and object_type IN ('PROCEDURE','TABLE','VIEW','SEQUENCE','PACKAGE','MATERIALIZED VIEW','FUNCTION');

                            Just change YOUR_USER to be the user you want the synonymns created under and YOUR_SCHEMA_NAME with the schema that contains the objects you wish to use.