Forms :: Dynamically Passing Where Condition
Jul 4, 2011
I am working on 6i reports.I have below scenario to achieve.
I have FIND window and MAIN Window.
FIND Window: contains EMPNO, ENAME,and FIND button.
MAIN WINDOW: EMPNO, ENAME, SAL, JOB
If i enter something in FIND window, by clicking FIND button, it will populate data in MAIN window.I have below query in WHEN BUTTON PRESSED TRIGGER.
declare
cursor c1 is select empno, ename, job, sal from emp
where empno=nvl(:FIND.empno,empno)
and ename=nvl(:FIND.ename,ename)
begin
--here i am populating data into MAIN window block
end;
It's working fine.
REQUIREMENT: i want to pass where statement dynamically. Let us say if i pass only empno in FIND window, i want to pass 'WHERE empno=:FIND.empno' to existing select statement [I will keep select statement same without where condition]
If i pass empno and ename both, i want to pass 'WHERE empno=:FIND.empno and ename=:FIND.ename.
How to pass where condition to existing select statement based on parameters we pass in FIND window.
View 8 Replies
ADVERTISEMENT
Aug 5, 2013
I am trying a pl/sql block which which take a string and execute it dynamically. Suppose below is string
M_COL := Q'[(P_CODE=> ':DEPTNO',P_CODE_TYPE => 'STATE')]';
Now trying to execute it. using below
M_STR := 'SELECT CHK_DEPT' || M_COL || ' FROM EMP WHERE EMPNO=''7499''';
EXECUTE IMMEDIATE M_STR
INTO M_DATE;
Now what i want is M_STR vairable to executed as
SELECT CHK_DEPT(P_CODE=> DEPTNO,P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
instead of
SELECT CHK_DEPT(P_CODE=> 'DEPTNO',P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
other in other way in parameter P_CODE Column value of DEPTNO should be passed.Also note that DEPTNO column in string M_COL is Dynamic.i.e
M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]';
View 9 Replies
View Related
Sep 6, 2010
I am passing the column name for a rowtype dynamically and it is considered as a string by oracle and not giving the value of rowtype.column.i have ANONY 1 where i am passing value to attribute1 and ANONY 2 i am passing value as NULL.
IF l_comp_column IS NOT NULL AND l_mand_column = 'Y'
THEN
dbms_output.put_line(l_comp_column ||' IS NOT NULL');
l_flag := 'Y';
ELSIF l_comp_column IS NOT NULL AND l_mand_column = 'N'
THEN
l_flag := 'N';
END IF;
[code]....
View 9 Replies
View Related
Dec 27, 2012
I have the following control table.
ID S_OWNER SOURCE_TABLE A_OWNER ARC_TABLE CONDITION_COLUMN PERIOD_VALUE PERIOD_UNIT
1 wedb Auction_table wedb Arc_Auction_tableAuction_date 15 Days
1 wedb Sales_table wedb ArcSales_table Sales_date 180 Days
1 hr Accounts_table hr Arc_Accounts_tableAccount_date 2 Years
2 concor Concur_table con Arc_Concur_table Last_update_date 4 Months
Like this 1000 entries are there in the control_table.
I want to pass all the columns from my control table dynamically.I am able to pass all the columns dynamically,except the PERIOD_VALUE.I have stucked up how to implement this condition.My aim is to get the data which is <= sysdate-PERIOD_VALUES based on PERIOD_UNIT.
For Example:
For Auction_date column I want to get the data which 15 days old.
For Sales_date column I want to get the data which 180 days old.
For Account_date column I want to get the data which 2 yers old.
For Last_update_date column I want to get the data which 4 months old.
[code]....
View 12 Replies
View Related
Jul 23, 2013
,I have a queryIn my query iam passing parameters using IN clause.The parameters contains in two tablesfor ex..select a.deptno, b.deptnofrom dept1 a, dept2 bwhere a.deptno = b.deptnoand NVL (a.deptno, b.deptno) in (10, 20, 30,.....) is this correct way to use NVL like this.i have a deptno consists in both the tables. How can i use this condition.
View 4 Replies
View Related
Feb 17, 2010
I have created one form using oracle ids. i want add value into combo box in runtime.
View 3 Replies
View Related
Jun 17, 2013
In alters I want to display a value dynamically.
like when deptno=10 came then it should dynamically displays the message like Department 10''.
when deptno=20 came then it should dynamically displays the message like Department 20''. and.....
View 4 Replies
View Related
Oct 18, 2011
I am trying to pass null value '' in form but still failed
---SQL---
select distinct column1 from abc
Y
NULL
two records found.
---FORM---
I have a list item name user_pick which is in two values SOUND-Y, DAMAGE-'' passing null value
--BUTTON--
select count(column1) into A from abc
where column1=:user_pick;
when user pick from list SOUND it is ok..When user pick damage this will show 0 means null value are not passing correctly.i also tried in radio group,check box
View 4 Replies
View Related
Jul 20, 2010
i have list item populated with many table names from a schema. i have grid in oracle forms 10g and i want to fill the grid with at least four/more columns.I want to fire the list change trigger when each time any one table name is selected.
how can i find the columns names dynamically for filling the grid.
View 6 Replies
View Related
Mar 15, 2013
i want to create the list item dynamically on the when new form instance this is the code....
group_id := create_group_from_query('lst','select (imt.item_id), to_char(imt.item_code, from items_mt imt');
v_num := Populate_group(group_id);
populate_list(v_num,group_id);
but the list is not the populate.
View 14 Replies
View Related
Mar 3, 2011
I've got a situation where I need to populate a list box of lab numbers when the user selects a matching date received date.
Right now it only works when the user presses enter on the field used to accept a date.
It also works when a user fills in date and then clicks twice on the listbox of lab numbers. (assuming there are some records for the date in question).
Both fields are in a data block that is not connected to a table. Here is the when-mouse-doubleclicked trigger
LAUNCH_CALENDAR('DISPLAY.DATE_BEAN');
IF :DISPLAY.DATE_RECEIVED IS NOT NULL THEN
GO_ITEM('display.list_labno');
--POPULATE_LIST_ITEMS('date_received_records','display.list_labno');
do_key('list_values');
[Code]....
As you can see we have tried several different triggers to get this to work with a calendar date chooser (java bean). The bean works and allows for a date to be returned. However, it does not want to populate the listbox.
View 5 Replies
View Related
Jun 1, 2009
i want to pass parameters from xyz form to abc form when i press a button. how to pass parameters.....
View 5 Replies
View Related
Sep 17, 2010
I need to pass two parameters (STUD_ID) and (TERM) from Form A to Form B. I've read a lot but I'm not familiar with terms and oracle jargon yet! So I really need your straight forward clarification
From the Calling form I'm using when_button_pressed trigger...what shall i write in here?! In the Called form, I'm declaring the following under (When_new_form-instance):
declare
p1_id paramlist;
stud_id number(8);
term number(5);
begin
p1_id := get_parameter_list(stud_id);
p1_id := get_parameter_list(term);
:TEST_STUD_CRSE_REG.stud_id :=:parameter.stud_id;
:TEST_STUD_CRSE_REG.term :=:parameter.term;
next_block;
end;
-----------------
on the calling form:when_button_pressed I am using:
DECLARE
p1_id paramlist;
stud_id number(8);
term number(5);
BEGIN
call_FORM('test16SEP',NO_HIDE);
GO_BLOCK('TEST_STUD_CRSE_REG');
set_block_property('TEST_STUD_CRSE_REG',default_where,'STUD_ID=:TEST_STUDENT_INFO.STUD_ID');
execute_query;
END;
View 2 Replies
View Related
Apr 18, 2013
How to pass data of one procedure to another procedure in oracle forms.
View 1 Replies
View Related
Oct 12, 2013
How to create folder using oracle form. I used HOST command but its not working.
HOST ('md C:Emp_Photo2013');
View 5 Replies
View Related
Nov 17, 2011
I am working on forms 6i, ebs 11i and data base 9i. I have below requirement to achieve.
I want to create a lov for a column called 'FORM'.
I have lookup for this as follows
lookup code meaning description
FORM FORM XXSMCINV_FORM
in the description, it's value set name.
I am manually checking the value set name and writing static record group.
Right now, i have static record group for this like below.
select ffvv.flex_value "FORM"
from fnd_flex_value_sets ffvs,fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id=ffvv.flex_value_set_id
and ffvs.flex_value_set_name='XXSMCINV_FORM' order by flex_value;
It's working fine.
But in the lookup, they can add one more FORM like below.
LOOKUP_CODE MEANING DESCRIPTION
FORM FORM XXSMCINV_FORM
FORM1 FORM1 XXSMCINV_FORM1
Now i have to change the record group query manullay like below.
select ffvv.flex_value "FORM"
from fnd_flex_value_sets ffvs,fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id=ffvv.flex_value_set_id
and ffvs.flex_value_set_name='XXSMCINV_FORM' order by flex_value;
UNION
select ffvv.flex_value "FORM"
from fnd_flex_value_sets ffvs,fnd_flex_values_vl ffvv
where ffvs.flex_value_set_id=ffvv.flex_value_set_id
and ffvs.flex_value_set_name='XXSMCINV_FORM1' order by flex_value;
Is there anyway i can make this automatic whenever they add new FORM lookup.
Is this possible to achieve using dynamic record group.
View 1 Replies
View Related
Dec 26, 2011
I am working with forms 6i..Is it possible to create form dynamically at run time..
View 2 Replies
View Related
May 10, 2011
how we can find file path dynamically. In paramenter we can give only destination file name like EXCEL.EXE. and its give me full path of EXCEL.EXE file.
View 1 Replies
View Related
Apr 17, 2012
Is it possible to pass parameters from menu file to form when calling a form from menu item?
View 4 Replies
View Related
Jan 24, 2012
How to pass a values from one to another, but without using parameters or globals. I've used parameters and every time i have to list the values of the form that values have been passed to, it ask do you want to save... even if I didn't passed any values. It's because my code in where-new-form instance, where i give values to a parameters.
View 17 Replies
View Related
Jan 5, 2011
can we pass parameter in a link. i.e when we open a new form clicking on the link, some values to be passed to the form so that new form get populated with that value.
View 2 Replies
View Related
Sep 6, 2010
I have a form in which the user selects values above from a LOV and then selects a run query button which then uses the values selected in the LOV to refine the query and output the appropriate values.
What i have done in the pre-query trigger is: (the block i am querying is called cars where the block i am selecting values from the LOV is called selection)
:cars.make := :selection.make; (repeated for other fields)
this worked fine untill i introduced a from and to date in my form in which i passed the data through to the query like this:
declare
v_dates_between varchar2(1000);
begin
v_dates_between := 'reg_date between ''' || :selection.from_date || ''' and ''' || :selection.to_date;
Set_Block_Property( 'cars', DEFAULT_WHERE, v_dates_between ) ;
Now i have added the above bit of code the from and to date work fine, but when a car make is selected it still bring back every car make and not just the one selected.
is it possibly because i am setting the where clause in the block property? perhaps i need to now incoperate my other selections into this where clause? are the two blocks exclusive to eachother? and if i put the whole code into the block property I assume i will get problems. E.G. the user leaves a selection criteria blank (if they want to query all car models they wouldnt select one from the list) i assume the query would only return back values which contain no car model which would be 0 records.
View 6 Replies
View Related
Oct 26, 2013
I have created a Data block - 'CONTACTS' (Database data block) and has database item - 'Code', 'Descr'
The number of records displayed is set to 5.
Value When checked - 'Y'
Value When Unchecked - 'N'
Check box mapping of other values - 'unchecked'
The requirement is when i check one or multiple checkboxes, i should pass the 'Code' item values to the WHERE clause.
Right now whenver i am trying to do so, only the current record value is copied to the WHERE clause.
I have tried using basic loop as well as while loop but things havmt worked. Below is a basic code which will work for one record, request to guide me with muliple checkbox ticked.
IF :contacts.cb = 'Y' THEN
IF p_where is null then
p_where := :contacts.code;
else
p_where := p_where ||','||:contacts.code;
end if;
end if;
p_where:= 'where code in ('||p_where||')';
View 6 Replies
View Related
Sep 6, 2013
I want to get file directory path dynamically with out using directory in database or not hard code like below
the purpose is i need to check image in the path directory if not found i unable to generate report, is there any possible to get dynamically
function CF_URLFormula return Char is
v_handle utl_file.file_type;
v_file_dir VARCHAR2(60) := '/u002/app/applmgr/temp/';
begin
v_handle := utl_file.fopen(v_file_dir, :photo_name, 'R');
utl_file.fclose(v_handle);
RETURN v_file_dir||:photo_name;
exception
when others then
srw.message(100,SQLERRM);
return null;
end;
View 1 Replies
View Related
Apr 10, 2013
i want to create a lov and record group dynamically at run time. i have the code in when-form-instance..
Declare
v_lov lov;
rg_id recordgroup;
gc_id groupcolumn;
[Code]....
whether i create first a lov and record group at design time or not? if i create a lov and have the recordgroup column then what is the benefit of dynamically lov.?
View 5 Replies
View Related
Mar 20, 2011
Can i change the property of item dynamically in pl sql coding. Actually i want that whenever the user press some button then that field(textbox) become non-database item of the database block. put some value in it and press another button to restore its property like before.
View 10 Replies
View Related
Jun 25, 2010
i have a cursor which selects the codes based on the school records now i have a multi record block b1 the requirement is that if no of records selected in cusor is 10 then in block b1 10 records should be displayed and if it is 15 then 15 records should be displayed. Is there any way that i can set the no of records displayed in a block dynamically.The block b1 is non database block i am only using a create_record to populate the block.
View 2 Replies
View Related
Mar 24, 2012
I have a master-detail form showing multiple records (tabular) in data-block. Now my problem is, based on certain column's value of a particular item in a particular row on the data-block to ENABLE/DISABLE during run-time .
For example, in a detail-block of 5 rows, if 3rd row's 'detail-block.item1' is equal to lets say, "X", then 'detail-block.item2' of that particular row ONLY must be 'disabled'. otherwise, it must 'enable' the 'detail-block.item2' of that particular item in that particular row on the data-block.
Even when, I query the form, it must enable/disable that particular item of that particular row from the data-block based on item1's value.
View 4 Replies
View Related
Mar 31, 2011
I am currently on a project which has a requirement of changing the number of records dynamically. Oracle Forms version 10g.
View 2 Replies
View Related
Nov 29, 2010
Is there any way could alter the sequence depending on specific condition to start with 1 in the form builder?
i wanna to reset the sequence to 1 whenever the month change.is there any way?
Could i make detail block invisible and whenever button pressed i will display the block? I want to put the where clause for that block in the button and if the button did not pressed i want to display nothing in the detail block?
View 1 Replies
View Related