SQL & PL/SQL :: Difference Between Cursor And Normal For / While Loop?
Mar 7, 2011what is the difference between using a cursor and using a normal for/while loop to retrieve and process the result set.
View 3 Replies
ADVERTISEMENT
PL/SQL :: Difference Between Materialized And Normal View?
Aug 29, 2012the difference in detail with an example.
View 5 Replies View RelatedSQL & PL/SQL :: Difference Between Normal And Cluster Index On Table?
Aug 4, 2010Difference between Normal Index on Table and Cluster Index on Table?
View -1 Replies View RelatedSQL & PL/SQL :: Cursor For Loop
Jul 30, 2012When 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]........
SQL & PL/SQL :: Cursor FOR Loop?
Jun 15, 2012I 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
Loop Inside Cursor
Sep 11, 2012I 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
SQL & PL/SQL :: Inner And Outer Loop In Cursor?
Jul 9, 2013BEGIN
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
SQL & PL/SQL :: Loop Through Db_links Via Cursor
Jun 10, 2013I 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]....
SQL & PL/SQL :: How To Use Ref Cursor Values In For Loop
Nov 19, 2011I 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]..........
SQL & PL/SQL :: Parametrized Cursor For Loop?
Apr 20, 2011declare 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 ..
SQL & PL/SQL :: Can FOR Not Be Used To Loop Through Records Of A Ref Cursor
Jul 1, 2011I 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 ?
SQL & PL/SQL :: SELECT From Within A CURSOR LOOP
Nov 19, 2010I 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]....
SQL & PL/SQL :: Find Previous Cursor Loop Value
Dec 15, 2011I 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.
SQL & PL/SQL :: How To Exit From For Loop On Cursor Correctly
Jul 25, 2011I 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]...
SQL & PL/SQL :: Procedure Cursor For Loop Don't Execute
May 13, 2010I'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 RelatedSQL & PL/SQL :: Transfer Control From Second Loop To Cursor?
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]...
Passing Column In Cursor For Loop
Aug 8, 2012How can we pass column index or column name in cursor for loop.
View 3 Replies View RelatedSQL & PL/SQL :: Cursor For Loop Updates Only One Record?
Feb 17, 2012I 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
SQL & PL/SQL :: How To Loop Through Nested Cursors Based On Value From 1st Cursor
Jul 21, 2011I 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]...
SQL & PL/SQL :: Choose Explicit Cursor To Loop Through At Runtime?
Nov 1, 2011Oracle 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]....
SQL & PL/SQL :: Cursor Loop With 3 Commits In Middle Based
Feb 19, 2010We 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.
SQL & PL/SQL :: Dynamic Cursor / Loop Will Accept Table Name From Parameter
Oct 11, 2013I 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]....
Precompilers, OCI & OCCI :: Pro* C Code To Fetch Cursor And Loop
Nov 18, 2009i 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]....
Forms :: Click Checkbox And Then Loop - Current Cursor Going To Last Record
Dec 7, 2010i 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
PL/SQL :: Difference Between Ref Cursor And Sys_refcursor?
Dec 6, 2012i found this difference between ref cursor and sys_refcursor.
If you specify return_type, then the REF CURSOR type and cursor variables of that type are strong; if not, they are weak. SYS_REFCURSOR and cursor variables of that type are weak.
my situation is i have to write many stored procedure for reports and also for entery pages. few enter pages use typed datasets and few does not.
which one is better in terms of performance and maintainability in above three conditions?
SQL & PL/SQL :: Difference Between Implicit And Explicit Cursor?
Feb 21, 2013What is the difference between implicit cursor and explicit cursor in PL/SqL?
And what is ref cursor ?
PL/SQL :: Difference Between Explicit And Implicit Cursor?
Nov 5, 2013difference between between these two constructs. Finally when i read the asktom.oracle.com , I was totally confused. The reason is thatTom says...we can retrieve more than one row in implicit cursor. If that would be case, what's the difference between these two cursors?? when to use?? My understanding was implicit cursors" ---> single-row queryExplicit cursors ---> multi-row query Experts
View 10 Replies View RelatedPL/SQL :: Difference Between Strong Ref Cursor And Weak?
Jul 13, 2012differnce between strong ref cursor and weak ref cursor
View 4 Replies View RelatedSQL & PL/SQL :: Difference Between With Parameter And Without Cursor In Pasted Code?
Apr 7, 2012create table eml(num numner(10),email varchar2(100));
begin
for i in 1..20 loop
insert into eml values(i,'email.'||i||'@mmm.com');
end loop;
end;
I just want to know what is the difference between below two pl/sql block, in one block i am passing the p_email_id as paramter to cursor.Both block are working fine and giving the same result.first thing , i want to know how the p_email_id is passing to cursor get_emil_info without paramter.and which is good to use, i mean without paramter or with paramter.
--without paramterised cursor
declare
v_cnt number;
p_email_id number;
TYPE email_tab is TABLE of NUMBER index by binary_integer ;
eml_id email_tab ;
CURSOR get_eml_id is select num from eml;
[code]...
--with paramterised cursor
declare
p_email_id number;
TYPE email_tab is TABLE of NUMBER index by binary_integer ;
eml_id email_tab ;
CURSOR get_eml_id is select num from eml;
[[code]...
PL/SQL :: Difference Using Cursor In Specifications And Body In Packages
Jun 24, 2013what is the difference using cursor in specifications & body in packages?
View 1 Replies View Related