Does the formatting of a tnsnames.ora file really make a difference?

I recently set up Oracle on my Mac so I could develop an application that uses Oracle for the database tier. I use the option of running Oracle server on a VM and configuring it to be available to my Mac using port-forwarding.

• http://dimitrisli.wordpress.com/2012/08/08/how-to-install-oracle-database-on-mac-os-any-version/
• https://tech.lds.org/wiki/Oracle_VM

I pulled together some notes as I knew I would be going through the procedure again. Going through that procedure the second time, I encountered the following common error:

TNS:could not resolve the connect identifier specified

I checked to make sure I had set up port forwarding correctly on VirtualBox

I verified that I could telnet to the VM on port 1521 (from my Mac)

telnet 127.0.0.1 1521

I verified that I could successfully log in through sqlplus from the VM

sqlplus my_admin@orcl

I verified that the listener was up (from the VM)

lsnrctl status

I verified that my Mac had a tnsnames.ora file and that it was located at $ORACLE_HOME/network/admin/tnsnames.ora and that read permissions were open.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

I verified my environment was configured to properly use the sqlplus client on my Mac.

export NLS_LANG="AMERICAN_AMERICA.UTF8"
export ORACLE_HOME=/opt/oracle/instantclient
export DYLD_LIBRARY_PATH=$ORACLE_HOME

Everything seemed to be correct, but I was still getting errors. As I compared the tnsnames.ora on my one Mac that was working correctly and the other, I noticed that the one that wasn't had the entire configuration block indented. I had indented the code in my notes and so when it was copied over, the indent had remained. It couldn't really be that simple, could it?!

It could.

As soon as I removed the indentation so that the service declaration was bumped up directly with the left edge without any spaces, I could successfully connect.

Wow.

oracle and apache cxf

How to setup Ruby and new Oracle Instant Client on Leopard

http://blog.rayapps.com/2008/04/24/how-to-setup-ruby-and-new-oracle-instant-client-on-leopard/ ul>li*>a[href='$#']{$#}

• http://blog.rayapps.com/2008/04/24/how-to-setup-ruby-and-new-oracle-instant-client-on-leopard/
• http://blog.rayapps.com/2009/09/06/how-to-setup-ruby-and-oracle-instant-client-on-snow-leopard/

Get Oracle Instant Client working on Mac. Then get it working with Ruby!

export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient_10_2"
export SQLPATH="/usr/local/oracle/instantclient_10_2"
export TNS_ADMIN="/usr/local/oracle/network/admin"
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export PATH=$PATH:$DYLD_LIBRARY_PATH

RubyForge: ruby-oci8: Project Filelist

http://rubyforge.org/frs/?group_id=256

Download Ruby OCI8.

Tnsnames.ora - Oracle FAQ

http://www.orafaq.com/wiki/Tnsnames.ora

SERVICE_NAME is the same thing as SID?

ORA11 =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA11)
 )
)

Another example

connection_label =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = server.name.org)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = service.name)
 )
)

Class: OCI8 - Documentation by YARD 0.7.5

http://ruby-oci8.rubyforge.org/en/OCI8.html

Documentation for OCI8. What are the parameters for the constructor again?

 - (OCI8) initialize(username, password, dbname = nil, privilege = nil) constructor 

Instant Client downloads for Mac OS X (Intel x86)

http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html

You need a username/password to get access to these, but it's no big deal.

• http://download.oracle.com/otn/mac/instantclient/11203/instantclient-basic-macos.x64-11.2.0.3.0.zip
• http://download.oracle.com/otn/mac/instantclient/11203/instantclient-sqlplus-macos.x64-11.2.0.3.0.zip
• http://download.oracle.com/otn/mac/instantclient/11203/instantclient-sdk-macos.x64-11.2.0.3.0.zip

It might also be handy to have Oracle JDBC drivers around as well.

Markdown - Wikipedia, the free encyclopedia

http://en.wikipedia.org/wiki/Markdown

I finally checked out how to add markdown for SO (Stack Overflow) entries.

Text attributes *Italic*, **bold**, `monospace`.

<p>Text attributes <em>Italic</em>,
<strong>bold</strong>,
<code>monospace</code>.</p>

Bash - Manipulating Strings - Substring Extraction

http://tldp.org/LDP/abs/html/string-manipulation.html

Extracts $length characters of substring from $string at $position.

${string:position:length}

echo ${stringZ:0}                            # abcABC123ABCabc
echo ${stringZ:1}                            # bcABC123ABCabc
echo ${stringZ:7}                            # 23ABCabc

echo ${stringZ:7:3}                          # 23A
                                             # Three characters of substring.

Apache CXF -- FAQ

http://cxf.apache.org/faq.html#FAQ-HowcanIturnonschemavalidationforjaxwsendpoint%3F

It appears there is a configuration setting that can be used to have Apache CXF handle validation. It also appears that this will not be a turn-key solution for me. Something is not quite right as it seems CXF doesn't have access to the XSD files, even though they are included in the class path.

weblog; a miraculous build

Maven

Developing Web services using Apache CXF and Maven

http://www.ctrl-alt-dev.nl/Articles/CXF-Maven/CXF-Maven.html

Step-by-step instructions on how to develop a web service using CXF and Maven. Starts off by creating a web service without CXF, adds tests and then replaces non-test code with code generated by CXF. Should be a worth while walkthrough.

