Ajit Kumar Dubey

Split String and Get as Table Type in Oracle

2014-09-22T00:41:00.003-07:00

CREATE OR REPLACE FUNCTION SPLIT_STRING(P_LIST VARCHAR2,
P_DEL VARCHAR2 DEFAULT ',')
RETURN SPLIT_TBL
PIPELINED IS
/*********************************************************
This Function will split the string and return as table type.
Created by :- Ajit Kumar Dubey
Date :- 12-Sep-2014
----------------------------------------------------------
Create a type as given below for this function.

create or replace type split_tbl as table of varchar2(32767);
*********************************************************/
L_IDX PLS_INTEGER;
L_LIST VARCHAR2(32767) := P_LIST;
L_VALUE VARCHAR2(32767);
BEGIN
LOOP
L_IDX := INSTR(L_LIST, P_DEL);
IF L_IDX > 0 THEN
PIPE ROW(SUBSTR(L_LIST, 1, L_IDX - 1));
L_LIST := SUBSTR(L_LIST, L_IDX + LENGTH(P_DEL));

ELSE
PIPE ROW(L_LIST);
EXIT;
END IF;
END LOOP;
RETURN;
END SPLIT_STRING;

Extract Merge or Insert Script of a table

2014-09-21T21:59:00.002-07:00

CREATE OR REPLACE PROCEDURE PROC_GEN_MERGE_SCRIPT(P_TABLENAME VARCHAR2,
P_WHERE_CLAUSE VARCHAR2 DEFAULT '1=1') AS
X_CNT_CLOB NUMBER;
X_INSERT_COLUMN_LIST VARCHAR2(10000);
IS_PRIMARY_KEY_THERE PLS_INTEGER := 1; -- 1 Stand for Yes and 0 for No
/*
----------------------------------------
Create By : Ajit Kumar Dubey
Version : ß (Beta)
Creation Date : 18 Sep 2014
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@
There are two procedure in this Proc
GEN_MERGE_WITH_COB_COL
GEN_MERGE_WITHOUT_CLOB

Table Needed for this Proc is
create table SEEDED_MERGE_SCRIPT
(
TABLE_NAME VARCHAR2(30),
SCRIPT CLOB
);
@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
----------------------------------------
*/

/*
####################################################
*****************************
*************
This Procedure will be used when there is/are clob column(s) in the table;
*************
*****************************
#####################################################
*/

PROCEDURE GEN_MERGE_WITH_COB_COL(P_TABLENAME VARCHAR2,
P_WHERE_CLAUSE VARCHAR2) AS
L_TABLENAME VARCHAR2(30) := NULL;
L_WHERE_CLAUSE CLOB := P_WHERE_CLAUSE;
L_TABLENAME1 CLOB;

ERRPOS NUMBER(5);

-- Cursors defined here -----
CURSOR CUR_CONSTRAINT_COLS IS
SELECT COLUMN_NAME
FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
WHERE UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
AND UCC.TABLE_NAME = L_TABLENAME
ORDER BY POSITION;

