Monday, June 10, 2013

HOW TO FIND WHETHER A STRING IS NUMERIC OR NOT IN ORACLE


REGEXP_LIKE(my_col1, '[[:alnum:]]')
To test a string for numeric characters, you could use a combination of the LENGTH, TRIM, AND TRANSLATE functions built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' ')))

string1 is the string value that you are testing

This function will return a null value if string1 is numeric.
It will return a value "greater than 0" if string1 contains any non-numeric characters.

For example,

LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' '))); would return 1
LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))); would return 2
LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))); would return null
LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' ')));

Labels:

HOW TO CHECK FOR A TABLE IN A PARTICULAR TABLESPACE BEFORE DROPPING IN ORACLE


select
tab.table_name,tab.tablespace_name,tab.avg_row_len ROWCHN,
obj.created CREATEDON,obj.last_ddl_time DDLCHNGON,tab.last_analyzed ANALYSEDON,obj.timestamp TIMESTAMP
from all_tables tab,all_objects obj
where
tab.tablespace_name = 'REGN_BIG1_DAT_LM'
and
obj.object_type = 'TABLE'
and object_name = tab.table_name

Labels:

HOW TO ESTIMATE THE SIZE OF A TABLE IN BYTES IN ORACLE

To find the size of bytes allocated to a table:

sql > analyze table emp compute statistics;

sql > select num_rows * avg_row_len "Bytes Used" from dba_tables where table_name = 'TEST';

Bytes Used
-----------
560

sql > select bytes "Bytes Allocated" from dba_segments where segment_name = 'TEST';


Bytes Allocated
----------
524288

The result of the query shows that the emp table is using 560 bytes of the 524,288 bytes allocated to it.

Labels:

HOW TO FIND ALL GRANTS GIVEN TO A SPECIFIC USER (GRANTEE_USER) IN ORACLE

select * from dba_tab_privs where grantor = 'GRANTOR_USER'
and grantee = 'GRANTEE_USER'


Labels:

How to DROP multiple columns in Oracle

ALTER TABLE MY_TABLE
DROP (
MY_COL1,
MY_COL2
);

Labels:

How to search in a CLOB field in Oracle

SELECT * FROM MYTABLE WHERE dbms_lob.INSTR(CLOBDATA, 'C00001880386') >0

Labels:

How to use CASE in WHERE clause Oracle

SELECT * FROM parts p
WHERE
case when slogic = 'begins' and partnum like sPartStrp||'%'
     then 1
     when slogic = 'contains' and partnum like '%'||spartStrp||'%'
     then 1
     when slogic = 'equals' and partnum = sPartStrp
     then 1
     when partnum like sPartStrp || '%'
     then 1
     else 0
 end = 1
and p.stock_type = 1

Labels:

Wednesday, April 03, 2013

How to check whether PowerExchange Listener is running or not from PowerExchange Navigator?


It can be checked using DTLREXE utility.

c:\Informatica\PowerExchange9.1.0>dtlrexe loc=MainFrameDEV prog=ping
PWX-00750 DTLREXE Input LOC=MainFrameDEV, PROG=PING, PARMS=, UID=<>.
PWX-33304 10.*.*.*:* : taskid: 0, operation timeout: (none) secs, heartbeat interval: 30 secs
PWX-00755 DTLREXE Command OK!

Pick up the loc from dbmover.cfg.

Labels:

How to get the filename associated with various data maps in mainframe using Informatica PowerExchange?



Create dtlurdmo.ini as follows:-
OUTPUT D:\MY_PWX_SCRIPTS\TGT;
USER mypwxuser;
PWD mypwxpassword;
TARGETUSER mypwxusertgt;
TARGETPWD mypwxpasswordtgt;
SOURCE MVSDEV;
TARGET local;
DETAIL;
VALIDATE;
DM_COPY;

Run it as:-
c:\Informatica\PowerExchange9.1.0>dtlurdmo D:\MY_PWX_SCRIPTS\dtlurdmo.ini

Check Detail.logs in PWX installation directory for the filenames.

Labels:

Monday, January 21, 2013

HowToOracle:-How to insert a xml in a CLOB field in a Oracle table

Step 1:- Create a sample table as follows:-
create table my_clob_table ( id VARCHAR2(32), data CLOB);

Step 2:-
Create a control file as follows:-
test.ctl
-----------------

LOAD DATA
INFILE *
append
   INTO TABLE my_clob_table
   FIELDS TERMINATED BY ','
   (
id           CHAR(32),
clob_filename FILLER char,
  data         LOBFILE(clob_filename) TERMINATED BY EOF
)
begindata
eeeeee8a525e4e0e4e1,C:\TESTFOLDER\1810358.xml

Run sqlldr as:-
sqlldr userid=dbuser/dbpassword@orcl control=C:\TESTFOLDER\test.ctl

