jump to navigation

Connecting to Oracle behind a firewall May 21, 2008

Posted by maxmil in : Oracle , add a comment

Although 1521 is Oracles default listener port it is not enough to only open this port. When a client connects to Oracle it then picks another port for the communication. If this port happens to be closed in the firewall then the connection fails with a timeout error.

To force Oracle to use port 1521 on Windows you have to add a key to the registry.

– Open regedit.
– Navegate to \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
– Create string USE_SHARED_SOCKET
– Edit and set value to TRUE

format prices in Oracle February 1, 2007

Posted by maxmil in : Oracle , add a comment

I’ve just stumbled on a useful SQL syntax for formating prices in Oracle whilst taking internationalization into account.TO_CHAR(field, '999G990D00L') AS importeThis would give ###,###.##$ in the US and ###.###,###€ in Spain

Automatically update timestamps on Oracle January 26, 2007

Posted by maxmil in : Oracle , add a comment

I often use the Mysql feature that automátically updates a timestamp column every time that the row is modified or inserted. This is very useful for keeping track of the last edit of an object. In Mysql i use this syntax when defining a column:
`fieldName` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
I have been looking for something similar on Oracle 9i. I’ve found that the triggers that Oracle offers are very potent and can easily achieve this. Heres how you do it.

CREATE OR REPLACE TRIGGER <SCHEMA>.<TRIGGERNAME>
BEFORE INSERT OR UPDATE
ON <SCHEMA>.<TABLENAME>
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
:NEW.<DATE FIELD> := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
/

View open cursors in Java January 23, 2007

Posted by maxmil in : Java,Oracle , add a comment

Been having problems with an application that doesn’t close its ResultSets and Statements, the infamous ORA-01000: maximum open cursors exceeded. Searching for the code responsable would have been a long tedious process. The code that i was interested in was contained in a big for loop.

Luckily i found that oracle (version 9i) as a view in the sys schema called v$open_cursor that contains information about the open cursors at any given moment.

Putting a query at the end of each iteration of my for loop i was able to quickly find the culprit.

The necessary query was: select sql_text from v$open_cursor where user_name = 'SOPORTETECNICO'where SOPORTETECNICO is the user that my application uses to connect to the database.

The code snippet that i used was:ResultSet rsoc = dblink.query("select sql_text from v$open_cursor where user_name = 'SOPORTETECNICO'");
while (rsoc.next()){
System.out.println(rsoc.getString("sql_text"));
}
rsoc.getStatement().close();

File permissions for Java Procedure in Oracle October 6, 2006

Posted by maxmil in : Java,Oracle , add a comment

In order to read, write, delete, execute files from an Oracle proceedure written in Java a dba user must issue the following grants:

EXEC DBMS_JAVA.grant_permission(‘SCHEMA-NAME’, ‘java.io.FilePermission’, ‘<<ALL FILES>>’, ‘read ,write, execute, delete’);
EXEC DBMS_JAVA.grant_permission(‘SCHEMA-NAME’, ‘SYS:java.lang.RuntimePermission’, ‘writeFileDescriptor’, ”);
EXEC DBMS_JAVA.grant_permission(‘SCHEMA-NAME’, ‘SYS:java.lang.RuntimePermission’, ‘readFileDescriptor’, ”);

JDBC connection wrappers September 20, 2006

Posted by maxmil in : Java,Oracle , 2 comments

http://www.databasegunforhire.com/

Imp dump to different table space July 13, 2006

Posted by maxmil in : Oracle , add a comment

Having problem importing a dumped schema into a new table space. This looks like the answer…

http://www.experts-exchange.com/Databases/Oracle/Q_20963033.html

Order by ascii in Oracle July 11, 2006

Posted by maxmil in : Oracle , add a comment

I needed to order a table num-alpha instead of alpha-num which is the Oracle default ordering. By num-alpha i just mean numbers before letters, for example 0,1,2,3,4,5,6,7,8,9,a,b,c,d…

My first intent was to use Oracles ascii function:

select idproducto from productos order by ASCII(idproducto);

However this returns a value based only on the first character of idproducto, in this way aa and az return the same value, so that was no good.

The solution that i have found is to do a binary sort using the NSLSORT function:

select idproducto from productos order by NLSSORT(idproducto, 'NLS_SORT=BINARY');

Voila!

JNDI Oracle and Tomcat 5 July 5, 2006

Posted by maxmil in : Java,Oracle,tomcat , add a comment

Been having problems setting up a JNDI oracle datasource in tomcat 5. With apaches common-dbcp i was able to use the getConnection() method of java.sql.Datasource but the getConnection(String username, String password) method ALWAYS returns a java.lang.UnsupportedOperationException.

From what i have seen on the web this is a limitation of the apache common connection pool. However for Oracle there is another factory that is bundled within classes12.jar that you can use. I found the solution here…. http://www.microdeveloper.com/html/JNDI_Orcl_Tomcat.html

Basically its just a question of specifying the type of the datasource as oracle.jdbc.pool.OracleDataSource and the connection pool factory as oracle.jdbc.pool.OracleDataSourceFactory.

This is my context.xml

<?xml version='1.0' encoding='utf-8'?>
<Context docBase="E:/work/proj/vaesa-st/bo/webroot" path="/soporte-tecnico/backoffice" reloadable="true" debug="1">
<Resource name="jdbc/SoporteTecnico_DataSource" auth="Container"
type="oracle.jdbc.pool.OracleDataSource"/>
<ResourceParams name="jdbc/SoporteTecnico_DataSource">
<parameter>
<name>factory</name>
<value>oracle.jdbc.pool.OracleDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:oracle:thin:@193.106.32.211:1521:VAESA</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>20</value>
</parameter>
<parameter>
<name>username</name>
<value>soportetecnico</value>
</parameter>
<parameter>
<name>password</name>
<value>vaesa</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>10</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>-1</value>
</parameter>
</ResourceParams>
</Context>

and from my web.xml

<resource-ref>
<description>Soporte Tecnico datasource</description>
<res-ref-name>jdbc/SoporteTecnico_DataSource</res-ref-name>
<res-type>oracle.jdbc.pool.OracleDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

and the code connection snippet

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/SoporteTecnico_DataSource");
conn = ds.getConnection("xxx", "xxx");

Set serveroutput on in sqlplus July 4, 2006

Posted by maxmil in : Oracle , add a comment

in sql plus or sql worksheet to use commands like dbms_output.put_line to write output you first need to issue a SET SERVEROUTPUT ON