Tag: Oracle

PL/SQL – Retrieving current milliseconds

The function to_char with sysdate is the most known way to retrieve current time in PL/SQL. The way:

select to_char(sysdate,'dd/MM/yyyy HH24:mi:ss') date from dual;

You get this:

DATE
------------------------------
14/08/2012 13:24:32

But, if need get milliseconds also, we should to use systimestamp and the characters ‘FFn‘, where ‘n’ will be number of digits. The milliseconds are the goal, then ’4′ is the number.

select to_char(systimestamp,'dd/MM/yyyy HH24:mi:ss.FF4') date from dual;

And results:

DATE
------------------------------
14/08/2012 13:24:33.2110

If you wants only milliseconds, jus keep ‘FF4’ and remove the others characters:

select to_char(systimestamp,'FF4') millis from dual;

Now you will get:

MILLIS
------------------------------
2110

Good coding!


Oracle – Escaping character ‘_’ in LIKE clause

How should you know, underline(‘_’), in LIKE, is considered a wildcard that means “any character”. Query will replace underline by any other character where ‘_’ is.

Following table bellow:

+---------+
|  WORD   |
+---------+
| COLUMN  |
+---------+
| BEE     |
| SEE     |
| FEE     |
| SHE     |
| _EE     |
+---------+

And running

select column from word where column LIKE '_EE';

Results

+---------+
| COLUMN  |
+---------+
| BEE     |
| SEE     |
| FEE     |
| _EE     |
+---------+

Besides “_EE“, expected initially, “BEE“, “SEE” and “FEE” has been returned.

We have to use escape after LIKE to include ‘_’ at the search. Escape needs a character to transform it in a wildcard that ignores the next one, thus, the new wildcard should be added before ‘_’, like this.

select column from word where column LIKE '\_EE' escape '\';

Now, the result will be:

+---------+
| COLUMN  |
+---------+
| _EE     |
+---------+

I hope this helps some one else! 🙂


A easy way to compile many objects in Oracle database

When compiles an object that is referenced by many others, they stay invalidated until Oracle compile they again and sometimes it fails.

So, here’s a tip to compile several invalid packages, procedures and/or functions and fix this.

There is a command to compile all invalid objects under an user, the command is:

exec dbms_ultility.compile_schema(<USER>)

Replace  <USER> for user, or for user name where invalid objects are.

This command is slow and not compiles objects invalidated by coding error. To find which are invalid you need runs the query below:

select object_name
from all_objects
where status = 'INVALID'

Invalid objects will be listed.


Saving files in BLOB table column of a data base

To insert a file, is it in any format, you need call the method setBinaryStream, implemented by PreparedStatement.

PreparedStatemente.setBinaryStream(int index, Inputstream is, int length);

In sample, we set a table called FILEthat contains BLOB column called BIN.

//Normal connection, as any JDBC connection
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@<IP>:<PORT>:<SID>","<USER>","<PASSWORD>");

//Reading the file and retrieving an InputStream
File file= new File("<COMPLETE_FILE_PATH>");
FileInputStream fis = new FileInputStream(file);

//Preparing statement
PreparedStatement ps = conn.prepareStatement("INSERT INTO FILE(bin) VALUES(?)");

//Passing InputStream and file length
ps.setBinaryStream(1, fis, (int)file.length());

ps.execute();

ps.close();
conn.close();

I used Oracle 8i to execute this sample. I haven’t a MySQL/PostgreSQL/MS SQL Server in my dispose, then you’ll responsible for testing in this data bases and send me the results, OK 😉

Thanks! Until next time!


Retrieving an Oracle cursor in Java

Many people come here looking for one way to retrieve cursors of Oracle procedures in Java. To them, I’ve here are a tutorial showing how to do this.

To retrieve the cursor you should declare him how a REF CURSOR in Package spec.

  --Creating the REF CURSOR type
  type g_cursor is ref cursor;

In both, spec and body, you need declare an out REF CURSOR variable in procedure signature, how cited above.

  procedure PRO_RETURN_CARS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor);

The cursor must be opened in procedure’s body to return, this way:

open o_cursor for
          select car_id, company, model, color, hp, price
          from tbl_car
          where car_id = i_id;

The complete Package:

create or replace package PAC_CURSOR is
  --Creating REF CURSOR type
  type g_cursor is ref cursor;

  --Procedure that return the cursor
  procedure PRO_RETURN_CARS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor); -- Our cursor

end PAC_CURSOR;
/

create or replace package body PAC_CURSOR is
  procedure PRO_RETURN_CARS(
    i_id     in     tbl_car.car_id%type,
    o_cursor in out g_cursor) is

       begin
        --Opening the cursor to return matched rows
        open o_cursor for
          select car_id, company, model, color, hp, price
          from tbl_car
          where car_id = i_id;

  end PRO_RETURN_CARS;

end PAC_CURSOR;

We have Oracle side ready, now we need create Java call

How the cursors are being returned by a procedure, we’ll used a java.sql.CallableStatement instance.

CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}");

The registerOutParameter will obtain oracle.jdbc.OracleTypes.CURSOR type and return a java.sql.ResultSet instance. We can iterate the ResultSet like a common Iterator.
Each row column returned by SELECT will be represented how a map, using correspondent getter. For example, we will call getString(<column name>) method when value of column is a varchar, getDate(<column name>) when is a date and etc.

The complete code will be like this:

//Calling Oracle procedure
CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}");

//Defining type of return
cs.registerOutParameter("o_cursor", OracleTypes.CURSOR);
cs.setLong("i_id", id);

cs.execute();//Running the call

//Retrieving the cursor as ResultSet
ResultSet rs = (ResultSet)cs.getObject("o_cursor");

//Iterating the returned rows
while(rs.next()){
	//Getting column values
	System.out.println("ID: " + rs.getLong("car_id"));
	System.out.println("Manufacturer: " + rs.getString("company"));
	System.out.println("Model: " + rs.getString("model"));
	System.out.println("Color: " + rs.getString("color"));
	System.out.println("HP: " + rs.getString("hp"));
	System.out.println("Price: " + rs.getFloat("price"));
}

In the end you will get any value returned in a SELECT clause.

See ya!


  • Advertisement

  • Advertisement

  • Copyright © 1996-2010 André L. S.. All rights reserved.
    iDream theme by Templates Next | Powered by WordPress