CURSOR CUR_COLUMNS IS
SELECT UT.COLUMN_NAME,
NVL(UCCC.CONSTRAINT_TYPE, 'X') CONSTRAINT_TYPE,
SUBSTR(UT.DATA_TYPE, 1, 9) DATATYPE
FROM USER_TAB_COLUMNS UT,
(SELECT UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
WHERE UCC.TABLE_NAME = L_TABLENAME
AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P') UCCC
WHERE UT.COLUMN_NAME = UCCC.COLUMN_NAME(+)
AND UT.TABLE_NAME = L_TABLENAME
ORDER BY COLUMN_ID;

CUR_DATA_SET SYS_REFCURSOR;
CUR_DATA_SET1 SYS_REFCURSOR;
--- Cursor Definition Ends Here -----

RES_DATA_SET CLOB;
L_DATA_SET CLOB := NULL;
L_SQL_STMT CLOB := NULL;
L_SQL_STMT1 CLOB := NULL;
L_DATA_PREP_SQL_STR CLOB := NULL;
L_DATA_PREP_SQL_STR1 CLOB := NULL;
L_COL_LIST CLOB := NULL;
L_JOIN_COND CLOB := NULL;
L_UPD_STR CLOB := NULL;
L_INSRT_VALUES CLOB := NULL;
L_TABLE_EXIST_IND NUMBER(1) := 0;
L_COL_FORMAT CLOB := NULL;
NO_PRIMARY_KEY EXCEPTION;
TABLE_DOES_NOT_EXIST EXCEPTION;
----------------------------------------
X_CLOB_INDEX NUMBER := 0;

TYPE STORE_RELATION_TAB IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
X_STORE_RELATION_TAB STORE_RELATION_TAB;
X_MODEL_CLOB_OUT CLOB;

X_CLOB_COLUMN_LIST VARCHAR2(4000);
X_PK_COLUMN_STRING VARCHAR2(10000);
X_PK_COLUMN_WITH_VALS VARCHAR2(10000);
X_ASSIGN_NULL_TO_CLOB VARCHAR2(10000);
X_ROWID VARCHAR2(100);

BEGIN
L_TABLENAME := UPPER(P_TABLENAME);

BEGIN
SELECT COUNT(1)
INTO L_TABLE_EXIST_IND
FROM USER_TABLES
WHERE TABLE_NAME = L_TABLENAME;

IF L_TABLE_EXIST_IND = 0 THEN
RAISE TABLE_DOES_NOT_EXIST;
END IF;

END;

BEGIN
FOR RES_CONSTRAINT_COLS IN CUR_CONSTRAINT_COLS LOOP
L_JOIN_COND := L_JOIN_COND || 'T.' ||
RES_CONSTRAINT_COLS.COLUMN_NAME || ' = ' || 'S.' ||
RES_CONSTRAINT_COLS.COLUMN_NAME || ' AND ';
END LOOP;
L_JOIN_COND := SUBSTR(L_JOIN_COND, 1, LENGTH(L_JOIN_COND) - 5);

IF L_JOIN_COND IS NULL THEN
--RAISE NO_PRIMARY_KEY;
IS_PRIMARY_KEY_THERE := 0;
END IF;
END;
--$#$#$#-----------------------------------------------
FOR RES_COLUMNS IN CUR_COLUMNS LOOP

IF RES_COLUMNS.DATATYPE = 'TIMESTAMP' THEN
L_COL_FORMAT := '''to_timestamp(''' || '||' || '''''''''' || '||' ||
'to_char(' || RES_COLUMNS.COLUMN_NAME ||
',''dd-mm-yyyy hh24:mi:ss.ff'')' || '||' ||
'''''''''' || '||' || ''',''' || '||' ||
'''''''dd-mm-yyyy hh24:mi:ss.ff''' || ''')''';

ELSIF RES_COLUMNS.DATATYPE = 'DATE' THEN

L_COL_FORMAT := '''to_date(''' || '||' || '''''''''' || '||' ||
'to_char(' || RES_COLUMNS.COLUMN_NAME ||
',''dd-mm-yyyy'')' || '||' || '''''''''' || '||' ||
''',''' || '||' || '''''''dd-mm-yyyy''' || ''')''';
ELSIF RES_COLUMNS.DATATYPE = 'CLOB' THEN

X_CLOB_INDEX := X_CLOB_INDEX + 1;
X_STORE_RELATION_TAB(X_CLOB_INDEX) := RES_COLUMNS.COLUMN_NAME;
L_COL_FORMAT := q'~ q'#X_CLOB_VAR(~' || X_CLOB_INDEX || ')#'''; /*'*/
X_CLOB_COLUMN_LIST := CASE WHEN X_CLOB_COLUMN_LIST IS NULL THEN RES_COLUMNS.COLUMN_NAME ELSE X_CLOB_COLUMN_LIST || ',' || RES_COLUMNS.COLUMN_NAME END;
ELSE
L_COL_FORMAT := '''''''''' || '||' || 'Replace(' ||
RES_COLUMNS.COLUMN_NAME || ',''''''''' ||
',''''''''''''' || ')' || '||' || '''''''''';
END IF;
L_DATA_PREP_SQL_STR1 := L_DATA_PREP_SQL_STR1 || L_COL_FORMAT || '||' ||
''' ' || RES_COLUMNS.COLUMN_NAME || '''' || '||' ||
''',''' || '||';
L_COL_LIST := L_COL_LIST || RES_COLUMNS.COLUMN_NAME || ',';
IF RES_COLUMNS.CONSTRAINT_TYPE <> 'P' THEN
L_UPD_STR := L_UPD_STR || 'T.' || RES_COLUMNS.COLUMN_NAME || ' = ' || 'S.' ||
RES_COLUMNS.COLUMN_NAME || ',' || CHR(10) || ' ';
END IF;

---PK Columns match and populate_its value
IF INSTR(L_JOIN_COND, '.' || RES_COLUMNS.COLUMN_NAME) > 0 THEN
X_PK_COLUMN_STRING := CASE WHEN X_PK_COLUMN_STRING IS NULL THEN '''' || RES_COLUMNS.COLUMN_NAME || '''' || '||''=''||' || L_COL_FORMAT ELSE X_PK_COLUMN_STRING || '||'' AND ''||' || '''' || RES_COLUMNS.COLUMN_NAME || '''' || '||''=''||' || L_COL_FORMAT END;
END IF;

L_INSRT_VALUES := L_INSRT_VALUES || 'S.' ||
RES_COLUMNS.COLUMN_NAME || ',';
X_INSERT_COLUMN_LIST := CASE WHEN X_INSERT_COLUMN_LIST IS NULL THEN RES_COLUMNS.COLUMN_NAME ELSE X_INSERT_COLUMN_LIST || ',' || RES_COLUMNS.COLUMN_NAME END;
END LOOP;
--$#$#$#-----------------------------------------------
ERRPOS := 1;
SELECT REGEXP_REPLACE(L_UPD_STR, CHR(10), '', LENGTH(L_UPD_STR) - 7)
INTO L_UPD_STR
FROM DUAL;
L_DATA_PREP_SQL_STR1 := SUBSTR(L_DATA_PREP_SQL_STR1,
1,
LENGTH(L_DATA_PREP_SQL_STR1) - 7);
L_COL_LIST := SUBSTR(L_COL_LIST, 1, LENGTH(L_COL_LIST) - 1);
L_UPD_STR := SUBSTR(L_UPD_STR, 1, LENGTH(L_UPD_STR) - 6);
L_INSRT_VALUES := SUBSTR(L_INSRT_VALUES,
1,
LENGTH(L_INSRT_VALUES) - 1);
--DBMS_OUTPUT.PUT_LINE(L_DATA_PREP_SQL_STR1);
L_TABLENAME1 := L_TABLENAME;
IF IS_PRIMARY_KEY_THERE = 0 THEN
X_PK_COLUMN_STRING := '''1=1''';
END IF;
L_DATA_PREP_SQL_STR := 'SELECT ' || L_DATA_PREP_SQL_STR1 || ' ,' ||
X_PK_COLUMN_STRING || ', ROWID' || ' FROM ' ||
L_TABLENAME1 || ' where ' || L_WHERE_CLAUSE;
--INSERT INTO DROP_MSG_LOG VALUES (L_DATA_PREP_SQL_STR, SYSDATE);
--COMMIT;
ERRPOS := 2;
--~$~#~$~#---------------------------------------
OPEN CUR_DATA_SET FOR L_DATA_PREP_SQL_STR;
LOOP
FETCH CUR_DATA_SET
INTO RES_DATA_SET, X_PK_COLUMN_WITH_VALS, X_ROWID;
EXIT WHEN CUR_DATA_SET%NOTFOUND;
X_MODEL_CLOB_OUT := NULL;
--$$##--------------------------------------------
DECLARE
X_UNION_STMT VARCHAR2(20000);
X_CLB_VALUE CLOB;
X_INDEX NUMBER;

BEGIN
X_ASSIGN_NULL_TO_CLOB := NULL;
FOR I_INDX IN 1 .. X_STORE_RELATION_TAB.COUNT LOOP
-- X_UNION_STMT := CASE WHEN X_UNION_STMT IS NULL THEN 'SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ' COL1,' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ' || X_PK_COLUMN_WITH_VALS ELSE X_UNION_STMT || 'UNION ALL SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ',' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ' || X_PK_COLUMN_WITH_VALS END;
X_UNION_STMT := CASE WHEN X_UNION_STMT IS NULL THEN 'SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ' COL1,' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ROWID =''' || X_ROWID || '''' ELSE X_UNION_STMT || 'UNION ALL SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ',' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ROWID =''' || X_ROWID || '''' END;
X_ASSIGN_NULL_TO_CLOB := CASE WHEN X_ASSIGN_NULL_TO_CLOB IS NULL THEN 'X_CLOB_VAR(' || I_INDX || ') := NULL; ' ELSE X_ASSIGN_NULL_TO_CLOB || CHR(10) || 'X_CLOB_VAR(' || I_INDX || ') := NULL; ' END;
END LOOP;
X_UNION_STMT := 'Select * from (' || X_UNION_STMT ||
') order by RNM ASC';

OPEN CUR_DATA_SET1 FOR X_UNION_STMT;
LOOP
FETCH CUR_DATA_SET1
INTO X_CLB_VALUE, X_INDEX;
EXIT WHEN CUR_DATA_SET1%NOTFOUND;
--##------------------------------------
DECLARE
X_STMT CLOB;
X_MODEL_QUERY CLOB;
X_LENGTH NUMBER := 0;
X_START_POSITION NUMBER := 1;
X_END_POSITION NUMBER;
X_EXIT_LOOP NUMBER := 0;
BEGIN

X_MODEL_QUERY := X_CLB_VALUE;
X_LENGTH := DBMS_LOB.GETLENGTH(X_MODEL_QUERY);
WHILE X_START_POSITION < X_LENGTH LOOP
X_END_POSITION := CASE WHEN X_START_POSITION + 1600 > X_LENGTH THEN X_LENGTH ELSE X_START_POSITION + 1600 END;
-- DBMS_OUTPUT.PUT_LINE('End Postion :' || X_END_POSITION);
X_STMT := X_STMT || CHR(10) || 'X_CLOB_VAR(' ||
X_INDEX || ') := X_CLOB_VAR(' || X_INDEX ||
')|| q''^' ||
DBMS_LOB.SUBSTR(X_MODEL_QUERY,
1600,
X_START_POSITION) ||
'^'';';
X_START_POSITION := X_END_POSITION;
--DBMS_OUTPUT.PUT_LINE('Start Position :' || X_START_POSITION);
X_EXIT_LOOP := X_EXIT_LOOP + 1;
--DBMS_OUTPUT.PUT_LINE('Loop :' || X_EXIT_LOOP);
EXIT WHEN X_EXIT_LOOP > 5;
END LOOP;
--X_MODEL_CLOB_OUT := NULL;
X_MODEL_CLOB_OUT := X_MODEL_CLOB_OUT || CHR(10) || X_STMT;
--EXIT;
END;
--##------------------------------------
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
NULL;
END;
--$$##--------------------------------------------
---End fetch clob
L_DATA_SET := ' SELECT ' || RES_DATA_SET || ' FROM DUAL' || CHR(10);

SELECT REGEXP_REPLACE(L_DATA_SET, CHR(10), '', LENGTH(L_DATA_SET) - 1)
INTO L_DATA_SET
FROM DUAL;
--L_DATA_SET := SUBSTR(L_DATA_SET, 1, LENGTH(L_DATA_SET) - 8);
ERRPOS := 3;
IF IS_PRIMARY_KEY_THERE = 1 THEN
X_MODEL_CLOB_OUT := CHR(10) || 'DECLARE' || CHR(10) ||
'TYPE CLOB_TAB is TABLE of CLOB INDEX by PLS_INTEGER;' ||
CHR(10) || 'X_CLOB_VAR CLOB_TAB;' || CHR(10) ||
' BEGIN ' || CHR(10) || X_ASSIGN_NULL_TO_CLOB ||
CHR(10) || X_MODEL_CLOB_OUT;
L_SQL_STMT := X_MODEL_CLOB_OUT || CHR(10) || 'MERGE INTO ' ||
L_TABLENAME1 || ' T ' || CHR(10) || 'USING ( ' ||
CHR(10) || L_DATA_SET || CHR(10) || ' ) S' ||
CHR(10) || 'ON (' || L_JOIN_COND || ')' ||
CHR(10) || 'WHEN MATCHED THEN ' || CHR(10) ||
' UPDATE ' || CHR(10) || ' SET ' || L_UPD_STR ||
CHR(10) || 'WHEN NOT MATCHED THEN ' || CHR(10) ||
' INSERT ' || CHR(10) || ' (' || L_COL_LIST || ')' ||
CHR(10) || ' VALUES' || CHR(10) || ' (' ||
L_INSRT_VALUES || ' );' || CHR(10) || 'END;' ||
CHR(10) || '/';
L_SQL_STMT1 := L_SQL_STMT1 || L_SQL_STMT;
ELSE
X_MODEL_CLOB_OUT := CHR(10) || 'DECLARE' || CHR(10) ||
'TYPE CLOB_TAB is TABLE of CLOB INDEX by PLS_INTEGER;' ||
CHR(10) || 'X_CLOB_VAR CLOB_TAB;' || CHR(10) ||
' BEGIN ' || CHR(10) || X_ASSIGN_NULL_TO_CLOB ||
CHR(10) || X_MODEL_CLOB_OUT;
L_SQL_STMT := X_MODEL_CLOB_OUT || CHR(10) || CHR(10) ||
'INSERT INTO ' || L_TABLENAME1 || ' (' ||
X_INSERT_COLUMN_LIST || ') ' || CHR(10) ||
L_DATA_SET || ' ;' || CHR(10) ||
'EXCEPTION WHEN OTHERS THEN NULL;' || CHR(10) ||
'END;' || CHR(10) || '/';
L_SQL_STMT1 := L_SQL_STMT1 || L_SQL_STMT;
END IF;
END LOOP;
L_SQL_STMT1 := L_SQL_STMT1 || CHR(10) || 'COMMIT' || CHR(10) || '/';
--~$~#~$~#---------------------------------------
--DBMS_OUTPUT.put_line(l_sql_stmt);
DELETE FROM SEEDED_MERGE_SCRIPT WHERE TABLE_NAME = L_TABLENAME;
INSERT INTO SEEDED_MERGE_SCRIPT VALUES (L_TABLENAME, L_SQL_STMT1);

ERRPOS := 4;
EXCEPTION
WHEN NO_PRIMARY_KEY THEN
RAISE_APPLICATION_ERROR(-20001,
'No Primary Key found for the Table' ||
L_TABLENAME);

WHEN TABLE_DOES_NOT_EXIST THEN
RAISE_APPLICATION_ERROR(-20002,
'The table does not exist' || L_TABLENAME);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Reached :' || ERRPOS || ' --- ' || SQLERRM ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
/*RAISE_APPLICATION_ERROR(-20003,
'Some Error - ' || L_TABLENAME || ' - ' ||
ERRPOS);*/

END GEN_MERGE_WITH_COB_COL;
/*
##########################################
*****************************
*************
This Procedure will be used when there is no clob column in the table;
*************
*****************************
#########################################
*/
PROCEDURE GEN_MERGE_WITHOUT_CLOB_COL(P_TABLENAME VARCHAR2,
P_WHERE_CLAUSE VARCHAR2) AS

L_TABLENAME VARCHAR2(30) := NULL;
L_WHERE_CLAUSE CLOB := P_WHERE_CLAUSE;
L_TABLENAME1 CLOB;

ERRPOS NUMBER(5);

-- Cursors defined here -----
CURSOR CUR_CONSTRAINT_COLS IS
SELECT COLUMN_NAME
FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
WHERE UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
AND UCC.TABLE_NAME = L_TABLENAME
ORDER BY POSITION;

CURSOR CUR_COLUMNS IS
SELECT UT.COLUMN_NAME,
NVL(UCCC.CONSTRAINT_TYPE, 'X') CONSTRAINT_TYPE,
SUBSTR(UT.DATA_TYPE, 1, 9) DATATYPE
FROM USER_TAB_COLUMNS UT,
(SELECT UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
WHERE UCC.TABLE_NAME = L_TABLENAME
AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P') UCCC
WHERE UT.COLUMN_NAME = UCCC.COLUMN_NAME(+)
AND UT.TABLE_NAME = L_TABLENAME
ORDER BY COLUMN_ID;

CUR_DATA_SET SYS_REFCURSOR;
--- Cursor Definition Ends Here -----

RES_DATA_SET CLOB;
L_DATA_SET CLOB := NULL;
L_SQL_STMT CLOB := NULL;
L_DATA_PREP_SQL_STR CLOB := NULL;
L_DATA_PREP_SQL_STR1 CLOB := NULL;
L_COL_LIST CLOB := NULL;
L_JOIN_COND CLOB := NULL;
L_UPD_STR CLOB := NULL;
L_INSRT_VALUES CLOB := NULL;
L_TABLE_EXIST_IND NUMBER(1) := 0;
L_COL_FORMAT CLOB := NULL;
NO_PRIMARY_KEY EXCEPTION;
TABLE_DOES_NOT_EXIST EXCEPTION;

BEGIN
L_TABLENAME := UPPER(P_TABLENAME);

BEGIN
SELECT COUNT(1)
INTO L_TABLE_EXIST_IND
FROM USER_TABLES
WHERE TABLE_NAME = L_TABLENAME;

IF L_TABLE_EXIST_IND = 0 THEN
RAISE TABLE_DOES_NOT_EXIST;
END IF;

END;

BEGIN
FOR RES_CONSTRAINT_COLS IN CUR_CONSTRAINT_COLS LOOP
L_JOIN_COND := L_JOIN_COND || 'T.' ||
RES_CONSTRAINT_COLS.COLUMN_NAME || ' = ' || 'S.' ||
RES_CONSTRAINT_COLS.COLUMN_NAME || ' AND ';
END LOOP;
L_JOIN_COND := SUBSTR(L_JOIN_COND, 1, LENGTH(L_JOIN_COND) - 5);

IF L_JOIN_COND IS NULL THEN
--RAISE NO_PRIMARY_KEY;
IS_PRIMARY_KEY_THERE := 0;
END IF;
END;

FOR RES_COLUMNS IN CUR_COLUMNS LOOP

IF RES_COLUMNS.DATATYPE = 'TIMESTAMP' THEN
L_COL_FORMAT := '''to_timestamp(''' || '||' || '''''''''' || '||' ||
'to_char(' || RES_COLUMNS.COLUMN_NAME ||
',''dd-mm-yyyy hh24:mi:ss.ff'')' || '||' ||
'''''''''' || '||' || ''',''' || '||' ||
'''''''dd-mm-yyyy hh24:mi:ss.ff''' || ''')''';

ELSIF RES_COLUMNS.DATATYPE = 'DATE' THEN

L_COL_FORMAT := '''to_date(''' || '||' || '''''''''' || '||' ||
'to_char(' || RES_COLUMNS.COLUMN_NAME ||
',''dd-mm-yyyy'')' || '||' || '''''''''' || '||' ||
''',''' || '||' || '''''''dd-mm-yyyy''' || ''')''';
ELSE
L_COL_FORMAT := '''''''''' || '||' || 'Replace(' ||
RES_COLUMNS.COLUMN_NAME || ',''''''''' ||
',''''''''''''' || ')' || '||' || '''''''''';
END IF;
L_DATA_PREP_SQL_STR1 := L_DATA_PREP_SQL_STR1 || L_COL_FORMAT || '||' ||
''' ' || RES_COLUMNS.COLUMN_NAME || '''' || '||' ||
''',''' || '||';
L_COL_LIST := L_COL_LIST || RES_COLUMNS.COLUMN_NAME || ',';
IF RES_COLUMNS.CONSTRAINT_TYPE <> 'P' THEN
L_UPD_STR := L_UPD_STR || 'T.' || RES_COLUMNS.COLUMN_NAME || ' = ' || 'S.' ||
RES_COLUMNS.COLUMN_NAME || ',' || CHR(10) || ' ';
END IF;

L_INSRT_VALUES := L_INSRT_VALUES || 'S.' ||
RES_COLUMNS.COLUMN_NAME || ',';
X_INSERT_COLUMN_LIST := CASE WHEN X_INSERT_COLUMN_LIST IS NULL THEN RES_COLUMNS.COLUMN_NAME ELSE X_INSERT_COLUMN_LIST || ',' || RES_COLUMNS.COLUMN_NAME END;
END LOOP;
ERRPOS := 1;
SELECT REGEXP_REPLACE(L_UPD_STR, CHR(10), '', LENGTH(L_UPD_STR) - 7)
INTO L_UPD_STR
FROM DUAL;
L_DATA_PREP_SQL_STR1 := SUBSTR(L_DATA_PREP_SQL_STR1,
1,
LENGTH(L_DATA_PREP_SQL_STR1) - 7);
L_COL_LIST := SUBSTR(L_COL_LIST, 1, LENGTH(L_COL_LIST) - 1);
L_UPD_STR := SUBSTR(L_UPD_STR, 1, LENGTH(L_UPD_STR) - 6);
L_INSRT_VALUES := SUBSTR(L_INSRT_VALUES,
1,
LENGTH(L_INSRT_VALUES) - 1);
DBMS_OUTPUT.PUT_LINE(L_DATA_PREP_SQL_STR1);
L_TABLENAME1 := L_TABLENAME;
L_DATA_PREP_SQL_STR := 'SELECT ' || L_DATA_PREP_SQL_STR1 || ' FROM ' ||
L_TABLENAME1 || ' where ' || L_WHERE_CLAUSE;
ERRPOS := 2;
OPEN CUR_DATA_SET FOR L_DATA_PREP_SQL_STR;
LOOP
FETCH CUR_DATA_SET
INTO RES_DATA_SET;
EXIT WHEN CUR_DATA_SET%NOTFOUND;
L_DATA_SET := ' SELECT ' || RES_DATA_SET || ' FROM DUAL' || CHR(10);

SELECT REGEXP_REPLACE(L_DATA_SET, CHR(10), '', LENGTH(L_DATA_SET) - 1)
INTO L_DATA_SET
FROM DUAL;
--L_DATA_SET := SUBSTR(L_DATA_SET, 1, LENGTH(L_DATA_SET) - 8);
ERRPOS := 3;
IF IS_PRIMARY_KEY_THERE = 1 THEN
L_SQL_STMT := L_SQL_STMT || CHR(10) || 'MERGE INTO ' ||
L_TABLENAME1 || ' T ' || CHR(10) || 'USING ( ' ||
CHR(10) || L_DATA_SET || CHR(10) || ' ) S' || CHR(10) ||
'ON (' || L_JOIN_COND || ')' || CHR(10) ||
'WHEN MATCHED THEN ' || CHR(10) || ' UPDATE ' ||
CHR(10) || ' SET ' || L_UPD_STR || CHR(10) ||
'WHEN NOT MATCHED THEN ' || CHR(10) || ' INSERT ' ||
CHR(10) || ' (' || L_COL_LIST || ')' || CHR(10) ||
' VALUES' || CHR(10) || ' (' || L_INSRT_VALUES || ' )' ||
CHR(10) || '/';
ELSE
L_SQL_STMT := L_SQL_STMT || CHR(10) || 'BEGIN ' || CHR(10) ||
'INSERT INTO ' || L_TABLENAME1 || ' (' ||
X_INSERT_COLUMN_LIST || ') ' || CHR(10) || L_DATA_SET || ' ;' ||
CHR(10) || 'EXCEPTION WHEN OTHERS THEN NULL;' ||
CHR(10) || 'END;' || CHR(10) || '/';
END IF;
END LOOP;
L_SQL_STMT := L_SQL_STMT || CHR(10) || 'COMMIT' || CHR(10) || '/';
--DBMS_OUTPUT.put_line(l_sql_stmt);
DELETE FROM SEEDED_MERGE_SCRIPT WHERE TABLE_NAME = L_TABLENAME;
INSERT INTO SEEDED_MERGE_SCRIPT VALUES (L_TABLENAME, L_SQL_STMT);

ERRPOS := 4;
EXCEPTION
WHEN NO_PRIMARY_KEY THEN
RAISE_APPLICATION_ERROR(-20001,
'No Primary Key found for the Table' ||
L_TABLENAME);

WHEN TABLE_DOES_NOT_EXIST THEN
RAISE_APPLICATION_ERROR(-20002,
'The table does not exist' || L_TABLENAME);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20003,
'Some Error - ' || L_TABLENAME || ' - ' ||
ERRPOS);

END GEN_MERGE_WITHOUT_CLOB_COL;
/*
##########################################
*****************************
*************
The below block is the wrapper of the procedures.
This block will decide which Procedure to call.
*************
*****************************
############################################
*/
BEGIN
EXECUTE IMMEDIATE 'Select Count(1) from USER_TAB_COLUMNS where Table_name = TRIM(Upper(''' ||
P_TABLENAME || ''')) and DATA_TYPE=''CLOB'''
INTO X_CNT_CLOB;
IF X_CNT_CLOB > 0 THEN
GEN_MERGE_WITH_COB_COL(P_TABLENAME, P_WHERE_CLAUSE);
ELSE
GEN_MERGE_WITHOUT_CLOB_COL(P_TABLENAME, P_WHERE_CLAUSE);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || '--' ||
SQLERRM);
END PROC_GEN_MERGE_SCRIPT;

Export or Download Gmail mails and open it offline using Thunderbird

2014-03-13T09:07:00.001-07:00

Well if you are looking for a way to download email from gmail, and then open it offline, here is the best way to do it.

1. Click https://www.google.com/settings/takeout
2. Click Create an archive.
3. Select the product, like gmail, calendar etc. You can specifically select a label of your Gmail too.
4. Create Archive.
5. You will get an email once archive is over, download it. That's all.

Opening the downloaded mails using Thunderbird.
1. Launch Thunderbird.
2. Left side you will find local folder in Thunderbird.
3. Click local folder and check properties to find the physical location of the folder.
4. Extract the archive file of your gmail, you will get .mbox for mails.
5. Copy that file to the local folder of Thunderbird.
6. Restart Thunderbird and you will find your mail under local folder.

Find three consecutive available seats

2014-01-18T06:37:00.000-08:00

Table name is SEATS_TABLE and following are the rows of the table:

SEAT_NO	BOOKED
A1	N
A2	N
A3	Y
A4	N
A5	N
A6	Y
A7	N
A8	N
A9	N
A10	Y
A11	N
A12	N
A13	Y
A14	N
B1	N
B2	N
B3	N
B4	Y
B5	N
B6	Y
B7	N
B8	N
B9	N
B10	Y
B11	Y
B12	N
B13	N
B14	N

Solution 1 :

with not_booked_seats as (select seat_no,

lead(seat_no, 1, 0) over(order by substr(seat_no, 1, 1), to_number(substr(seat_no, 2))) seat_no_2,

lead(seat_no, 2, 0) over(order by substr(seat_no, 1, 1), to_number(substr(seat_no, 2))) seat_no_3

from Seats_table t1

where t1.booked = 'N')

select * from not_booked_seats

where to_number(substr(seat_no,2))+1= to_number(substr(seat_no_2,2))

and to_number(substr(seat_no,2))+2= to_number(substr(seat_no_3,2))

Solution 2:

select *

from (select t1.seat_no col1, t2.seat_no col2, t3.seat_no col3

from Seats_table t1, Seats_table t2, Seats_table t3

where t1.booked = 'N'

and t2.booked = 'N'

and t3.booked = 'N')

where substr(col1, 2) + 1 = substr(col2, 2)

and substr(col2, 2) + 1 = substr(col3, 2)

and substr(col1,1,1) = substr(col2,1,1)

and substr(col2,1,1) = substr(col3,1,1)

Oracle SQL Interview Questions

2013-12-22T21:27:00.001-08:00

1. How to print Mathematical table using SQL in oracle.

Solution : SELECT &TABLE_OF * ROWNUM AS Print_Table FROM DUAL
CONNECT BY LEVEL <= 10;

2. How to split and print all the characters of a word in rows using SQL in Oracle. Exp - Input : Hello, Output :

H
E
L
L
O

Solution : SELECT substr('&YourWord',ROWNUM,1) FROM DUAL

CONNECT BY LEVEL <= length('&YourWord');

3. How to print all the dates between two given dates.

Solution : SELECT TO_DATE('01-Dec-2013', 'DD-Mon-YYYY') + ROWNUM - 1

FROM DUAL

CONNECT BY LEVEL <= TO_DATE('10-Dec-2013', 'DD-Mon-YYYY') + 1 -

TO_DATE('01-Dec-2013', 'DD-Mon-YYYY')

4.Split comma separated string into rows in Oracle.

Solution : SELECT REGEXP_SUBSTR('Jai,Shri,Ram,Hare,Ram', '[^,]+', 1, LEVEL)

FROM DUAL

CONNECT BY REGEXP_SUBSTR('Jai,Shri,Ram,Hare,Ram', '[^,]+', 1, LEVEL) IS NOT NULL;

Yamaha FZ - Lord of the Street

2012-08-18T08:00:00.001-07:00

Yamaha FZ16 Midnight Limited Edition

It was year 2009, I was in Hyderabad for training, when I saw FZ for the first time. The time i saw it, I got curious to know about it. After some research, I gathered all the information about it. The brand name Yamaha, Its rear thick tyre and its Leopard look made me crazy for it. But that time I could gather information only :) . Days, months and year passed but my craze for this bike never ended. I got job. But I had some other priorities, so could not think of spending 85,000 rupees for bike. Few months back one evening in office some brain attack :) came and I thought I will buy a bike. I discussed my parents, gave all the logic to convince them for a bike. I spent almost half an hour on phone for it. Finally they were ready. But the Question was, Which bike?

