Oracle Forms Dynamic Data Source

hi
i have oracle form based on view
then i would select only data between 2 range of date based on a non_database items in the form
but as view not accepting parameters
is there any way to set the query from clause as a trigger in the form
here is the view

create or replace view cat_sum as
select M_CAT_ID, M_CAT_NAME , CAT_ID , CAT_NAME,gross, net, discount, 
(to_char(nvl(100 * (gross/sum(gross) over()),0),'fm9990.00')||'%' ) Cat_Perc,
(to_char(nvl(100 * (gross/sum(gross) over(partition by M_CAT_ID)),0),'fm9990.00')||'%' ) In_m_Cat_Perc,
(to_char(nvl(100 * (Discount/gross),0),'fm9990.00')||'%') Disc_Perc,
 sum(gross) over() total_price from (
select M_CAT_ID, M_CAT_NAME , CAT_ID , CAT_NAME,
sum(PRICE) Gross,sum(DISCPRIC) Net,(sum(discount)) Discount , sum(quantity) QTY 
from 
sales_det
group by M_CAT_ID, M_CAT_NAME , CAT_ID , CAT_NAME )
order by gross desc ;

Open in new window


i want to view data block based on a value of 2 non_database items for the field : dob = date
dob is a field of view sales_det
another problem also , "over" is not identified by oracle forms 6i ,
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wasim Akram ShaikCommented:
Parameterized view is possible..

google over it.. you will get many examples to do so..

https://asktom.oracle.com/pls/asktom/f?p=100:11%3A0%3A%3A%3A%3AP11_QUESTION_ID:906341500346611919


In your case, you can use an alternative approach, use context data in the form, and pass it to a procedure in oracle database from your form module..

in the procedure you will insert the data in your temp table(which you have to create one)
make the form to select data from this form.. as this is temp table,. data will get flush out and each user will have his own data.. this will work in 6i too..
NiceMan331Author Commented:
i google it first , i found that article , but i didnt understand it
could you please explain it step by step
thanx
Wasim Akram ShaikCommented:
see this link.. the author has explained in a detailed way possible.. you can refer and use option no.3 and pass the parameters to initialize from your form module

http://halimdba.blogspot.in/2010/04/how-to-pass-parameter-to-oracle-view-1.html
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark GeerlingsDatabase AdministratorCommented:
The easiest way to do this is to include the date in your view, then you can simply query the date(s) as database column values in your forms.  This also allows you to include newer SQL operators (like: "over") in the view definition that Forms6 does not support directly.

I'm not sure that including the "to_char" operators and the "order by" in your view definition are the best way to get the job done, but that can work if you always use that data that way.
slightwv (䄆 Netminder) Commented:
Why do you need to pass parameters to the view?

I'm not understanding what this will do for you.  If you want to limit the view on some DOB column, just add the where clause when you select from the view.
NiceMan331Author Commented:
slightwv
If you want to limit the view on some DOB column, just add the where clause when you select from the view.
no , i want to every time change the creteria of within a form , so adding it to the view will fix the range of data , while i need all records , but when i select specific date every time to return related records
NiceMan331Author Commented:
markger
The easiest way to do this is to include the date in your view
yes , but the view giving summary of records , if i include the date , it will not gives the summary
and , it still related to this topic
http://www.experts-exchange.com/questions/28687901/Error-Writing-On-A-Non-Database-Form.html
which i not yet finalize it
slightwv (䄆 Netminder) Commented:
>> i want to every time change the creteria of within a form

I still do not understand.  It sounds like you want a dynamic where clause based on values selected on the form.  Sort of like Query By Example (QBE)?

If so, you don't need parameters in a view.
NiceMan331Author Commented:
If so, you don't need parameters in a view.
look at the view script , "dob" is not included in
"where" clause will not work here
slightwv (䄆 Netminder) Commented:
I'm pretty sure a parameterized view will NOT let you dynamically build columns in the query.

The columns returned need to be known at parse time.  A view is a psrsed/stored query.

