Menu
I have an Oracle schema, where I have some functions and some packages that have functions inside.
How can I test if a string value is a function name in my schema, and if it is, return the function result, or if it's not a function to return the string value?
I tried to start from
SELECT * FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE_BODY')
but this does not return the function names from inside the packages. Thank you!
733
The Oracle EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Type a short Oracle program, using the following code as a guide: DECLARE record_exists INTEGER; BEGIN SELECT COUNT(*) INTO record_exists FROM your_table WHERE search_field = 'search value' AND ROWNUM = 1; IF record_exists = 1 THEN DBMS_OUTPUT. put_line('Record Exists') ELSE DBMS_OUTPUT.
SQL> select table_name from user_tables where table_name='MYTABLE'; Another way to test if a table exists is to try to drop the table and catch the exception if it does not exist.
Answers. Exist is more faster than IN because IN doesn't use indexes at the time of fetching but Exist uses Index at the time of fetching.
Use the view ALL_PROCEDURES.
The column OBJECT_NAME will show you the name of standalone functions and procedures, and packages names. The column PROCEDURE_NAME will show you the names of functions and procedures inside a package.
As you're only interested in functions you might be better off using ALL_ARGUMENTS. This views records the parameters used by a procedure or function. If a given program unit has a POSITION with a value of zero, that indicates the return value for a function.
115
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
