SQL & PL/SQL :: Handling No Of Rows Selected In Loops?
Feb 25, 2012
I'm attempting to write a plsql for finding missing archived logs for streams.
requirement is to run a select statement and print
1. 'NOT FOUND' if name column is null
2. '<name of the files>' if rows are returned
3. 'NOT FOUND' if no rows are selected. (here is were i'm having trouble)
code i developed so for:
for cr in (select decode(name, NULL, 'NOT FOUND', name) from v$archived_log where deleted='YES'
and status!='A')
loop
if (cr.name = 'NOT FOUND')
[code]...
View 6 Replies
ADVERTISEMENT
Jun 23, 2013
I have homework due, and I'm so stuck: This worked when I first ran it, and now all 6 queries return the same error No Rows selected. I did have to add a column to a table, and insert data for that column before this worked.
> --------------------------
> --REPORT 1
> ---------------------------
SELECT sur.channo,
supp.supplierid,
sur.packtype,
program_name,
ratingcode,
[code]....
View 2 Replies
View Related
Aug 3, 2011
Can we select table's rows of 5 to 15, like
select * from emp
where rowid <=5;
It is through an error when i used the below method.
select * from emp
where rowid >=5 and rowid <=15;
View 4 Replies
View Related
Jun 18, 2013
I am trying to run the following queries in my database.
Q-1)select * from item where trunc(approval_date)='21-MAY-13'
When Q-1 is executed it returned row that has approval_date of 21-MAY-13 from database.
Q-2)select * from item where approval_date=to_date('21-MAY-13')
When Q-2 is executed it says no rows selected.
difference or if any links related to this.
View 4 Replies
View Related
Mar 23, 2011
Can I apply Referential Integrity to only selected rows of a particular column? This is the reference key to the primary key to another table. But, the issue is, this reference column is not having mandatory data for all the rows. So, whenever this is null, I dont want it to be referred by parent table.
no, and it makes no sense to do so either
We have an appointment form in our HIS, where patients take telephonic appointment. That time they may not know their user id given by hospital. So, it remains blank & name is entered manually. But if the user-id is entered, it must fetch patient name from the master.
The candidate key must be unique within its domain.The candidate key can not hold NULL values.
ALTER TABLE HLTHCHKAPPOINTHD ADD (
CONSTRAINT SYS_C007145
FOREIGN KEY (N_PATIENTMR_ID)
REFERENCES MRREGISTRTNHD (N_PATIENTMR_ID));
MRREGISTRTNHD is a patient master & has a primary key named N_PATIENTMR_ID.
HLTHCHKAPPOINTHD is a appointment table & has a foreign key named N_PATIENTMR_ID which references N_PATIENTMR_ID of MRREGISTRTNHD.
Also, N_PATIENTMR_ID of HLTHCHKAPPOINTHD is not a unique key & it can contain null values also. I want to define constraint or any other method such that only the not null values are referenced to the master i.e. it should validate in the master. And null values should skip this reference.
Now, what happens is due to this constraint, when I'm trying to edit & update the rows having null value in N_PATIENTMR_ID, it gives the following error.
ORA-02291: Integrity constraint (SYS_C007145) violated - parent key not found
So, can I give some condition in the above constraint saying, apply this constraint to table HLTHCHKAPPOINTHD only having the not-null values in N_PATIENTMR_ID coulmn?
View 2 Replies
View Related
Nov 11, 2011
I 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.
View 7 Replies
View Related
Jun 17, 2010
Am using the SQL*PLus tool via a command line to execute some simple "select" scripts. Scripts work fine in what they produce - except for something that is missing occasionally.
For SOME scripts, I'm getting no "xx Rows Selected" type messages at the bottom and I can't figure out why. When no data si returned in some (different) scripts I do get a "no rows selected" message. Am looking for this message in all outputs as an "end of report"-style marker.
No complicated SQL logic and I have the following standard settings applied into each script.
set linesize 500
set pagesize 50000
set tab off
set wrap off
set colsep '|'
I have 20 years of coding experience so it's not a basic error....albeit I'm far more familiar with MS SQL Server product set as well as the PL/SQL Developer client front ends for Oracle.
View 6 Replies
View Related
Jul 6, 2012
I have a I/R report with a checkbox is column 1 of each row using:
HTMLDB_ITEM.CHECKBOX
(1,R.RESEARCH_ID,
decode(:Pxx_SELECT_ALL_ROWS,'Y','CHECKED',
decode(instr(nvl(:Pxx_SELECTED_IDS,0),R.RESEARCH_ID),0,NULL,'CHECKED'))
)
AS select_flag
I want to use Javscript or a Dynamic Action to "count" how many selected rows there are, when a UPDATE button is pressed. Right now, the UPDATE button "submits" the page, then a page item computation runs the following code to get a count:
DECLARE
v_cnt number(9);
BEGIN
v_cnt := 0;
FOR i in 1..htmldb_application.g_f01.count
LOOP
v_cnt := v_cnt + 1;
END LOOP;
return v_cnt;
This works... but I dont want to submit (and rerun the report) just to get this count.
I have copied this loop into a Dynamic Action using the Click action of the UPDATE button, but it doesnt seem to recognize htmldb_application.g_f01.count .. so my value returns as 0.
View 7 Replies
View Related
Nov 21, 2011
I have a table where each record has a numerical x-coordinate value as one of its fields. I want to loop through a group of the records that have another field in common in a given order. In a nested loop, I would like to subtract the coordinate of the outer loop from the coordinate of the inner loop for all records in the inner loop that appear later in the sequence. The result is a list of the distances between all coordinates.
Example:
x-coordinate
3
4
6
7
It would look like this:
4-3, 6-3, 7-3,
6-4, 7-4,
7-6
I can do this using two nested cursors that select the same thing basically. But the table being selected from is pretty large and it takes forever to keep selecting from the huge table when the inner cursor could just copy the results of the other cursor and repeatedly iterate through them.
Is it possible to copy a cursor's results into another cursor or reset the cursor index back to the beginning so that it doesn't have to do the select statement every time?
View 1 Replies
View Related
Mar 16, 2011
What is the difference between Hash join and Nested Loops in pl / sql?
View 1 Replies
View Related
May 31, 2011
homework assignment using pl/sql based on 2 tables I have created below? I am not sure of how to use cursors, loops and proper syntax.
ASSIGNMENT:
1. Create a PL/SQL Procedure (cursor to loop through the records, check the LastName, then update the grade table
where id=id on grade table)
Rule:
A
‐ LastName ends with a character between A‐F
B
‐ LastName ends with a character between G‐K
C
‐ LastName ends with a character between L‐P
D
‐ LastName ends with a character between Q‐T
E
‐ LastName ends with a character between U‐Z
Create TABLE Registration (RegistrationID number(10), SectionID number(10), CourseID number(10),
SectionNumber varchar2(10),
StudentID number(10), FirstName varchar2(20),
LastName varchar2(20), CourseNumber varchar2(20), CourseName varchar(20));
[code].....
View 20 Replies
View Related
Apr 10, 2013
I have a select query that was working with no problems. The results are used to insert data into a temp table.
Recently, it would not complete executing. The explain plan shows a cartesian. But, there could be problems with using nested loops on the outer join.
Interestingly, when I copy production code and rename the temp table and rename the view, it works.
CREATE TABLE "CT"
( "TN" VARCHAR2(30) NOT NULL ENABLE,
"COL_NAME" VARCHAR2(30) NOT NULL ENABLE,
"CDE" VARCHAR2(5) NOT NULL ENABLE,
"CDE_DESC" VARCHAR2(80) NOT NULL ENABLE,
[Code]....
View 1 Replies
View Related
Sep 14, 2010
10.2.0.1
I am trying to understand the concept of nested loops.
--------
| 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00
:00:01 |
| 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00
:00:01 |
[Code]....
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
Nested loop by defintion means,for every row returned by the outer query,the inner query is executed that many times.
In the above example,oracle does a full table scan and returned 14 rows.Now for dept table,it does a index unique scan and applies the predicate a.deptno=b.deptno and returns 1 row.
My question is why it is returning only 1 row? That measn for every 14 rows,this one row is fetched 14 times.
View 10 Replies
View Related
Jan 12, 2013
i am trying to use loops to retrieve the ename of emp table of scott schema , then under they are retrieved , all "sal" values appear under them , i do not want employee's salary under each employee , i need retrieve the 14 record of enames , then under them the 14 record of sal column appears , so i used this code :
declare
cursor emp_cur is
select ename from emp;
emp_rec emp_cur%rowtype ;
[code]....
View 16 Replies
View Related
Mar 10, 2012
Main Aim : To find all those id's who have taken all the tests within a rolling window of 45 days.
I have a table "MBS_FIRST_DATE" with the following data :This table has the patients who have the test along with the first date..This table is derived such that it has only one record with the first date of the test irrespective of the test.
create table MBS_FIRST_DATE
(
medical_record_number VARCHAR2(600),
requested_test_name VARCHAR2(39),
result_date DATE
[code]..
Process :will be explaining with a patient id :
1) Consider the patient 1001274 from mbs_first_date table.
2) This patient has an date of July 08th 2008 & test SBP from first table. (keep this test an an anchor).
3) For the patient above loop through the all_recs table with test & result date ordered for the patient. (excluding SBP)
4) The first record we have is CHL with 08/05/2009 (May 8th 2009)..
5) Since this record is not within 45 days from SBP date for the patient..we go to the next record of SBP for the patient.
6) The next record for SBP is 11/05/2009 (May 11th 2009) .
7) Consider the CHL date again which is with 08/05/2009 (May 8th 2009)..
Since both are within 45 days ..store both the values keeping SBP date as an anchor date as it's the test that's having minimum date from table 1. Even though there is one more CHL date which is within 45 days from SBP we don't care about it.
9) Go to the next test for the same patient which is DBP..
10) The DBP first date is July 08th 2008..
11) Since it's not within 45 days from previously stored SBP date (11/05/2009) ignore the record.
12) GO to the next record which is 10/05/2009..as this is within 45 days from SBP & already CHL (stored date) is within 45 days..Grab all the 3 dates as all are within 45 days from anchor date (SBP date).
SO the o/p will be
1001274 SBP 11/05/2009
1001274 CHL 08/05/2009
1001274 DBP 10/05/2009
Code which I wrote :I know some where I am missing the loop
declare
V_ID1 VARCHAR2(200) := '';
V_TEST1 VARCHAR2(200) := '';
V_DATE1 DATE := NULL;
[code]...
View 2 Replies
View Related
Oct 30, 2012
I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).
What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?
I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).
SELECT A.POLICY ,
F.MIN_MEMBER_ID,
MIN(A.EFF_DATE) EFF_DATE,
A.EXP_DATE ,
G.DESCRIPTION PROGRAM_NAME,
[code]...
View 5 Replies
View Related
Jul 16, 2012
Is it possible to take execution control back from exception handling section to Execution statement?.. If Yes then How?..
View 25 Replies
View Related
Dec 13, 2012
I have below query which works fine if column 'XML_COL' has values. This select statement fails if the value is NULL for
select xmltype(t.xml_col).extract('//fax/text()').getStringVal() from mytab t
How to handle rows with NULL values in the column 'XML_COL'.
View 3 Replies
View Related
Jan 23, 2013
I have 2 tables, AFF_TEMP and COUNTY
AFF_TEMP has the following columns FNAME, LNAME, EMAIL and COUNTY
COUNTY has 2 columns COUNTY_ID and CNAME
Both tables have the following test data
AFF_TEMP
Joe, Bloggs, joe@gmail.com, ''
Ann, Bloggs, anne@gmail.com,Donegal
and COUNTY column in AFF_TEMP can contain a NULL value
County table has the following Test data,
1, Dublin
2, Donegal
3, Tipperary,
4, Galway
I am trying to select the following from both tables FNAME, LNAME, EMAIL, COUNTY_ID.Tried the following queries
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A LEFT OUTER JOIN COUNTY C ON A.COUNTY=C.CNAME
OR (A.COUNTY IS NULL)
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A, COUNTY C
WHERE C.CNAME IN (SELECT UPPER(A.COUNTY) FROM TEMP_AFF A)
[code]...
View 3 Replies
View Related
Jan 13, 2011
I have a snippet of code
EXCEPTION
WHEN OTHERS THEN
H_Report_Error('sql',Sqlerrm);
end;
And I'm trying to figure out how to catch a error from a loop such as:
OPEN My_cursor FOR SQLCODEBLOCK
LOOP
FETCH a_variable INTO ResultCount;
EXIT
WHEN My_cursor % NOTFOUND ;
BEGIN
--Stuff done
End;
End Loop;
So right now I have a error in the SQLCODEBLOCK, but I can't catch that error. I've tried putting the exception in various places but it wont compile.
View 6 Replies
View Related
Oct 8, 2012
I am using for writing text files data to database. The problem here is let us assume there are 6 records in text file and if there is a problem at 2nd record, the later records are not getting inserted.
CREATE OR REPLACE PROCEDURE PROC1
IS
temp varchar2(500);
tmp_name varchar2(5);
tmp_no varchar2(4);
.
BEGIN
WHILE NOT end_of_file
LOOP
IF i = 18 THEN
tmp_no := temp;
END IF;
IF i = 21 THEN
tmp_name := temp;
END IF;
END LOOP;
END;
/If i=18 and temp = '12345' here, then tmp_no := temp; won't work (tmp_no varchar2(4);)
Similarly, If i=21 and temp = 'ABCDEFG' here, then tmp_name := temp; won't work (tmp_name varchar2(5));
how to handle this through EXCEPTIONS so that even if there is a problem with 1 record, while loop remain working for further records..
View 4 Replies
View Related
Feb 12, 2007
I'd like to achieve the following (and YES, I do know that this is not multi-user safe, but that's not the point here):
Before inserting a record, the trigger shall check if there's already a duplicate one. Duplicate means in this case when there is an intersection of the time frame, defined by two numeric timestamps. That's also the cause why I cannot use a simple UNIQUE constraint here (in my opinion).
Okay, that already works (see code below). But now I need colliding records to be written to a temporary table so that those records can be returned and presented to the user for selection.
create or replace
TRIGGER TRIGGER1
BEFORE INSERT ON FLIGHT_TABLE
FOR EACH ROW
BEGIN
FOR fs_entry IN (SELECT * FROM FLIGHT_TABLE)
LOOP
[Code] .........
View 1 Replies
View Related
Dec 8, 2010
Basically I've created a function, when I run it there is a user input. Mine is a customer number between 1-10.
I was wondering is there a way to add in error check so if I typed in an invalid number it would give me a message saying "Wrong customer_number" or something along the lines of that?
I was told I wasn't able to use "DBMS_OUTPUT.PUT_LINE" in the function I need to tamper with the function header?
Here is my header -
CREATE OR REPLACE FUNCTION hours (custid customer.cust_id%TYPE)
RETURN NUMBER IS
multiply NUMBER;
returnanswer NUMBER;
View 2 Replies
View Related
Sep 14, 2010
I have a set of 500 insert queries which i need to execute. I have included exception handler to catch any errors encountered. I don't want the execution to halt when an exception is encountered, i want the next insert statement to be executed
I am pasting the sample code below
DECLARE
error_code NUMBER := SQLCODE;
error_msg VARCHAR2 (300) := SQLERRM;
BEGIN
INSERT statement 1
INSERT statement 2.....
....................
INSERT statement 500.....
commit;
EXCEPTION WHEN OTHERS THEN
IF (SQLCODE != 0) THEN
DBMS_OUTPUT.put_line(SQLERRM);
END IF;
END;
For example if insert statement 100 fails the exception should be caught and execution should pass to 101 statement.
View 13 Replies
View Related
Jun 12, 2012
I am writing a after trigger for one of my tables on every insert update and delete for my dataware house staging area. The process here is when ever there is a change in the production database we need to capture this change in our changing area through triggers.
I am able to create the triggers but i am stuck with the exception handling portion of the trigger. I want to write an exception in the trigger where when the staging area is locked or for any other matter the data needs to be able to go to a error table when the staging area is not able to accept the data for some reason.
how i can write this excepyion in the trigger or anyother method i can follow to be able to handle this scenerio.
View 2 Replies
View Related
Aug 17, 2012
For the following procedure if I send the existed employee number of emp table as input. The procedure is executing successfully. But if I send the employee number as input which does not exist in the emp table . The execution block does not handling the exception.
I am getting the following error.
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "RAKULA.SP_TEST_EXCEPTION_BULK", line 8
ORA-06512: at line 7
If I use WHEN OTHERS exception then I am able to handle that exception. Why it's happening like this.
CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception_bulk(i_empno NUMBER)
IS
t type_test1;
BEGIN
SELECT deptno BULK COLLECT INTO t
FROM emp
WHERE empno=i_empno;
[code].......
how to handle that exception.
If I create the procedure without using
BULK COLLECT then I am able to handle that exception using WHEN NO_DATA_FOUND
In the following procedure I am able to handle the exception.
CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception(i_empno NUMBER,v_dept_no OUT NUMBER)
IS
BEGIN
SELECT deptno INTO v_dept_no FROM emp
WHERE empno=i_empno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('employee number' ||i_empno|| 'does not exist');
END sp_test_exception;
/
View 2 Replies
View Related
Dec 31, 2012
I am writing a cursor and inside that cursor I am checking record exists or not and based on that I am doing my operation.But I am getting error that i can not use exception inside cursor see the below sample code (syntax may not be correct)
sample code------------------
cur c1
is select * from T1;
open c1
loop
fetch c1 into cur_id;
select name into var_name from t2 where id = cur_id;
exception
when no_data_found then
continue with next cursor value
end
update t3 set name = var_name where t3.id = cur_id;
commit;
end loop;
end;
View 6 Replies
View Related
Feb 7, 2013
I have the following data stored as a CLOB in my table
<financialTransaction xmlns="http://lombardrisk.com/reform/dfa/2012/02" xmlns:cfpml="http://www.fpml.org/FpML-5/reform/2012/02" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://lombardrisk.com/reform/dfa/2012/02 StandardisedTransactionInterface.xsd ">
<header>
[code]........
I am trying to convert this into a relational DB view.
--CREATE OR REPLACE VIEW V_TRADES AS
SELECT
x.*
FROM ENGINE_ENTITIES,
XMLTABLE(xmlnamespaces('http://lombardrisk.com/reform/dfa/2012/02' as "n1",
'http://www.fpml.org/FpML-5/reform/2012/02' as "n2",
'http://www.matchingservice.com/coding-scheme/messageId' as "n3",
'http://www.matchingservice.com/partyId' as "n4",
[code].......
I have multiple namespaces used in that CLOB. how to use it in the x Query?
View 3 Replies
View Related
Aug 8, 2013
I have created a procedure which sends e-mail using UTL_SMTP. The procedure has a part in which we add the attachments to e-mail. Now , the issue is when i am adding an attachment which contains multibyte characters , these characters are replaced with '?'.
View 6 Replies
View Related
Jul 17, 2012
Here's sample code :
declare
i number;
l_rec number;
cursor c1 is select i from t1;
begin
[code]....
After executing Data must be inserted into t2..If any error is there it should insert into t2 with status 'E' and move on T2
i fflag
---------
11 E
111 Y
1111 Y
The code above is failing after inserting 11 E into t2 table
View 5 Replies
View Related
