C# – From C#, How to pass a clob value to oracle stored procedure

asp.netcc#-4.0oracleoracle10g

My Oracle stored procedure is accepting a clob type parameter.

procedure  p_insert_data(xml_string in clob)

From C#, I am trying to call the procedure by passing clob data, which is an XML file.

Following is the way which I tried:

Converting XML to string

 XmlDocument xmlDoc = new XmlDocument();
 xmlDoc.Load(@"D:\Response_min.xml");

 string xml = xmlDoc.OuterXml;

Passing clob data to stored procedure from C#

OracleParameter p_data = new OracleParameter("p_xml_string", OracleDbType.Clob);
p_data.Direction = ParameterDirection.Input;
p_data.Value = xml; //xml is of string type 
dbCommand.Parameters.Add(p_data);
dbCommand.ExecuteNonQuery();

I am getting exception

operation not valid due to current state of object

Instead of xml string, I tried passing bytes[] to clob parameter but still no luck.

I also tried to use XML which has less content.
Anyone has any clue about how I should pass clob value ?

Best Answer

I got it working yesterday , Hope it will help some one.

Clob data we need to pass to stored procedure by creating an OracleClob object

OracleClob clob = new OracleClob(connection);
clob.Write(xmlContent.ToArray(), 0, xmlContent.Length);
clob_Param.Value = clob;

where clob_Param is of type OracleParameter.
After creating the OracleClob object, write xml content to it !!!

Related Solutions

Asp.net-mvc – A potentially dangerous Request.Form value was detected from the client

I think you are attacking it from the wrong angle by trying to encode all posted data.

Note that a "<" could also come from other outside sources, like a database field, a configuration, a file, a feed and so on.

Furthermore, "<" is not inherently dangerous. It's only dangerous in a specific context: when writing strings that haven't been encoded to HTML output (because of XSS).

In other contexts different sub-strings are dangerous, for example, if you write an user-provided URL into a link, the sub-string "javascript:" may be dangerous. The single quote character on the other hand is dangerous when interpolating strings in SQL queries, but perfectly safe if it is a part of a name submitted from a form or read from a database field.

The bottom line is: you can't filter random input for dangerous characters, because any character may be dangerous under the right circumstances. You should encode at the point where some specific characters may become dangerous because they cross into a different sub-language where they have special meaning. When you write a string to HTML, you should encode characters that have special meaning in HTML, using Server.HtmlEncode. If you pass a string to a dynamic SQL statement, you should encode different characters (or better, let the framework do it for you by using prepared statements or the like)..

When you are sure you HTML-encode everywhere you pass strings to HTML, then set ValidateRequest="false" in the <%@ Page ... %> directive in your .aspx file(s).

In .NET 4 you may need to do a little more. Sometimes it's necessary to also add <httpRuntime requestValidationMode="2.0" /> to web.config (reference).

Sql – How to limit the number of rows returned by an Oracle query after ordering

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

Best Answer

Related Solutions

Asp.net-mvc – A potentially dangerous Request.Form value was detected from the client

Sql – How to limit the number of rows returned by an Oracle query after ordering

Related Topic