learned/learning/to learn RSS 2.0
# Friday, 01 May 2009

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:

  1. We can write an inline query.
  2. We can split the strings and group it as a table type.
  3. 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

Friday, 01 May 2009 18:33:29 (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
Oracle
# Thursday, 16 April 2009

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

)

AS

 

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

Thursday, 16 April 2009 18:08:18 (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
.NET | Oracle
Navigation
Archive
<2017 November>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2017
Gokulnath
Sign In
Statistics
Total Posts: 41
This Year: 0
This Month: 0
This Week: 0
Comments: 47
Themes
Pick a theme:
All Content © 2017, Gokulnath
DasBlog theme 'Business' created by Christoph De Baene (delarou)
The new movement has made a number of changes. First, precise instantaneous jump calendar display tag heuer replica large switching time is scheduled for midnight. The power required for this process will slowly build up within hours. Furthermore, LANGE 1 escapement now available eccentric balance weight balance wheel and homemade free omega uk watch sprung. Means provided in the hand-carved balance wheel splint omega replica underneath, 21,600 vibrations per hour. This table also hublot replica retains the reliable double-barrel, power reserve of 72 hours.