Insert and by pass unique constraint

col1 is the unique index

insert into table1 (col1,col2)
SELECT 'VALUE1','VALUE2' FROM dual where 'VALUE1' not in (select col1 from table1 where col1='VALUE1')

update and select in oracle

UPDATE INCOMING_LEAD
SET (INCOMING_LEAD.DEPARTMENT_CD, INCOMING_LEAD.PROGRAM_CD,
    INCOMING_LEAD.EFFORT_CD, INCOMING_LEAD.OFFER_CD) =
   (SELECT OFFER_INPUT_KEYCODE.DEPARTMENT_CD,
       OFFER_INPUT_KEYCODE.PROGRAM_CD, OFFER_INPUT_KEYCODE.EFFORT_CD,
       OFFER_INPUT_KEYCODE.OFFER_CD
    FROM OFFER_INPUT_KEYCODE
    WHERE OFFER_INPUT_KEYCODE.INPUT_KEYCODE =
          INCOMING_LEAD.PROMO_INITIAL_KEYCODE)

/bin/rm: Argument list too long.Removing files

find . -name 'spam-*' | xargs rm

/bin/tar: Argument list too long

If you find yourself stuck with over 30,000 files in a directory (text files in this example), packing them into a tar file can be tricky. You can get around it with this:

find . -name '*.txt' -print >/tmp/test.manifest
tar -cvzf textfiles.tar.gz --files-from /tmp/test.manifest
find . -name '*.txt' | xargs rm -v

/bin/tar: Argument list too long

If you find yourself stuck with over 30,000 files in a directory (text files in this example), packing them into a tar file can be tricky. You can get around it with this:

find . -name '*.txt' -print >/tmp/test.manifest
tar -cvzf textfiles.tar.gz --files-from /tmp/test.manifest
find . -name '*.txt' | xargs rm -v

String to DateTime

// String to DateTime
String MyString;
MyString = "1999-09-01 21:34 PM";
//MyString = "1999-09-01 21:34 p.m."; //Depends on your regional settings

DateTime MyDateTime;
MyDateTime = new DateTime();
MyDateTime = DateTime.ParseExact(MyString, "yyyy-MM-dd HH:mm tt",
null);

DateTime to String

//DateTime to String
MyDateTime = new DateTime(1999, 09, 01, 21, 34, 00);
String MyString;
MyString = MyDateTime.ToString("yyyy-MM-dd HH:mm tt");

http://msdn2.microsoft.com/en-us/library/w2sa9yss.aspx

This is a simple code in VB.net that send Http POST to a webpage and display the response.

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
Dim myWebReq As HttpWebRequest
Dim myWebResp As HttpWebResponse
Dim encoding As New System.Text.ASCIIEncoding()
Dim postData As String
Dim data() As Byte
Dim sr As StreamReader
Dim sw As StreamWriter

postData = "username=b"
postData += "&"
postData += "password=k"
data = encoding.GetBytes(postData)

myWebReq = WebRequest.Create("http://localhost/authenticate")
myWebReq.Method = "POST"
myWebReq.ContentType = "application/x-www-form-urlencoded"
myWebReq.ContentLength = data.Length

Dim myStream As Stream = myWebReq.GetRequestStream()
myStream.Write(data, 0, data.Length)
myStream.Close()
myWebResp = myWebReq.GetResponse
sr = New StreamReader(myWebResp.GetResponseStream)
Dim strHTML As String = sr.ReadToEnd
MessageBox.Show(strHTML)
End Sub
This code is invoked when user submit a button, easy and simple

I have some simple solutions to validate Malaysian IC / (NewIc) in PL/SQL. This solution may not be the best but at least helps a little.
The format
YYMMDD######

CREATE OR REPLACE FUNCTION is_validic(char_in VARCHAR2) RETURN VARCHAR2 IS
n NUMBER;
incorrect EXCEPTION;
BEGIN
n:=0;
if to_date(substr(char_in,1,6),'YYMMDD') !=to_date(substr(char_in,1,6),'YYMMDD') then
n:=n+1;
end if;

if length(nvl(char_in,'0')) !=12 then
n:=n+1;
end if;

if n=0 then
return 'TRUE';
else
return 'FALSE';
end if;

EXCEPTION
WHEN OTHERS THEN
RETURN 'FALSE';
END is_validic;

Loading data into non-empty table
This information was gethered from
http://www.cise.ufl.edu/~jhammer/classes/Oracle/Oracle_bulk_loader.htm and is useful for others.

LOAD DATAINFILE APPEND INTO TABLE FIELDS TERMINATED BY ''()

If the tables you are loading into already contain data, you have three options:

APPEND
REPLACE
TRUNCATE


APPEND

If data already exists in the table, SQL*Loader appends the new rows to it. If data does not already exist, the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.

REPLACE

With REPLACE, all rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table.

The row deletes cause any delete triggers defined on the table to fire. If DELETE CASCADE has been specified for the table, then the cascaded deletes are carried out.

TRUNCATE

Using this method, SQL*Loader uses the SQL TRUNCATE statement to achieve the best possible performance. For the TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error.

Once the integrity constraints have been disabled, DELETE CASCADE is no longer defined for the table. If the DELETE CASCADE functionality is needed, then the contents of the table must be manually deleted before the load begins.

About SQL*Loader / Oracle Bulk Loader

1. SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads. http://www.orafaq.com/faq/what_is_sql_loader_and_what_is_it_used_for
2. Example

I have a data files of inventories you want to load into your oracle database. my_data.txt

09-NOV-2006|Noke Shirt|68254701000037
09-NOV-2006|Adibas Pant|71565900330059
09-NOV-2006|Umbros Shoe|69840500270016


I want to load the data into this table below

SQL> CREATE TABLE my_inventory
2 (salesdate DATE,
3 product_name VARCHAR(50),
4 barcode VARCHAR(20));

Then the control file control.ctl

LOAD DATA
INFILE 'my_data.txt'
APPEND INTO TABLE my_inventory
FIELDS TERMINATED BY '|'
(salesdate ,
product_name,
barcode
)

To load the data simply invoke sqlldr command from my Oracle bin.
c:/ORACLE_HOME/bin/sqlldr /@ control=control.ctl

I will see below output created during the loading

Commit point reached - logical record count 1
Commit point reached - logical record count 2

Finding the latest / max records
I've just came up with aniother problem of getting a set of results. Finding the latest records, Example:

ID	Mobileno	created
1	+90990	01-FEB-06
2	+90990	02-FEB-06
3	+90991	02-FEB-06
4	+90991	01-FEB-06

The results must be like this , only 1 mobileno with the latest creation date

ID	Mobileno	created
2	+90990	02-FEB-06
3	+90991	02-FEB-06

The answer:
select t.*
from (select a.*,dense_rank() over (partition by mobileno order by created desc) dr from my_table a) t
where dr=1;