I said, 'I have three options Pulsar, Apache or FZ'. But I like FZ.

Then my father told to gather all the information about that bike and advised me to purchase on Dhanteras ; a Holy Day which comes one day before Diwali. My father wanted me to buy Hero Honda Bike (Which is Hero now) or Discover.

I told every one is having Discover so i will not purchase it :). Finally my search started. I liked the FZ look, but i could not invest approx 85K just for looks.

Then I started enquiring with people having these bikes.
TVS Apache : I asked one of my colleague who is having Apache 160. He told me that its good but requires more maintenance. Its engine has some problem as it gets older. And personally i dont have much faith in TVS.

Next Bajaj Pulsar: Well first thing about pulsar is that every 3rd bike is Pulsar, So you will never feel that you have taken new bike. It is a good bike, its successful but it has gone old now.

And then Yamaha FZ: Well this is a new bike, with Yamaha power, having leopard looks. Its a clone of Yamaha FZ1. It's really Lord of Streets.
Good power, fast pick-up, awesome balance and fantastic control of FZ makes it better than any bike of its range. Its thick rear tyre adds more control and balance while riding. Its more comfortable then any other bike.

Well in FZ we have 2 models, one is FZ16 and other is FZS. Among these which one?
If we talk about old fz16, the only demerit was that it was not having kick start. But now in all models we have this. FZS come in dual color. Having some graphics. FZ16 comes in single color. For those graphics it costs around INR 2000 extra. Luckily for Dewali Yamaha lunched Midnight Limited Edition in Golden-Black Color in both the models. I liked the color. And for that edition in FZ and FZS there is no any difference other than the tank sticker which contains one extra S for FZS. So I finally decided to go with it. I paid 85,000 which included 3 years extended warranty, Buzzer, grip cover, Seat Cover, Leg Guard, engine guard, bike cover and Teflon Coating. And then I took my bike out of Showroom. Its start sound made me feel happy. Its smooth acceleration was awesome.

