Sitecore XM Cloud, Ordercloud, CDP, Personalize, ContentHub and Send: April 2011

Friday, April 22, 2011

Microsoft Certified Technology Specialist NET Framework 4 Web Application

I'm glad that I got this certification.

Thursday, April 21, 2011

Compare Date in oracle with date time

select * from TableName c
where c. id = 25326424
and c.time_create= to_date('4/21/2011 3:43:24 PM','mm/dd/yyyy:hh:mi:ssam')

Wednesday, April 20, 2011

Some Important operation in DUAL table

1) SELECT greatest(12,54,2,75,142) from dual;
2) SELECT level as ID
FROM DUAL
CONNECT BY level < 101
3) select 5 * 6 from dual
SELECT USER FROM DUAL
SELECT COUNT(*) FROM DUAL;
INSERT INTO DUAL VALUES ('X');
4) select 'test' from dual;
5) select object_name, owner, object_type from dba_objects where object_name = 'DUAL';

How to generate random number in oracle

SELECT greatest(12,54,2,75,142) from dual;

select output,
count(*)
from (
select round(dbms_random.value(1,9)) output
from dual
connect by level <= 1000000)
group by output
order by 1

1 62423
2 125302
3 125038
4 125207
5 124892
6 124235
7 124832
8 125514
9 62557

SELECT MOD(Round(DBMS_RANDOM.Value(1, 99)), 9) + 1 FROM DUAL

How to get max value in oracle

Sample Query to get Top n Record from oracle

Select * from tableName where FiledName =”” and rownum<=5

The above statement will give u top 5 record
rownum<=5

A few more queries -

Not in

SELECT 'TRUE'
FROM emp

WHERE deptno NOT IN (5,15);

LIKE Operator

SELECT sal
FROM emp

WHERE ename LIKE 'SM%';

Case Sensitivity and Pattern Matching

UPPER(ename) LIKE 'SM%'

Oracle UNION Example

SELECT part, partnum, to_date(null) date_in
FROM orders_list1
UNION
SELECT part, to_null(null), date_in
FROM orders_list2;

Oracle UNION ALL Example
Result - SELECT part
FROM orders_list1
UNION ALL
SELECT part
FROM orders_list2;

MINUS Example

SELECT part
FROM orders_list1
MINUS
SELECT part
FROM orders_list2;

INTERSECT Example
SELECT part
FROM orders_list1
INTERSECT
SELECT part

FROM orders_list2;

The Oracle DUAL table

dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.

rene@ora92> desc dual
Name Null? Type
----------------------- -------- ----------------
DUMMY VARCHAR2(1)

rene@ora92> select * from dual;

D
-
X

