Usefull SQLs

To convert the number in words
select to_char(to_Date(5373484,'J'),'Jsp') Result from dual

The result is : Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four
Note : This is the maximum number u can convert in words ( 5373484 , because Julian year ranges between 1 and 5373484 )


Procedure to copy values from one column to another column

DROP TABLE xxtest;

CREATE TABLE xxtest(col1 NUMBER,col2 NUMBER);

INSERT INTO xxtest VALUES (1, NULL);
INSERT INTO xxtest VALUES (2, NULL);
INSERT INTO xxtest VALUES (3, NULL);
INSERT INTO xxtest VALUES (4, NULL);

BEGIN
  FOR i IN (SELECT ROWID, col1 FROM xxtest)
  LOOP
     UPDATE xxtest SET col2 = i.col1
      WHERE ROWID = i.ROWID;
  END LOOP;
END;

Procedure to change first 3 character in a column

DROP TABLE xxtest2;

CREATE TABLE xxtest2(NAME VARCHAR2(20));

INSERT INTO xxtest2 VALUES ('abc123');
INSERT INTO xxtest2 VALUES ('abc124');
INSERT INTO xxtest2 VALUES ('abc125');

BEGIN
  FOR i IN (SELECT ROWID, NAME FROM xxtest2
             WHERE SUBSTR (NAME, 1, 3) = 'abc')
  LOOP
     UPDATE xxtest2 SET NAME = 'xyz' || SUBSTR (i.NAME, 4, LENGTH (i.NAME))
      WHERE ROWID = i.ROWID;
  END LOOP;
END;