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

Select or Insert or Update or Delete rows in Excel using .NET. i.e., CRUD operations in EXCEL using .NET. We can literally use the excel sheet as a database, just the connection string matters. Note: 1. This code cannot be used for password protected excel sheets. 2. Delete does not work. See below for explanations. There might be some other good way to do this, if so, let me know.

 

Insert a row in an excel sheet

 

protected void btn_create_Click(object sender, EventArgs e)

{

    OleDbConnection objConnection;

    OleDbCommand objCommand;

    OleDbParameter objParameter;

 

    // Here HDR= YES represents that header is present

    // If the excelsheet you are dealing with does not have header, you can mention HDR = NO,

    // and while fetching columns you have to mention F1, F2 rather than the column names

    objConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Excel\CRUD_EXCEL.xls;

                                                                                                            Extended Properties=""Excel 8.0;HDR=YES;""");

    objCommand = new OleDbCommand();

 

    objCommand.Connection = objConnection;

    objCommand.CommandType = CommandType.Text;

 

    // The sheet name should be followed by $ sign

    objCommand.CommandText = "INSERT INTO [SHEETNAME$](COLUMN_INT, COLUMN_VARCHAR) VALUES(@COLUMN_INT, @COLUMN_VARCHAR)";

 

    objParameter = new OleDbParameter("@COLUMN_INT", OleDbType.Integer);

    objParameter.Value = 3;

    objCommand.Parameters.Add(objParameter);

 

    objParameter = new OleDbParameter("@COLUMN_VARCHAR", OleDbType.VarChar);

    objParameter.Value = "testing";

    objCommand.Parameters.Add(objParameter);

 

    objConnection.Open();

    objCommand.ExecuteNonQuery();

    objConnection.Close();

}

 

Select rows in an Excel sheet

 

protected void btn_read_Click(object sender, EventArgs e)

{

    OleDbConnection objConnection;

    OleDbCommand objCommand;

    OleDbParameter objParameter;

 

    // Here HDR= YES represents that header is present

    // If the excelsheet you are dealing with does not have header, you can mention HDR = NO,

    //and while fetching columns you have to mention F1, F2 rather than the column names

    objConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Excel\CRUD_EXCEL.xls;

                                                                                                                Extended Properties=""Excel 8.0;HDR=YES;""");

    objCommand = new OleDbCommand();

 

    objCommand.Connection = objConnection;

    objCommand.CommandType = CommandType.Text;

 

    // If the excel sheet you are dealing with does not have header, your query will be SELECT F1, F2 FROM [SHEETNAME$]

    objCommand.CommandText = "SELECT COLUMN_INT as COLUMN1, COLUMN_VARCHAR AS COLUMN2 FROM [SHEETNAME$]";

 

    objDataAdapter = new OleDbDataAdapter();

    objDataSet = new DataSet();

    objDataAdapter.SelectCommand = objCommand;

 

    objConnection.Open();

    objDataAdapter.Fill(objDataSet);

    objConnection.Close();

}

 

Update a row in an excel sheet

 

protected void btn_update_Click(object sender, EventArgs e)

{

    OleDbConnection objConnection;

    OleDbCommand objCommand;

    OleDbParameter objParameter;

 

    // Here HDR= YES represents that header is present

    // If the excelsheet you are dealing with does not have header, you can mention HDR = NO,

    // and while fetching columns you have to mention F1, F2 rather than the column names

    objConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Excel\CRUD_EXCEL.xls;

                                                                                                            Extended Properties=""Excel 8.0;HDR=YES;""");

    objCommand = new OleDbCommand();

 

    objCommand.Connection = objConnection;

    objCommand.CommandType = CommandType.Text;

 

    objCommand.CommandText = "UPDATE [SHEETNAME$] SET COLUMN_VARCHAR =  @COLUMN_VARCHAR WHERE COLUMN_INT = @COLUMN_INT";

 

    objParameter = new OleDbParameter("@COLUMN_VARCHAR", OleDbType.VarChar);

    objParameter.Value = "PQR";

    objCommand.Parameters.Add(objParameter);

 

    objParameter = new OleDbParameter("@COLUMN_INT", OleDbType.Integer);

    objParameter.Value = 3;

    objCommand.Parameters.Add(objParameter);

 

    objConnection.Open();

    objCommand.ExecuteNonQuery();

    objConnection.Close();

}

 

Delete

 

protected void btn_delete_Click(object sender, EventArgs e)

{

        As far as I know ADO does not support Excel row delete

 

        You are more restricted in deleting Excel data than data from a relational data source.

        In a relational database, "row" has no meaning or existence apart from "record";

        in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot:

 

        1. Delete an entire record at once or you receive the following error message:

            Deleting data in a linked table is not supported by this ISAM.

            You can only delete a record by blanking out the contents of each individual field.

        2. Delete the value in a cell containing an Excel formula or you receive the following error message:

            Operation is not allowed in this context.

        3. You cannot delete the empty spreadsheet row(s) in which the deleted data was located,

            and your recordset will continue to display empty records corresponding to these empty rows.

}

 

Thursday, 16 April 2009 18:10:39 (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
.NET

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
# Wednesday, 11 March 2009

After logging in, be sure to visit all the options under Configuration in the Admin Menu Bar above. There are 26 themes to choose from, and you can also create your own.

 

Wednesday, 11 March 2009 07:00:00 (GMT Standard Time, UTC+00:00)  #    Comments [0] -

Navigation
Archive
<2009 May>
SunMonTueWedThuFriSat
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456
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 bulk of humans who will end up affairs Artya's expensive, bound assembly articles are small. These are mostly flush collectors who accept omega replica an aberrant yield on activity and are annihilation but amateur watch lovers. After a lifetime of affairs or seeing bourgeois timepieces, for abounding it can be abundantly auspicious to rolex replica don something weird, wild, or just apparent controversial. We feel that it is our role as a watch media destination not alone to acquaint with those alcove buyers who abide our breitling replica admired readership forth with anybody else, but aswell to bell ross replica accumulate humans abreast of what is new and potentially discussion-worthy in the archival world.