If you want dynamic columns with the where clause, I would probably go with a function that returns a cursor or pipelined data.
Mark GeerlingsDatabase AdministratorCommented:
Another approach to the problem would be to have two non-database date fields in your form, and a trigger (or button) in the form to pass the two date values to a stored procedure that populates a global temporary table, then your form database block can query the global temporary table.
NiceMan331Author Commented:
How to do it ?
slightwv (䄆 Netminder) Commented:
How to do what?

I'm still trying to understand the requirements.

Do you want dynamic columns and a where clause based on fields selected off the form?
NiceMan331Author Commented:
Look at the script of my view , then I need dynamic date rAnge for it
Mark GeerlingsDatabase AdministratorCommented:
Create a form with two blocks:
1. a small non-database block that contains two date fields, and a button
2. a database block based on a Global Temporary Table (or: GTT)

Create the GTT
Create a stored PL\SQL procedure to accept two date parameters, then do your query and insert the results into the GTT.
Write a when-button-pressed trigger in the form to: clear the GTT, call the procedure, then automatically query the database block.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>>Look at the script of my view ,

I have.  I still don't understand what is "dynamic" about it.

>>then I need dynamic date rAnge for it

To me, that sounds like a where clause.
NiceMan331Author Commented:
Ok Mark , how to create the GTT and what the the procedure speak about ? Any example please
Mark GeerlingsDatabase AdministratorCommented:
I don't know what your column lengths are for the varchar2 columns, and I don't know what lengths and/or number of decimal positions you want for the number columns.  You have to provide those values in place of the "?" markers.

create global temporary table cat_sum_gt
(M_CAT_ID		number(?), 
 M_CAT_NAME 	varchar2(??), 
 CAT_ID 		number(?), 
 CAT_NAME		varchar2(??),
 gross			number(??,?), 
 net			number(??,?), 
 discount		number(??,?),
 Cat_Perc		number(??,?),
 In_m_Cat_Perc	number(??,?),
 Disc_Perc		number(??,?),
 total_price	number(??,?))
 on commit preserve rows;
 
create or replace procedure populate_cat_sum (from_date in date, to_date in date) as
begin
-- Clear the GTT first, just to make sure it is empty:
  delete cat_sum_gt;
  insert into cat_sum_gt
   ( [list of column names] )
  select M_CAT_ID, M_CAT_NAME , CAT_ID , CAT_NAME,gross, net, discount, 
	(to_char(nvl(100 * (gross/sum(gross) over()),0),'fm9990.00')||'%' ) Cat_Perc,
	(to_char(nvl(100 * (gross/sum(gross) over(partition by M_CAT_ID)),0),'fm9990.00')||'%' ) In_m_Cat_Perc,
	(to_char(nvl(100 * (Discount/gross),0),'fm9990.00')||'%') Disc_Perc,
	sum(gross) over() total_price from (
		select M_CAT_ID, M_CAT_NAME , CAT_ID , CAT_NAME,
		sum(PRICE) Gross,sum(DISCPRIC) Net,(sum(discount)) Discount , 
	sum(quantity) QTY 
  from sales_det
  where [DOB_column] between from_date and to_date
  group by M_CAT_ID, M_CAT_NAME , CAT_ID , CAT_NAME );
end;
/

Open in new window

slightwv (䄆 Netminder) Commented:
Mark,

That looks to me just like a dynamic where clause.  If that is all that is needed, I don't see the need for the GTT and procedure.

I think the requirement is dynamic columns and a dynamic where clause but who knows...
Mark GeerlingsDatabase AdministratorCommented:
I only offered the GTT and procedure combination as something to consider *IF* a dynamic where clause by itself can't get the job done,
NiceMan331Author Commented:
Greaat mark , I understood it now , it is very simple , will try it tomorrow ,
NiceMan331Author Commented:
Slightwv , what is the meaning of : dynamic column ?
slightwv (䄆 Netminder) Commented:
>>Slightwv , what is the meaning of : dynamic column ?