The owner of dual is SYS but dual can be accessed by every user.
As dual contains exactly one row (unless someone fiddled with it), it is guaranteed to return exactly one row in select statements. Therefor, dual is the prefered table to select a pseudo column (such as sysdate

select sysdate from dual

Although it is possible to delete the one record, or insert additional records, one really should not do that!.
-------------------------
Note that the outer query references the dummy table called “dual” the dual table is used in Oracle when you need to run SQL that does not logically have a table name. For example, we can select our current user ID from dual and the current date from dual.

select user from dual;

USER
------------------------------
PUBS

select sysdate from dual;

SYSDATE
---------
27-MAY-02

We can also use the dual table for date arithmetic. This last query displays the day of the week for any date in the past 1,000 years. To see the day of the week that you were born, copy this query into your c: directory, add your birth date, and run the query see what day of the week you were born on.

select
to_char(to_date('25-MAR-1956','dd-mon-yyyy'),'day')
from dual;

TO_CHAR(T
---------
sunday

Oracle Date Functions

Date
Current Date CURRENT_DATE
SYSDATE
SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;
Formats
Day Month Year Fill Mode Julian Date
D MM YY FM J
DD MON YYYY
DDTH RR
DAY RRRR

+ AND -
+ +
SELECT SYSDATE + 1 FROM dual;
- -
SELECT SYSDATE - 1 FROM dual;

ADD_MONTHS

Add A Month To A Date ADD_MONTHS(,
SELECT add_months(SYSDATE, 2) FROM dual;

-- but be aware of what it is doing
SELECT add_months(TO_DATE('27-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('28-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('29-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('30-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('31-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('01-FEB-2007'), 1) FROM dual;

CURRENT_DATE

Returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE
col sessiontimezone format a30

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET TIME_ZONE = '-5:0';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET TIME_ZONE = '-7:0';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

DUMP

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value DUMP( [,[,[,]]])

8 Octal
10 Decimal
16 Hexidecimal
17 Single Characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name
col drows format a40

SELECT DUMP(SYSDATE) DROWS FROM dual;

SELECT DUMP(SYSDATE, 8) DROWS FROM dual;

SELECT DUMP(SYSDATE, 16) DROWS FROM dual;

GREATEST

Return the Latest Date GREATEST(, , , ...)
CREATE TABLE t (
datecol1 DATE,
datecol2 DATE,
datecol3 DATE)
PCTFREE 0;

INSERT INTO t VALUES (SYSDATE+23, SYSDATE-10, SYSDATE-24);
INSERT INTO t VALUES (SYSDATE-15, SYSDATE, SYSDATE+15);
INSERT INTO t VALUES (SYSDATE-7, SYSDATE-18, SYSDATE-9);
COMMIT;

SELECT * FROM t;

SELECT GREATEST(datecol1, datecol2, datecol3)
FROM t;

INTERVAL

Interval to adjust date-time INTERVAL ''
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS')
FROM dual;

SELECT TO_CHAR(SYSDATE + INTERVAL '10' MINUTE, 'HH:MI:SS')
FROM dual;

SELECT TO_CHAR(SYSDATE - INTERVAL '10' MINUTE, 'HH:MI:SS')
FROM dual;

LAST_DAY
Returns The Last Date Of A Month LAST_DAY()
SELECT * FROM t;

SELECT LAST_DAY(datecol1) FROM t;

LEAST
Return the Earliest Date LEAST(, , , ...)
SELECT * FROM t;

SELECT LEAST(datecol1, datecol2, datecol3) FROM t;

LENGTH
Returns length in characters LENGTH()
SELECT LENGTH(last_ddl_time) FROM user_objects;

LENGTHB
Returns length in bytes LENGTHB()
SELECT LENGTHB(last_ddl_time) FROM user_objects;
Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available.

MAX
Return the Latest Date MAX()
SELECT * FROM t;

SELECT MAX(datecol1) FROM t;

MIN
Return the Earliest Date MIN()
SELECT * FROM t;

SELECT MIN(datecol1) FROM t;

MONTHS_BETWEEN
Returns The Months Separating Two Dates MONTHS_BETWEEN(, )
SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;

SELECT MONTHS_BETWEEN(SYSDATE-365, SYSDATE+365) FROM dual;

NEW_TIME

Returns the date and time in time zone zone2 when date and time in time zone zone1 are date Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.
SELECT NEW_TIME(TO_DATE('11-10-99 01:23:45',
'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT NEW_TIME(TO_DATE('11-10-99 01:23:45',
'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

NEXT_DAY
Date of next specified date following a date NEXT_DAY(, )

Options are SUN, MON, TUE, WED, THU, FRI, and SAT
SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual;

ROUND
Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day ROUND(, )
SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR
FROM dual;

Spelled Out Using TO_CHAR

Spelled Demo
DDSP HH24SP MISP MMSP SSSP
SELECT TO_CHAR(TO_DATE('10:30:18', 'HH24:MI:SS'), 'HH24SP:MISP:SSSP')
FROM dual;

SELECT TO_CHAR(TO_DATE('01-JAN-2008', 'DD-MON-YYYY'), 'DDSP-MONTH-YYYYSP')
FROM dual;

SELECT TO_CHAR(TO_DATE('01-JAN-2008', 'DD-MM-YYYY'), 'DDSP-MMSP-YYYYSP')
FROM dual;

SELECT TO_CHAR(TO_DATE(sal,'J'), 'JSP')
FROM emp;

SYSDATE
Returns the current date and time set for the operating system on which the database resides SYSDATE
SELECT SYSDATE FROM dual;

TO_DATE

In Oracle/PLSQL, the to_date function converts a string to a date. TO_DATE(, [ format_mask ], [ nls_language ])
string1 is the string that will be converted to a date.

The format_mask parameter is optional. It is the format that will be used to convert string1 to a date.

nls_language is optional. The nls_language parameter sets the default language of the database. This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. This parameter also determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT.

The following table shows options for the format_mask parameter. These parameters can be used in various combinations.

Parameter Explanation
YEAR Year, spelled out alphabetically
YYYY 4-digit year
YYY
YY
Y Last 3, 2, or 1 digit(s) of year.
IYY
IY
I Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
RRRR Accepts a 2-digit year and returns a 4-digit year.
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of the month.
MONTH The name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW The week of the year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W The week of the month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW The week of year (1-52 or 1-53) based on the ISO standard.
D Day of the week (1-7). Sunday is day 1 when nls_territory is set to 'AMERICA' but differs if another nls_territory is set (i.e. 'UNITED KINGDOM' or 'GERMANY' - in these cases Monday is 1.
DAY Name of the day.
DD The day of month (1-31).
DDD The day of year (1-366).
DY Abbreviated name of the day. (Mon, Tue, Wed, etc)
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Number of seconds past midnight (0-86399).
FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF5'.
AM, A.M., PM, or P.M. Meridian indicator
AD or A.D AD indicator
BC or B.C. BC indicator
TZD Daylight savings identifier. For example, 'PST'
TZH Time zone hour.
TZM Time zone minute.
TZR Time zone region.

TRUNC

Convert a date to the date at midnight TRUNC()
CREATE TABLE t (
datecol DATE);

INSERT INTO t (datecol) VALUES (SYSDATE);

INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE));

INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'HH'));

INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'MI'));

COMMIT;

SELECT TO_CHAR(datecol, 'DD-MON-YYYY HH:MI:SS')
FROM t;

Selectively remove part of the date information

Special thanks to Dave Hayes for reminding me of this. TRUNC(, '')
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')
FROM dual;

-- first day of the month
SELECT TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'MON'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'MONTH'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

-- first day of the year
SELECT TO_CHAR(TRUNC(SYSDATE, 'YYYY'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'YEAR'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

Dates in WHERE Clause Joins SELECT SYSDATE FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT SYSDATE FROM dual;

/

/

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

CREATE TABLE t (
datecol DATE);

INSERT INTO t
(datecol)
VALUES
(SYSDATE);

SELECT * FROM t;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT * FROM t;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

SELECT * FROM t;

SELECT SYSDATE FROM dual;

SELECT * FROM t
WHERE datecol = SYSDATE;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT * FROM t;

SELECT SYSDATE FROM dual;

SELECT TRUNC(SYSDATE) FROM dual;

SELECT * FROM t
WHERE TRUNC(datecol) = TRUNC(SYSDATE);

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

VSIZE
Returns The Number Of Bytes Required By A Value VSIZE(e IN DATE) RETURN NUMBER
SELECT VSIZE(SYSDATE) FROM dual;

Date Calculations

Returns A Day A Specified Number Of Days In The Future Skipping Weekends CREATE OR REPLACE FUNCTION business_date (start_date DATE,
Days2Add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
BEGIN
WHILE Counter < Days2Add LOOP
CurDate := CurDate+1;
DayNum := TO_CHAR(CurDate, 'D');

IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;
RETURN start_date + Counter + SkipCntr;
END business_date;
/

Business Date function, above, enhanced by Larry Benton to handle negative values for the days2add parameter. CREATE OR REPLACE FUNCTION business_date (start_date DATE,
days2add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
Direction INTEGER := 1; -- days after start_date
BusinessDays NUMBER := Days2Add;
BEGIN
IF Days2Add < 0 THEN
Direction := - 1; -- days before start_date
BusinessDays := (-1) * BusinessDays;
END IF;

WHILE Counter < BusinessDays LOOP
CurDate := CurDate + Direction;
DayNum := TO_CHAR( CurDate, 'D');

IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;

RETURN start_date + (Direction * (Counter + SkipCntr));
END business_date;
/

Returns The First Day Of A Month CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
vMo VARCHAR2(2);
vYr VARCHAR2(4);
BEGIN
vMo := TO_CHAR(value_in, 'MM');
vYr := TO_CHAR(value_in, 'YYYY');
RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
/

Time Calculations

Returns The Number Of Seconds Between Two Date-Time Values CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
-- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

-- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

-- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

-- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/
Calculating time from seconds

Posted by John K. Hinsdale
12/30/06 to c.d.o.misc SELECT DECODE(FLOOR(999999/86400), 0, '',
FLOOR(999999/86400) || ' day(s), ') ||
TO_CHAR(TO_DATE(MOD(999999, 86400),'SSSSS'), 'HH24:MI:SS') AS elapsed
FROM dual;

Obtain counts per ten minute increment

Posted by Michele Cadot
03/09/08 to c.d.o.misc ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT TRUNC(SYSDATE) + dbms_random.value(0,86400)/86400
FROM dual
CONNECT BY LEVEL <= 10;

WITH data AS (
SELECT TRUNC(SYSDATE)+dbms_random.value(0,86400)/86400 h
FROM dual
CONNECT BY LEVEL <= 10)
SELECT TO_CHAR(h,'DD/MM/YYYY HH24:MI:SS') h, TO_CHAR(TRUNC(h)
+ TRUNC(TO_CHAR(h,'SSSSS')/600)/144, 'DD/MM/YYYY HH24:MI:SS') "10m"
FROM data
ORDER BY h;

How to add month in oracle

SELECT ADD_MONTHS('01-Aug-03', 3) FROM dual;
returns '01-Nov-03'

SELECT ADD_MONTHS('01-Aug-03', -3) FROM dual;
returns '01-May-03'

SELECT ADD_MONTHS('21-Aug-03', -3) FROM dual;
returns '21-May-03'

SELECT ADD_MONTHS('31-Jan-03', 1) FROM dual;
returns '28-Feb-03'

Monday, April 18, 2011

Oracle system table which store that table definatin

select * from user_tab_columns

select * from all_tab_columns

Oracle add new coloum in table with check existing

DECLARE
v_count NUMBER(1) := 0;
BEGIN
SELECT 1
INTO v_count
FROM USER_TAB_COLUMNS
WHERE lower(table_name) = 'cbcomm_dependent_gbl'
AND lower(column_name) = 'smoker';
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE 'alter table cbcomm_dependent_gbl add smoker1 NUMBER(1) NULL';
END;

Thursday, April 14, 2011

Alter Oracle table and add new field

alter table
table_name
add
(
column1_name column1_datatype column1_constraint,
column2_name column2_datatype column2_constraint,
column3_name column3_datatype column3_constraint
);

Here are some examples of Oracle "alter table" syntax to add data columns.

alter table
cust_table
add
cust_sex varchar2(1) NOT NULL;

Her is an example of Oracle "alter table" syntax to add multiple data columns.

ALTER TABLE
cust_table
ADD
(
cust_sex char(1) NOT NULL,
cust_credit_rating number
);

Tuesday, April 5, 2011

ASP change dataset in string and response.write() on browser

Recently, I got a requirement to enhance asp page functionality and improve the performance, Here is a sample code to convert dataset into string - response browser for the debug purpose.


Dim sw As New StringWriter()

dt.DataSet.WriteXml(sw, XmlWriteMode.WriteSchema)

Dim s As String = sw.ToString()

Response.Write("")

Response.Write(s)

Friday, April 1, 2011

validate large XML file

Recently, I got a requirement to validate a large XML file almost 1000000 lines.

Here is a sample code to do that against

       
class SampleXMLValidation

{

 [STAThread]

 static void Main(string[] args)

 {

  // Read the file.

  XmlTextReader Reader = new XmlTextReader("MyFile.xml");

  XmlValidatingReader validater = new XmlValidatingReader(Reader);
  //Schema Validator

  validater.ValidationType = ValidationType.Schema;

  validater.ValidationEventHandler += new ValidationEventHandler

  (ValidationHandler);

  while (validater.Read());

  Console.WriteLine("Validation was successful...No Errors ");

  Console.Read();

 }

 public static void ValidationHandler(object sender,

 ValidationEventArgs args)

 {

  Console.WriteLine("Validation Error");

  Console.WriteLine("\tSeverity:{0}", args.Severity);

  Console.WriteLine("\tMessage :{0}", args.Message);

  Console.Read();

 }

}

}

If we want to validate online, Here is a good link for that