** Here C:\TESTFOLDER\1810358.xml is your xml which you want to insert in my_clob_table.data field.

 Click for more information :-

Labels:

Monday, August 20, 2012

Source System Analysis

Step – 1 - Go through the BRS or business requirement specification and then:-
1. Determine whatever attributes business is asking to be in datawarehouse is already existing in datawarehouse or not. Sometimes it happens that you might be extracting that attribute in the datawarehouse.
2. Determine what can be your most relevant source. Most of the times, you are supposed to get the required attributes from a single source or some from source A and some from source B. But sometimes you may find that some attributes can be extracted from source A as well as source B.At that time you need to consider which source you are going to use. You may consider that some source is already existing in your dawarehouse to use that.

Step – 2 – Meet the source system to know the following:-
1. What are the source tables needed. Primary key of the table. If more than one tables needed to fetch data of a specfic flow what is the relation between the table ( PK-FK).
2. The total data volume the source get in the source tables. Is there any update of not. What is determining column to know whether a record in that table is an update/insert. What is the data field to know when that insertion/updation happened.
3. What will be volume of data in datawarehouse in case of incremental load or in case of initial load.
4. What is the retention period of the table data for source system?
5. Know the business meanings of the entities. Collect the data dictionary of they have. Find our the list of values if any.
6. Do they get batch.

Step – 3 – Check back with your requirement which are :-
1. Did you get all the attributes from source system as per the business requirement specification? If not, complete the step -1 once again and when you are satisfied with the meeting with the source system that there is nothing more information to extract from source system you can send your gap analysis to the business team. Gap analysis tells about some attributes which are not present in the source system so that business personals are aware what they are expecting to get.

Step – 4 – Hands on the table and attributes.
1. Get the access of the source tables from where you are to extract from source system DBAs.
2. Do some random check on data like duplicates in date columns.
3. How much nulls or junks in some attributes.Are the attributes from the LOVs or not etc.
4. Relationship among tables. It is basically verifying whatever information you have got from the source system is correct or not. In case of any doubt anywhere perform step 2 and step 3 again.

Step -5 – Start High Level design of Staging Tables:-
1. At first, determine whether you require new staging table or you can accomodate the new attributes to some existing tables.
2. If you are going the ammend the new attributes in some existing table
3. In case of new staging table:-
a. Are you about to truncate and re-load?
b. Or the table will be append only?
c. Which datatype you prefer for the fields?
d. Do you want any constraint to there?
e. What about pk/fk or indexes?
f. Are you going to pull all columns as is from source or only fields you need as per BRS.
g. Did you think about surrogate key?
Recommended: Keep you staging table as similar as the source flat data/table.

Step -6 – Start High Level design of DW Tables:-
1. Put all the columns which will be more accessed at the beginning.
2. Try to put the date columns together in date format.
3. Try always to assign some default values instead of leaving a column value as NULL.
4. Determine your partitioning and index strategy as per the query to be performed on this table.
5. Determine the retention time for the table.
6. To make DW table with richer information and easier reporting/BI, replace any coded values to LOV.

Labels:

Thursday, August 19, 2010

HowToOracle:- Everything about exceptions

Every thing about Oracle Exception Handling
================================================

Facts:-
1. There are 2 kinds of exceptions:-
System defined / pre-defined exceptions
Programmer-Defined Exceptions

2. System defined exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.

Oracle has a standard set of exceptions already named as follows:

Oracle Exception Name
Oracle Error
Explanation

DUP_VAL_ON_INDEX
ORA-00001
You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.

TIMEOUT_ON_RESOURCE
ORA-00051
You were waiting for a resource and you timed out.

TRANSACTION_BACKED_OUT
ORA-00061
The remote portion of a transaction has rolled back.

INVALID_CURSOR
ORA-01001
You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.

NOT_LOGGED_ON
ORA-01012
You tried to execute a call to Oracle before logging in.

LOGIN_DENIED
ORA-01017
You tried to log into Oracle with an invalid username/password combination.

NO_DATA_FOUND
ORA-01403
You tried one of the following:
You executed a SELECT INTO statement and no rows were returned.
You referenced an uninitialized row in a table.
You read past the end of file with the UTL_FILE package.

TOO_MANY_ROWS
ORA-01422
You tried to execute a SELECT INTO statement and more than one row was returned.

ZERO_DIVIDE
ORA-01476
You tried to divide a number by zero.

INVALID_NUMBER
ORA-01722
You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.

STORAGE_ERROR
ORA-06500
You ran out of memory or memory was corrupted.

PROGRAM_ERROR
ORA-06501
This is a generic "Contact Oracle support" message because an internal problem was encountered.

VALUE_ERROR
ORA-06502
You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.

CURSOR_ALREADY_OPEN
ORA-06511
You tried to open a cursor that is already open.


