Oracle - How to use a parameter in a Select query with ‘IN’ condition

Fri, 01 May 2009 17:33:29 GMT

This works in Oracle, hope there is something similar to this in Sql Server too! There might be some other good way to do this, if so, please let me know.

SELECT * FROM TABLE1 WHERE COLUMN1 IN (‘ABC’,’XYZ’,’PQR’)

Say suppose the strings ‘ABC’,’XYZ’ and ’PQR’ need to be grouped as a single string in the frontend and to be passed as a parameter. How can we do it? We cannot write like this –

SELECT * FROM TABLE1 WHERE COLUMN1 IN (IN_PARAMTER)

There are many ways to do it:

We can write an inline query.
We can split the strings and group it as a table type.
We can write dynamic sql.

There is one more way to do it and found it to be easy, so thought of sharing.

Here it is:

SELECT * FROM TABLE1 WHERE COLUMN1 IN
(
SELECT REGEXP_SUBSTR(In_Parameter ,'[^,]+',1,LEVEL)
FROM    DUAL
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (In_Parameter, '[^,]')) + 1
)

It is enough if we pass the parameter as ‘ABC,XYZ,PQR’ instead of “‘ABC’,’XYZ’,’PQR’”.

More about Oracle Regular Expressions - http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10759/functions116.htm

Importing XML data to Oracle database

Thu, 16 Apr 2009 17:08:18 GMT

Here is a method to import/send XML to Oracle and thus we need not call the database repeatedly for mass insert, update etc. DBMS_XMLSTORE has been used here. There might be some other good way to do this, if so, please let me know.

Here you go:

1. Importing Excel from .NET interface to Oracle. Just to get some xml, I have used an excel sheet here, you can replace it with your xml data.

COL1	COL2	COL3
1	Aaa	abc
2	Bbb	pqr
3	Aaa	Abcdef
4	ddd	pqrst

.NET Code

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Excel\\excel.xls;Extended Properties=Excel 8.0;";

OleDbDataAdapter daImportExcel = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

DataSet dsExcelData = new DataSet();

daImportExcel.Fill(dsExcelData, "TABLE_NAME");

OracleParameter parameter = new OracleParameter();

parameter = new OracleParameter("XML_CLOB", OracleDbType.Clob);

parameter.Direction = ParameterDirection.Input;

parameter.Value = dsExcelData.GetXml();

OracleCommand objCommandImport = new OracleCommand();

objCommandImport.Connection = objConnection;

objCommandImport.CommandType = CommandType.StoredProcedure;

objCommandImport.CommandText = "BIMS.XML_PARSER";

objCommandImport.Parameters.Add(parameter);

objCommandImport.ExecuteNonQuery();

Note: I am passing XML as CLOB.

Oracle Procedure

I am inserting the dataset, I mean the XML into table XML_TESTING. Here COL1 is integer and other 2 columns are varchar.

CREATE OR REPLACE PROCEDURE XML_PARSER

(

XML_CLOB IN CLOB

)

INSERT_CONTEXT DBMS_XMLSTORE.CTXTYPE;

ROW_COUNT NUMBER;

BEGIN

INSERT_CONTEXT := DBMS_XMLSTORE.NEWCONTEXT('XML_TESTING');

DBMS_XMLSTORE.SETROWTAG(INSERT_CONTEXT, 'TABLE_NAME');

DBMS_XMLSTORE.CLEARUPDATECOLUMNLIST(INSERT_CONTEXT);

DBMS_XMLSTORE.SETUPDATECOLUMN(INSERT_CONTEXT,'COL1');

DBMS_XMLSTORE.SETUPDATECOLUMN(INSERT_CONTEXT,'COL2');

DBMS_XMLSTORE.SETUPDATECOLUMN(INSERT_CONTEXT,'COL3');

ROW_COUNT := DBMS_XMLSTORE.INSERTXML(INSERT_CONTEXT, XML_CLOB);

DBMS_XMLSTORE.CLOSECONTEXT(INSERT_CONTEXT);

END XML_PARSER;

Note:

Names of the columns that are inserted and of the underlying database table should be same. By default, XML documents are expected to identify rows with the <ROW> tag. This is the same default used by DBMS_XMLGEN when generating XML. This may be overridden by calling the setRowTag function. Here ‘TABLE_NAME'’ has been set as rowtag as it is our table name.

Drawback:

.NET is not allowing to use GetXml in huge datasets. If somebody has a solution for it, please let me know.

You can understand better about DBMS_XMLSTORE from this article - http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10790/xdb_dbmstore.htm#CACEJGEH

Gokulnath's weBLOG - Oracle

Oracle - How to use a parameter in a Select query with ‘IN’ condition

Importing XML data to Oracle database