You want to select different columns based on the selected form values.

So if you select nothing:
select col1, col2, col3 from some_table;

If you select a date you now want to change the columns you select:
select col1, col2, col3, SOME_DATE from some_table;

If that isn't what you want, then you only want to dynamically change the WHERE clause.  See below...


>>it is very simple , will try it tomorrow

If that works, why wouldn't a dynamic where clause?  The dynamic where clause is sooooooo much easier and doesn't require a new table or procedure.

Just write the static select in such a way that ALL possible forms selections are "optional".

select * from your_view
where
(
     ([DOB_column] between from_date and to_date)
     or
     (from_date is null and to_date is null)
)
and
...next set of values
/
NiceMan331Author Commented:
Sligtwv , dob is not a column in my view , how I can include where clause on it ?
Mark GeerlingsDatabase AdministratorCommented:
That is why I suggested adding that column to your view, so it could be included an a "where" clause, but you indicated you want the view to summarize the data without including the date.  That's why I suggested the GTT/procedure combination as an option instead.  I'm not convinced that adding a date to your view is a bad idea, but depending on the data volumes involved, it may make sense to use the GTT/procedure combination instead.
NiceMan331Author Commented:
Yes , I agree , but let also see if slightwv may success to include dob in the view and got summary of sales per group
slightwv (䄆 Netminder) Commented:
>>dob is not a column in my view , how I can include where clause on it ?

AH, NOW I understand what you are asking...  Yes, that could be a problem...

Now the GTT makes sense.
slightwv (䄆 Netminder) Commented:
>> and got summary of sales per group

You might be able to add "partition by" to the OVER to get the same summaries and include the date column in the view.

for us to know for sure, I would need sample data and expected results so what I come up with matches what you have.
slightwv (䄆 Netminder) Commented:
Will forms 6i allow a table function in the select?

This approach might be more flexible in that you pass in whatever where clause you want.  No need to change/add procedure parameters.

The down side is you still need to create database objects.

drop table tab1 purge;
create table tab1(col1 char(1), col2 number);
insert into tab1 values('a',1);
insert into tab1 values('b',2);
commit;

drop type my_type_tab;
drop type my_type;

create or replace type my_type as object(
	col1 char(1),
	col2 number,
	total number
);
/

create or replace type my_type_tab is table of my_type;
/

create or replace function my_pipe_func(p_where_clause in varchar2) return my_type_tab pipelined
is
	v_rec my_type := my_type(null,null,null);
	v_cur sys_refcursor;

begin

	open v_cur for 'select col1, col2, sum(col2) over() total from tab1 ' ||
				p_where_clause ||
				' order by col1 desc ';

	loop
		fetch v_cur into v_rec.col1, v_rec.col2, v_rec.total;
		exit when v_cur%NOTFOUND;
		pipe row (v_rec);
		end loop;

	close v_cur;
end;
/

show errors

select * from table(my_pipe_func('where col2=1'));
select * from table(my_pipe_func('where col2=2'));

Open in new window

Wasim Akram ShaikCommented:
I suggested the same approach nice man .. Create a temp table in the very first comment... Any how that's the simplest one.. Parameterized view or setting context from form will reduce the need for creating temp table.. As results can be fetched dynamically
NiceMan331Author Commented:
Slightwv , it seems nice , but , may you explain it ? Is it like you create a matrix an fill the records into , then finally you select all records of this matrix ?
slightwv (䄆 Netminder) Commented:
You create a 'record' that has all the columns you want returned:  my_type

This is what you want your result set to look like.  For you, it will have every column in your select.

You then create a table of those.

Then the pipelined function returns one record at a time from the cursor loop.
NiceMan331Author Commented:
Let me see tomorrow if I can do it as per my data ,
NiceMan331Author Commented:
If I fail , I will do the GTT , with full thanx to every body
NiceMan331Author Commented:
Slightwv , here in the wizard of creating the  block , what I will select as source of the data block ? And then , if I have another 4 data blocks having same scenario but with different selections and different numbers of columns , do I need to create types and functions for every one or I can use one type and function ?
slightwv (䄆 Netminder) Commented:
As long as you are selecting the same columns from the same table and the only thing changing is the where clause create everything once.  Same for Mark's GTT example.