Sample Procedure describing handling NO_DATA_FOUND error:-
---------------------------------------------------------------

CREATE OR REPLACE PROCEDURE proc_named_exception
AS
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename FROM emp WHERE ename = 'SMITH';
Dbms_Output.put_line('Employe name is = '||v_ename); -- Valid record willbe shown in output
/* the following will throw NO_DATA_FOUND exception */
SELECT ename INTO v_ename FROM emp WHERE ename = 'TITIKAKA';
Dbms_Output.put_line('Employe name is = '||v_ename); -- NO_DATA_FOUND, so control will go to exception block
-- Nothing will be shown in output
SELECT ename INTO v_ename FROM emp WHERE ename = 'KING';
Dbms_Output.put_line('Employe name is = '||v_ename);
EXCEPTION
WHEN No_Data_Found THEN
Dbms_Output.put_line('no data found');
END;

Facts 3:- By default, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue executing from the spot where an exception happens, enclose the code that might raise an exception inside another BEGIN-END block with its own exception handler. For example, you might put separate BEGIN-END blocks around groups of SQL statements that might raise NO_DATA_FOUND, or around arithmetic operations that might raise DIVIDE_BY_ZERO. By putting a BEGIN-END block with an exception handler inside a loop, you can continue executing the loop even if
some loop iterations raise exceptions.See sample 2 below.

Sample 2 Procedure describing handling NO_DATA_FOUND and DUP_VAL_ON_INDEX error:-
-------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE proc_named_exception1
AS
v_ename emp.ename%TYPE;
BEGIN
BEGIN -- This block is to handle NDF
SELECT ename INTO v_ename FROM emp WHERE ename = 'SMITH';
Dbms_Output.put_line('Employe name is = '||v_ename); -- Valid record willbe shown in output
/* the following will throw NO_DATA_FOUND exception */
SELECT ename INTO v_ename FROM emp WHERE ename = 'TITIKAKA';
Dbms_Output.put_line('Employe name is = '||v_ename); -- NO_DATA_FOUND, so control will go to exception block
-- Nothing will be shown in output
SELECT ename INTO v_ename FROM emp WHERE ename = 'KING';
Dbms_Output.put_line('Employe name is = '||v_ename);
EXCEPTION
WHEN No_Data_Found THEN
Dbms_Output.put_line('NO_DATA_FOUND');
END;

BEGIN -- This block is to handle DVOI
INSERT INTO emp VALUES(7369,'Hasim','Admin',9898,SYSDATE,7000,NULL,10);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
Dbms_Output.put_line('DUP_VAL_ON_INDEX');
END;
END;


Fact 4:- You can handle more than one exception in a single handler as follows:-

EXCEPTION
When NO_DATA_FOUND or TOO_MANY_ROWS then
Statements;
END;

Fact 5:- A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as

exception.They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. RAISE statement can also be used to

raise internal exceptions.

Declaring Exception:
--------------------

DECLARE
myexception EXCEPTION;
BEGIN

Raising Exception:
------------------
BEGIN
RAISE myexception;

Handling Exception:
----------------------
BEGIN
EXCEPTION
WHEN myexception THEN
Statements;
END;

Sample 3 Procedure addemp in the package is having an user defined exception
-------------------------------------------------------------------------------

PROMPT CREATE OR REPLACE PACKAGE hasim_pack2
CREATE OR REPLACE PACKAGE hasim_pack2 AS
PROCEDURE addemp;
PROCEDURE delemp;
END hasim_pack2;
/

PROMPT CREATE OR REPLACE PACKAGE BODY hasim_pack2
CREATE OR REPLACE PACKAGE BODY hasim_pack2 AS
PROCEDURE addemp IS
is_exists EXCEPTION;
cnt NUMBER :=0;
BEGIN
SELECT Count(1) INTO cnt FROM emp WHERE empno IN (9898,9897);
IF cnt > 0 THEN
RAISE is_exists;
else
INSERT ALL
INTO emp VALUES(9898,'Timothy','CEO',7839,SYSDATE,7000,NULL,10)
INTO emp VALUES(9897,'Hasim','Admin',9898,SYSDATE,7000,NULL,10)
SELECT * FROM dual;
COMMIT;
END IF;
EXCEPTION
WHEN is_exists THEN
Raise_Application_Error(-20001,'You must delete Timothy/hasim first');
WHEN OTHERS then
Raise_Application_Error(-20002,'Some error happened while inserting records');
END addemp;


PROCEDURE delemp IS
begin
DELETE FROM emp WHERE empno IN (9898,9897);
COMMIT;
END delemp;

END hasim_pack2;
/

Fact 6:-
An Exception cannot be declared twice in the same block.
Exceptions declared in a block are considered as local to that block and global to its sub-blocks.An enclosing block cannot access Exceptions declared in its sub-block. Where as it possible for a sub-block to refer its enclosing Exceptions.