I read full user manual and following all the instruction to knock the engine.

Its really a good experience with Yamaha FZ. I will recommend to go for it if you really want somthing new and best. Its mileage is 35KM/L in city. I have gone to a max speed of 115KM/Hour.Being a small bike in length we can easily move in traffic. I bought my FZ in October 2011. Its 10 months now, I am really loving it.

Arranged Marriage :- A Crucial Decision "Whom to Marry?"

2012-05-01T08:05:00.001-07:00

(Applicable to person belongs to Republic of India and Looking for web Based Marriage)

There are times in a person's life when he needs to take crucial decisions on his own. Marriage is one of them. Believe me, the decision on whom to marry is the most important decision a person will make in his life. After marriage, your wife is the most important person in your life. She can make or break your life. The mere thought of this is very frightening.

Some of the questions that crop up are - a.. What sort of a girl do I marry? b.. Will she adjust in my family? c.. How can I decide on a girl by just meeting her for a few times? d.. When should I get married? e.. This is my life. So, I should choose the girl I marry, but then what if I make a mistake? .. so on and so forth. I will try to address these & many more questions in the following sections. The Nine Rules of Arranged marriage.

Rule 1 - Magic no. 28

In an ideal scenario, a girl goes to college at the age of 18. By the time she graduates, goes for her post graduation and/ or works for 1-2 years, she will be about 23- 24. This means that she has spent about 5 years away from her home. In the 5 years period, she would meet many smart guys at college or during her first few years on job. So, in all probability it would be difficult to find a good girl older than 24 yrs. Secondly, in Indian families there is lot of pressure on the girl's to get married by the time they become 24-25.

Statistics says that there is a generation gap after every 5 years. So, in such scenario, one would prefer to marry a girl who is about 3-4 years younger to you. Thus, working backwards, an ideal age for a guy to get married is by 28. Earlier the marriage, the better it is.

Well, as we all know, in the current market scenario, there will never be stability in our career. So, I believe there is no such thing as, "I will marry when I settle down".

Rule 2 -- Subset of marriage-able girls

At times you hear statements like, "I am not getting the right match, I will look after 3 months, I will find a better match then". Well the truth is otherwise. The subset of unmarried girl looking for a match is fixed. From this subset, there would be girls who would get married & there would be new girls added who would be looking for a match. The net result is that at any given time, the variety & number of marriage-able girls are fixed.

Rule 3 - Competition for girls

Like all other facets of life, there is lot of competition for good girls. So, if you are looking for a girl who is post graduate, done her Engg, is working, very beautiful, smart, from a good family etc. etc, just think again. There are other guys who are also looking for similar girls & probably they are better off than you in terms of career, looks personality etc. Given a choice every guy would like to marry Aishwarya Rai. So, set your expectations accordingly.

Rule 4 -- Understanding girls

You would have met a lot of people during your life. As we all know, its difficult to judge a person based on a few meetings. I am sure you would agree with me that in case of girls it is even more difficult to understand them in a few meetings. I know people who are still trying to understand their wife. ;-).. Understanding your spouse is a life long assignment. So, then how do you select a girl based on a few meeting? This is where you need to take the help of your parents/ friends & latest technologies like email/chat to choose your girl.

Rule 5 - Society expectation

The selection process is tough on every one who is involved in the process. In arranged marriage, involvement of family & society is pretty high. You can't meet a girl 3-4 times & then say no to her. It is bad for her future. So, you should have a good short-listing criterion. Meet only a few girls & be sure what you are looking for. It is for the benefit of everyone involved.

Rule 6 -- Marriage between equals

Unlike love marriage, in arranged marriage you also marry into the girl's family. In arranged marriages, family support plays a major role in ensuring a successful marriage. This is where the compatibility of social status, family values & caste/religion plays a major role. Its important to note that in case there is a perfect match between the two families, the marriage is destined to succeed.

Rule 7 - Know yourself

Unlike love marriage, in arranged marriage you first marry a person & then fall in love. So, it's very important that you do a self-assessment on the kind of person you would love. They say, "Opposite attract", while they also say, "Bird of same feather flock together". So, you take a call on what sort of person you like. Take a pen & paper; write down the kind of attributes you are looking for in a girl. Say, she should ideally have the looks of Sonia, the style of Monica, the voice of Sheena, the patience of Rashmi. You will certainly not find the perfect girl, but then you would have a good idea of what you are looking for. The secret here is to set some minimum criteria for selection. Don't forget rule no.3 here.

Rule 8 -- Girl's Beauty

A girl's looks attract, but then no one wants to end up marrying a dumb It is like buying your bike. When you initially buy it, you are crazy about the looks, but later on you love it for its reliability, fuel economy & comfort level. Similarly, a girl's looks are important, but then it should not be the most important criteria. Later on in life, you will get bored of her looks. It is then that her personality & behavior will make all the difference to your marriage. I am sure your parents will be able to advice you a lot better on this topic.

Rule 9 -- Taking advice

As I have mentioned in the next rule, it's very important that the final decision on whom to marry must necessarily be yours. However, don't do the mistake of isolating yourself from the world while planning your marriage. Discuss with your parents & very close friends on this issue. They are your well wishers. Secondly, in such important matters its necessary that you analyze all possibilities. Remember, I am not suggesting that you follow others' advice, but don't forget to take their advice.

Rule 10 -- Own decision

All said & done, it's your marriage & your life that is at stake. Once you are married, you & your wife are the only persons who will be facing the music. Don't marry a girl just because your parents or friends asked you to do so. After marriage, if things don't work out & you end up saying, "It's because of my friends or my parents that I married you", then your marriage is destined for disaster. If the girl is of your choice, it is you who will be responsible for whatever happens. That's when the marriage works out perfectly. So, ensure that you marry the girl of your choice. How to approach the selection process?

From the day, a person decides to get married; the selection process takes a minimum of 3 months. The whole process needs a lot of patience & commitment. The ideal steps to be followed are:

a.. Definition phase –

Define the minimum criteria for the kind of life partner you are looking for in terms of education, physical appearance, social status, family values, future career plans. Remember the Rule 3 here.

b.. Lead Generation phase –

Place ads in various newspapers, magazines, websites, through friends, family friends, family societies & association etc. You need to exhaust all possible means of getting bio-datas at one go. Remember the Rule 2 here.

c.. Short listing phase –

Based on your selection criteria, short-list the interesting bio-datas. The general process followed for correspondence is as follows:

1. The initiator sends a one page profile of himself/herself.
2. Based on the profile, the receiver sends his/her one page profile along with request for detailed profile, photo, horoscope.

3. The initiator then sends the requested information along with a request for similar information.
4. The receiver send similar information.

5. If the bio-data is selected, it is passed over to the next phase.

A.. Casual interaction phase –

Based on short listing, about 7 to 10 bio-datas are taken forwarded to this phase. The next step to follow here is to exchange email/ chat ids. The guy & the girl then interact for 10 - 15 days to try & judge mutual compatibility through email/chat.

B.. Family interaction phase –

Based on the earlier phase, about 5 leads are taken for consideration in this phase. During this phase, the parents get involved & check the background information about the families to find mutual compatibility.

C.. The dating phase –

Based on the earlier phase about 3 leads are taken forward to this phase. During this phase, the guy & the girl interact by going out alone for 2-3 times. The guy needs to prepare a set of simple questions like who is your favorite star, what are your hobbies? He needs to use his judgment to analyze the girl based on her responses.

D.. The D-day phase –

Finally, the D-day comes when the guy has to select the girl he wants to spend his life with. If the process if followed systematically, there will be no ambiguity in deciding who should be your life partner.

Finally, my dear friends, marriage is all about compromises. In spite of all the planning that you do, there are a lot of uncertainties in a marriage. In fact this is the best part about marriage. Just remember that the person you marry must be of your choice. In such case, there would be no going back for both of you.

A few words of advice: To make your marriage a success; just believe in the age-old virtue, "Never do anything to others that you don't like for yourself".

Don't be Serious, Be sincere.

2012-04-02T19:33:00.000-07:00

Don’t just have career or academic goals. Set goals to give you a balanced, successful life. I use the word balanced before successful. Balanced means ensuring your health, relationships, mental peace are all in good order.

There is no point of getting a promotion on the day of your breakup. There is no fun in driving a car if your back hurts. Shopping is not enjoyable if your mind is full of tensions.

"Life is one of those races in nursery school where you have to run with a marble in a spoon kept in your mouth. If the marble falls, there is no point coming first. Same is with life where health and relationships are the marble. Your striving is only worth it if there is harmony in your life. Else, you may achieve the success, but this spark, this feeling of being excited and alive, will start to die.

One thing about nurturing the spark - don't take life seriously. Life is not meant to be taken seriously, as we are really temporary here. We are like a pre-paid card with limited validity. If we are lucky, we may last another 50 years. And 50 years is just 2,500 weekends. Do we really need to get so worked up?

It's ok , Bunk a few classes, scoring low in couple of papers. Goof up a few interviews, take leave from work, Enjoy with your friends. Fall in love, little fight with your loved ones. We are people not programmed devices.

Don't be Serious, Be sincere.

Speech by Chetan Bhagat.

Airtel Digital TV

2011-11-18T02:34:00.001-08:00

I remember my first mobile, It was reliance CDMA. The year was 2004. I used Reliance CDMA for 2 years. Then I joined B.Tech college and got a chance to use GSM mobile for the first time in 2006. One of my friend suggested me to go with Airtel. I started using Airtel and I could feel the difference. The voice clarity was awesome, connectivity was brilliant and signal strength was good every where. And from the very first day I became Airtel fan and till date I am using Airtel as my primary number. Now I am in Bangalore. I brought a LG LCD TV, for entertainment and mostly for the upcoming ICC world Cup-2011. Then I needed a Disc connection. I inquired a lot about the different service providers, like Tata Sky,Airel, Dish TV, Reliance etc. And finally my faith on Airtel GSM service compelled me to go for Airtel Digital TV. I booked one. Once you bring a Airtel Digital TV, the CC people will ask for Address of Installation. They will ask for pin code to locate their nearest dealer. Then they will book your installation. They will give you an ID. On that ID you have to Recharge your Initial Package and monthly rental onwards. The same process was followed for me but I did one mistake. I was asked for my area pin code, I was not sure about the pin code, and instead of 560068 I gave 500056 which is pin code of Hyderabad. And then my problem started. They booked my installation in Hyderabad. Airtel Technicians started calling me from Hyderabad for installation. That day I understood the real use of pin code :). I spoke to Customer Care to change my Pin code. But something else happened. In their terms, my ID got corrupted (Which is not logical in our terms). Any way, as every CC people say; they also told “we will solve this problem and get back to you soon”. New LCD TV was there in my room waiting for AV input signal. You can imagine my feeling, I spent INR 13,000 and from past 4-5 days only thing I could see on the screen was black display when there was no power or mosquito flying if i switch it on :). Every day I was calling 2-3 times to Airtel CC but there was no progress(from my angle). Then one day I got an Idea. I searched for Sunil Mittal's email. I got few. I don't know which was correct or all where fake. I added few more as sunilmittal@airtel.in, mittal@airtel.in etc from my side. Along with that I kept all 4 zone MD in loop and wrote a mail. Here is the content.

