Convert a number to word

select to_char(to_date('1983','J'),'JSP') from dual;

In this query the number is converting into Julian date then by using fromat  'SP' to spell out

Example

  select to_char(sysdate,'DDSP'),to_char(sysdate,'MMSP'),to_char(sysdate,'YYYYSP') from dual  -- Spells the day ,month and the year

 In this only value upto 5373484 can be spelled  ; giving greater value will result in the following error

 ORA-01854: Julian date must be between 1 and 5373484

 select to_char(to_date('190000083','J'),'JSP') from dual;

 ORA-01830: date format picture ends before converting entire input string


Query to Spell  Rupee and Paise

select
TO_CHAR(TO_DATE(substr(10000.123,1,instr(10000.123,'.')-1),'J'),'JSP')||' RUPEES AND  '||
replace(replace(replace(replace(TO_CHAR(TO_DATE(substr(10000.123,instr(10000.123,'.')+1,length(10000.123)),'J'),'JSP'),'MILLION',''),'HUNDRED',''),'THOUSAND',''),'-',' ')  ||' PAISE ONLY' as number_char
from dual;

----------

Query to convert NUMBER to words

In Oracle 9i


SELECT  LEVEL+1 asnumber FROM dual WHERE to_char(to_date(LEVEL+1,'J'), 'JSP')  = 'ONE THOUSAND'
CONNECT BY to_char(to_date(LEVEL,'J'), 'JSP')  != 'ONE THOUSAND';

OR

SELECT  LEVEL FROM dual WHERE to_char(to_date(LEVEL,'J'), 'JSP')  = 'ONE THOUSAND ONE HUNDRED ELEVEN'
CONNECT BY to_char(to_date(LEVEL-1,'J'), 'JSP')  != 'ONE THOUSAND ONE HUNDRED ELEVEN'
AND LEVEL < 10001  -- Your get out of jail clause!;


In Oracle 10g

select sp, n from (select 'FIVE THOUSAND ONE' sp from dual)
   model dimension by (1 dim) measures (0 n, sp)  rules iterate (10000) until (to_char(date '2000-01-01' +
(ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])  ( n[1]=ITERATION_NUMBER);


-------

ORA-20171: Workspace Error during BEGIN DDL

SQL > exec dbms_wm.beginddl('TABLE_NAME');

ORA-20171: WM error: 'CREATE TABLE' and 'CREATE SEQUENCE' privileges needed.


This error can be avoided by giving these below  grants to the particular user.

SQL > grant create TABLE  to username ;

SQL >  grant create SEQUENCE  to username;

Then execute the script ;it will be successful

SQL > exec dbms_wm.beginddl('TABLE_NAME');


--------

ORA-20104: cannot version disable this table

ORA-20104: cannot version disable this table
ORA-06512: at "SYS.LT", line 9152
ORA-06512: at line 3

the following error will occur if the TESTP table is a parent table and all the child table that refers TESTP is not versiondisabled .  

 An Example

create table TEST (ee number(10),fg varchar2(100)) ;

ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY (EE);

create table TESTP (ff number(10),ee number(10) ,constraint fk_fg foreign key(ee) references TEST(ee) );

ALTER TABLE TESTP ADD CONSTRAINT TESTp_PK PRIMARY KEY (ff);

execute dbms_wm.enableversioning('TEST');

execute dbms_wm.enableversioning('TESTP');

execute dbms_wm.disableversioning('TESTP');  This will result an error ( ORA-20104: cannot version disable this table )

to avoid this first you should disableversion child table  TEST and then parent table TESTP

execute dbms_wm.disableversioning('TEST');

execute dbms_wm.disableversioning('TESTP');

Impact of SQL Injection

   Retrieval of Crucial Data
   Data Manupulation
   Alter database State
   Revoke Database Service

Developers are ignorant that their above query can be misused to an extent that it can list login names and passwords, which is relevant and crucial information for an organization. An invader can give input as
Code :

p'or upper(s_empname) like'%
p'or'p'='p

Code sql:

SELECT USERNAME, PASSWORD FROM USERS
WHERE USERNAME = ''
OR 1=1

SQL Injection: Example

A procedure P_GET_SAL was created to get the salary of input Employee Id.

Code sql:

CREATE OR REPLACE PROCEDURE P_GET_SAL  (P_ENAME VARCHAR2 DEFAULT NULL)
AS
CUR SYS_REFCURSOR;
V_ENAME VARCHAR2(100);
V_SAL NUMBER;
BEGIN
  V_STMT := 'SELECT ENAME, SALARY FROM EMPLOYEE  WHERE ENAME = '''|| P_ENAME || '''';
  DBMS_OUTPUT.PUT_LINE(V_STMT); 
  OPEN CUR FOR V_STMT;
  LOOP
    FETCH CUR INTO V_ENAME, V_SAL;
    EXIT WHEN CUR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee : '||V_ENAME||' draws '||TO_CHAR(V_SAL));
  END LOOP;
  CLOSE CUR;
END;



Code sql:

SQL> EXEC P_GET_SAL(‘KING’);
Employee KING draws 4500

PL/SQL PROCEDURE successfully completed.


Code sql:

SQL> EXEC P_GET_SAL('KING'' UNION SELECT ENAME, SALARY FROM EMPLOYEE WHERE 1=1');
Employee KING draws 4500
Employee ALLEN draws 1200
Employee MIKE draws 3400
Employee KATE draws 2300
Employee PAUL draws 6400
Employee TOMY draws 2700
Employee JENNY draws 6200
Employee JAKES draws 4600

PL/SQL PROCEDURE successfully completed.



Several strategies can be adopted to safeguard the SQL code and eradicate the impacts of SQL injection in applications. Some of them are listed below.

1. Use of Static SQL
2. Using Invoker’s rights
3. Use of Dynamic SQL with bind arguments
4. Validate and sanitize input using DBMS_ASSERT

-------

Oracle Spatial Errorcodes

ORA-29913 the error in executing ODCI .

This error occurs when executing SDO_AGGR_UNION (SDOAGGRTYPE(SDO_GEOMETRY),0.5) is mainly due to the variation in the geometry of its ordinates if  operation is divided and done this error can be avoided .

-----------------------------------------------------------------------------