ORA-24338: statement handle not executed tips Oracle Error Tips by Stephanie F.

 

The Oracle docs note this on the ora-24338 error:

 

Error: ORA-24338
Text: statement handle not executed
---------------------------------------------------------------------------
Cause: A fetch was attempted before executing a statement handle.
Action: Execute a statement and then fetch the data.
 

On the Oracle Technology Network Forums, a user posts after having problems with ORA-24338.  The error was occurring after assigning FetchPermission to a new ArrayList, as can be seen in the statement below:

// CLASS CODE
using System;
using System.Collections;
using System.Data;
using Oracle.DataAccess.Client;

namespace SurgAuthenication
{
public class UserAuthenicate
{
// get user permissions for all folders
public ArrayList FetchPermissions(string usr, string srv)
{
string connectString = "";
if ( srv.ToLower() == "demo" )
{
connectString = "Data Source=surgdemo;User ID=surgery;Password=server;pooling=false";
}
else
{
connectString = "Data Source=surgsrv.wrightpatterson.afmc.ds.af.mil;User ID=surgery;Password=server;pooling=false";
}
ArrayList al = new ArrayList();

string sql = "SELECT access_buckslip, " +
"access_messaging, " +
"access_scheduling, " +
"access_supplies, " +
"access_anesthesia, " +
"access_reports, " +
"access_admin " +
"FROM users " +
"WHERE LOWER(username) = LOWER('"+usr+"')";

IDataReader odr = GetDataReader(sql, connectString);

while ( odr.Read() )
{
al.Add(odr["access_buckslip"].ToString());
al.Add(odr["access_messaging"].ToString());
al.Add(odr["access_scheduling"].ToString());
al.Add(odr["access_supplies"].ToString());
al.Add(odr["access_anesthesia"].ToString());
al.Add(odr["access_reports"].ToString());
al.Add(odr["access_admin"].ToString());

}

odr.Close();
odr.Dispose();
return al;
}

private static IDataReader GetDataReader(string sql, string conn)
{
OracleCommand cmd = new OracleCommand();

try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Connection = new OracleConnection(conn);
cmd.Connection.Open();
return cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);
}
catch ( Oracle.DataAccess.Client.OracleException oe )
{
if ( cmd.Connection != null )
{
if ( cmd.Connection.State != ConnectionState.Closed )
{
cmd.Connection.Close();
}
}
throw oe;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
}
}
}
}

// CLIENT CODE
using System;
using System.Collections;
using SurgAuthenication;

namespace Test
{
class MainClass
{
public static void Main(string[] args)
{
string usr;
string conn;
//string folder;
Console.Write("Enter username: ");
usr = Console.ReadLine();
Console.Write("Enter server: ");
conn = Console.ReadLine();
SurgAuthenication.UserAuthenicate au = new UserAuthenicate();
// ERROR OCCURS ON THE NEXT LINE
ArrayList al = new ArrayList(au.FetchPermissions(usr,conn));
IEnumerator en = al.GetEnumerator();

if ( al.Count == 0 )
{
Console.WriteLine("Username does not exist!");
}
else
{
while ( en.MoveNext() )
{
Console.WriteLine(en.Current);
}
}
}
}
}

// TABLE
CREATE TABLE users
(
username varchar2(15) not null,
access_buckslip number(1),
access_messaging number(1),
access_supplies number(1),
access_anesthesia number(1),
access_reports number(1),
access_admin number(1),
CONSTRAINT users_pk PRIMARY KEY(username)
)
TABLESPACES surgtbs

INSERT INTO users VALUES('brockj',3,3,3,3,3,3)
/

• It was suggested that the Create Table Statement should first be checked to make sure it is all correct.
   
• The second suggestion was successful in resolving ORA-24338
  • In the GetDataReader method, you are closing and disposing the command object in the finally block. The data reader is a connected object - can you try your code without closing and disposing the command object?