Invalid Lob Locator from Stored Procedure

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I am trying to return a clob from a stored procedure. Specfically, I
am trying to call Oracle Intermedia's CTX_DOC.MARKUP procedure. I
get the error "ORA-20000: interMedia Text error: ORA-22275: invalid
LOB locator specified." I have not much luck finding helpful
information from OracleClient documentation, Google Groups, or
Metalink. any help would be great.

Thanks,

Sean

OracleConnection conn = new OracleConnection(
ConfigurationSettings.AppSettings.Get("ConnectionString") );
conn.Open();

OracleCommand proc = null;
OracleLob clob = OracleLob.Null;

try
{
proc = new OracleCommand("CTX_DOC.MARKUP" ,conn );
proc.CommandType = CommandType.StoredProcedure;
proc.Parameters.Add( new
OracleParameter("index_name",OracleType.VarChar,50) ).Value =
"IMT_LIB";
proc.Parameters.Add( new
OracleParameter("textkey",OracleType.VarChar,50) ).Value = docid ;
proc.Parameters.Add( new
OracleParameter("text_query",OracleType.VarChar,200) ).Value = query ;
proc.Parameters.Add( new OracleParameter("restab",OracleType.Clob)
).Direction = ParameterDirection.Output;
proc.Parameters.Add( new
OracleParameter("starttag",OracleType.VarChar,50) ).Value = "<SPAN
CLASS='hit'>" ;
proc.Parameters.Add( new
OracleParameter("endtag",OracleType.VarChar,50) ).Value = "</SPAN>" ;

proc.ExecuteNonQuery();

clob = (OracleLob)proc.Parameters["restab"].Value;
proc.Dispose();

}
catch (OracleException oe )
{
Response.Write("<!-- Error: " + oe.Code + " " + oe.Message + "
-->");
}
 
I solved my own problem. I had to copy the CreateTempLob method in
the .NET Oracle Client example. I guess you can't define a lob as a
parameter to the stored procedure, but you can in the wrapper stored
procedure. Not sure why.

proc = new OracleCommand("DECLARE A CLOB; BEGIN
CTX_DOC.MARKUP(:index_name,:textkey,:text_query,A, endtag => :endtag,
starttag => :starttag); :restab := A; END;" ,conn );
proc.Parameters.Add( new
OracleParameter("index_name",OracleType.VarChar,50) ).Value =
"IMT_LIB"; // INDEX_NAME
proc.Parameters.Add( new
OracleParameter("textkey",OracleType.VarChar,50) ).Value = docid
; // TEXTKEY
proc.Parameters.Add( new
OracleParameter("text_query",OracleType.VarChar,200) ).Value = query
; // QUERY_TEXT
proc.Parameters.Add( new
OracleParameter("starttag",OracleType.VarChar,50) ).Value = "<SPAN
CLASS='hit'>" ; // STARTTAG
proc.Parameters.Add( new
OracleParameter("endtag",OracleType.VarChar,50) ).Value = "</SPAN>"
; // ENDTAG
proc.Parameters.Add( new OracleParameter("restab",OracleType.Clob)
).Direction = ParameterDirection.Output;

Response.Write("<!-- Higlighting (inmemory) Execute -->\n");
Response.Write("<!-- " + proc.CommandText + " -->\n");

proc.ExecuteNonQuery();

Response.Write("<!-- Higlighting (inmemory) Get Value -->\n");
clob = (OracleLob)proc.Parameters["restab"].Value;
 
Back
Top