Oracle SQL

Hi Albert,

You can use DBMS_LOB.substr like this for example:

DBMS_LOB.substr(column, 3000)

but don’t forget that substring of a CLOB column has size/buffer restrictions sometimes you would need to set the BUFFER to a larger size.

For example while using SQL Plus use the SET BUFFER 10000 to set it to 10000 as the default is 4000

and you can refer to Oracle Substr Function Article for more information about the substr function

one example to create a function to return ‘Y’ if parameter value is number otherwise which is exception in this case to return (‘N’)

ex:

[code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
PARALLEL_ENABLE
IS
l_num NUMBER;
BEGIN
l_num := TO_NUMBER (p_string);
RETURN ‘Y’;
EXCEPTION
WHEN VALUE_ERROR
THEN
RETURN ‘N’;
END is_number;[/code]

and here you can call the function created above to identify the value passed is number or not like :

[code]SELECT (CASE
WHEN (is_number (mycolumn) = ‘Y’)
THEN
‘your column value is number’
ELSE
‘your column value is not number’
END)
FROM myTable;[/code]

hope this help you.

You can find the following view to access all metadata about the columns :

user_tab_cols; — For all tables owned by the user

all_tab_cols ; — For all tables accessible to the user

dba_tab_cols; — For all tables in the Database.

and lets consider you want to add new column only if doesn’t exists you can use this pl/sql to check and add new column

[code]DECLARE
v_column_exists number := 0;
BEGIN
Select count(*)
into v_column_exists
from user_tab_cols
where column_name = ‘ADD_COLUMN’
and table_name = ‘departments’;

if (v_column_exists = 0) then
execute immediate ‘alter table departments add (ADD_COLUMN NUMBER)’;
end if;
end;[/code]

hope this help.