I want to raise a alert after the records retrieved from a query. i.e, if the query fetches 0 records then the alert show be displayed. Is is possible? If yes tell me the trigger to be used to raise such kind of alert.
View 4 Repliesis there any way other than this way to retrieve the number of record displayed in the block ?
last record ;
l := :system.cursor_record
I developed a report in 10g which displays more than 100 records.The requirement is to display each record on single page.Does Oracle Reports have the option to display each on single page?
View 7 Replies View RelatedI have table T with 50,000 rows
create table T
(student_id number,
class_id number,
quiz_id number,
marks number)
some sample rows like
INSERT INTO T VALUES (1,1, 1, 50);
INSERT INTO T VALUES (2,2, 2, 40);
INSERT INTO T VALUES (3,1, 3, 34);
INSERT INTO T VALUES (1,1, 4, 10);
INSERT INTO T VALUES (1,1, 5, 30);
INSERT INTO T VALUES (1,1, 6, 29);
INSERT INTO T VALUES (3,2, 7, 34);
INSERT INTO T VALUES (3,2, 8, 33);
INSERT INTO T VALUES (3,2, 9, 56);
INSERT INTO T VALUES (1,1, 7, 90);
INSERT INTO T VALUES (2,2, 8, 0,);
INSERT INTO T VALUES (1,1, 8, 80);
INSERT INTO T VALUES (2,2, 8, 65);
INSERT INTO T VALUES (1,1, 9, 34);
INSERT INTO T VALUES (2,2, 9, 11);
each student belongs to one class_id. each student participates in many quizes. each quiz has its unique id. each student can appear once in a quiz_id
I am doing the below analysis and query:
1. with below query I am finding which student_id had most marks in any 3 successive quizes (see the 3-1 part below) in the query..
SELECT QUIZ_ID,
STUDENT_ID,
SUM (MARKS)
[Code]....
SQL> /
QUIZ_ID STUDENT_ID CONSECMARKS
---------- ---------- -----------
7 1 170
6 1 166
8 1 129
5 1 106
8 3 89
8 2 76
3 3 68
7 3 67
8 2 65
1 1 60
9 3 56
9 1 49
2 2 40
4 1 40
9 2 11
15 rows selected.
With above query, I can play around and find for any 'n' number of consecutive quizes, like marks in 2 consecutives quizes, 3, 4 and so on but for each 'n' value I've to run a seperate query mentioning (2-1) or (3-1) or (4-1) and so on..
since my table is big and there are about 400 quizes so what I want to find out is for each 'n' consecutive quiz (from 1 to 400) which student had most marks for each consecutie 'n' quiz. Like in 1 (consecutive) quiz which student had the highest marks and then 2 conseuctive quiz who had most marks and then in 3 consecutive quiz who had most marks and so on till 400 consecutive quiz who had most marks... rather than running query for each 'n' value seperately i want a single query that can give me a summary of most marks in each n consecutive quizes...
my sample output is:
Nth consecutive quiz student_id sum(marks)
1 1 90
2 1 170
3 1 246
4
.
.
.
100
.
.
200
.
.
300
.
400 ? ?
Is this possible to get the above output from one single query? If there are two or more students with equal most marks for any 'n' conseutive quizes then both should come in the summary.
I am trying to query a form having two data blocks. one is a master and the other is a detail block. the problem is when i insert data in the form, save it, and press enter query key, the data in the master block fields are still there. while the data in the detail block field are cleared and i can insert values. how to clear data from master block field so i can query
View 2 Replies View RelatedI have a Master block and a Detail Block. They are related using two columns, Document_ID and Page_no. I want to display all the records in master table with corresponding detail records beside them in single line. i.e., as shown below:
MasterItem1 DetailItem1 DetailItem2 DetailItem3
I created the relation between them and executed query. When I execute query in the form, I can find that all the Master Items are displayed in vertical records, but i can only see one record of detail fields. They are displayed as shown below:
mitem1-value1 ditem1value1 ditem1value2 ditem1value3
mitem1-value2
mitem1-value3
mitem1-value4
When the navigate down in master items, then the corresponding detail values are being shown on the same first record.
Version information:
Forms [32 Bit] Version 10.1.2.3.0 (Production)
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
When an user enters a value in a field of a Screen, I want to query up the next screen records based on that user input value in the previous screen.The previous screen is based on a control block i.e.
Q_EMP
items- Q_EMP.emp_id, Q_EMP.FY, Q_EMP.hire_flag.
I have one table test_pc :
EVENT_IDPARENT_EVENT_ID
2001
2002 2001
1001 2002
1002 1001
1004
1003 2005
1006
Scripts are mentioned below :
create table test_pc
(event_id number(10), parent_event_id number(10));
insert into test_pc values(2001,null);
insert into test_pc values(1006,null);
insert into test_pc values(1004,null);
insert into test_pc values(2002,2001);
insert into test_pc values(1001,2002);
insert into test_pc values(1002,1001);
insert into test_pc values(1003,2005);
In order to derive parent child relationship I have ran the following query :
select *
from test_pc
start with parent_event_id is null
connect by nocycle prior event_id = parent_event_id;
It is giving all the records except the last one (i.e. event_id = 1003 and parent_event_id = 2005), because the parent_event_id does not exists in the table.
But my requirement is to show all the record, i.e. if the parent_child relationship is present then show accordingly and also show the rest of the records where the parent_child does not exists even the parent_event_id exists.
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
Query for anyone interested:
Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.
i am using .dot net frame work to show a pop up on desktop like reminder , can i do that using any oracle tools i am using oracle 10g along with forms 6i , i am using one query for this.
View 4 Replies View RelatedI want to create one alert while deleting the record from the custom form. So i have written the below code in the when-remove-record trigger.
DECLARE
alert_button NUMBER;
BEGIN
IF :XXWIPRELFRM.PRINTED = 'Yes' THEN
alert_button := SHOW_ALERT ('DELETE_ALERT');
[Code]..
When i click on the delete(X) symbol in the form, it is showing alert but when i click on "No" also its removing the record..what i need to write.
I need to display my alert prompt right after I detect the discrepancy on amount. I place my code on POST-BLOCK. The problem is, after the query, the alert pops out right before the new canvas where the block with POST-BLOCK trigger shows.
I need to display first the canvas before the alert but seems like the process of displaying canvas is the last job oracle does. So even if I place my code on post-record or any other trigger it will be the same.
I am working on Forms [32 Bit] Version 11.1.1.4.0 (Production). OS is Win 7 (32 bit). Internet Explorer 8. My Requirement is I need to raise an alert when user close the browser window before closing the Form.
View 2 Replies View RelatedI need code for calling two reports using alerts.
View 4 Replies View Relatedi've created an alert with 2 buttons and the code for cancel button is as follows:
...
if alert_id = alert_button2
then
raise form_trigger_failure;
end if;
...
this code is in a pll and when called from a form it displays a non oracle exception message. How to do nothing when the user presses cancel on alert pop-up? apart from using null instead of raising the above exception?
Am using Oracle 10g Forms and Databse.
My form Consists data block blk_user with two items username and password
Both user name and Password are required Fields.
When user left these items Empty To show Alert
I Created Below Procedure and Called in Form_Level ON-ERROR Trigger.
PROCEDURE pcd_io_alert IS
itm_name VARCHAR2(20);
BEGIN
[Code].....
how to call or create this procedure as Library functions and call the created library in form To Show Alert. Actually I want to create library functions to Validate and Show Alert Mesages.
I have a forms 11g application that of course has several message and alert windows that pop up when various errors or messages need to be displayed. This all works great on the developer machine, but when I try and run the form on another machine using any browser the message and alert windows appear to be hidden and the application locks up with the rolling bar across the bottom. I would guess it is waiting for a response, but I cannot get the window to appear.
Following is an example of an alert to be displayed:
declare
vAlertButton number;
begin
vAlertButton := show_alert('NO_EMAIL');
end;
I am using Oracle developer Suite 10g, and i want to customize the position of ALERT message on the screen as per requirement.
View 4 Replies View RelatedI want to display an alert when we click on Insert Record in tool bar. I put allow update->yes and insert->no.
but when i click on the insert record in tool bar it showing message only.
Instead of message I need an alert using triggers. I added the image also.
Is it possible to hide/suppress an user defined alert message in Oracle Forms application?
View 1 Replies View RelatedCan we tune below mentioned query without creating any indexes :
SELECT /*+ PARALLEL(a,64) */x_abc_41, x_abc_44, CALLED_FROM_NUM, X_abc_25, created, evt_stat_cd, last_date, x_abc_number_from,
x_abc_complete_date
FROM table a
WHERE a.todo_cd in('MNPIN','MNPOUT')
AND a.x_abc_25 = 'NP RFS'
AND a.created BETWEEN sysdate-180 AND sysdate
Explain Plan :
PLAN_TABLE_OUTPUT
Plan hash value: 3718629559
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2376 | 9462 (4)| 00:02:51 | | | |
[code]...
Note : Currently this query is taking 3 Minutes for retrieving 8000 records,But should be executed in less than 1 min.
I have a table with three columns mentioned below :
Col1 Col2 Col3
121
221
331
431
531
622
722
Col1 is the primary key.I need a sql query which will show the data set mentioned below :
Col1 Col2 Col3 Seq
121 1
221 1
331 2
431 2
531 2
622 3
722 3
I need all the 7 records to be displayed.
How can I create PLSQL table type dynamically?
I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.
creating Oracle SQL query to fetch the information using PIVOT option.We are populating audit table using triggers. For every update, there will be two rows into audit table, one row with all OLD values and another with all NEW values. Also every updated is uniquely identified by Sequence No. Example for phone audit is mentioned below :
CREATE TABLE test_audit_phone
(
emplid VARCHAR2(10),
seqno NUMBER,
action VARCHAR2(3),
office NUMBER,
mobile NUMBER
);
Insert some rows into table.
INSERT INTO test_audit_phone VALUES ('100',1,'OLD',1111,9999)
/
INSERT INTO test_audit_phone VALUES ('100',1,'NEW',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'OLD',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'NEW',2222,8888)
/
Table will look like the following :
SQL> SELECT * FROM sysadm.test_audit_phone ;
EMPLID SEQNO ACT OFFICE MOBILE
---------- ---------- --- ---------- ----------
100 1 OLD 1111 9999
100 1 NEW 2222 9999
100 2 OLD 2222 9999
100 2 NEW 2222 8888
Now we have to present data in different format. For each field, display OLD and NEW values in column format.
EMPLIDFIELDOLDNEW
----- ------ ---- -----
100OFFICE11112222
100MOBILE99998888
Challenges :
1) Make pivoting with old and new values
2) For each field we have to show old and new values
3)if old and new values are same, dont show in report.
I need to find the most recent rows (top 10) in a table... I have about 300 tables so the fields are different, is there a way to generically find it?
View 17 Replies View RelatedI created a form to create a username and password.i tried creating a user in forms 6i using the following code on the when button pressed trigger:
BEGIN
forms_ddl('create user'||:user_name||'identified by'||:pasword);
commit_form;
END;
i have granted the user to create user but it does not work at all. password and user_name are captured in the form.
I am creating a procedure oracle forms and database Which one is fires first ..If suppose i need data base fires first...
View 3 Replies View RelatedI need to write a query to sort the records in a particular order,
Say if I group the records by Dept number
Dept no Name
10A
10G
10f
20B
20K
30I
30M
30R
30Y
I need to write a query that will make this records listed like
Dept No Name
10A
20B
30I
10G
20K
30M
10F
20null
30R
10null
20null
30Y
I have a Select query which is not fetching records in 11g (11.2.0.2.0) but working fine in 10g (10.2.0.4.0). The query is as below.
--CREATE TABLE t1 (col1 NUMBER, col2 VARCHAR2 (15 CHAR), flag varchar2(1))
--insert into t1(col1, col2, flag) values(1, 'a', 'Y');
--insert into t1(col1, col2, flag) values(2, 'b', 'N');
SELECT *
FROM t1 x
WHERE col1 = 1 AND col2 = 'a' -------------- condition1
AND 0 = -------------- condition2
NVL (
(SELECT COUNT (1)
FROM t1 y
WHERE y.flag = 'N'
AND x.col1 = y.col1
AND x.col2 = y.col2),0)--=0
When remove NVL function or change the condition by having AND NVL(SELECT) =0 the query working fine.
The requirement is I have a table (TAB1), wherein I have 3 columns, ID, LID and STATUS.
The value in ID column = ID_SEQ.NEXTVAL,and LID will be either 0 or 1 and the possible values for STATUS are 'ED','CP', NULL. The ID column is not suppose to have duplicate values, but there is no check on the table for the same.
Someone has updated the existing data and ID column is containing duplicate values. Wherever LID = 0 and STATUS = NULL and if only if ID is duplicated then the ID_SEQ.NEXTVAL has to be assigned to ID field, so that there are no more duplicate values.
CREATE TABLE tab1 (id NUMBER , lid NUMBER, status VARCHAR2(10));
Existing Data
------------------
INSERT INTO tab1 VALUES (1,0, 'ED');
INSERT INTO tab1 VALUES (1,0, 'CP');
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
[code]....
get the result using a single update statement.