Then all you do is keep changing the where clause.

As far as what goes with what Forms Block, I have no idea.  I'm not a Forms person.

What you need to call in Forms is just the select with the correct where parameter:
select * from table(my_pipe_func('where col2=2'));

That is as long as 6i accepts the TABLE syntax in that select.
NiceMan331Author Commented:
slightwv
i created the objects in database
types , m_type & function
then i have problem in date format
i tried to run the function like this

select * from table(str_type_func('where trunc(dob) = trunc(to_date(' || chr(39) || '6/15/2015' || chr(39) || ',' || chr(39) || 'mm/dd/yyyy' || chr(39) ||'))'));

Open in new window


it returns error , not a valid month
select trunc(sysdate) from dual;
6/17/2015
and here is the creation of the function

create or replace function str_type_func(p_where_clause in varchar2) return str_type_tab pipelined
is
    v_rec str_type := str_type(null,null,null,null,null);
    v_cur sys_refcursor;

begin
 

open v_cur for 
' select STR_NO , sum(gross) gross, sum(net) net, sum(discount) disc, count(dob) d_count from (
select to_number(a.STR_NO) str_no,b.dob ,nvl(sum(b.PRICE),0) Gross,nvl(sum(b.DISCPRIC),0) Net,(nvl(sum(b.PRICE),0) - nvl(sum(b.DISCPRIC),0)) Discount 
from 
cr_store a
left join cr_gnditem b on a.str_no = B.UNIT
WHERE a.STR_ACT = 1
and ' || p_where_clause || '
group by a.str_no,b.dob
order by sum(b.PRICE) desc 
)
group by str_no ' ;

         
  
    loop
        fetch v_cur into v_rec.str, v_rec.grs, v_rec.net,v_rec.disc,v_rec.d_c;
        exit when v_cur%NOTFOUND;
        pipe row (v_rec);
        end loop;

    close v_cur;
end;
/

Open in new window

slightwv (䄆 Netminder) Commented:
After adding a dob column to my test table, this works for me:
select * from table(str_type_func('where trunc(dob) = trunc(to_date(''6/15/2015'',''mm/dd/yyyy''))'));

If you still get the error, the issue has to be in the function.
Manually reconstruct and run the select to make sure it is OK.


FYI:  if you have time portions in your dob column, your group by in the function might produce bad results since you are grouping on dob.
Mark GeerlingsDatabase AdministratorCommented:
Even if you get this syntax to work, I don't like this approach from a performance perspective, because you are using a SQL operator (trunc) on a database column value (dob).  That usually prevents Oracle from using an index on this column, and forces Oracle to read and convert every value in this column (at least those values that meet any other "where" clause criteria) .  If your table has only a small number of records, or if query performance is not an issue in your system. then this may not matter for you.
NiceMan331Author Commented:
markger , what is your opinion so ?
slightwv
the error is mine , i put 'where' which should not be
now the sql working well
remaining in the form
i have it now too big and not works again
declare
sql_string varchar2(2000);
begin
sql_string:= 'select * from table(str_type_func('||chr(39)||'trunc(dob) >= trunc(to_date('':BASIC_BLOCK.d_from'',''mm/dd/yyyy'')) and trunc(dob) <= trunc(to_date('':BASIC_BLOCK.d_TO'',''mm/dd/yyyy''))'||chr(39)||'))';
set_block_property('STR_SUM',QUERY_DATA_SOURCE_NAME,sql_string);

go_block('STR_SUM');
clear_block;
execute_query;

end;

Open in new window

slightwv (䄆 Netminder) Commented:
>> i put 'where' which should not be

Yep, missed that.  I didn't look at it since I didn't have that in my example.

>>i have it now too big and not works again

