Archive

Archive for June, 2010

substring in shell script

Example 1:
======
Testing=”karuvatchi”
str=`expr substr “$Testing” 4 1`
echo “Substring =$str”

Output:
======
         u

Example 2:
======
Testing=”karuvatchi”
str=`expr substr “$Testing” 4 3`
echo “Substring =$str”

Output:
======
         uva

Advertisements
Categories: Technical, unix commands

Find substring in string/Index of substring in string using awk

Syntax:
======
    index(string,substr)
if result of index(string,substr) is zero then substring is not found
else returns the left most position of the substring.

Example:
======
    index(“This is a string”,”is a”)

Output:
======
     6

Categories: awk, Technical

Update multiple columns in sql

Syntax:
======

UPDATE
               table_name
SET
              Column_name=Value,
              Column_name=Value,
              Column_name=Value,…..
WHERE
              conditions;

Example:
======
UPDATE
              employee
SET
              emp_name=”karuvatchi”,
              emp_performance_rate=8
WHERE
              emp_no=303;

Categories: SQL, Technical

Basic Exception Handling in PL/SQL

Syntax:
======
DECLARE
………Declare statements
BEGIN
…………….
EXCEPTION
WHEN exception THEN
………actions
END;


Example:

======
SET SERVEROUTPUT ON
DECLARE
      OutputStr VARCHAR2(50);
BEGIN
      SELECT
            emp_no|| chr(2) || emp_salary
      INTO
            OutputStr
      FROM
            employee
      WHERE
            emp_name=”karuvatchi”;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE(‘No Data Found Error occured’);
END;

Categories: SQL, Technical

Variable Initialization in PL/SQL

Syntax:
=======
Method 1:
variable_name data_type(size) := value;

Method 2:
variable_name data_type(size);
variable_name := value;

Example:

======
1:
emp_name varchar2(50) := “Karuvatchi”;

2:
roll_no INTEGER;
roll_no := 303;

Categories: SQL, Technical

ORA-01741: illegal zero-length identifier

if(variable = “”) then
dbms_output.put_line(“varaiable is empty”)
end if;

While running the above code in pl/sql i got the error as “ORA-01741: illegal zero-length identifier”
and the fix is
if(variable is NULL) then
dbms_output.put_line(“varaiable is empty”)
end if;

Today learnt that In PL/SQL Cannot use empty double quotes (“”) for condition checking.

Categories: SQL, Technical

If condition in PL/SQL

IF condition syntax:
==============
IF condition THEN
…….
…….
END IF;

IF ELSE Syntax:
============
IF condition THEN
…….
ELSE
…….
END IF;

IF ELSEIF Syntax:
=============

IF condition THEN
…….
ELSEIF condition THEN
……..
ELSE
…….
END IF;

Categories: SQL, Technical