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

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.

Weak Hash Map Example

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

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

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

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

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#

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(); }