Oracle

WadeLiu Blog On Oracle: query and kill oracle connection session

• http://focusoracle.blogspot.com/2008/04/query-and-kill-oracle-connection.html
• http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php

My tests failed initially due to the fact I had not added new trusted certificate entries into the existing client certificate. This happened repeatedly and must have used up all the connections in the database pool. They were eventually released, but I tried to see if I could hurry that cleanup along.

I had permissions to execute the first two lines, but not the last one.

SQL> SELECT s.sid, s.serial#, s.osuser, s.program FROM v$session s; 

SQL> select sid from v$session where audsid= userenv('SESSIONID'); 

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 

Work notes; getting to know Oracle

What is the current (or default) schema in the database being accessed?

select user from dual;

https://forums.oracle.com/thread/495481

How do I create a schema?

Schemas are associated with database users in a one-to-one relationship. To create a schema, create a new user. This usually means you will need to have administrative access to the database.

CREATE USER sidney 
    IDENTIFIED BY out_standing1 
    DEFAULT TABLESPACE example 
    QUOTA 10M ON example 
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON system 
    PROFILE app_user 
    PASSWORD EXPIRE;

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8003.htm#i2065278

How do I find out what schemas are available on the database I am accessesing?

Since schemas are linked to user accounts,

SELECT username FROM all_users ORDER BY username;

http://stackoverflow.com/questions/4833459/oracle-sql-query-for-listing-all-schemas-in-a-db

Creating table in other schema oracle using select.

CREATE TABLE schema1.table1 AS (SELECT * FROM SCHEMA.table@LinkedDB);

http://stackoverflow.com/questions/10730596/creating-table-in-other-schema-oracle-using-select

When issuing a command in maven that appears to go wrong, guard the arguments with double-quotes:

mvn install:install-file "-DgroupId=org.mozilla" "-DartifactId=jss" "-Dversion=4.2.5" "-Dpackaging=jar" "-Dfile=C:\Users\AArmijos\workspace\componentes-1.0.4\deps\jss-4.2.5.jar"

http://stackoverflow.com/questions/16348459/maven-3-0-5-command-error-the-goal-you-specified-requires-a-project-to-execute

Add the Oracle JDBC driver to your Maven environment.

mvn install:install-file "-Dfile={Path/to/your/ojdbc.jar}" "-DgroupId=com.oracle" "-DartifactId=ojdbc6" "-Dversion=11.2.0" "-Dpackaging=jar"

http://www.mkyong.com/maven/how-to-add-oracle-jdbc-driver-in-your-maven-local-repository/

Use the Oracle JDBC driver in your Maven project.

# pom.xml
   <!-- ORACLE JDBC driver, need install yourself -->
   <dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0</version>
   </dependency>
<br />

# hibernate.cfg.xml
    <hibernate-configuration>
     <session-factory>
      <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
      <property name="hibernate.connection.url">jdbc:oracle:thin:@127.0.0.1:1521:MKYONG</property>
      <property name="hibernate.connection.username">mkyong</property>
      <property name="hibernate.connection.password">password</property>
      <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
      <property name="hibernate.default_schema">MKYONG</property>
      <property name="show_sql">true</property>
      <mapping resource="com/mkyong/user/DBUser.hbm.xml"></mapping>
    </session-factory>
   </hibernate-configuration>
<br />

# HibernateUtil.java
 static {
  try {
   factory = new AnnotationConfiguration().configure().
     setNamingStrategy(SrvTableNamePrefixNamingStrategy.INSTANCE).
     // addPackage("com.xyz") //add package if used.
     buildSessionFactory();
   
  } catch (Throwable ex) {
   // Make sure you log the exception, as it might be swallowed
   System.err.println("Failed to create sessionFactory object." + ex);
   throw new ExceptionInInitializerError(ex);
  }
 }

# Runner.java
 protected Session getSession(){
  Session session = HibernateUtil.getSessionFactory().openSession();
  return session;
 }

Convert a Maven project to an eclipse project.

mvn eclipse:eclipse

http://www.mkyong.com/hibernate/maven-3-hibernate-3-6-oracle-11g-example-xml-mapping/

Skip running the test phase when running Maven.

"-Dmaven.test.skip=true"

http://stackoverflow.com/questions/1607315/build-maven-project-without-running-unit-tests

What is the "printf" equivalent for Java?

String status = String.format("The rename status is (%d)", RENAME_SUCCEEDED);

http://alvinalexander.com/blog/post/java/use-string-format-java-string-output

Strip off '[' and ']' from a line.

:%s/^\[\([^\]]\+\)\]$/\1/g

Convert code blocks using '{{' and '}}' to a <pre class="code"> block

:%s/^}}/ç/g
:%s/\n{{\(\([\r\n]\+[^ç]\+.*\)\+\)\nç/\r<pre class=\"code\">\1\r<\/pre>/g

Create titles

:%s/^==\+ \(.\+\)$/<h4>\1<\/h4>/g

Create links

:%s/^\[\([^\]]\+\)\]$/<a href=\"\1\" target=\"_new\">\1<\/a>/g

Create breaks

:%s/>\n\n/>\r<br \/>\r\r/g

Escape < in code example blocks

:%s/\t</\t \&lt;/g
:%s/</\&lt;/g