I do not know what this means.
NiceMan331Author Commented:
Look the syntax of sql_string ,is it correct ?
slightwv (䄆 Netminder) Commented:
>>Look the syntax of sql_string ,is it correct ?

I don't know your data or tables so I have no way to know what is "correct".

If it runs, then the syntax is OK.

I second what Mark posted about the TRUNC on the DOB column.  If you have an index on that column, TRUNC will cause it to be ignored.
NiceMan331Author Commented:
No it had no index , and dob had no time part
Mark GeerlingsDatabase AdministratorCommented:
"what is your opinion?"
My opinion (and my recommendation) is that SQL operators, including: to_char, to_date, upper, lower, trunc, etc. should be avoided on database column values that are referenced in "where" clauses of SQL statements.  These can be used in the "select..." portion of the statement, or on bind variable values with very little impact on performance.  But, if you use these on database column values in "where" clauses, these will usually cause performance problems.
NiceMan331Author Commented:
Slightwv , the SQL works well , I just copied to the trigger of the form , it gives no data select , and when I replaced date value by the item name in the forms it return error enable to perform query , please just check the concat is correct or not in my trigger
Mark GeerlingsDatabase AdministratorCommented:
I suspect that query syntax is not directly supported in Oracle Forms6i.
slightwv (䄆 Netminder) Commented:
>>please just check the concat is correct or not in my trigger

I don't like using the concat with chr(39).  Look at what I posted, you just need to double up the single quotes where necessary.

you also don't need TRUNC on the dates with TO_DATE.  All TRUNC does is zero out the time portion.  If you don't provide it in the TO_DATE calls, it is zero by default.

Does this still return an error?

select * from table(str_type_func(' trunc(dob) < trunc(to_date(''6/15/2025'',''mm/dd/yyyy'')) '));

Notice I picked a date WAY in the future.

If not, you'll need to copy the select into a script and manually run it in something like sqlplus.

If you are still getting the "not a valid month" error, triple check all the strings you are converting to dates.

I assume DOB is a date data type and not a VARCHAR2/CHAR?

>>I suspect that query syntax is not directly supported in Oracle Forms6i.

That could be!  I mentioned that as long as 6i supported it...
NiceMan331Author Commented:
Yes , dob is date format                                       Mark , the very first function of slightwv works well in my form and return a record , I'm sure here my problem is in the format of date and may be also in the numbers of qouuts
slightwv (䄆 Netminder) Commented:
The "not a valid month" error is from a date issue.  It is either from converting a string to a date with to_date or converting a date to a string using to_char.

Make sure :BASIC_BLOCK.d_from and :BASIC_BLOCK.d_from  are strings and have the format mm/dd/yyyy.  If not, that is the problem.
NiceMan331Author Commented:
I settled them both as date , with mm/dd/yyyy format
slightwv (䄆 Netminder) Commented:
Does this return the same error?

select * from table(str_type_func(' dob < sysdate+500 '));

If that returns the month error, the issue is I the function not in the where clause.
NiceMan331Author Commented:
"unable to perform query"
Mark GeerlingsDatabase AdministratorCommented:
That is likely because Oracle Forms6i does not support this SQL syntax: "select * from table(...)".  Did you get that syntax to work at all in Forms6i, even if you make it as simple as possible?
NiceMan331Author Commented:
so why the temp function
select * from table(my_pipe_func('where col2=1'));
works and return a record ?
Mark GeerlingsDatabase AdministratorCommented:
Good question!  That makes it look like Forms6i actually does support this syntax.  Or, maybe it doesn't actually have to interpret it.  Maybe it just passes "select ...;" to the database, and lets the database figure out what to do, and what to pass back.
NiceMan331Author Commented:
ok , mark
the problem not in the function , it is in the form itself
i created another block based
i make data source from clause
query_data_source_name ; = select * from str_sum (which is view having same syntax)
then , when i execute query it success
but when i used this code in push button trigger
declare
sql_string varchar2(9000);
p_where_clause varchar2(2000);
begin
	sql_string := 'select * from str_sum';