My first Mail to Digital TV CC.

Hello Sir/Mam,

I am not getting what is going on in my life. First My ID was corrupted on
19-Nov-2010(in ur database). Some how you people managed and on 20th
November evening I got massage that DTH will be installed on 23rd of
November. Today On 22nd November I got a call from AP ( I think from
Hyderabad) who was asking for the place where he had to install DTH. I have
given the Correct Address (detailed Address). When I checked with your
Customer Care People, they have given a pin code of Hyderabad instead of
Bangalore. The have given 500056 instead of 560068.
Now when I told to update my profile to Customer care executive, he told me
that he is unable to update my profile.
Now I came on http://www.airtel.in/digitaltv and tried to update my profile,
here also some error is coming that ""We are sorry to update ur profile
right now, please try later""

Now I want to ask a simple question.* Had I done a mistake in choosing Airtel Digital TV??*

I am using Airtel mobile service from past 6 years, thats g8. But what u DTH
people are doing?

Update my profile ASAP.
Here is my full detail

Ajit Kumar Dubey

H-No-

Street-

......... etc etc

And I want my installation as soon as possible, Max by tomorrow

Thanks

Ajit Kumar Dubey

My follow up Mail to all nodal officers including Sunil Mittal

Hello Sir,

I have gone mad after choosing Airtel Digital TV. I dont know who else
but these Bangalore People(Airtel Digital TV) are just playing with
it's(Aitel's) big name and its valuable customers.
You people can check that I took the connection on 22nd of Nov and
made many complain in CC and to nodal officer.
You cant believe that from nodal office of Karnatka, I got two system
generated mail only. No body even care about the customer. Some how
yesterday some technician came and installed the STB and other tolls.
But when they tried to activate the STB, the CC people said that My
Account has been expired. And again the story began. I started calling
the dealer and CC people. Some body told me to wait for 30 minutes and
few convinced me for 2-5 hours. But the problem has not been solved
yet. Its 8 days and still I am unable to use Airtel Digital TV. Sunil
Mittal is spending crore of Indian Rupees for logo and theme songs.
But these people are wasting every thing. I am a supporter of Airtel
but in future I will never advise anyone to go for Airtel DTH. And on
Monday I am going in consumer court.
I am trying to get the email address of Mr. Ajai Puri to send this
mail. Please help me and give me the working mail address of Ajai Puri
and Sunit Mittal.

Thanks with Regards
Ajit Kumar Dubey

You can not believe the effect.

I got call from some senior technical guy. He was on call and created a new Id for me, transfered my money in that account. And told me that today only the whole process will be completed. He called me three times. And finally the whole Installation got complete that day itself. But the account was not active so I could see only channel no. 100 which is Airtel Digital TV channel. I could see how to use remote, how to use the wonderful functionality of the Set up box :). It took 24 hours to get ready to show me the common channels. And it was Saturday. In those 24 hours I saw the user guide so many times that i remembered whole thing.

At the last the common channels started and from that day I am very happy with Airtel Digitl TV again. The picture quality is superb. Set box is small and having good response to remote also. My LCD is not HD, but the picture is so clear that every body get confused thinking that its a HD tv. I took North India Pack of INR 221 monthly. Its having almost all common Hindi channels, News, Entertainment, Music, Sports, Lifestyle, Information, Kids Zone etc. Few months back I changed my room. Had to reallocate my Airtel Digital TV also. I called CC, gave the new address with correct Pin code this time. They charged 300 for relocation. Now I am enjoying it.

I will recommend Airtel Digital TV over any other. I have heard that Tata Sky i also good. But Reliance is bad. Personally I am very happy with Airtel Digital TV also with me LG 22' LCD.

Side Effects Of Getting A Job

2011-06-27T08:32:00.000-07:00

It’s a long time I have not posted anything here. The reason is easily predictable. Last written post (Not copy Pasted) was written when I was not in Job. Now I understand the meaning of “Students Life Is Golden Life”. These days I am not getting time for even myself. I remember the time when I was in College. We had no restriction of time; actually we had no restriction for anything. Whatever we wanted to do, we did, either it's a Class bunk for no reason or for a reason like trip/tour etc. Those days we were having everything, the only guilt was; not supporting our family economically; rather we were taking money from them. But now I am in job, the only thing which I am getting now is money, rest all is gone. Any way it’s the normal life. We born, we start from KG's then SS then College and then a good job.

The other side effect of getting a job (specially a software job) is that your belly will be visible when you wear a T-Shirts.

You will not get time for you hobbies.

So above description states a few basic side effects of getting a job. But that is not all. The biggest one is something else (at least for me as of now).

I stared my career as a trainee/Intern. After few months of getting job my mom started asking me about my marriage. The questions were simple but the answers were very complex (same as our university papers).Qu- When you are planning to marry? What type of girl you want? And many other question related to this topic. That time it was easy for me to answer. My answers were “I am not settled, let me get settled first; Let me get some proper salary package; Then only I will think of my marriage".

But real problem has begun now. My training got completed on 11-May. I was also offered a good package of 3.6L/A. But my mom was silent. But I could feel the storm behind that silence. I knew the reason. My mom and My Dad were planning to come to Bangalore. They had to go to Rameswaram, Tirupathi, Kanyakumari etc. So the ticket was booked for 27-May. Finally they reached Banglore on the expected date. Those days I was very busy in one of my project. So in week days they did not get time to ask much on the marriage topic. And in weekends we were out for Some Location (Tirupathi, Rameswaram etc.). After that my dad returned back. Every day my dad was calling my mom and asking for my choice of girl. So my Mom started asking me.

This problem is the biggest side effect of getting a Job. This Side effect, affected many people. After my dad returned home, there were around 40-50 people reached him for my marriage in just 2 weeks. The people who are close to my family, they are saying ‘we have already seen a good girl for Ajit'. And as people are approaching my dad, he is putting pressure on my mom and as a result she is putting pressure on me.

And at the last whole pressure is on me indirectly.

Now it can be also asked that why I am not telling my choice. Honestly saying I do also not know how to get a girl of my choice. I thought a lot but did not get any proper answer. I know what type of girl I should marry. But I am not getting any criteria to find that girl.

Because the entire requirement is software related.

Here I am listing the features:-

1. She should be down to earth.

2. She should be intelligent (She should really be; not she should think that she is)

3. She should be understanding

4. She should know how to respect every one specially elders.

5. She should be versatile (i.e. she can adjust into my family culture and also different situating which comes unknowingly in the life.)

6. She has a thinking to live as family (My family definition include My Mom-Dad and Sisters not like only Husband-wife and children).

7. Along with this she should be smart (Smart thinking, smart dressing (Indian), and smart by looks ).

8. We are a village cultured family so she should be ready to adopt that also.

These are a few qualities, but the problem is; there is no parameter to judge these qualities. Now, what I will say to my family. Any way I tried to tell these qualities to my mom. lets see what happens.

A Nice & Meaningful Message.....

2010-09-16T01:42:00.000-07:00

Accept the Pain, Future will be Fruitful

Don't ask for a lighter load, but pray god for a stronger back.

Inside the Wave...

2010-09-16T01:27:00.000-07:00

Wonderful Definitions

2010-09-13T03:34:00.000-07:00

School

A place where Parents pay and children play

Life Insurance

A contract that keeps you poor all your life
so that you can die Rich.

Nurse:

A person who wakes you up to give you sleeping pills.

Marriage

It's an agreement in which a man loses his bachelor degree and a woman gains her masters..

Divorce

Future tense of Marriage.

Tears

The hydraulic force by which masculine willpower is defeated by feminine waterpower.

Lecture

An art of transferring information from the notes of the Lecturer to the notes of the students without passing through "the minds of either"

Conference

The confusion of one man multiplied by the number present.

Compromise

The art of dividing a cake in such a way that everybody believes he got the biggest piece

Dictionary

A place where success comes before work

Conference Room

A place where everybody talks, nobody listens and everybody disagrees later on

Father

A banker provided by nature

Criminal

A person no different from the rest
...except that he/she got caught

Boss

Someone who is early when you are late and late when you are early

Politician

One who shakes your hand before elections and
your Confidence after

DOCTOR

A person who holds your ills by pills, and kills you by bills.

Classic

Books, which people praise, but do not read.

Smile

A curve that can set a lot of things straight.

Office

A place where you can relax after your strenuous home life.

Yawn

The only time some married men ever get to open their mouth.

Etc.

A sign to make others believe that you know more than you actually do.

Committee

Individuals who can do nothing individually and sit to decide that nothing can be done together.

Experience

The name men give to their mistakes.

Atom Bomb

An invention to end all inventions.

Philosopher

A fool who torments himself during life, to be wise

CHECK CONSTRAINTS ON SYSDATE IN ORACLE

2010-08-28T02:49:00.001-07:00

RESTRICTIONS ON CHECK CONSTRAINTS

I.We cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.
II.The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.
III.Conditions of check constraints cannot contain the following constructs:

Subqueries and scalar subquery expressions
Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)
Calls to user-defined functions
Dereferencing of REF columns (for example, using the DEREF function)
Nested table columns or attributes
The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
Date constants that are not fully specified

So from the above detail its quite clear that we can not write any comparison with SYSDATE in check constrains..

Now imagine a situation that we are having one table and in that table we want some validation. The requirement is that we are taking Date Of Birth from the user and we want that only that user's details can reside inside our table who is older then 18 years else his insert should not take place.

So for this validation we have to write one trigger to take care of this situation.

/*This trigger will fire on every insert on table name TABLE_NAME and check that the date inserted in DOB_COLUMN_NAME column is greater than the difference of SYSDATE and inserted value by 18 and accordingly either it will allow the data to go inside the table or abort the transaction. :NEW and :OLD are co-relation identifiers used to check for the value inserted, updated or deleted. It will work only in triggers*/

CREATE OR REPLACE TRIGGER PROJECT_NAME_AGE_CHECK_TRIGGER

AFTER INSERT AFTER UPDATE ON TABLE_NAME

FOR EACH ROW

DECLARE

V_AGECHECK EXCEPTION;

BEGIN

IF MONTHS_BETWEEN(SYSDATE,:NEW.DOB_COLUMN_NAME)/12 < 18 THEN

RAISE V_AGECHECK;

END IF;

EXCEPTION

WHEN V_AGECHECK THEN

RAISE_APPLICATION_ERROR(-20001,'YOU ARE MINOR') ;

END;

A Glass Jar And Two Cups Of Tea

2010-08-28T00:11:00.000-07:00

Glass Jar And Two Cups Of Tea

A life reforming Story

