Use user defined functions in
SQL statements
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.
Have a Oracle Question
Do
you have an Oracle Question?
Oracle Books
Oracle Certification,
Database Administration, SQL, Application, Programming Reference Books
Oracle Application
Oracle
Application Hints and Tips
Oracle Home
Oracle
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.
|