SQL & PL/SQL :: Cursor For Loop Updates Only One Record?
Feb 17, 2012
I have a table of 3 columns:
SQL> show user
USER is "ANDREY"
SQL>
SQL>
SQL>
SQL> --create the table:
[code]...
I insert rows into it:
SQL> --fill it with data:
SQL>
SQL> insert into a(key1 , key2) values (1 , 1);
1 row created.
SQL> insert into a(key1 , key2) values (1 , 5);
[code]...
i want to perform a logic by which:for every distinct value of key1 - values of key2 will be checked in all records holding that particular key1 value, and update the key3 field to 'inactive' where the key2 value for that particular key1 is the highest in number.
i've found out that i could do it by an SQL statement:
update a
set key3 = 'inactive'
where key2 = (
select max(key2)
from a a2 where a2.key1=a.key1
);
however I wanted to use the cursor to "load" the max key2 values FOR EACH distinct key1 value exists in the table,and do the same thing as the update statement above WITH A CURSOR,So tried and wrote the following:
SQL> create or replace procedure proc1
2 IS
3
4
5 var1 a.key1%type;
[code]...
unfortunately, it works only for one row, and i don't understand what's wrong, I executed, and checked what has changed:
SQL> exec proc1;
PL/SQL procedure successfully completed.
SQL> select * from a;
KEY1 KEY2 KEY3
---------- ---------- ----------
1 1 active
1 5 incative
2 24 active
2 21 active
ORA-01034: ORACLE not available
View 10 Replies
ADVERTISEMENT
Dec 7, 2010
i m using oracle 10g 10.2.0.2 version.i create a form and using check box on this form.when i click this check box then loop is using behind it.and current cursor is going to last record
i want if i click 4 record then cursor is still showing on 4 record mean i click which record after using loopmy current cursor is showing on that particular record
how it is possible
View 1 Replies
View Related
Feb 5, 2013
This is my stored procedure
I have below store procedure:
create or replace
PROCEDURE TESTPERFORMANCE (
o_statuscode OUT NUMBER,
o_statusdescription OUT VARCHAR2,
starttime out timestamp,
time_after_query_TESTJOB out timestamp,
[Code]...
This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.
View 12 Replies
View Related
Aug 30, 2012
How to update a form fields which taking a searched value from another table .
I have a form that records student data and which has a drop down to select the 'student registration No' that comes from different table.
When insert or update a student record, first we select the student registration No from drop down to see if he is an existing student.
If he is already registered student then relevant student name and other details should be shown in the form and should be able to enter few other details as well.
View 3 Replies
View Related
Jul 30, 2012
When a cursor returns no records, how to handle this in Cursor for loop..
I had given sample code for handling this by using normal open,fetch statement, provide by applying for -Cursor For loop
DECLARE
P_BU_ID NUMBER;
P_SUMRY_DATA_TY_ID NUMBER;
P_OP_DATE VARCHAR2(32767);
P_PROC_DATE VARCHAR2(32767);
P_FIN_YEAR NUMBER;
[code]........
View 3 Replies
View Related
Jun 15, 2012
I am an experienced SAS programmer jumping into PL/SQL for the first time and have already encountered a problem.Let's assume I have 7 records (shown below). (In reality, I have millions of records, but the concept's the same.) The Value field is only populated when it changes. Therefore, I am forced to "fill in the gaps" as I read the data file. It's fairly straightforward. I carry the value foward one record at a time, using it if the Value field in not populated. The ANSWER I want is also shown.
In reading through a PL/SQL book, I realized that only 2 chapters are relevant to what I do. My guess is this solution involves cursors; probably a Cursor FOR loop,
ID Year Amt ANSWER
1 2010 200 200
1 2011 ..... 200
1 2012 ..... 200
2 2009 300 300
2 2010 ..... 300
2 2011 450 450
2 2012 ..... 450
View 5 Replies
View Related
Sep 11, 2012
I have a cursor returning some value.
for each value returned by the cursor i need to traverse through 31 rows(1 row per day * no of days in the month).
E.g. if cursor returns service_name as xyz then for xyz there can be 31 rows(service may not be used on some days)
I need to go to all of them and take some values and move them to a flat file. how should that be done?
Attached File(s)
Query.png ( 20.99K )
Number of downloads: 9
View 1 Replies
View Related
Jul 9, 2013
BEGIN
FOR i IN 1 .. 10
LOOP
[Code]....
Assume the above is my code; there is inner and outer block.
Currently if inner block is failed, the outer block also going to exception block
But, my requirement is if inner block is failed it should not go to outer block, still the loop should continue and print 1...10 rows
View 9 Replies
View Related
Jun 10, 2013
I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.
However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'...how to handle db_links using a cursor in a pl/sql block? The code is as follows:
DECLARE
db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;
[code]....
View 3 Replies
View Related
Nov 19, 2011
I am having a scenario where i have a ref cursor opening and fetching though dynamic sql query. And those values which i get from ref cursor i want to use them for other parameter cursor in for loop.
for example
PROCEDURE script (
p_account_no IN VARCHAR2,
p_from_date IN DATE,
p_to_date IN DATE,
p_subledger_code IN VARCHAR2,
p_posted IN VARCHAR2,
v_alloc_unalloc IN OUT alloc_unalloc, -- ref cursor declared in package specification.
[code]..........
View 12 Replies
View Related
Apr 20, 2011
declare cursor c_abc (v_eno in varchar2,v_ename in varchar2) is
select empno,ename from emp
where empno=v_eno
and ename=v_ename;
v_eno emp.empno%type;
v_ename emp.ename%type;
begin
for re_ab in c_abc(1,'Dummy') loop
dbms_output.put_line( to_char(re_ab.v_eno)||' - '|| re_ab.v_ename);
end loop;
end;
/
I am getting error message ORA:06550
V_ENO must declared ..
Parameterized cursor for loop ..
View 3 Replies
View Related
Jul 1, 2011
I wrote the following block :
set serveroutput on
declare
rec employees%rowtype;
cur SYS_REFCURSOR;
begin
open cur for 'select * from employees where rownum<:a' using 4;
for i in cur
[code]....
It gave errors if we execute is as such, but worked when I commented out the for loop and instead de-commented the simple loop. Does that mean that FOR cannot be used to loop through the records of a ref cursor ?
View 14 Replies
View Related
Nov 19, 2010
I am not an Oracle programmer but I have been given a task to produce a number of Text files from an Oracle table based on a selection from the table itself.The table consists of many records with a currency code and I need to extract the data into named files based on the currency code.My first idea was to use Cursors and try a select statement matching on the returned value of the cursor but no Joy I can't make it work - perhaps I need to take a new approach.
Current / latest attempt below
DECLARE
CURSOR tmp_splitter_cur IS
SELECT DISTINCT end_consumer_country
FROM tmp_splitter
WHERE 1 = 1;
currency_rec tmp_splitter_cur%ROWTYPE;
[code]....
View 6 Replies
View Related
Feb 25, 2011
Is it possible to:
-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;
[Code]....
View 3 Replies
View Related
Dec 15, 2011
I take a select into a cursor and process it record by record.I have to do sum based on a column and display row by row by using dbms_output.put_line .... So the sum has to happen based on a column. Based on the column value i need to display the cumulative sum as well.
Example:-
col1 col2 amount
DL AADD 25
DL BBCC 10
DL BBRR 15
Sum value for DL ----- 50
TX ADED 20
TX EDWW 60
Sum value for TX ----- 80
All the above data should be displayed using DBMS_OUTPUT.PUT_LINE in a pl/sql code. I use cursor to take the values from the table but the problem i face is .... I am not able to display the sum based in the col1 values.
Since i use the cursor .. i took the col1 values in to a variable and checked every time
old_variable = new_variable
if yes then continue the sum
else
display the sum value.
once i get the above check satisfied i am loosing a new col1 row in the check. The next loop only run for the new col1 values -1( which is used in the check loop).So is there any better way to get the solution or is there a facility to store the previous loop values in a cursor ? so that i dont have to loose that one row of data.
I am not able to come up with proper loop so which can identify that the col1 has changed and you have to display the sum value.
View 17 Replies
View Related
Jul 25, 2011
I would like to exit from a cursor loop based on certain conditional checking. I am checking for a lot of different parameters and if they fail, I want to bypass it and fetch the next record in the cursor. I tried just putting an 'Exit' statement in the logic, but it fails. An example of my code is below:
For Row1 in cursor1
Loop
If amount < 0 then
balance := 0;
Else
[code]...
View 8 Replies
View Related
May 13, 2010
I'm running a PL/SQL with a For Loop cursor, but when trying to execute it doesn't run. It is as if there is no data, but I ran the cursor separately in a SQL Plus session and it runs perfectly. I'm enclosing the file with the procedure.
View 18 Replies
View Related
Dec 26, 2012
the problem we are facing is that we are having some problems with names in a filed which are not letters but strange symbols inserted by a webservice which by the way has been corrected, but we have the discrepancy on out DB, so I've decided to write a PL/SQL procedure to correct them.
Here is the example of an incorrect record.
4047254| STEVE; ROVINSON (THE INCORRECT SYMBOL IS CHR(32), I do not know why the forum doesn't show it)
So, if you look there's a symbol that is not interpreted by the DB and my original idea was to extract them and correct then with another procedure. I have originally wrote a procedure with the logic if there's a symbol that is not beween currect letters (from A to Z including semicolon (;)) extract them and that's it. So, the big question is, How I transfer the control from the second loop to the cursor loop in order to analice the next record.
here is the code which hasn't been completed yet.
DECLARE
CURSOR get_nombre IS
SELECT CNTA_NOM_ABRV
FROM CUENTA;
[code]...
View 9 Replies
View Related
Mar 7, 2011
what is the difference between using a cursor and using a normal for/while loop to retrieve and process the result set.
View 3 Replies
View Related
Aug 8, 2012
How can we pass column index or column name in cursor for loop.
View 3 Replies
View Related
Jul 21, 2011
I loop through the 1st cursor (account_csr), while in the 1st csr loop, based on some conditions being true, I want to loop through a 2nd cursor (acctper_csr) but I only want to retrieve data/rows in the 2nd cursor where the account_id column in 1st cursor = account_id column in the 2nd cursor. This will enable me to pull all the account_periods for each account I loop through in the first cursor.
I have attempted several different ways and cannot make this work. Thought I could somehow define a variable and store the account_id from 1st cursor and use on the 'where' clause in the 2nd cursor definition. Have not been able to make this work successfully.
Following is the sample of my
--First cursor (accounts)
CURSOR account_csr is
SELECT *
FROM s_dev_xref1.account A
WHERE a.source = 1
[Code]...
View 7 Replies
View Related
Nov 1, 2011
Oracle Version: 11.2.0.2.0. I have two explicit cursors and I would like to choose at run time which one to run. Here is a simplified code snippet of what I am doing today:
DECLARE
CURSOR Cursor_A IS
SELECT * FROM EMP_A;
CURSOR Cursor_B IS
SELECT * FROM EMP_B;
RUNA CHAR(1) := 'Y';
[code]....
I want to avoid maintaining the same long list of transformations. I also want to avoid, if possible, an explicit FETCH INTO, because there are hundreds of fields in both tables. I'm looking for something like this (and I know this doesnt work):
DECLARE
CURSOR Cursor_A IS
SELECT * FROM EMP_A;
CURSOR Cursor_B IS
SELECT * FROM EMP_B;
RUNA CHAR(1) := 'Y';
CursorToRun IS REF CURSOR;
[code]....
View 4 Replies
View Related
Feb 19, 2010
We have a fact table t1 in the warehouse which has above 6 million records.There is to be an update like this where t2 has aid+bid as composite primary key. column aid repeats in t1.There's performance problem and we'v been told to break this huge update into pieces with few commits in the middle.
update t1
set t1.aid =
(select t2.aid from t2
where t1.bid = t2.bid
)
I've tried cursor loop with 3 commits in the middle based on if condition that evaluates on every iteration.
View 8 Replies
View Related
May 25, 2011
I have a cursor which I am opening and then looping through. Within this loop I am comparing attributes within this cursor with attributes from another loop that this one is within.
you will see in the IF statements (there are several distinct IF statements within the loop) that there is a check which assesses if the attributes are not equal.
If they are NOT, the value of v_mismatch is set to 1
What I need to do instead of setting this to 1, is to go to the next record in the loop. How can I achieve this?
OPEN c_distMatrix;
LOOP
FETCH c_distMatrix INTO r_dist;
EXIT
WHEN c_distMatrix%NOTFOUND;
--compare each field and update the counter
[code].....
View 5 Replies
View Related
Aug 27, 2012
in my loop with cursor in a procedure body i am displaying some field values to create a report and after every record i am displaying horizontal line(-------) but i don t want this line to appear after the last record displayed like below,
gfsfsf gsgfsfds gsdgfdg
------------------------------------
edyet gdgtdgt gtdfdfdgd
------------------------------------
dfds hedhgg idudhdh
how can i achieve this within pls/sql procedure body.
View 20 Replies
View Related
Oct 11, 2013
I have a plsql block construct where i want to use for loop dynamically , the query which for cursor for for loop will accept the table name from parameter and join them to return the result. the resultant data will iterate in loop and do the execution.
DECLARE
--initialize variables here
v_date varchar2(10);
v_rebuild_index varchar2(250);
v_sql VARCHAR2(250);
p_table_name varchar2(250) := 'DS_ABSENCE';
p_source_table varchar2(30) := 'STG_ABSENCE';
p_source_owner varchar2(30) := 'STG_SAP';
v_for_sql varchar2(1000);
[code]....
View 7 Replies
View Related
Nov 18, 2009
i HAVE THE FOLLOWING CODE WRITTEN IN A *.pc FILE. I am trying to loop to fetch data from cursor. But the code exist after it fetches the first record. Let me know what is it the right way to fetch data from cursor?
EXEC SQL BEGIN DECLARE SECTION;
char str[64];
EXEC SQL END DECLARE SECTION;
/*cursor declarations*/
EXEC SQL DECLARE Get_SQLText_Cursor CURSOR FOR
[Code]....
View 6 Replies
View Related
Jul 3, 2010
i made this loop to commit the same item but depend on the count of suppliers to set the same item every time to different supplier . but the problem this code commit only the first sypplier but not any othere one ..
code : { key - commit - trigger }
declare
var_record_count number ;
VAR_p_request number;
begin
var_record_count:= get_block_property('control',CURRENT_RECORD);
[code]....
View 2 Replies
View Related
Jul 29, 2013
I am using a record type to print some column in a same line.
Eg: I want to create index on some composite key columns. But i dont know how many columns are there. So want to use a loop which will count the number of column and then create the index like:
CREATE INDEX PRODUCT.XIF1AGMNT_PROD ON PRODUCT.AGMNT_PROD(LOAN_ID,LOAN_PROD_STRT_DT) TABLESPACE PRODUCT_INDEX;
View 5 Replies
View Related
Jul 5, 2012
How to insert null record (for some columns) in table using loop.
sample data of x_tab
order_id order_name
231 xxx
123
345
111 vvvv
View 5 Replies
View Related