Oracle Interview Question

How to implement ISNUMERIC function in SQL *Plus ?

Method 1:
Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;

Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)

Method 2:
select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;

It returns 0 if it is a number, 1 if it is not.

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

What is the difference between Truncate and Delete interms of Referential Integrity?

DELETE removes one or more records in a table, checking referential
Constraints (to see if there are dependent child records) and firing any
DELETE triggers. In the order you are deleting (child first then parent)
There will be no problems.

TRUNCATE removes ALL records in a table. It does not execute any triggers.
Also, it only checks for the existence (and status) of another foreign key
Pointing to the table. If one exists and is enabled, then you will get
The following error. This is true even if you do the child tables first.

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

You should disable the foreign key constraints in the child tables before
issuing the TRUNCATE command, then re-enable them afterwards.