ORA-01502 Oracle

Error Message :

The OCI function OCIStmtExecute returned status 1. Error code: 1502, Error message: ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state.

You will get above error messages in Datastage job or toad/sql developer when you are trying insert duplicate records into a table.

Solution :

1) check the index status in table by going to Index tab.
2) If it is in "UNUSABLE" state then you need to rebuild the index using below alter statement :

ALTER INDEX INDEX_NAME REBUILD;

Then run your job or insert sql.

Debug in Datastage 8.7

Step 1 : Right click on the link where you want to create a break point       

  Step 2 : Then click on Toggle Breakpoint                                                          

Step 4 :  Then From the Menu bar click "Debug" then click "Go".

Then Job Run window and Debug window will appear, then provide required parameter values and click ok on Jon Run window.

It will show like below when Debug is running

Next it will show up the first row values like below.We can change number of rows you want to see by clicking "Edit Break Point" from the "Menu > Debug " or by right clicking on the "Break Point" that we created in Step 1.

How to search only for numbers in a column in Oracle : rtrim

 with test as
(select '1gnec16z15j129947' vin from dual union all
 SELECT '99999999999999999' vin FROM DUAL UNION ALL
 SELECT '00000000000000000' vin FROM DUAL UNION ALL
 SELECT '11111111111111111' vin FROM DUAL UNION ALL
  select 'abcdefghijklmnop' vin from dual union all
 select 'jtmbk31v076017323' vin from dual
 )
 SELECT  * FROM TEST WHERE not rtrim(vin, '0123456789') is null


OUTPUT :

below is the output of the above query which is eliminating the numbers:

1gnec16z15j129947
abcdefghijklmnop
jtmbk31v076017323

*************************************************
 with test as
(select '1gnec16z15j129947' vin from dual union all
 SELECT '99999999999999999' vin FROM DUAL UNION ALL
 SELECT '00000000000000000' vin FROM DUAL UNION ALL
 SELECT '11111111111111111' vin FROM DUAL UNION ALL
  select 'abcdefghijklmnop' vin from dual union all
 select 'jtmbk31v076017323' vin from dual
 )
 SELECT  * FROM TEST WHERE rtrim(vin, '0123456789') is null


OUTPUT :

below is the output of the above query which is eliminating rows other than numbers.

99999999999999999
00000000000000000
11111111111111111

What is UNICODE ??

Unicode Provides unique number for every character  no matter what the language,program and platform.

Difference between Unicode and UTF : 

vi editor commands

To locate instances of particular sets of characters (or strings), use the following commands.

/string       search forward for occurrence of string in text
?string      search backward for occurrence of string in text
n               move to next occurrence of search string
N              move to next occurrence of search string in opposite direction
u               undo
U              undo all the changes

Replace
------------
:%s/oldword/newword/g
:%s/^M//g                           ----  to replace control-m characters (press ctrl-v& ctrl-m )

look for special characters:
---------------------------------
:set list

Unix ksh won't execute and gives error 127

If you get the error : 127 when you execute the shell script.

There might be one of these 2 issues :

1) shebong line is wrong
2) you might have copied the file from windows to unix - that will create a control/special characters at end of the line.

SOLUTION :
execute the below command

head script.ksh | cat -vet | head -1

If you see "^M" at the end of the output - then you need to remove that special character by either using "dos2unix" command or using cat command like below :

1)  cp padaddwip.ksh padaddwip.ksh.bak
     dos2unix padaddwip.ksh.bak > padaddwip.ksh

2)  cat script.ksh.bak | tr -d "\r" > script.ksh

SET SYSTEM VARIABLES - ORACLE/SQL PLUS

SET DEF[INE] {& | c | ON | OFF}

Sets the charavter used to prefix substitution variables to c.

ON or OFF controls whether SQL * Plus will scan commands for substitution variables and replace them with their values.

Example :

sql > SET DEFINE ON

        SELECT 'HELLO & ?' FROM DUAL;

       ? = WORLD   (IT WILL ASK FOR USER INPUT,HERE THE 'WORLD' IS A USER INPUT)

       OUTPUT : "HELLO WORLD"


sql > SET DEFINE OFF
        SELECT 'HELLO & ?' FROM DUAL;

        (IT WON'T ASK FOR USER INPUT AS THE DEFINE IS OFF)

        OUTPUT : "HELLO & ?"