Getting Yesterdays or Tomorrows day with shell date command

$ date --date='2 year ago' # past
$ date --date='3 years' # go into future
$ date --date='2 days' # future
$ date --date='1 month ago' # past
$ date --date='2 months' # future

Create functions to join and split strings in SQL

http://articles.techrepublic.com.com/5100-9592-5259821.html

Another way of manipulating large amount of data in oracle (Remove duplicate)

Below example explains, 1 way to remove duplicate data. tmp_data table contains 6millions of record, but with duplicate. Table tmp_duplicate contains all duplicates data that exists in tmp_data.

create or replace procedure delete_duplicate is

cursor c is select sn from tmp_data a where a.sn in (select sn from tmp_duplicate);

type c1_type is table of c%rowtype;

l_c1 c1_type;

begin

open c;

loop

fetch c bulk collect into l_c1 LIMIT 1000;

for i in 1 .. l_c1.count

loop

--Expecting to do the delete

dbms_output.put_line('Delete this ' || l_c1(i).sn);

end loop;

dbms_output.put_line('----------------------');

exit when c%notfound;

end loop;

close c;

end delete_duplicate;

Seperate certain data in file to another file.

Let say, i have many raw files, example data files generated from system. Every day the system generates the raw files, with pipe | seperated. At the end of the month i want to extract certain data from each of the files. Another problem arise is, these files contains more 100K of lines.

List of files in my unix server
mixed_raw_data_20080101.csv
mixed_raw_data_20080102.csv
mixed_raw_data_20080103.csv
mixed_raw_data_20080104.csv
mixed_raw_data_20080105.csv
mixed_raw_data_20080106.csv
.....

Content of the file
Hello|amy
Hello|amy
Hello|amy
Hello|amy
Hello|aby
Hello|aby

e.g:
Extract aby from all the listed files. Manually i can do each files like this
more mixed_raw_data_20080101.csv | grep '|aby' > mixed_raw_data_20080101.csv.extract

But what if i have to extract for the whole month which has 30 files....

for f in mixed_raw_data_*.csv; do more $f | grep '|aby' > $f.extract ; done;

Find all large files on a Linux machine

Finds all files over 20,000KB (roughly 20MB) in size and presents their names and size in a human readable format:

find / -type f -size +20000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'

Change file extensions with bash shell

To change all .htm files in a folder to .html files:

for f in *.htm; do mv $f `basename $f .htm`.html; done;

Ps command with full string in unix?

# ps -ef | grep pr_next
root 4510 1 0 14:16:36 pts/1 0:23 /usr/java/bin/java -classpath /data1/DEVENV/APP/NEXT_INTERFACE/PROGRAM/pr_next_
root 4767 3157 0 15:43:52 pts/2 0:00 grep pr_next

but i need the full path of the command

# /usr/ucb/ps -auxxxwww | grep pr_next | more
root 4510 0.1 1.225023295680 pts/1 S 14:16:36 0:23 /usr/java/bin/java -classpath /data1/DEVENV/APP/NEXT_INTERFACE/PROGRAM/pr_next_sp_inv/etc:/data
1/DEVENV/APP/NEXT_INTERFACE/PROGRAM/pr_next_sp_inv/lib/pr_next_sp_inv.jar com.next.prepaid.sp.inventory.RemoteServer
root 4769 0.0 0.0 1224 888 pts/2 S 15:45:20 0:00 grep pr_next

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;