A time comes in life when we desire to do everything very fast ,to get everything as soon as possible, and then twenty-four hours a day seems less to have that, at that time we should remember this life reforming story, "A glass jar and two cups of tea".
A professor of philosophy came in the classroom and told the students that he is going to teach an important lesson of life.
He brought along a big glass jar and kept it on the table and then he began putting table tennis balls in the jar until the jar was full enough, not to take any more balls.
He asked the students, “Is the jar completely filled?”
Students shouted, “Yes.”
Then Professor started putting small stone fillings in the jar. He shook the jar and a good amount of stone filling were adjusted in the jar.
The Professor asked again, “Is the jar completely filled now?”
Students shouted once again, ”Yes.”
Then the Professor started pouring sand slowly in that jar.
Sand made its way into the jar where it was possible.
Now students laughed at their innocence.
Professor asked again “Now this jar is completely filled, isn’t it?”
Students said,” Yes, Now it is full.”
Then professor took two cup of tea and poured into the jar.The tea made its place in the jar and was soaked by the sand.
Now Professor started explaining everything in a serious voice.
Consider this glass jar as your life. The most important things are table tennis balls i.e. God, family, kids, friends, Health and hobbies.
Small stone fillings symbolises your job, car, house etc.
Sand means some more small useless things like estrangement, misunderstandings, quarrels etc.
Now if you fill the glass jar with sand first then there will be no space left for balls and stone fillings. Or if you put stone fillings first then there will be no room for table tennis ball, but still that could hold sands.
Same philosophy applies to life.
If you are going behind those little things and destroying you energy on them then you will not have much time to spend on the necessary and important things.
What is important for the happiness of soul? First decide that. Play with your children, put water in the garden, and go for morning walk with wife.Throw out all useless stuff from home; go for medical check-up regularly.Worry about table tennis balls first, that is most important. First decide what is important? Rest all is send only.
Students were listening attentively.
Suddenly a student asked, “Sir, but you have not told anything about those two cups of tea." What is that meant for?
Professor smiled and said,” I was expecting this question for a long time.”
The answer is, “no matter how busy, complete and satisfied your life seems, but there must be place for sharing two cups of tea with your special friend.
So consider this story seriously and try to implement it in your life to make your life happy and peaceful.

Armstrong number also called Narcissistic numbers.

2010-08-20T21:29:00.000-07:00

Definition:

Armstrong numbers are the sum of their own digits to the power of the number of digits.

In other words

An Armstrong number is an n-digit base b number such that the sum of its (base b) digits raised to the power n is the number itself.

And according to wikipedia

In recreational number theory, a narcissistic number (also known as a pluperfect digital invariant (PPDI), an Armstrong number or a plus perfect number) is a number that is the sum of its own digits each raised to the power of the number of digits. This definition depends on the base b of the number system used, e.g. b = 10 for the decimal system or b = 2 for the binary system or

For Example:

There are just four numbers, after unity, which are the sums of the cubes of their digits:

153 = 1 3 + 5 3 + 3 3

370 = 3 3 + 7 3 + 0 3

371 = 3 3 + 7 3 + 1 3

407 = 4 3 + 0 3 + 7 3

1.Program to check for Armstrong Number

#include

//Prototype of all the function being used in the program

int number_of_digits(int give_number);
int check_armstrong(int given_number,int number_of_digits);
int power(int temp_var,int number_of_digits);

//Main function starts

int main()
{
int give_number,no_of_digits,flag;
printf("\nEnter the number you want to check for Armstrong\n");
scanf("%d",&give_number);
no_of_digits=number_of_digits(give_number);
flag=check_armstrong(give_number,no_of_digits);
flag?printf("\n%d is Armstron\n",give_number):printf("\n%d is not Armstrong number\n",give_number);
return 0;

}

//Function to check how many degits are there in the given number

int number_of_digits(int given_number)
{
int count=0,temp;
while(given_number>0)
{
temp=given_number%10;
given_number=given_number/10;
count++;
}
return count;
}

//Function to check that the number is Armstrong or not

int check_armstrong(int given_number,int number_of_digits)
{
int temp_var,clone_number,regenerate_number=0;
clone_number = given_number;
while(clone_number>0)
{
temp_var=clone_number%10;
regenerate_number=regenerate_number + power(temp_var,number_of_digits);
clone_number=clone_number/10;
}
if(regenerate_number==given_number)
return 1;
else
return 0;
}

//Function the calculate the power same as pow() function

