Oracle Forms Dynamic Data Source

i google it first , i found that article , but i didnt understand it
could you please explain it step by step
thanx

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

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.

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.

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

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
https://www.experts-exchange.com/questions/28687901/Error-Writing-On-A-Non-Database-Form.html
which i not yet finalize it

>> 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.

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

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.

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.

How to do it ?

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?

Look at the script of my view , then I need dynamic date rAnge for it

>>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.

Ok Mark , how to create the GTT and what the the procedure speak about ? Any example please

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;
/

Select allOpen in new window

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...

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,

Greaat mark , I understood it now , it is very simple , will try it tomorrow ,

Slightwv , what is the meaning of : dynamic column ?

>>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
/

Sligtwv , dob is not a column in my view , how I can include where clause on it ?

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.

Yes , I agree , but let also see if slightwv may success to include dob in the view and got summary of sales per group

>>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.

>> 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.

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

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 ?

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.

Let me see tomorrow if I can do it as per my data ,

If I fail , I will do the GTT , with full thanx to every body

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 ?

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.

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) ||'))'));

Select allOpen 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;
/

Select allOpen in new window

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.

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.

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;

Select allOpen in new window

>> 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.

Look the syntax of sql_string ,is it correct ?

>>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.

No it had no index , and dob had no time part

"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.

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

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

>>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...

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

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.

I settled them both as date , with mm/dd/yyyy format

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.

"unable to perform query"

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?

so why the temp function
select * from table(my_pipe_func('where col2=1'));
works and return a record ?

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.

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;

Select allOpen in new window

it return error : unable to perform query

Please, after receiving an error (unable to perform query) click Ctrl + Shift + E for displaying more information about the error and post it here.

ok
now i investigate it
when i used this statement in the block property

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

Select allOpen 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'))

Select allOpen in new window

it return unable to perform query
shift + f1 :
not all variable bound
error function line 9 ( which is the select )

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;

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 , ?

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.

For your solution about GTT it completely success and I have no issue about it , I'm just asking about your other suggestion here.           https://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

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.

So , what will be the use of that procedure ?

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)

Great , it will be very useful for me in another form , but how to use it , or where to use the different select statement ?

"how to use it?"  
What do you mean by "it" here?

"where to use the different select statement ?"
Which select statement?

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 ?

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.

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