I have a view t1 having the records:
1 A
1 A+
2 B
3 C
4 D
I want to display that in an LOV but without the first record (1 A) I used the code :
SELECT DISTINCT num,
DECODE(num,1,(SELECT DISTINCT desc FROM t1 a
WHERE a.desc='A+'AND a.num=1),desc) desc
FROM t1
this worked fine but how to apply this in a post_change trigger where a user inters the num and the desc appears in atext-item in my form
I have an issue with the oracle forms trigger. This is the scenario. The form is a query only form . A user can only query on this form . No update or insert is allowed.
The base table say TABLE1 consists of these fields. 1) pro id, pro item, empno , fname, lname, deptno .
The user can query on all items except the fname and lname.
The issue is few records in the TABLE1 have null values for the empno.
When the user queries on proid, pro item , empno or deptno - when the empno on TABLE1 is not NULL , the fname and lname should be fetched from a different table say TABLE2. otherwise, if the empno on TABLE1 is null , the fname and lname from the same table i.e, TABLE1 are retrieved. I have tried with the post change trigger on empno . Also tried the When validate item trigger . But it doesnt work the way it is expected to. what is the appropriate trigger that must be used.
tell me excat diff. between when- validate -item vs. post-change in a simple way...
View 4 Replies View RelatedRecently I am facing a problem while working with Oracle reports 2.5.
My requirement is:
there is a report in which there are 5 columns right now. Now what i want is, whenever a condition will satisfy , a new column should be also display in that report otherwise it must be stay as it is.
Previously : a b c d
Now if a=1 : a b z c d
else
a b c d
where a,b,c,d,z are columns.
Can we change the condition of an item from always to none on the real time ? How ?
View 3 Replies View RelatedI create a master detail form. I created also a post quey trigger, inside I wrote the code below.A cursor looks to another table and returns to the form some values i need!
declare
cursor C1 is
select apofaa, startdate, enddate, transport
from trapofdet
where trapofdet.apofcode = :trapof.apofcode;
[code]....
How to solve the
a. frm-40737, illegal restricted procedure go block in post-query trigger
b. It seems tha returns all the records but it appears only the last one.
I have been testing out a form using 10.1.2.0.2 on a v10.2.0.1 db and in my local env. the form works correctly i.e. if I make a change and 'post' it and then exit and press NO (when asked to save changes) then it correctly leaves the value in the database as it originally was.
The process works by the user pressing a button in form A (read only form) and this opens form B (using open_mode,session,activate) and the user makes their change(s) in form B (a 'post' command is issued in a When New Rec Inst trigger on a db block when the user navigates to a new record within the same block if it is determined that the block status <> 'QUERY') before returning to form A and pressing 'NO' when prompted to save changes.
However, if I run the same process in the TEST env. using the same executable against the same database then it actually updates the database value.
I have tested this by adding a debug message at the end of form B to retrieve the db value back AFTER having issued a clear_form(no_commit) just for the sake of the test and it still returns me the 'new' i.e. amended value - which is obviously incorrect. From what I can see it would appear that the commit occurs straight after the 'post' has been issued and well before the user even exits the form.
Is this a known bug with the 'post' built-in or could it be that a parameter is set to act in this way (i.e. is there an 'autocommit' setting that is 'ON') within the application server?
I have this layout
[URL]
and a post trigger insert and update
IF :item1 IS NULL
AND :item3 IS NOT NULL THEN
alert_id := FIND_ALERT('blank_alert');
SET_ALERT_PROPERTY(alert_id,ALERT_MESSAGE_TEXT,'Item3 must have a value.');
v_alert := SHOW_ALERT(alert_id);
RAISE Form_trigger_Failure;
END IF;
My problem was as it fires, the cursor will be on the item1. I've already use
SET_ITEM_INSTANCE_PROPERTY
NEXT_ITEM
GO_ITEM
but it was all illegal. The cursor must be in the item3.
Don't question me why I do not set the item3 to required and why post triggers. It is just that. It is the requirement.
i m using oracle 10g forms. in one form i m using post keyword when i use this function then records saved in temporary database but i want to remove one record from this form but it is allrady saved through post.
View 1 Replies View RelatedWe just migrated our technology platform on one of the six servers yesterday on production d/b as follows :
OAS Server - Linux Patch Applied (2.6.18-53.el5 #1 SMP Wed Oct 10 16:34:19 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux)
D/B Server - From Oracle 10.2.0.2.0 to Oracle 10.2.0.4.0
Batch Server - From OpenVMS to Red Hat Linux
Post Migration, we are facing some issues with Oracle Forms 10g, Text Items which contains one or more than one multibyte characters, is displaying only # character in the entire text box instead of what is stored in the database. In other words, information in the database is stored correctly. Before Migration everything was working perfectly fine.
One other thing which I would like to tell here is that we compile forms using the following environment settings:
NLS_LANG_SEMANTICS=CHAR
NLS_LANG=AMERICAN_AMERICA.UTF8
I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.
The block has 5 items as follows:
1) RECORD_STATUS = a non-base table column which is a checkbox.
2) ITEM_TYPE = a text-item which has an LOV attached.
3) ITEM_TEXT = a text-item which is free format text.
4) LAST_UPDATE_DATE a date column
5) STATUS = a text item either 'Open' or 'Closed'
The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.
I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.
Consider this scenario...
The block has 3 records.
record 1 has 'Closed' status so no updates are allowed.
record 2 has 'Open' status so updating of Item_Text is allowed.
record 3 has 'Open' status so updating of Item_Text is allowed.
I check the RECORD_STATUS checkbox on record2.
(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).
I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.
I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.
(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)
How can i stop this from happening?
I have a form which will call another and do some transactions and come back to original form and do other processes. save if successful. or roll back with error message.i m doing POST when i exit from the called form. and continue process in calling form. if any error, i m raising an error message with rollback.
But sometimes, it is getting committed with that error trn. especially in new database, first time try the scenario, it any error comes then getting commited.I m not able to find where the commit is happening? if any other sessions commit will affect this trn?
I am facing a post trigger problem.No error while compiling.
DECLARE
mYPACCT VARCHAR2(10);
mTRNNO NUMBER; mVNO NUMBER; mSR NUMBER; mNARRATION VARCHAR2(200); mCHCODE VARCHAR2(10); mPARTY VARCHAR2(200);
mBRAND VARCHAR2(200); mYARN VARCHAR2(200);
BEGIN
BEGIN
SELECT YPACCT INTO mYPACCT FROM CONFIG;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
[code].....
I have a form w/a tab canvas. BlkA is on Tab1 and Blk2 is on Tab2. In BlkA I have several items that have when-validate-item triggers. However, the trigger does not fire when I enter a value into item1 of BlkA followed by clicking on Tab2. To solve, I placed validate(form_scope) in the When-Tab-Page-Changed trigger. This works well with invoking the validation. However, focus remains on Tab2 (Blk2).
I would like focus to return to failed validation item in Blk1. I can capture the block name using :system.cursor_block in the Post-Block trigger of BlkA, assign that value to a form parameter and when a validation fails via the When-Tab-Page-Changed trigger I can return user back to BlkA(Tab1). However, I need direction on how to return user to the specific item in BlkA that failed validation
I have sort button that have the following code
go_block('TEL_OTHER_INFO');
set_block_property('TEL_OTHER_INFO',order_by,:system.cursor_item);
execute_query;
I have in the same form display item that its value will be sitting according to the post trigger of the previous text field when i press the sort button i get frm-40735 post-text-item unhandled exception Ora-01403 error.
I am working on form 10g, here i am using post command in save procedure to apply some record in database temporary.
But Frm-40404 Message raise during saving record i need post commond but i dont want to this message should popup, is there any method to avoid the message only?
i want to know that how can i use like operator with if condition. i m using oracle10g form builder and it's for search purpose .
for example ...
if search string=string2 then
message('Record found');
end if ;
i want to use like '%search string%'.
I am working on forms 6i. I have set where condition in pre-query of a trigger, my requirement is, in post-query, i want to delete the where condition.
pre-query:
set_block_property('HEADER_S',default_where,'STATUS IN (select meaning from XXSMCQSS_LOOKUP_VALUES where lookup_type=''CONCESSION STATUS CODES'' and primary_flag=''N'' )');
post_query: I want to delete this where condition, i just want make execute query normal by removing the where condition.
After committing form i want to clear few item and retain few item and make the form ready for next record to be inserted along with retained item.
View 4 Replies View RelatedI 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.
I am working on Forms 6i. I have data block A on table EMP.
Datablock A: Empno, Ename, Hiredate
I have FIND Screen: EMPNO, HIREDATE_FM,HIREDATE_TO and FIND Button.
I will search the records through FIND Screen.
I want to set the where condition of block A in pre-query trigger.
set_block_property('BLOCKA',default_where,'HIRE_DATE BETWEEN FIND.HIREDATE_FM and FIND.HIREDATE_TO');
Note: FIND in above is FIND Screen block.
It's not fetching any records, i doubt that there is syntax error in above statement.
I have master detail datablocks. Master block is course and detail block is trainee. students can schedule their courses in a location at particular time.
lets assume student1 has scheduled course A1 in location LA1 from 9am to 12 pm. Now second student shouldn't be able to schedule same course at same time in same location. He can schedule at different time duration. or else same time duration but different location. Or same time, same location but different course.
so, what i did is, i wrote code in when-validate-record of trainee block to display a message saying " cannot schedule course.choose another time." if he try to insert same time duration or same location .
when i am trying to insert a record with time which is already scheduled and in same location, it displays the message but it is also saving the record. i don't want to save the record.
how should i do this? i tried to give ROLLBACK in this trigger but it doesn't accept. i tried pre-insert trigger also but it is also displaying message but saving the record
my code in when-validate-record is:
declare
cursor c1 is
select start_time,end_time,loc
from trainee
where cname=:course.cname;
[code].......
how can i execute query using a where condition?
View 9 Replies View RelatedIn form i call a record from database table through execute_query. i have lov on ITEM_NO
i want to display on that form, no. of records of ITEM_NO which i select from the list. like if i have 10 records with item_no which i select from list is must show 1/10.
if i press the down arrow it should show next record of the same item_no.
I'm using forms 9i. Let's say I have a Block in the form B1. It has two items I1 and I2. I have a table called T1 that has a column say C1. The data in the only record in T1 is 'NAME_IN(''B1.I1'') = 1 OR NAME_IN(''B1.I2'') = 2'..In forms I need to execute the following at POST-QUERY trigger of the block:-
DECLARE
VAR1 VARCHAR2(1000);
BEGIN
SELECT C1
INTO VAR1
FROM T1;
[code]....
As you could see that the IF statement is wrong. But I get the condition (for IF condition) as a string value from some database table.
,i have a table called travel detail from which i have retrieved few rows based on 1 condition by using LOV and on next few rows modification is allowed. The rows are getting modified but it is also allowing me to enter in next blank row and insert any data in that row.how can i retrieve only that data which is based on the given condition. Further blank rows should not b displayed.
View 2 Replies View RelatedI'm trying to create a relation from child block to the master block that I've created . Foreighn key is there from child to parent table.The error I get is below:
FRM-15004: Error while parsing join condition
The join condition is correct, but I'm still clueless as to what it could be.Is it a form bug?
i want to update record that is fetched based on join condition on form
1. made a block manually :::: EMPSAL
2. Query DATA SOURCE NAME :::: EMP a, Sal b
3. Where Clause :::: a.empid = b.empid
4. DML DATA Target Type :::: Table
5. DML DATA Target Name :::: EMP a, Sal b
6. All Columns are marked a.empid, a.empname, b.sal, b.date etc
It does not allow me to update record.
I receive a "FRM-15004 Error while parsing join condition" when attempting to create a relation between block1 (parent table) and block2 (child table). If I do a simple straight join the statement is accepted but if I use a Decode then it results in an error. How to successfully use a decode or a nvl in a join statement of a relation?
Results in Error:
block1.case_id = block2.case_id and
decode(block1.employee_id, null, block1.entity_id, block1.employee_id) =
decode(block2.employee_id, null, block2.entity_id, block2.employee_id)
This join is accepted but I would like to use a decode since a record can have either a employee_id or entity_id, not both
block1.case_id = block2.case_id and
block1.employee_id = block2.employee_id and
block1.entity_id = block2.entity_id
i want to display records from a table in tabular form with one condition.
i made a tabular form.and in WHEN-NEW-FORM-INSTANCE i write
DECLARE
CURSOR hascr IS
SELECT
BILL_ID,
BILL_DATE,
MAT_CODE,
PLACE_FROM,
[code]....
when i run the form its not showing any errors but displaying only one record.that is the first record whose bill_type is send to other store.
am i using the right trigger or my select statement has problem?