Technical Blog

Oracle: Display Multiple Records in One Row....

2012-02-22T22:39:00.000-08:00

Oracle: Display Multiple Records in One Row

Solutions for “ORA-01427: single-row subquery returns more than one row”

Recently I got the oracle exception “ORA-01427: single-row subquery returns more than one row”, when I tried to execute one of my SQL queries in Oracle which contains another sub query. After googling the root cause and possible ways, I was able to figure out the culprit rows that makes this error. The solutions I found are described below;

Actually, this is also a solution to display multiple records in one row in Oracle…..

Let us first reproduce the above error with simple example; say, I have two tables with the following details;

TableName	ColumnNames
tblStudent	studentid,studentname,studentage
tblCourse	studentid,coursename

The simple select query from the above table’s tblStudent and tblCourse will result;

SQL> select t1.studentid, t1.studentname, t1.studentage from tblStudent t1;

STUDENTID STUDENTNAME STUDENTAGE

--------- ----------- ----------

1 STUDENT:1 30

2 STUDENT:2 30

3 STUDENT:3 30

4 STUDENT:4 30

5 STUDENT:5 30

6 STUDENT:6 30

7 STUDENT:7 30

8 STUDENT:8 30

9 STUDENT:9 30

10 STUDENT:10 30

10 rows selected

SQL> select t2.studentid, t2.coursename from tblCourse t2;

STUDENTID COURSENAME

--------- -----------

1 COURSE:1

1 COURSE:2

1 COURSE:3

1 COURSE:4

3 COURSE:5

3 COURSE:6

3 COURSE:7

3 COURSE:8

3 COURSE:9

3 COURSE:10

10 rows selected

Now if I want to retrieve information of particular “Course” for the students within certain criteria, I will throw the below query.

select studentId as Student_Id,

studentname as Student_Name,

(

select t2.coursename from tblCourse t2

where t2.studentid = studentid

) as Student_Course

from

(

select t1.studentid, t1.studentname, t1.studentage
from tblStudent t1 where t1.studentid <= 5

)

Here we are using the sub-query “select t2.coursename from tblCourse t2 where t2.studentid = studentid” to find out the course details.

As you already know, this sub-query to will result in returning multiple rows. So on executing the above query, the oracle exception “ORA-01427” will be returned.

As a fix, I can use the condition “and rownum = 1” to meet my requirement. But if suppose I want to know/display all the students with their course name from that sub-query… then?????

Yes. It’s possible to display multiple records in row with the following approaches.

Approach 1: Using WM_CAT in Workspace manager