Sample 4 Exceptions raised in sub-block is handled by main block
-----------------------------------------------------------------

CREATE OR REPLACE PROCEDURE proc_named_exception3
AS
v_ename emp.ename%TYPE;
res NUMBER;
BEGIN
BEGIN -- This block is to handle TMR
SELECT ename INTO v_ename FROM emp WHERE job = 'PRESIDENT';
Dbms_Output.put_line('President Employe name is = '||v_ename); -- Valid record willbe shown in output
SELECT (10/2) INTO res FROM dual;
Dbms_Output.put_line('10/2 = '||res); -- Valid record willbe shown in output
SELECT (10/0) INTO res FROM dual;
Dbms_Output.put_line('10/0 = '||res); -- Zero_Divide,no record willbe shown in output,exception catcher is in main block
EXCEPTION
WHEN TOO_MANY_ROWS THEN
Dbms_Output.put_line('TOO_MANY_ROWS');
END;

BEGIN -- This block is to handle DVOI
INSERT INTO emp VALUES(7369,'Hasim','Admin',9898,SYSDATE,7000,NULL,10);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
Dbms_Output.put_line('DUP_VAL_ON_INDEX');
END;
EXCEPTION
WHEN Zero_Divide THEN
Dbms_Output.put_line('Zero_Divide');
END;



Fact 7:- RAISE_APPLICATION_ERROR
To display your own error messages one can use the built-in RAISE_APPLICATION_ERROR. They display the error message in the same way as Oracle errors. You should use a negative number between –20000 to –20999 for the error_number and the error message should not exceed 512 characters. Check sample 3.

Fact 8:- The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by the System defined Exceptions and Programmer-Defined Exceptions.

Sample 5 Usage of when others in the previous addemp procedure
----------------------------------------------------------------

create or replace PROCEDURE addemp1 IS
is_exists EXCEPTION;
cnt NUMBER :=0;
BEGIN
SELECT Count(1) INTO cnt FROM emp WHERE empno IN (9898,9897);
IF cnt > 0 THEN
RAISE is_exists;
else
INSERT ALL
INTO emp VALUES(9898,'Timothy','CEO',7839,SYSDATE,7000,NULL,10)
INTO emp VALUES(9897,'Hasim','Admin',9898,SYSDATE,7000,NULL,10)
SELECT * FROM dual;
COMMIT;
END IF;
SELECT (10/0) INTO cnt FROM dual;
EXCEPTION
WHEN is_exists THEN
Raise_Application_Error(-20001,'You must delete Timothy/hasim first');
WHEN OTHERS then
Raise_Application_Error(-20002,'Some error happened while inserting records');
END addemp1;




SQL> exec addemp1;
BEGIN addemp1; END;

*
ERROR at line 1:
ORA-20001: You must delete Timothy/hasim first
ORA-06512: at "SCOTT.ADDEMP1", line 17
ORA-06512: at line 1

SQL> exec hasim_pack2.delemp;

PL/SQL procedure successfully completed.

SQL> exec addemp1;
BEGIN addemp1; END;

*
ERROR at line 1:
ORA-20002: Some error happened while inserting records
ORA-06512: at "SCOTT.ADDEMP1", line 20
ORA-06512: at line 1


SQL>



Fact 9:- The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code.

Sample 5 Usage of when others in the previous addemp procedure
----------------------------------------------------------------

create or replace PROCEDURE addemp2 IS
is_exists EXCEPTION;
cnt NUMBER :=0;
BEGIN
SELECT Count(1) INTO cnt FROM emp WHERE empno IN (9898,9897);
IF cnt > 0 THEN
RAISE is_exists;
else
INSERT ALL
INTO emp VALUES(9898,'Timothy','CEO',7839,SYSDATE,7000,NULL,10)
INTO emp VALUES(9897,'Hasim','Admin',9898,SYSDATE,7000,NULL,10)
SELECT * FROM dual;
COMMIT;
END IF;
SELECT (10/0) INTO cnt FROM dual;
EXCEPTION
WHEN is_exists THEN
Raise_Application_Error(-20001,'You must delete Timothy/hasim first');
WHEN OTHERS then
Raise_Application_Error(-20002,'Some error happened while inserting records'||SQLCODE||' -ERROR- '||SQLERRM);
END addemp2;


Samplerun:-
SQL> exec hasim_pack2.delemp;

PL/SQL procedure successfully completed.

SQL> exec addemp2;
BEGIN addemp2; END;

*
ERROR at line 1:
ORA-20002: Some error happened while inserting records-1476 -ERROR- ORA-01476:
divisor is equal to zero
ORA-06512: at "SCOTT.ADDEMP2", line 20
ORA-06512: at line 1

Labels: