rowtype pl/sql function in a view
I am trying to use a function I have built inside a view and I want to pass the whoel row into the function.
create or replace view Mac_Commis_Rev_Dtl_ial as
select
x.cest_customer_no
,JA_TEST.Commission_Zip (u.* ,x.* ) Zip_code
from mac_commission_revenue_tab
,mac_commission_product_gr
where u.product_group_code = x.cest_product_group_code
WITH read only;
my function works inside a normal pl/sql block, but I want to generaically pass the row to the function. This will help me with maintanence.
I dont want to list out each column name from the table, just want to pass the row
Currently I get a ORA-01747 error
create or replace view Mac_Commis_Rev_Dtl_ial as
select
x.cest_customer_no
,JA_TEST.Commission_Zip (u.* ,x.* ) Zip_code
from mac_commission_revenue_tab
,mac_commission_product_gr
where u.product_group_code = x.cest_product_group_code
WITH read only;
my function works inside a normal pl/sql block, but I want to generaically pass the row to the function. This will help me with maintanence.
I dont want to list out each column name from the table, just want to pass the row
Currently I get a ORA-01747 error
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Naveen Kumar
but i think it can work if you have created a type as a database object and used this type in your table and you have created the function to receive this type object and then internally in your function code you can access each member ( basically each column ) of this type object accordingly.
using * to refer to all the fields may be simple and help at some times but beware of the other side as well like ( what happens to your function if one existing column is dropped from the table or a new column is added to the table or some column is renamed to some other name i,e your function has to be written in a generic fashion to take care of all these cases )
" my function works inside a normal pl/sql block " --> what does this mean ? Can you give sample code here to understand this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it cant be done !!!!! oh well hope oracle an come up with an improvement in their SQL to handle this ...