In oracle 10g onwards, WM_SYS schema package uses STRAGG for work space management (http://docs.oracle.com/cd/B19306_01/appdev.102/b14253/long_intro.htm ).

We can use “WM_CAT” utility to concat the student details with course name in the above sub query. That is;

SQL> select wmsys.wm_concat(t2.coursename) from tblCourse t2 ;

WMSYS.WM_CONCAT(T2.COURSENAME)

-------------------------------------------------------------

COURSE:1,COURSE:2,COURSE:3,COURSE:4,COURSE:5,

COURSE:6,COURSE:7,COURSE:8

Approach 2: Using the analytical function ROW_NUMBER()

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1. Refer oracle sitehttp://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm for more details.

This approach is explained below with an example;

SELECT studentid, LTRIM(SYS_CONNECT_BY_PATH(coursename,','))

FROM (
SELECT studentid,coursename,

ROW_NUMBER() OVER (PARTITION BY studentid ORDER BY
coursename ) -1 AS seq

FROM tblCourse
)

WHERE connect_by_isleaf = 1

CONNECT BY seq = PRIOR seq +1 AND studentid = PRIOR studentid

START WITH seq = 1;

Approach 3: Using the AGGREAGE function XMLAgg ()

XMLAgg is an aggregate function provided by oracle in connection with XML related operations. Please refer http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm for more details. The original query can be rewritten with “XMLAgg” in the following way;

SELECT studentid

, RTRIM

( XMLAGG (XMLELEMENT (ele, coursename || ',')

ORDER BY coursename).extract ('//text()')

, ',' ) AS CourseNames

FROM tblCourse

GROUP BY studentid;

With these alternatives we can show multiple results in a single row with oracle.

Weak Hash Map Example

2012-01-24T02:41:00.001-08:00

Please find one simple example of usage of Weak Hash Map.

import java.lang.ref.WeakReference;
import java.util.Iterator;
import java.util.Map;
import java.util.WeakHashMap;

public class WeakHashMapExample {

public static void main(String args[]) {
final Map<String, String> map = new WeakHashMap<String, String>();
map.put(new String("A"), "B");
Runnable runner = new Runnable() {
public void run() {
while (map.containsKey("A")) {
System.out.println("map contains key A");
try { Thread.sleep(500);}
catch (InterruptedException ignored) {}
//System.gc();
}
}
};

Iterator it = map.keySet().iterator();
while (it.hasNext()) {
Object key = it.next();
Object weakValue = map.get(key);
if (weakValue == null) {System.out.println("Value has been garbage-collected");}
else {System.out.println("Get value:" + weakValue);}
}

Thread t = new Thread(runner);
t.start();
try {t.join();}
catch (InterruptedException ignored) {}

}
}

Writing JDBC Applications with MySQL

2012-01-24T02:38:00.001-08:00

Please find one simple example of using JDBC with MySQL in java.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class MySQLAccess {

private Connection connect = null;
private Statement statement = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
//=====================================================
public void ReadDataBase() throws Exception {
try {

// This will load the MySQL driver, each DB has its own driver
Class.forName("com.mysql.jdbc.Driver");

// Setup the connection with the DB
connect = DriverManager.getConnection("jdbc:mysql://localhost/testdb?" + user=user&password=pwd");

// Statements allow to issue SQL queries to the database
statement = connect.createStatement();

// Result set get the result of the SQL query
resultSet = statement.executeQuery("select * from testdb.testtable");

PrintResultSet(resultSet);

// PreparedStatements can use variables and are more efficient
preparedStatement = connect.prepareStatement("
insert into testtable values ( ?, ?, ?, ? , ?, ?)");

// Parameters start with 1
preparedStatement.setString(1, "va1ue1");
preparedStatement.setString(2, "va1ue2");
preparedStatement.setString(3, "value3");
preparedStatement.setDate(4, new java.sql.Date(2011, 01, 01));
preparedStatement.setString(5, "value4");
preparedStatement.executeUpdate();

preparedStatement = connect.prepareStatement("
SELECT col1, col2, col3, col4 from testtable ");
resultSet = preparedStatement.executeQuery();
PrintResultSet(resultSet);

}catch (Exception e) { throw e; }
finally { close();}
}

private void PrintMetaData(ResultSet resultSet) throws SQLException {

// Result set get the result of the SQL query
System.out.println("The columns in the table are: ");
System.out.println("Table: " + resultSet.getMetaData().getTableName(1));

for (int i = 1; i<= resultSet.getMetaData().getColumnCount();i++){
System.out.println(
"Column " + i + " " + resultSet.getMetaData().getColumnName(i));
}
}

//=======================================================================

private void PrintResultSet(ResultSet resultSet) throws SQLException {

// ResultSet is initially before the first data set
while (resultSet.next()) {
// It is possible to get the columns via name
// also possible to get the columns via the column number
// which starts at 1
// e.g. resultSet.getSTring(2);
String val1 = resultSet.getString(1);
String website = resultSet.getString(2);
String summery = resultSet.getString(3);
Date date = resultSet.getDate(4);
String comment = resultSet.getString(5);

System.out.println("val1: " + val1);
System.out.println("val2: " + val2);
System.out.println("val3: " + val3);
System.out.println("val4: " + val4);
}
}

//=======================================================================

// You need to close the result Set
private void close() {

if (resultSet != null) {resultSet.close();}
if (statement != null) {statement.close();}
if (connect != null) {connect.close();}
}

Using a DataReader Against an Oracle Stored Procedure

2012-01-20T19:02:00.001-08:00

Following code snippet uses the DataReader object to retrieve data from an Oracle stored procedure. You can use the DataReader to retrieve a read-only, forward-only stream of data from a database.

Using the DataReader can increase application performance and reduce system overhead because only one row is ever in memory.


using(OracleConnection conn = new OracleConnection
(@"DataSource=source;User Id=username;Password=password;"))
{
          OracleCommand cmd = new OracleCommand();
          cmd.Connection = conn;
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = "StoredProcedureName";
          cmd.Parameters.Add("Result",OracleType.Cursor).Direction =
          ParameterDirection.Output;
          conn.Open();
          OracleDataReader dataReader = cmd.ExecuteReader();
          while(dataReader .Read())
          {
                // do your logic here
          }
          conn.Close();
}

Populating OracleDataReader From REF CURSOR

2012-01-20T19:01:00.000-08:00

Populating

A REF CURSOR data type can be obtained as an OracleDataReader object by calling theExecuteReader method of the OracleCommand object. The output parameter with the OracleDbType property set is bound to OracleDbType.RefCursor. None of the output parameters of type OracleDbType.RefCursor is populated after the ExecuteReader method is invoked.

If there are multiple output REF CURSOR parameters, use the NextResult method of the OracleDataReader object to access the next REF CURSOR data type. The OracleDataReader NextResult method provides sequential access to the REF CURSOR data types; only one REF CURSOR data type can be accessed at a given time.

The order in which OracleDataReader objects are created for the corresponding REF CURSOR data types depends on the order in which the parameters are bound. If a PL/SQL stored function returns a REF CURSOR data type, then it becomes the first OracleDataReader object and all the output REF CURSOR data types follow the order in which the parameters are bound.

The following sample code will illustrate the usage.

using(OracleConnection conn = new OracleConnection(@"DataSource = source;
                                                            User Id = username;Password = password;"))
 {

                                                                      OracleCommand cmd = new OracleCommand();

                                                                      cmd.Connection = conn;

                                                                      cmd.CommandType = CommandType.StoredProcedure;

                                                                      cmd.CommandText = "StoredProcedureName";

                                                                      cmd.Parameters.Add("REF_CURSOR_SAMPLE",
                                                            OracleType.RefCursor).Direction =

                                                                      ParameterDirection.Output;

                                                                      conn.Open();

                                                                      OracleDataReader reader = cmd.ExecuteReader();

                                                                      do

                                                                      {

                                                                          while (oracleDataReader.Read())

                                                                          {

                                                                                 for
                                                            (int i = 0; i < oracleDataReader.FieldCount; i++)

                                                                                 {

                                                                                  
                                                                   Console.WriteLine("Value is {0}", oracleDataReader.GetValue(i));

                                                                                 }

                                                                         }

                                                                    }while (oracleDataReader.NextResult());

                                                                    conn.Close();

                                                            }

ODP.NET : Using Fetch Size against Ref Cursors

2012-01-20T18:58:00.001-08:00

We can increase the performance of our ODP.NET applications by controlling the fetch size of either OracleCommand or OracleDataReader. By controlling the fetch size (i.e. the amount of data fetched during each round-trip to the database) you can dramatically decrease (or increase!) the amount of time taken to fetch the data.

However, if you are working with Ref Cursors rather than "plain" SQL statements, the ability to control the fetch size is not readily available. This is because the RowSize property of an OracleCommand object is set to 0 (zero) when working with PL/SQL code, as would be the case with Ref Cursor data.

However, if you have ever examined the members of the OracleDataReader in the debug window within Visual Studio, you may have noticed that the OracleDataReader does have a particularly non-public member named "m_rowSize" which just happens to represent the row size. If only you could access that value you could then easily set the FetchSize property on the OracleDataReader to a specific (meaningful) value rather than be forced to accept the default of 64KB (65536).

As "m_rowSize" is a Non-Public member, you can’t just reference "m_rowSize" in your application code to retrieve this value. However, using the "reflection" capabilities of the .NET Framework, you can access this value. Once you have done that, you can set the value of the FetchSize property to whatever value is appropriate for your application.

Please note that this technique is not a good coding approach, and, of course, if the "m_rowSize" member name should change, it would break any code using this technique.

The following sample code will illustrate the use of fetchsize in the case of Ref Cursors.

using(OracleConnection conn = new OracleConnection
(@"DataSource=source;User Id=username;Password=password;"))
{
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "StoredProcedureName";
        cmd.Parameters.Add("REF_CURSOR_SAMPLE",OracleType.RefCursor).Direction =  ParameterDirection.Output;
        conn.Open();
        OracleDataReader reader = cmd.ExecuteReader();
        FieldInfo fieldInfo = reader.GetType().GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);
        int cmdRowSize = (int)fieldInfo.GetValue(reader);
        reader.FetchSize = cmdRowSize * noOfRowsExpected;
        while(reader.Read())
        {
             // do your logic here
        }
        conn.Close();
}

How to Change Color of Tab Control in C#

2012-01-20T18:48:00.001-08:00

As there is no properties to change foreground and back ground color of tab control, it can be done by overriding the DrawItem event of the control.
Following are the steps involved to do the same.

1. Set the TabControl's DrawMode to OwnerDrawMode
2. Handle the DrawItem event to draw things.

Please see the following example;

private void tabControl1_DrawItem(object sender, DrawItemEventArgs e)
{
//get tabpage
TabPage tabPages = tabControl1.TabPages[e.Index];
Graphics graphics = e.Graphics;
Brush textBrush = new SolidBrush(Color.Green); //fore color brush
Rectangle tabBounds = tabControl1.GetTabRect(e.Index);
if (e.State == DrawItemState.Selected)
{
           graphics.FillRectangle(Brushes.SkyBlue, e.Bounds); //fill background color
}
else
{
            textBrush = new System.Drawing.SolidBrush(e.ForeColor);
            e.DrawBackground();
}
Font tabFont = new Font("Book Antiqua", 12, FontStyle.Italic | FontStyle.Bold, GraphicsUnit.Pixel);
StringFormat strFormat = new StringFormat();
strFormat.Alignment = StringAlignment.Near;
strFormat.LineAlignment = StringAlignment.Near;
// draw text
graphics.DrawString(tabPages.Text, tabFont, textBrush, tabBounds, new StringFormat(strFormat));
graphics.Dispose();
textBrush.Dispose();
}