set_block_property('STR_SUM1',QUERY_DATA_SOURCE_NAME,sql_string);
message(sql_string);
go_block('STR_SUM1');
clear_block;
execute_query;

end;

Open in new window


it return error : unable to perform query
Helena Markováprogrammer-analystCommented:
Please, after receiving an error (unable to perform query) click Ctrl + Shift + E for displaying more information about the error and post it here.
NiceMan331Author Commented:
ok
now i investigate it
when i used this statement in the block property
select * from table(str_type_func('dob < sysdate + 500'))

Open in new window

and F8 , it return the records
but , when i used this statement in the property also
select * from table(str_type_func('dob = :BASIC_BLOCK.d_from'))

Open in new window

it return unable to perform query
shift + f1 :
not all variable bound
error function line 9 ( which is the select )
Helena Markováprogrammer-analystCommented:
I have made some modification to your code, please, try it:

declare
sql_string varchar2(9000);
p_where_clause varchar2(2000);
begin
      sql_string := 'select * from str_sum';
go_block('STR_SUM1');

clear_form;
set_block_property('STR_SUM1',QUERY_DATA_SOURCE_NAME,sql_string);
message(sql_string);

execute_query;

display_error;

end;
NiceMan331Author Commented:
Thanx henka , same result , no change ,            I'm working now with the solution of markger about GTT , I did one data block , it success without any problem , but I'm still mark looking to apply your solution about change record source of the block , what I should use for variable : new source in your procedure , ?
Mark GeerlingsDatabase AdministratorCommented:
No, for my suggestion to use a procedure and a GTT, you don't need to change the data source dynamically.  You create the GTT first, then you can create the procedure to populate the GTT and the database block to query it.
NiceMan331Author Commented:
For your solution about GTT it completely success and I have no issue about it , I'm just asking about your other suggestion here.           http://www.experts-exchange.com/questions/28687901/Error-Writing-On-A-Non-Database-Form.html.                         Please look about the procedure you write it regarding change record source , I didn't understand how to apply it here in this example if I want to use my SQL here as a source of the block
Mark GeerlingsDatabase AdministratorCommented:
I've never tried using dynamic SQL as the data source for a block.  I was always able to create the database views that I needed for any Forms blocks that I wanted to base on something other than a table.
NiceMan331Author Commented:
So , what will be the use of that procedure ?
Mark GeerlingsDatabase AdministratorCommented:
That procedure is useful if you already have two (or more) different tables and or views (with identical column structures) that you want a single form to be able to query from any of them, and you want to change the data source for the block dynamically to one of these tables or views.

We used that technique in a single form that could look at transaction history records for a manufacturing system for different time-spans.  Recent records were in a table partitioned by week.  Older records were summarized and put into a different table that was partitioned by month.  We used three different views to present data for:
1. the current week only
2. current + recent weeks
3. all data (based on a "union" view)
NiceMan331Author Commented:
Great , it will be very useful for me in another form , but how to use it , or where to use the different select statement ?
Mark GeerlingsDatabase AdministratorCommented:
"how to use it?"  
What do you mean by "it" here?

"where to use the different select statement ?"
Which select statement?
NiceMan331Author Commented:
For example the block based on :    Select * from mytable.                                         Then I want it to be based on have view which is ,    Select Id, amount from mytable Union select id,amount from g_mytable.    , how to change the data source in your procedure ?
Mark GeerlingsDatabase AdministratorCommented:
My procedure was written to accept an input parameter, then construct the full name of the source based on a literal value plus the parameter.   You could change that a bit to just use exactly what you pass in as the parameter.  That may work for you.
NiceMan331Author Commented:
i would like to thank all who share in this question : markger , slightwv , wasim & henka
actually both of markger & slightwv provided correct solution , but the GTT is the one which works with me without any trouble
the function of slightwv is very nice , but for sorry it didn't work in my form , but it is correct
thanx for all again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.