int power(int temp_var,int number_of_digits)
{
int cal_power=1,i;
for(i=0;i
cal_power=cal_power*temp_var;
return cal_power;
}

2.Program to find Armstrong Number in a given range

#include

//Prototype of all the function being used in the program

int number_of_digits(long int give_number);
int check_armstrong(long int given_number,long int number_of_digits);
int power(long int temp_var,long int number_of_digits);

//Main function starts

int main()
{
long int give_number,no_of_digits,flag;
printf("\n Enter the number you want to check\n");
for(give_number=1;give_number<9999999;give_number++)
{
no_of_digits=number_of_digits(give_number);
flag=check_armstrong(give_number,no_of_digits);
flag?printf("\nNumber %ld is Armstron\n",give_number):printf("");
return 0;
}
}

//Function to check how many degits are there in the given number

int number_of_digits(long int given_number)
{
long int count=0,temp;
while(given_number>0)
{
temp=given_number%10;
given_number=given_number/10;
count++;
}
return count;
}

//Function to check that the number is Armstrong or not

int check_armstrong(long int given_number,long int number_of_digits)
{
long int temp_var,clone_number,regenerate_number=0;
clone_number = given_number;
while(clone_number>0)
{
temp_var=clone_number%10;
regenerate_number=regenerate_number + power(temp_var,number_of_digits);
clone_number=clone_number/10;
}
if(regenerate_number==given_number)
return 1;
else
return 0;
}

//Function the calculate the power same as pow() function

int power(long int temp_var,long int number_of_digits)
{
long int cal_power=1,i;
for(i=0;i
cal_power=cal_power*temp_var;
return cal_power;
}

Note: On compiling if you get any error than check it may that html has converted the lass than and greater than symbol as its own tags and also please do proper formatting. This program has been compiled and checked on GCC.

Bangalore's first experiance "Close save in struggle with Life and Dengue Fever"

2010-08-07T00:56:00.000-07:00

Early morning around 7 AM I reached platform number 1 of Bangalore City junction on 2nd July 2010. With lots of hope and with unlimited expectations; I entered this silicon city. From last 4 months I was in Hyderabad doing courses on Oracle PL/SQL and Java. Now it was the time to check my knowledge on industry standards. I took an auto rickshaw and reached one of my old friends, Manish Gupta’s flat. I was not much aware of this city. One of my friends was here from last six months. He guided me and told me to join a consultant. The name of the consultant is LuminUs. It is located in Malleswaram near 5^th cross. They charged me INR. 560 as their fee and assured me that they will give me chances for many interviews on java and PL/SQL. According to their rule I will have to pay half of my first month salary if I get a job through them.
They gave me first call on Monday 05-July. The name of the company was Xora Technologies. First LuminUs people use to take a written test and those who are selected, are sent to the company for further processes. I was selected by them. They gave me the venue and told me to go there on 8^th July. On Monday it self I got a call from another consultant named S V Consultancy. The caller was a lady and introduced herself as Vinutha. She got my resume from some job portal site (monster, Times job or Naukri.com). She confirmed my profile and told me that my profile is looking suitable for one of their client named Tom Bell Softech Ltd. They are looking for PL/SQL developer. She texted me the address of the company. She told me to reach there on Tuesday at 10 AM. That day was the best day because I wanted to make my career in PL/SQL only, and this opening was for PL/SQL and fresher also. I prepared well and reached the venue on time.
There I met Bells Softech HR named Pratibha. She asked about me and my family, she also asked 2 SQL query. She was not aware about the answers of those queries. She asked me “Write a query to get the salary of employee”. I smiled on her question and asked for the structure of the table. She did not understand. I asked her to give the name of the table and column, she unknowingly said “Salary table and salary column”. I wrote “SELECT Salary FROM Salary”, and passed the sheet to Prathibha. I guessed that she was not satisfied with my 4 words answer. I asked you want a SQL query or PL/SQL block. She again did a childish mistake and said “PL/SQL code”. I started my code with DECLARE and ended with END. She was happy now; it was almost all 7-8 lines of code. Then she asked me “If you will be asked to rate yourself in PL/SQL out of 10, what will be your rating about yourself?” I answered quietly “Mam its 9/10 and I can guarantee that no fresher will stand in front of me in PL/SQL". Her mouth was wide open for few seconds on my words and confidence. She said “Oh is it? Good!!” She took me to the GM. The GM was a wonderful person, Mr. Sadanand. He started with the common question as “Mr. Ajit, Tell me something about youself”. I vomited all what I know about myself. Then he asked the same question what Prathibha asked me, “Write a query to get the salary of employees”. I wrote the same 4 word query. Now he extended, “Get the second highest salary from employee table”. It was good question. But this is a common question asked in every interview. I had already practiced this question in 5 different ways. I gave one of the best answers among them. He was happy now and further he did not ask any question. He told me that you are good and you have been selected from my side, but you have to work for one of my client and you have to go there for the further rounds. The name of their client was MetricStream Inc. He said that you will get the confirmation by today evening or by tomorrow along with the venue. I was very happy and returned home. This was my first interview for job and I passed this round. I waited for the confirmation mail that evening.
Next day I got a call from LuminUs for some Core java opening in IPASS software. I went to LuminUs and took the test. I returned back to home. Still there was no confirmation mail or message from Tom Bells for my PL/SQL interview. I called them. They told me to wait for one more day. The interview was on Friday 9^th July.
Now it was 8^th july. I had to go to Xora. I went to Xora. There was 4 rounds. 1^st was aptitude round. We were given 30 question and 30 minutes. I did my best. They evaluated and according to their criteria they called few students name. My name was not there. I thought that I was not selected. But I was sure that I circled 25+ answers right. The called people went to them. They said, “all the best for future and Tata Bye Bye.” I understood that they were not selected and I qualified the first round. We were 12 now out of 25. The second round was technical and programing round. We were given a question paper and few sheets. The time limit was 40 minutes. 8 programs were there. No objective question. 1 was from database, 2 from core java, 5 from J2EE. As I saw the question paper I was confirmed about me. I did the database SQL question correctly which was encapsulating one join, one inline view, 2 sub-queries and few column aliases in it. Then I reached core java question. Actually they had not written in which language I had to write the program. I knew that this was a test for java developer, but I solved those questions in C. I felt easy to write them in C rather than in Core java. And rest J2EE question was not known to me. They collected the answer sheet on time. And told us to wait because it lunch break time. They told us to come back in 1 hour. I went and have my lunch. When we returned they told us that some clients have come so the panel is busy, we will evaluate it tomorrow and inform the shortlisted candidates on phone. I have not got any call even today. I knew this; because I do not know J2EE.
I was checking my mail for MetricStream’s interview confirmation. But there was no mail in my mail box. I was little bit disappointed and went outside to get some fresh air. I went to a nearby park. As I entered the park I started checking my mail once again at the same time I got a call from 080 numbers on my other phone. I wished that it was a call for that PL/SQL interview. At the same time my mail opened and there was a new mail. My eyes were twinkling to see the confirmation and ears were eager to hear the confirmation news. I picked the phone and opened the mail. The lady was Vinutha and the mail was from Prathibha. She asked me whether I got the mail or not. I replied positively and assured that I will reach the venue at exact time. The venue was 1 hours away from that place. And I had to reach there in the morning at 8:30 AM. As I saw the address, I realized that I have heard the name somewhere. Then I remembered that Sachin a friend of my friend live in that area only. I called him and asked about the place. He told that it’s at walking distance from his flat. I said that, “I am coming right now; I have an interview tomorrow early morning”. He said “Ok! Come then”. I packed all my basic goods and took a bus. I reached there at around 9 PM. I revised the whole PL/SQL and basic of java also. Because Vinutha told me that there will be java also. I was on time, there were 18 people waiting for the same from Tom Bell Softtech. We were told that there will be a written objective type test. Those who will qualify will be called for the next round. There were 12 general science and general Knowledge question, 12 from oracle PL/SQL, 10 from java and 10 from Aptitude. The qualifying marks were 5-7-5-5 respectively. No negative marking was there. The HR person was Maya. She distributed the question paper and an answer sheet and 30 minutes were given. My target was to first get the cut-off marks. I struggled most in first section because I am weak in general Knowledge. But I did somehow and time was up. I was happy because I thought I did well in rest 3 sections. I was little bit confused about the first section. As we were told by Tom Bells all the rounds was going to be completed on the same day. And joining date was Monday 12^th-July. But Maya told us “the panel is busy with some clients so we will inform the shortlisted candidate of phone today itself and next round will be scheduled on Monday”. I came back little bit sad, because I wanted to be part of company as soon as possible.
I was in the way to home and I got message from LuminUs that I have been sortlisted for IPASS. So I have to report LuminUs to collect the hall ticket. Actually every time LuminUs people use to take a signature on a bond paper that If I will be shortlisted I will give them the half of my first month salary. I took a bus and went to LuminUs. It was almost 2 hours journey due to traffic. I went there signed on the bond and they put a stamp of LuminUs on the top of my resume which was my hall ticket for further rounds in IPASS which was going to be scheduled on Monday 12^th july . I thought that it will be better if I will stay with Sachin. Because it was nearer to MetricStram. I took all my goods from Manish’s house and returned back to Maruthinagar (Sachin’s flat). But the tension was that, it was going to be 9 PM and I did not get any call from MetricStream. I was very tired and little bit tensed and sad. I took my dinner and went to bed early.
In the morning I called Vinutha. I asked about my result. I told her that Maya told us that we will get our result yesterday itself and I did not get any call. I told her to confirm whether I have been shortlisted or not. She clarified that the result has not been announced yet. You will get your result on Monday and on a short notice you have to go to attend the interview on the same day. I was in dilemma; I was having two Interviews on the same day. IPASS interview was at 3 PM on Monday. And I was sure that they will call me around 10 and tell me to attend the interview around 2 pm. I described the situation to my LuminUs HR. I told her to reschedule my interview. She told me to inform the situation on Monday, she will try to reschedule the interview at IPASS. Sunday was very confusing day for me. Every alternate day or even regularly LuminUs was calling me which was a 2 hours bus journey from Sachin’s room. And I was still hoping that I will be selected in MetricStream. I was confused and I went to Malleswaram for a room of my own. I wanted to be settled. I thought that I will take a room and then I will search for job. I went with one of my friends named Ladan. He is having Passion Plus. So it was easy to go on bike. It was less time consuming. I saw a room. I told the landlord that I will confirm him on Monday about the room. I wanted to wait one more day for MetricSream.
It was Monday. Early morning I got a message that my Interview for IPASS has been rescheduled on Tuesday at the same time. I was planning to go to one more consultant for registration. I was ready and about to leave. At around 10:30 I got a call from 080 series number. I remembered God and picked the phone. Surprisingly it was Prathibha. I took a long breath of relief. She told me that I have been shortlisted for the next round at MetricStream. She gave me two options 2:30 or 3 PM that day itself for interview. I said 3. She confirmed again and I said “Done”. The phone was disconnected now. I canceled all my programs and took the book and started reading PL/SQL again. After all my preparation I reached MetricStream at 2:50 PM. Two more guys were there. A guy whose interview time was 2:30 PM was still waiting for the interviewer. We all started discussing the topics. At 3:30 a young unmarried lady came and asked, “Who is Jainendra?” That was 2:30 guy. She took him and went. Now we two were left. We were discussing the topic and waiting for our turn. The minute hand of the clock moved 180 degree but there was no news of that 2:30 gentleman. When it was about 270 degree, the guard sent us on the third floor. We went there and still waiting. The minute hand completed 360, but no news. 10 more minutes passed and the chamber opened. The guy came out. I asked what was going on for so many hours. He said that she started from sorting searching algorithm and discussed almost all data structure, she also discussed on the project from half an hour. She was giving some situations for which she was asking to write code sometimes in java, sometimes in PL/SQL. I finalized that after him it’s my turn to be ******. I waited for another 15 minutes but no one called my name for interview. After few more minutes Maya came. She told “it’s late today, so those who have not been interviewed today will be given slots tomorrow and I will call and give you slots”. She said sorry and bye bye. I was happy that I was not interviewed by that lady. I was just praying from God that please give me some other interviewer. In the evening around 7 PM I got a call from Maya. She told me that you time slot is 9:30 and interviewer name is (I kept my hand on my hearts because if she was going to take the name of any lady it may lead to a heart attack) Mr. Abdul Raw. I said ok! Done! And bye. I revised the whole PL/SQL once again at night.
Next day in the morning I woke up at 5 AM and revised my project and JAVA also. I left my room at 9 AM. Took my breakfast and reached MetricStream. Abdul Raw was not there. Maya came after half an hour. She told me that Abdul left the company at 3 AM last night so he will be little bit late, sorry for in convenience. She asked me for coffee or tea. I denied. I opened my PL/SQL book and started reading once again. The minute hand of the watch made a couple of revolution but there was no trace of Abdul Raw. Now it was 1 PM, the guard sent me to second floor. I waited for next 10-15 minutes. A young guy came and introduced himself as Abdul Raw and asked who is Ajit. I stood up, shook hand. He showed me the way to come along with him. Now I was thinking that the next few minutes are going to be very important for me. All the cabins were engaged. He managed one and started with the as usual question “Mr. Ajit tell me something about yourself”. Actually he was having two resumes in his hand, he was talking to me and watching the resume of the other guy. In vomiting about myself I said I am 2010 pass out. He said “Here its 2009 in your resume”. I pointed my resume and said “Sir, this is my resume”. Then he said sorry and started reading my resume. When I completed my boasting about myself, he was on the second page of my resume which is my projects detail page. And he fired his first question from project.

Qu 1. Tell few tables from your project.

I had gone through the table name in the morning so I gave him almost all the name of table from my project. I know that if he will fire more questions from project then I am not having project proof jacket with me. Fortunately he went to the last page and instructed me to sign and write the current date. Then he asked

Qu 2. What are Codd’s rules?

Fortunately few days ago I copied all the Codd’s rule from Wikipedia.org and by hearted it. I started with first Codd’s rule and I don’t know how many I gave, I used ‘next’ keyword in place of 'numberings’. I started with one two three then converted it into next next. At last I gave the 0^th Codd rule also, referencing Wikipedia.

Qu 3. What makes difference between DBMS and RDBMS.

I answered with the reference of Codd’s rules only. He said to describe. I was not fully aware but I described the last 6 Codd’s rules saying that a DBMS supports 4 Codd’s rules. If it is supporting more than that it will be called as RDBMS.

Qu 4.He asked me the set operators in oracle.

It was a googly ball for me and I smashed it out of the boundary.

Qu 5. Write one example which uses set operator, one which uses sub query, one uses alieses, one to display today’s date, one to display 2 months later date, one to display 2 months earlier day?

He gave these questions and left the room. In his opinion these was a Yorker but I made these as over-pitch and smashed 6 sixes. For sub query example I used Level pseudo column, for aliases I used Dense_Rank() analytic function and simple selected Sysdate from dual. I had written this much only, he entered the room and took my sheet. I said I have to write 2 more answers. He said,”No problem”. He checked my queries very minutely but he was not able to get any thing missing. He asked “How to get 2 months later date and 2 months earlier date?” I said using ADD_MONTH() function. He was satisfied.

Qu 6: What are procedures and how they are different from function? When should we go for function and when to go with procedures? What are the modes of parameter and how and what operation we can perform with these parameters.

These questions were not in one go. He asked in a sequence. I answered them very perfectly, with almost 6-7 differences between procedure and function.

Qu 7: What is the significance of copy keyword in parameters?

I said sorry sir I don’t know. This was the first bouncer from his side.

Qu 8: What are triggers? What are instead of triggers? What is correlation identifier? How many types of triggers are there? When you should go for statement level triggers and row level triggers? What we cannot do with statement level trigger?

These questions were also in a sequence on after another. Again he gave me few easy over throw runs. He confused me little bit in the use of statement level trigger. But I passed that also.

Qu 9: What are cursors? What are different types of cursors? What is a ref cursor?

This was the question I was waiting for. Actually he asked cursor only. Before he will ask the next level of cursor, I vomit all the theory of cursors along with the cursor attributes and its usage.

Qu 10: Tell me about dual table?

I described starting from system schema object to its data type of the column and the value stored in the column. I forgot the column name that time. He asked “what is the column name?” I don’t know how I forgot the name of the column which is DUMMY. I was trying to recall but time was up and he asked the last question.

Qu 11: What is a transaction and how to complete a transaction?

It was again an easy one for me but I did a mistake. He asked, “What are TCL?” I said Transaction control language and I said Grant and revoke are TCL. Before he interrupts I said sorry and said Commit, Rollback and Save point. Then he asked what we call to Grant and Revoke. And again I tried a lot but was not able to remember DCL.

Finally he stood up and I felt relaxed that at least my interview is over now. It was almost all 1 hour. He shook his hand again and said that please wait on first floor, HR will meet you. I was happy with my performance. I thought that I will be selected. I waited for 10 minutes. Now I could see Maya coming towards me. She opened the glass door and with a smile she said those words; which I was dying to hear. She Said, “Congratulations Ajit! You have been selected, just give me five minutes; I will ask the director whether you have to talk to him or I will directly issue the OFFER LETTER”. I said “Thank you Mam. I am waiting”. She came and said “Director is on phone, and he will not be free for next few hours; so its 100% confirm, I will call you in the evening and tell you when to collect the offer letter”. I said “Ok Mam, Thank you”. And I left the company.
In the evening I called her. She said that you have been offered and you can come and collect your offer letter tomorrow. I said “Ok and disconnected the phone”. ##****## (Why I kept these hash & stars, I will explain it later). Next day I went to MetricStream and Maya told me that they have some conversation with Bell Softech (my consultancy) and Bell Softech is going to give me the offer letter. I asked Prathibha ( Bell Softech’s HR). She said,” You will get the offer letter by tomorrow or day after tomorrow (i.e. on 15^th or 16^th July)”. I was bit confused like what is happening. I had already left few final round interviews on Java hoping that I got this one and these people were doing ‘tamasha’.

On 15^th july no call on my cellphone from Bell Softech, my confusion increased. On 16^th till evening no call again, confusion went to its peak and finally I decided to call Prathibha. Actually Maya had already told me that I have to join by Monday i.e. 19^th july but I was not having any document regarding joining. And 17^th was Saturday and 18^th was Sunday. Finally I called Prathibha and she said that I will get the offer on Monday morning and I have to join the same day.
And on Thursday 15^th July itself I was writing this blog. You noticed those “##****##”. That was the time when I felt uncomfortable. And I left writing it. In an hour I was having little bit fever. I took some medicine at night. But I felt no relief. Next day in the evening I went to doctor, she also gave some other medicine, but no relief again. On Saturday I went to the doctor and described her the situation like I have to join on Monday and I want to be fit and fine by that time. She advised me that through medicines it will take time. If you want to recover soon we have to rely upon injections. She also advised me to admit there. I was admitted and a needle was pinched and they started keeping me on glucose water. It was the first time in my life I have been injected glucose water. They gave many IV(in vain) injections and few IM also. Fever came down but they suspected of Dengue Fever, because my platelets count were decreasing. Sunday evening I was bit fine because no fever was there. So I told them that I have to collect the offer letter and join from tomorrow. They were not ready to discharge me. But on my own risk they finally discharged me. I went to the room. Took some milk and slept. Actually I was still unable to eat any solid thing. Whenever I was trying to eat, I was vomiting. Monday morning I wake up, took bath and decided to leave for my offer letter. I have some Dosha in the morning. I took an auto rickshaw, reached Bellsoftech. Prathibha was late. When I reached there my situation was worse. I vomited there many times and now all I wanted was a bed to sleep. I was not having power to walk. Somehow I managed and Prathibha came. She saw me and understood that I am not well. She gave me the offer letter and said that first go and have good treatment. I left with Jainendra one of my colleague, who was also there to collect the offer letter. I returned and directly went to hospital. I was hospitalized again. My platelets count reached 37000. Normal human beings have these platelet counts in blood ranging between 1.5L to 4.5L. It was confirmed that I was having Dengue Fever. Till Tuesday morning platelets counts was between 25000-30000. They said that once it goes below 20000 then only the will transfuse platelets in my blood. And in afternoon it became 23000. Now doctor told that we need to bring platelets. Then she called Blood Banks. All known blood banks were having shortage of platelets. No one was ready to give platelets for me. Finally she referred me to Sagar Hospital Banaratt road. She said that they are having platelets and they will give to their own patients. I understood, it serious now. My friends took me to Sagar Hospital. When we reached there, we saw no one there was satisfied with their treatments, and also they were charging heavily. I told my friend to take me to Applo Hospital. We went there. The doctor in the emergency said, “It’s too serious and you will be kept in ICU, Just wait I will check the status of ICU”. After five minutes he said that we are not having any vacant seat in ICU. I told him to keep me in any ward till a seat is vacant. He denied. I asked him to advice that where we should go. He named a few, the nearest was Fortis Hospital. We went there. The doctor in the emergency took me very lightly. He said you will be kept in general ward. I told the Applo Doctor’s words. He said no, ICU is not required. He also told me to wait. After five minutes he said that we are not having any seat vacant in general ward. These word horrified me. And I thought that I am living my last moments on this planet. Because Blood Banks were not having platelets and those who were having, they have lack of beds. I requested that please keep me anywhere. Keep me in ICU or emergency itself, but please don’t send me anywhere else. He took few more minutes and Thank god he gave me a bed in the emergency. He started his treatment. Sudha Menon was my doctor there. She was brilliant. In the evening I was allotted a bed in a general ward room. That hospital is great. Food is good; treatments are good, much cleaned and well furnished rooms. I stayed there for 3 days and without transfusing platelets, my body generated those platelets by itself. I left the hospital when the graph of platelet count were increasing and was 67000. I came back home on Friday. My father and my maternal uncle reached that day only. In the next two days my platelet count reached 1.8L. I was fully fine. This is my new life. I can say that I have seen death from few meters away; it was a very close save. Thanks God for this new life. Special thanks to my friend Ladan, who was always ready to donate blood for me, actually his blood group is also B -ve like me. He was always with me those days. Also thanks to my two juniors Ankit and Abhishek, who came from Hyderabad to help me and look after me all the time, also thanks to all those friends and people who were with me either physically or mentally those days. This blog title was decided as “~~My first interview as my last interview~~” earlier. But now it has changed. On Monday 26^th July I got the appointment letter and on the same day I joined MetricStream.

( Spring Festival CCTV 2010 ) Acrobatics "trial sky high"

2010-06-21T19:52:00.000-07:00

Amazing video, amazing performance by Chinese People. First time you will not be able to believe what is happening here. How well trained and well disciplined these people are! Just awesome.

Solution to unhappy employees after Appraisal

2010-06-19T02:56:00.000-07:00

How to compile C/C++ program using Digital Mars Compiler (DMC).

2010-06-19T01:00:00.000-07:00

Digital Mars compiler is a wonderful compiler and its free software. We can easily write our program in any editor of our own choice and compile it on command prompt using few commands. Here is the full description starting from downloading compiler to compiling & running a c code. Try it....

Salary & Govt. Concessions for a Member of Parliament (MP)

2010-06-04T23:52:00.000-07:00

You can just cross verify the content of this page from http://parliamentofindia.nic.in/ls/intro/p8.htm ,

a government website page. The facilities mentioned

below are the summary of this page only.

Monthly Salary: Rs. 12,000/-

Expense for Constitution per month: Rs. 10,000/-
Office expenditure per month: Rs. 14,000/-

Traveling concession (Rs. 8 per km): Rs. 48,000/-

(eg. For a visit from South India to Delhi & return: 6000 km)

Daily DA TA during parliament meets: Rs. 500/day

Charge for 1 class (A/C) in train: Free (For any number of times)
(All over India )

Charge for Business Class in flights: Free for 40 trips / year (With wife or P.A.)

Rent for MP hostel at Delhi: Free.
Electricity costs at home: Free up to 50,000 units.

Local phone call charge: Free up to 1, 70,000 calls.

TOTAL expense for a MP [having no qualification] per year: Rs.32, 00,000/-

[i.e. 2.66 lakh/month]
TOTAL expense for 5 years: Rs. 1, 60, 00,000/-

For 534 MPs, the expense for 5 years:
Rs. 8,54,40,00,000/-

(Nearly 855 crores)

AND THE PRIME MINISTER IS ASKING THE HIGHLY QUALIFIED, OUT PERFORMING CEOs TO CUT DOWN THEIR SALARIES.....

This is how all our tax money is been swallowed and price hike on our regular commodities.........

And this is the present condition of our country:

855 crores could make their life livable!!
Think of the great democracy we have…

SQL Statement to get the 2nd highest salaried/paid Employee.

2010-06-04T10:44:00.000-07:00

Best Solution:

select *
from emp
where sal =
(
select max(sal)
from emp
where level=2
connect by prior sal>sal
)
;

SQL Statement to get Nth highest salaried/paid employee(s)

Best.

select *
from emp
where sal =
(
select max(sal)
from emp
where level=&level
connect by prior sal>sal
)
;

Few stills from My College Days

2010-06-02T10:15:00.000-07:00

Oil Price Increasing... I have solution for this.

2010-05-18T09:15:00.000-07:00

Petrol price hiked by Rs. 4.00!!!!!!!

So Bajaj Auto launched another fuel saver bike.....

After The SUCCESS of .........

CT100............

PLATINA ..............

Presenting.....................

COWasaki By BAJAJ

How to implement List Data Structure in C Using Array.

2010-05-16T02:03:00.000-07:00

If you are going to use this program on Windows then Delete all occurrences of \33[x where x may be 31,32,32.....,37. They have been used to produce colorful text.This program has be developed and tested on gcc.

First time it will ask the user to fill the list.
After that a menu will appear which will contain the following functions
1.View your list.
2.Add element at any position in the list.
3.Delete element(s) taking the value or the location.
4.Finding the location of the element(s) or element if location is provided.
5.Exit

#include<stdio.h>

int element,maxArraySize=65536;
int arr[65536];
int currentSizeOfArray=0;

//////Function Declarition//////

void printList(int x);
void addInput();
void addElement(int addPosition,int addItem);
void deleteInput();
void findItem();
void findByLocation(int location);
int findLocation(int item);
void findByValue(int elemn);
void deleteByLocation(int location);
void addList();

//////*****************//////////

//////Main Finction/////////////

int main()
{
int userOption;
int ch;
printf("\33[36mWelcome to the list Software.\n"); ///\33[36m has been used to Make text colorful.It will work in linux only
printf("The list is empty please enter some values in the list.\n");
addList();
printList(currentSizeOfArray);
do
{
printf("\33[32mWhat operation you want to perform:\nPlease Choose Your option.\n");
printf("\noption\tExplanation\n");
printf("1\tView your list\n2\tAdd an element\n3\tDelete an element\n4\tFind the position of an element\n5\tExit\n");
scanf("%d",&userOption);
switch(userOption)
{
case 1:
printList(currentSizeOfArray);
break;
case 2:
printf("\33[35mPlease enter the location and Item to be added\n");
addInput();
printList(currentSizeOfArray);
break;
case 3:
printf("\n\33[35mPlease enter the position or item to be deleted\n");
deleteInput();
printList(currentSizeOfArray);
break;
case 4:
printf("\n\33[35mEnter the position or value you are willing to find\n");
findItem();
break;
case 5:
printf("\n\33[37mThanks for using this application\n\n");
return 0;
default :
printf("\n\33[31mYou entered an in valid choise\n");
}
printf("\n\33[37mWant to do some more thing on list, press 1 to continue\n");
scanf("%d",&ch);
} while(ch==1);
printf("\n\n\n");
}

void addList()
{
printf("Enter each number and then press enter. When all your elements get inserted.please enter 9876 to go to the Main Menu\n");
int i=0;
int j=maxArraySize;
for(i=0;i<j;i++)
{
scanf("%d",&arr[i]);
if(arr[i]==9876)
{
arr[i]=0;
break;
}
currentSizeOfArray++;
}
}

void printList(int x)
{
int i;
printf("\33[32mYour list is looking like this:\n\nLocation\tValue\n");
for( i=0;i<x;i++)
{
printf("%d \t\t %d \n",i,arr[i]);
}
}

void addInput()
{
int addPosition,addItem;
printf("\33[33m\nEnter the location\n");
scanf("%d",&addPosition);
if(addPosition>(currentSizeOfArray))
{
printf("\nYou can not enter at that location\n");
return;
}
printf("\nEnter the element\n");
scanf("%d",&addItem);
addElement(addPosition,addItem);
}

void addElement(int addPosition,int addItem)
{
currentSizeOfArray++;
int i,settle=currentSizeOfArray;
for(i=currentSizeOfArray;i>addPosition;i--)
{
arr[i]=arr[--settle];
}
arr[addPosition]=addItem;
}

void deleteInput()
{
int choise;
int loca,z=1;
printf("\n\33[34mPlease specify the option wheather you want to delete by Location or by Item Value\n");
printf("\noption\tExplanation\n1\tDelete by Location\n2\tDelete By value\n");
scanf("%d",&choise);
switch(choise)
{
case 1:
{
printf("Enter the location\n");
scanf("%d",&loca);
deleteByLocation(loca);
break;
}
case 2:
{
printf("\nEnter the element/value to be deleted\n");
scanf("%d",&element);
do
{
loca=findLocation(element);
if(loca==-1)
{
if(z==1)
{
printf("\33[31mThe element is not found\n");
}
break;
}
deleteByLocation(loca);
}while(1);
}
}
}

void findItem()
{

int choise,z=1;
int location;
printf("\n\33[35mPlease specify the option wheather you want to find the element by Location or by Item/element Value\n");
printf("\noption\tExplanation\n1\tFind by Location\n2\tFind By value\n");
scanf("%d",&choise);
switch (choise)
{
case 1:
printf("Enter the location\n");
scanf("%d",&location);
findByLocation(location);
break;
case 2:
printf("Enter the element\n");
scanf("%d",&element);
location=findLocation(element);
if(location==-1)
{
printf("\n\33[31mThe element is not found\n");
break;
}
findByValue(element);
break;
default :
printf("\n\33[31mYou have not chossen the correct option\n");
break;
}

}

void findByLocation(int location)
{
int i;
if(location>=currentSizeOfArray ||location<0)
{
printf("\n\33[31mThe element is not found,please checj the element or its location\n");
return;
}
for(i=0;i<currentSizeOfArray;i++)
{
if(i==location)
{
printf("\33[32msearch Sucessful,Itme %d found at %d\n",arr[i],i);
break;
}
}

}

int findLocation(int item)
{
int i;
for (i=0;i<currentSizeOfArray;i++)
{
if(item==arr[i])
return i;
}
return -1;
}

void findByValue(int elemn)
{
int i=1;
for (i=0;i<currentSizeOfArray;i++)
{
if(elemn==arr[i])
printf("\33[32mSearch Sucessful,Itme %d found at %d\n",arr[i],i);
}
}

void deleteByLocation(int location)
{
int i;
if(location>=currentSizeOfArray)
{
printf("\33[31mThe element is not found,please check the element or its location\n");
return;
}
for(i=location;i<currentSizeOfArray;i++)
{
arr[i]=arr[++location];
}
--currentSizeOfArray;
}