From the category archives:

Oracle

  • Download the 32 bit flavor of “basiclite”, sqlplus, the sdk, and jdbc (if you need it) from download.oracle.com Note: as of 10.2, 64 bit works on snow leopard but not lion.
  • unzip the downloads and place them somewhere out of the way like /Applications/Develop/oracle  the files should be combined into a single folder.  In my case /Applications/Develop/oracle/instantclient_10_2
    this is a strange place, a personal quirk of mine, put it wherever you’d like, just make the appropriate adjustments below.

    unzip instantclient-basic-macosx-10.2.0.4.0.zip
    unzip instantclient-sqlplus-macosx-10.2.0.4.0.zip
    unzip instantclient-sdk-macosx-10.2.0.4.0.zip
    unzip instantclient-jdbc-macosx-10.2.0.4.0.zip
  • make links
    cd instantclient_10_2
    ln -s libclntsh.dylib.10.1 libclntsh.dylib
    ln -s libocci.dylib.10.1 libocci.dylib
  • setup some environment variables… edit your ~/.profile
    # oracle
    export DYLD_LIBRARY_PATH="/Applications/Develop/oracle/instantclient_10_2"
    export SQLPATH="/Applications/Develop/oracle/instantclient_10_2"
    export TNS_ADMIN="/Applications/Develop/oracle/network/admin"
    #export NLS_LANG="AMERICAN_AMERICA.UTF8"
    export PATH=$PATH:$DYLD_LIBRARY_PATH
  • setup a tnsnames.ora in /Applications/Develop/oracle/network (or elsewhere just fix TNS_ADMIN export line above)
  • populate it with something – you’re on your own here unless you happen to be using the ldstech oracle vm, then use this…
    xe=
      (DESCRIPTION=
        (ADDRESS_LIST=
          (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=192.168.56.101)
            (PORT=1521)
          )
        )
        (CONNECT_DATA=
          (SID=xe)
        )
      )
  • restart terminal and test a connection with something like sqlplus username@sid

{ 3 comments }

JDBC: Pass a List to a jdbc parameter

by Bruce on June 14, 2010

If you have a List and want to pass it to a JDBC query as a parameter, try this. In this example we have a list of widget names and we want in return a list of store Id’s. For the sake of this example we are not looking to associate which stores sell which widgets. We simply want to know of any store that sells any of the widgets.

public List<Map<String,Object>> getStoresByWidgets(List<String> widgetNameList) {
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    Map<String, Object> parameterMap = new HashMap<String, Object>();
    parameterMap.put("widgetNames", widgetNameList);
    String sql =
            "SELECT store_id \n" +
            "  FROM widget_store_vw wsv \n" +
            " WHERE store_status = 'Active' \n" +
            "   AND widget_name in (:widgetNames) ";
    return namedParameterJdbcTemplate.queryForList(sql, parameterMap);
}

{ 1 comment }

JPQL Syntax: joins are on the class member

by Bruce on June 14, 2010

In JPQL, if you want to join 2 or more tables explicitly, and not simply walk the relationships in your select or where clauses, you must list the class member of the second+ tables, not the the Object.

select c.keywords from Category c join c.keywords k where c.id = ...
not
select c.keywords from Category c join Keywords k where c.id = ...

I realize the above query could be written as ” from Keywords” but we needed to filter by Category Id and Keyword was mapped to Category via @ManyToMany. Is there still a better way? Please, teach me. Thanks.

{ 0 comments }