Home » SQL & PL/SQL » SQL & PL/SQL » How to get the functions name used by a column in the whole database (oracle 11g)
How to get the functions name used by a column in the whole database [message #668035] Thu, 01 February 2018 04:32 Go to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Hi team ,

I have a view called employee_vw which has the select below

view content:
select function_standard(ename) from employees;


now i want to get what all functions are applied on the respective column.I tries using select select dbms_metadata.get_ddl('COLUMN','ENAME','HR') FROM DUAL but there is no column object type as seen . My requirement is if i pass a column name to a statement it should return what are all functions are applied on that respective column in the whole database is there any way possible to get the data out.


Thanks in advance.





Re: How to get the functions name used by a column in the whole database [message #668037 is a reply to message #668035] Thu, 01 February 2018 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Even in the programs that are outside the database?

Re: How to get the functions name used by a column in the whole database [message #668038 is a reply to message #668037] Thu, 01 February 2018 04:42 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Hi Michel Cadot,

Thanks for your response . I need data related to inside the database not the outside the database.


Thank you.
Re: How to get the functions name used by a column in the whole database [message #668041 is a reply to message #668038] Thu, 01 February 2018 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you clarify.
You want the functions which use a column in their code or the functions that are called with a column as parameter?

You have to realize that if I call your SQL statement above from SQL*Plus the database is definitively unable to know I do this, is this ok for you?
Only something that is recorded in the database may be known from the database.

Clarify your requirement.

Re: How to get the functions name used by a column in the whole database [message #668043 is a reply to message #668035] Thu, 01 February 2018 06:31 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
POGAKU_SANTHOSH wrote on Thu, 01 February 2018 02:32
Hi team ,

I have a view called employee_vw which has the select below

view content:
select function_standard(ename) from employees;


now i want to get what all functions are applied on the respective column.I tries using select select dbms_metadata.get_ddl('COLUMN','ENAME','HR') FROM DUAL but there is no column object type as seen . My requirement is if i pass a column name to a statement it should return what are all functions are applied on that respective column in the whole database is there any way possible to get the data out.


Thanks in advance.





not possible since conceptually what you desire violate basic function operation to return a single value.
Re: How to get the functions name used by a column in the whole database [message #668052 is a reply to message #668035] Thu, 01 February 2018 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
POGAKU_SANTHOSH wrote on Thu, 01 February 2018 02:32
Hi team ,

I have a view called employee_vw which has the select below

view content:
select function_standard(ename) from employees;


Please post fully functional code example of a FUNCTION using column_name (as ename above) as input argument

How to decide from which table the column_name originates when it exists in more than 1 table in the FROM clause?
Re: How to get the functions name used by a column in the whole database [message #668200 is a reply to message #668052] Tue, 13 February 2018 04:20 Go to previous messageGo to next message
vishaljamdagni
Messages: 2
Registered: February 2018
Junior Member
Hey there.
In my opinion what you're trying to achieve is not possible because even if we do define a way of somehow returning the attributes which desire(functions applied in this case) it wouldn't return a single value which'll be directly conflicting the fundamental rules.
Hope this helps, pardon me if I got it wrong though.
Cheers.
Re: How to get the functions name used by a column in the whole database [message #668201 is a reply to message #668200] Tue, 13 February 2018 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is your second post and the second time you just repeat what has been previously said.
What is your purpose?

Re: How to get the functions name used by a column in the whole database [message #668204 is a reply to message #668041] Tue, 13 February 2018 10:50 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Hi Michel Cadot,

The way which i want to exclude the data is not impossible .So we are on our to develop a new approach to our requirement.Thank's for your valuable time.

Thanks and regards.

Re: How to get the functions name used by a column in the whole database [message #668205 is a reply to message #668204] Tue, 13 February 2018 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
POGAKU_SANTHOSH wrote on Tue, 13 February 2018 08:50
Hi Michel Cadot,

The way which i want to exclude the data is not impossible .

Please post working code that shows above is true.
Re: How to get the functions name used by a column in the whole database [message #668206 is a reply to message #668204] Tue, 13 February 2018 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I didn't say it is impossible I asked and you didn't answer:

Michel Cadot wrote on Thu, 01 February 2018 12:44

Can you clarify.
You want the functions which use a column in their code or the functions that are called with a column as parameter?

You have to realize that if I call your SQL statement above from SQL*Plus the database is definitively unable to know I do this, is this ok for you?
Only something that is recorded in the database may be known from the database.

Clarify your requirement.
Re: How to get the functions name used by a column in the whole database [message #668207 is a reply to message #668206] Tue, 13 February 2018 11:19 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
I am so sorry Michel .I need functions that are called with a column as parameter inside the view.

Re: How to get the functions name used by a column in the whole database [message #668208 is a reply to message #668207] Tue, 13 February 2018 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You do not answer the question you repeat what you have said, I didn't understand it, I still don't.
Post an example of what you want from what there is.
In short, what is the input, what is the output?

Re: How to get the functions name used by a column in the whole database [message #668209 is a reply to message #668205] Tue, 13 February 2018 11:30 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Hi swan/Cadot,

We have the below approach now as i am taking some sample table's now .

we have a table (temp) is has two columns (name,dob) and i am writing a view on the top of the table called as (text_vw) and (standard_character) is a function which modifies the name based on the name value which we insert .Now the script inside the view is as follows.

view_script:

select standard_character(name) from temp;

As we can see i am calling a function on the column (name) and i need functions that are called with a column as parameter inside the view.

New approach :

As we think it is impossible to obtain the function name applied on a specif columns from a view ,We are spooling the ddl of the view into a a.txt file and we are passing the column name and using grep to get the whole line ,so that we can get what sort of functions we are calling on that respective column. I hope i am clear .


Thank you.


[Updated on: Tue, 13 February 2018 11:34]

Report message to a moderator

Re: How to get the functions name used by a column in the whole database [message #668210 is a reply to message #668207] Tue, 13 February 2018 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
POGAKU_SANTHOSH wrote on Tue, 13 February 2018 09:19
I am so sorry Michel .I need functions that are called with a column as parameter inside the view.

What does this function do?
What does this function return?
Re: How to get the functions name used by a column in the whole database [message #668211 is a reply to message #668210] Tue, 13 February 2018 11:34 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
The funcion standard_character() will check for junk characters inside the value and replaces them with null
Re: How to get the functions name used by a column in the whole database [message #668212 is a reply to message #668211] Tue, 13 February 2018 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
POGAKU_SANTHOSH wrote on Tue, 13 February 2018 09:34
The funcion standard_character() will check for junk characters inside the value and replaces them with null

Function used in SQL can't do DML.
Re: How to get the functions name used by a column in the whole database [message #668213 is a reply to message #668212] Tue, 13 February 2018 11:39 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
swan,

my apologies . It cannot replace the data but in the view we can see the without junk characters rite

sample:

select name from temp;

o/p

$%^herbert

select name from text_vw;

o/p

herbert

on the top of view we can do analysis . Correct me if i am wrong .

[Updated on: Tue, 13 February 2018 11:40]

Report message to a moderator

Re: How to get the functions name used by a column in the whole database [message #668214 is a reply to message #668208] Tue, 13 February 2018 11:47 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
michel,

In short if a pass column called (name) and view name (text_vw) to a select statement ,It should return me the (standard_character) (i.e I am trying to get what sort of function we are applying on the column which we passed as the input to the select statement)


Thank you.
Re: How to get the functions name used by a column in the whole database [message #668215 is a reply to message #668209] Tue, 13 February 2018 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
POGAKU_SANTHOSH wrote on Tue, 13 February 2018 18:30
Hi swan/Cadot,

We have the below approach now as i am taking some sample table's now .

we have a table (temp) is has two columns (name,dob) and i am writing a view on the top of the table called as (text_vw) and (standard_character) is a function which modifies the name based on the name value which we insert .Now the script inside the view is as follows.

view_script:

select standard_character(name) from temp;

As we can see i am calling a function on the column (name) and i need functions that are called with a column as parameter inside the view.

New approach :

As we think it is impossible to obtain the function name applied on a specif columns from a view ,We are spooling the ddl of the view into a a.txt file and we are passing the column name and using grep to get the whole line ,so that we can get what sort of functions we are calling on that respective column. I hope i am clear .


Thank you.
Tell me if I understand.
If you have this view:
create view dept_view as 
select d.dname, f1(e.sal), f2(e.name), f3(d.loc), f4(1)
from emp e, dept d
where e.deptno = d.deptno
group by d.dname
containing calls to f1, f2, f3, f4
you want something (a PL/SQL block, a function, a procedure, whatever) which if you pass it "dept_view" and "emp" will return "f1, f2" (or the like); if you pass it "dept_view" and "dept" will return "f3".

Is this what you want?

Re: How to get the functions name used by a column in the whole database [message #668216 is a reply to message #668214] Tue, 13 February 2018 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
POGAKU_SANTHOSH wrote on Tue, 13 February 2018 18:47
michel,

In short if a pass column called (name) and view name (text_vw) to a select statement ,It should return me the (standard_character) (i.e I am trying to get what sort of function we are applying on the column which we passed as the input to the select statement)


Thank you.
Don't understand this, give example of input and output for my view.

Re: How to get the functions name used by a column in the whole database [message #668217 is a reply to message #668214] Tue, 13 February 2018 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
POGAKU_SANTHOSH wrote on Tue, 13 February 2018 09:47
michel,

In short if a pass column called (name) and view name (text_vw) to a select statement ,It should return me the (standard_character) (i.e I am trying to get what sort of function we are applying on the column which we passed as the input to the select statement)


Thank you.
FUNCTION returns a SINGLE value/result!

What gets returned for a multi-row table?
Re: How to get the functions name used by a column in the whole database [message #668218 is a reply to message #668215] Tue, 13 February 2018 11:56 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
michel,

My view is structured as you mentioned

create view dept_view as
select d.dname, f1(e.sal) sal, f2(e.name) name, f3(d.loc) loc, f4(1)
from emp e, dept d
where e.deptno = d.deptno
group by d.dname

containing calls to f1, f2, f3, f4


Note : I added alias for the columns in the view .

I need something (a PL/SQL block, a function, a procedure, whatever) which if i pass it "dept_view" and "sal" it should return "f1" and i pass "dept_view" and "name" it should return "f2".we are on the same page now michel.

[Updated on: Tue, 13 February 2018 11:57]

Report message to a moderator

Re: How to get the functions name used by a column in the whole database [message #668219 is a reply to message #668218] Tue, 13 February 2018 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not completely, why did you add aliases if your purpose is to check parameters of the functions, what is the need of this alias in your needs/question?
Or did you add it just to emphasize that if you have:
create view dept_view as
select d.dname, f1(e.sal), f2(e.name), f3(d.loc) loc, f4(1) sal
from emp e, dept d
where e.deptno = d.deptno
group by d.dname
and pass "dept_view" and "sal" then "f4" should not raise (which is obvious for me in your question as this is not a parameter of a function)?

Now "deptno" is in both tables, so what should be the result if there are "f5(e.deptno)" and "f6(d.deptno)" in the view definition (ignore the "where'" clause here)?
Should not there be 3 parameters to the "PL/SQL block, a function, a procedure, whatever"?

Re: How to get the functions name used by a column in the whole database [message #668220 is a reply to message #668219] Tue, 13 February 2018 12:17 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
I just added to emphasize .If i have a case as below

create view dept_view as
select d.dname, f1(e.sal) e_sal, f2(e.name) e_name, f3(d.loc) d_loc, f4(1) sal ,f5(e.deptno) e_deptno , f6(d.deptno) d_deptno
from emp e, dept d
where e.deptno = d.deptno
group by d.dname


If i want to take same column name from two different tables as you mentioned below that's the reason i am using alias as "e_deptno" and "d_deptno "in that case if i pass "dept_vw" and "d_dept_no" then o/p should be "f6".In this case.

[Updated on: Tue, 13 February 2018 12:18]

Report message to a moderator

Re: How to get the functions name used by a column in the whole database [message #668221 is a reply to message #668220] Tue, 13 February 2018 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, so the function to take are not the ones that have a column as parameter but the ones in expressions with aliases as a name of a column or derived from table name or table alias and column?
If there is no alias for an expression this one is not taken into account, a view like the one I first gave without any aliases will result to NULL for any question?

And an expression like "f4(1) sal" will it return "f4" is "sal" is asked?

Re: How to get the functions name used by a column in the whole database [message #668222 is a reply to message #668219] Tue, 13 February 2018 12:27 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
michel,

If we can make this view as simple by ignoring aliasing .

create view emp_view as
select ename,f1(sal),f2(no),f3(age) from emp;

containing calls to f1, f2, f3

I need something (a PL/SQL block, a function, a procedure, whatever) which if we pass it "emp_view" and "sal" it should return "f1" and if we pass "emp_view" and "no" then it should return "f2".

Thank you.

[Updated on: Tue, 13 February 2018 12:28]

Report message to a moderator

Re: How to get the functions name used by a column in the whole database [message #668223 is a reply to message #668222] Tue, 13 February 2018 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK restrict the question to only simple view with function with simple parameters (not like "f1(ln(sal)+comm*bonus)").
Do you accept in this restriction functions with several parameters "f2(sal,comm,bonus)" or only function with 1 parameter?

[Updated on: Tue, 13 February 2018 12:32]

Report message to a moderator

Re: How to get the functions name used by a column in the whole database [message #668224 is a reply to message #668222] Tue, 13 February 2018 12:35 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
Will parameter 1 (leftmost) always be only a view_name; not some other object type?
How to know or decide which schema owns "emp_view"?
Re: How to get the functions name used by a column in the whole database [message #668225 is a reply to message #668223] Tue, 13 February 2018 12:41 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
michel,

i accept only function with 1 parameter .
Re: How to get the functions name used by a column in the whole database [message #668228 is a reply to message #668225] Tue, 13 February 2018 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With this simple case you can do something like this.
Assuming this view:
create or replace force view emp_view as 
select d.dname, f1(e.sal) e_sal, f2(e.name) e_name, f3(d.loc) d_loc, f4(sal) sal, f5(1) 
from emp e, dept d
where e.deptno = d.deptno
group by d.dname
You have to create this function (because user_views.text is a LONG):
create or replace function get_text (p_view in varchar2) return varchar2
as
  ret long;
begin
  select text into ret from user_views where view_name = upper(p_view);
  return ret;
end;
/
then
SQL> with
  2    names as (select upper('&view') view_name, upper('&column') col from dual),
  3    text as (select get_text(view_name) text from names),
  4    nbcol as (select count(*) nbcol from user_tab_columns, names where table_name=view_name)
  5  select regexp_substr(
  6           regexp_substr(text, '\w+\((\w+\.)?'||col||'(\W|$)', 1, column_value, 'i'),
  7           '^\w+') func
  8  from names, text,
  9       table(cast(multiset(select level from dual
 10                           connect by level <= (select nbcol from nbcol))
 11                   as sys.odciNumberList))
 12  where regexp_substr(text, '\w+\((\w+\.)?'||col||'(\W|$)', 1, column_value, 'i') is not null
 13  /
Enter value for view: emp_view
Enter value for column: name
FUNC
--------------------------------------------------------------------------------------------------
f2

1 row selected.

SQL> /
Enter value for view: emp_view
Enter value for column: loc
FUNC
--------------------------------------------------------------------------------------------------
f3

1 row selected.

SQL> /
Enter value for view: emp_view
Enter value for column: sal
FUNC
--------------------------------------------------------------------------------------------------
f1
f4

2 rows selected.
Re: How to get the functions name used by a column in the whole database [message #668230 is a reply to message #668228] Tue, 13 February 2018 14:10 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
MICHEL,

Thank's for the response and your time. This thing actually works for me . The next posts from will be completely brief along with sample examples.Thank you once again.


Re: How to get the functions name used by a column in the whole database [message #668232 is a reply to message #668230] Tue, 13 February 2018 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
Now that you have a list of functions, what will you do next with this information?
Re: How to get the functions name used by a column in the whole database [message #668235 is a reply to message #668230] Tue, 13 February 2018 15:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is a case I handle which can't happen, so it can be slightly simplified to (and adding the possibility to have spaces around the "(" of the function calls):
SQL> with
  2    names as (select upper('&view') view_name, upper('&column') col from dual),
  3    text as (select get_text(view_name) text from names),
  4    nbcol as (select count(*) nbcol from user_tab_columns, names where table_name=view_name)
  5  select regexp_substr(
  6           regexp_substr(text, '\w+\s*\(\s*(\w+\.)?'||col||'\W', 1, column_value, 'i'),
  7           '^\w+') func
  8  from names, text,
  9       table(cast(multiset(select level from dual
 10                           connect by level <= (select nbcol from nbcol))
 11                   as sys.odciNumberList))
 12  where regexp_substr(text, '\w+\s*\(\s*(\w+\.)?'||col||'\W', 1, column_value, 'i') is not null
 13  /
Enter value for view: emp_view
Enter value for column: name
FUNC
-------------------------------------------------------------------------------------------------------
f2

1 row selected.

SQL> /
Enter value for view: emp_view
Enter value for column: loc
FUNC
-------------------------------------------------------------------------------------------------------
f3

1 row selected.

SQL> /
Enter value for view: emp_view
Enter value for column: sal
FUNC
-------------------------------------------------------------------------------------------------------
f1
f4

2 rows selected.
This assumes the view and column names are only upper alphabetical characters.
You can generalize this changing the \w+ and \W by the category of characters you allow using the "[]" syntax.

Re: How to get the functions name used by a column in the whole database [message #668237 is a reply to message #668235] Tue, 13 February 2018 15:44 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the end, this one is better for standard Oracle identifiers:
SQL> def view=emp_view
SQL> with
  2    names as (select upper('&view') view_name, upper('&column') col from dual),
  3    text as (select get_text(view_name) text from names),
  4    nbcol as (select count(*) nbcol from user_tab_columns, names where table_name=view_name)
  5  select regexp_substr(
  6           regexp_substr(text, '[[:alnum:]$_#]+\s*\(\s*([[:alnum:]$_#]+\.)?'||col||'[^[:alnum:]$_#]',
  7                               1, column_value, 'i'),
  8           '^[[:alnum:]$_#]+', 1, 1, 'i') func
  9  from names, text,
 10       table(cast(multiset(select level from dual
 11                           connect by level <= (select nbcol from nbcol))
 12                   as sys.odciNumberList))
 13  where regexp_substr(text, '[[:alnum:]$_#]+\s*\(\s*([[:alnum:]$_#]+\.)?'||col||'[^[:alnum:]$_#]',
 14                            1, column_value, 'i')
 15         is not null
 16  /
Enter value for column: name
FUNC
------------------------------------------------------------------------------------------------------------
f2

1 row selected.

SQL> /
Enter value for column: loc
FUNC
------------------------------------------------------------------------------------------------------------
f3

1 row selected.

SQL> /
Enter value for column: sal
FUNC
------------------------------------------------------------------------------------------------------------
f1
f4

2 rows selected.
Re: How to get the functions name used by a column in the whole database [message #668241 is a reply to message #668237] Wed, 14 February 2018 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about a view:
SQL> create or replace view view_function (view_name, parameter_name, function_name) as
  2  with
  3    -- valid patterns for identifiers and characters in them
  4    -- here Oracle standard identifiers
  5    valid_patterns as (select '[a-z][[:alnum:]$_#]*' ident, '[:alnum:]$_#' chars from dual),
  6    -- retrieve text of user views
  7    view_text as (select view_name, get_text(view_name) text from user_views),
  8    -- retrieve number of columns of user views
  9    view_nbcol as (
 10      select table_name view_name, count(*) nbcol from user_tab_columns group by table_name
 11    ),
 12    -- retrieve all function expressions as defined above in the topic:
 13    --   single parameter which must be a name, possibly prefixed by an alias or a table name
 14    function_exp as (
 15      select t.view_name,
 16             regexp_substr(t.text,
 17                           p.ident||'\s*\(\s*('||p.ident||'\.)?'||p.ident||'[^'||p.chars||']',
 18                           1, column_value, 'i') fct_exp
 19      from valid_patterns p, view_text t, view_nbcol n,
 20           table(cast(multiset(select level from dual connect by level <= n.nbcol)
 21                      as sys.odciNumberList))
 22      where n.view_name = t.view_name
 23        and regexp_substr(t.text,
 24                          p.ident||'\s*\(\s*('||p.ident||'\.)?'||p.ident||'[^'||p.chars||']',
 25                          1, column_value, 'i')
 26             is not null
 27    )
 28  -- break the function expression into parameter name and function name
 29  select f.view_name,
 30         cast(upper(regexp_substr(f.fct_exp,
 31                                  '\(\s*('||p.ident||'\.)?('||p.ident||')[^'||p.chars||']',
 32                                  1, 1, 'i', 2))
 33              as varchar2(30)) parameter_name,
 34         cast(upper(regexp_substr(fct_exp, '^'||p.ident, 1, 1, 'i')) as varchar2(30)) func
 35  from function_exp f, valid_patterns p
 36  /

View created.

SQL> select parameter_name, function_name
  2  from view_function
  3  where view_name='EMP_VIEW'
  4  order by 1, 2
  5  /
PARAMETER_NAME                 FUNCTION_NAME
------------------------------ ------------------------------
LOC                            F3
NAME                           F2
SAL                            F1
SAL                            F4

4 rows selected.

[Updated on: Wed, 14 February 2018 02:29]

Report message to a moderator

Re: How to get the functions name used by a column in the whole database [message #668242 is a reply to message #668232] Wed, 14 February 2018 04:12 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Hi swan,

We need to generate a report on this list of functions what we get .
Re: How to get the functions name used by a column in the whole database [message #668243 is a reply to message #668241] Wed, 14 February 2018 04:17 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Hi michel,

This view will help me a lot . I can maintain all the parameters along with the functions used for them.
Thank you once again for this approach .


Thank you.
Re: How to get the functions name used by a column in the whole database [message #668250 is a reply to message #668243] Wed, 14 February 2018 10:14 Go to previous message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My view does not really satisfy to retrieve "function expressions with a single parameter which must be a name, possibly prefixed by an alias or a table name", below a new one which satisfies this.
In addition, we are limited to 4000 bytes for view text. A better function to retrieve it is the following one; it "compresses" all spaces to a single one.
SQL> create or replace function view_function_text (p_view in varchar2) return varchar2
  2  as
  3    text varchar2(32767);
  4  begin
  5    select text into text from user_views
  6      where view_name = upper(p_view) and text_length <= 32767;
  7    text := regexp_replace(text, '\s+', ' ');
  8    return substr(text, 1, 4000);
  9  exception when no_data_found then return null;
 10  end;
 11  /

Function created.

SQL> create or replace view view_function (view_name, parameter_name, function_name) as
  2  with
  3    -- valid patterns for identifiers and characters in them
  4    -- here Oracle standard identifiers
  5    valid_patterns as (select '[a-z][[:alnum:]$_#]*' ident, '[:alnum:]$_#' chars from dual),
  6    -- retrieve text of user views
  7    view_text as (select view_name, view_function_text(view_name) text from user_views),
  8    -- retrieve number of columns of user views
  9    view_nbcol as (
 10      select table_name view_name, count(*) nbcol from user_tab_columns group by table_name
 11    ),
 12    -- retrieve all function expressions as defined above in the topic:
 13    --   single parameter which must be a name, possibly prefixed by an alias or a table name
 14    function_exp as (
 15      select t.view_name,
 16             regexp_substr(t.text,
 17                           p.ident||'\s*\(\s*('||p.ident||'\.)?'||p.ident||'\s*\)',
 18                           1, column_value, 'i') fct_exp
 19      from valid_patterns p, view_text t, view_nbcol n,
 20           table(cast(multiset(select level from dual connect by level <= n.nbcol)
 21                      as sys.odciNumberList))
 22      where n.view_name = t.view_name
 23        and regexp_substr(t.text,
 24                          p.ident||'\s*\(\s*('||p.ident||'\.)?'||p.ident||'\s*\)',
 25                          1, column_value, 'i')
 26             is not null
 27    )
 28  -- break the function expression into parameter name and function name
 29  select f.view_name,
 30         cast(upper(regexp_substr(f.fct_exp,
 31                                  '\(\s*('||p.ident||'\.)?('||p.ident||')\s*\)',
 32                                  1, 1, 'i', 2))
 33              as varchar2(30)) parameter_name,
 34         cast(upper(regexp_substr(fct_exp, '^'||p.ident, 1, 1, 'i')) as varchar2(30)) func
 35  from function_exp f, valid_patterns p
 36  /

View created.

SQL> select parameter_name, function_name
  2  from view_function
  3  where view_name='EMP_VIEW'
  4  order by 1, 2
  5  /
PARAMETER_NAME                 FUNCTION_NAME
------------------------------ ------------------------------
LOC                            F3
NAME                           F2
SAL                            F1
SAL                            F4

4 rows selected.
In the following we can see that "f8(name,sal)" is ignored.
Does ZZZZZZ/F7 should be returned from the expression "f6(f7(zzzzzz))"?
Also note there can be false result if there is a WHERE clause containing a function call (see ZZZZZZ/F9 from "f9(zzzzzz) = 1").
SQL> create or replace force view emp_view2
  2    (dname, deptno, e_sal, e_name, d_loc, sal, ignore1, valid, ignore2, cnt)
  3  as
  4  select d.dname, d.deptno, f1(e.sal), f2(e.name), f3(d.loc), f4(sal), f5(1),
  5         f6(f7(zzzzzz)), f8(name,sal), count(empno)
  6  from emp e, dept d
  7  where e.deptno = d.deptno
  8    and f9(zzzzzz) = 1
  9  group by d.dname, d.deptno
 10  /

Warning: View created with compilation errors.

SQL> select parameter_name, function_name
  2  from view_function
  3  where view_name='EMP_VIEW2'
  4  order by 1, 2
  5  /
PARAMETER_NAME                 FUNCTION_NAME
------------------------------ ------------------------------
EMPNO                          COUNT
LOC                            F3
NAME                           F2
SAL                            F1
SAL                            F4
ZZZZZZ                         F7
ZZZZZZ                         F9

7 rows selected.
Another question is: do you want standard function like COUNT?

[Updated on: Thu, 15 February 2018 23:46]

Report message to a moderator

Previous Topic: schedule job for multi threading
Next Topic: BULK COLLECT ISSUE
Goto Forum:
  


Current Time: Fri Oct 19 21:01:57 CDT 2018