Can we use user definbed functions in SQL statements, as we can use in built oracle function like date etc..?

Yeah.Of course provided there is no dml in your function
 

Thanks for reply, pls tell you can't we use functions with DML in it. What will hapen if it conatins dml in it.
 

We can use function which has only select statments in it, but it should not contain other DML statements like INSERT,UPDATE and DELETE oracle doesnot allow because it might get conflicts
 

Add A Function To User Defined Object

DATABASE: Oralce 9i
TOOL : OEM 9.2.0.1.0
Can I add a Function to a User Defined Object Type.
The User Defined Object Has Dependencies.
I tried adding the function using OEM but an error was reported,says, cannot alter the definition.
I tried Oracle Change Manager,but that does not display User defined Types.

This is well explained in the Oracle manuals. You cannot simple modify the already existing object type. In order to modify object attributes or methods, an object type must be dropped and re-created. An attempt to drop or replace a type with type or table dependents will result in an ORA-02303. Here is the manual part:

QUOTE:
The object types can be instantiated as types (nested tables or VARRAYs) or object tables. If data preservation is required, data depending on the type must be manually moved into temporary tables.
Per the SQL Reference guide, the DROP TYPE FORCE option is not recommended since this operation is not recoverable and could cause the data in the dependent tables to become inaccessible.

The following 3 queries can be run to determine dependencies:

-- Find nested tables
select owner, parent_table_name, parent_table_column
  from dba_nested_tables
where (table_type_owner, table_type_name) in
(select owner, type_name
from dba_coll_types
where elem_type_owner = '<typeOwner>'
and elem_type_name = '<typeName>');

-- Find VARRAYs
select owner, parent_table_name, parent_table_column
from dba_varrays
where (type_owner, type_name) in
(select owner, type_name
from dba_coll_types
where elem_type_owner = '<typeOwner>'
and elem_type_name = '<typeName');

-- Find object tables
select owner, table_name
from dba_object_tables
where table_type_owner = '<typeOwner>'
and table_type = '<typeName>'
and nested = 'NO';

So first find the object dependencies on object types, make sure you have a means to backup the dependent object (nested tables, varrays, tables), drop the dependent object (nested tables, varrays, tables), modify the object type, re-create (restore) the object (nested tables, varrays, tables).

Hope that works for you - no OEM involved, so it would be a good practice.

Quick Links:
Do you have an Oracle Question?

Best regards,
Oracle Database, SQL, Application, Programming Tips