SQL & PL/SQL :: Getting Next Data With Loop
Jun 14, 2013
This query that I pasted is working correctly.Let's say a case has 4 owners, it finds me the first owner and show me its address.
However, I want to execute this query for all owners so it should jump the previous owner it found. Lets say for that example that the loop ends at 4.
How may I fix this problem so that the loop do not return always the first owner but it keeps getting 1,2,3,4...also I should increase the sequence value for each situation
SELECT
DECODE(BREINV.NAMEKEY, NULL,'0','1') "BRE_INV",
DECODE(BREINV.NAMEKEY, NULL,' ',BREINV.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV.NAMEKEY, NULL, ' ', DECODE(BREINV.SEQUENCE,NULL,NULL,RTRIM(ADDINV.FORMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY.COUNTRYDESCRIPTION,1,30))) "BRE_NOMINV",
INDIVIDU.FIRSTNAME || ' ' || INV.NAME "BRE_NOMPREINV"
[code].......
View 3 Replies
ADVERTISEMENT
Mar 7, 2012
create table stg1(x number, y number);
create table stg2(x number, y number);
create table stg(x number, y number);
I want to insert data from stg1, stg2 into stg
Instead of writing two insert statements, I want to write only one in a for loop to insert data into stg from stg1 and stg2
I tried this
begin
for i in 1..2 loop
insert into stg(x,y) select stgi.x, stgi.y from stgi;
end loop;
end;
it gives me table does not exist error:
so by stgi, i mean it should take stg1, stg2 etc
View 4 Replies
View Related
Mar 23, 2012
I have dcs_sku table .The record count is 50 thousand in that table.My requirement is to fech every row,create an xml out of it and post the data to some third party.As the count is very huge,I can't select the entire record and do the operation at a time.way which I will run the sql query in a loop,which will fetch 1st from rown 1 to row 1000,next 1001 to 2000,2000 no 'n' row...
I tried the below query:
select * from dcs_sku where rownum between 1 and 200...This gave me the 1st 200 rows and worked fine.
but the moment I changed the query to :
select * from dcs_sku where rownum between 201 and 300:::No result was coming up.
View 1 Replies
View Related
Aug 3, 2011
Need to create Sproc(loop) to insert data..We have 2 table:
- BE Table
Name Null Type
---------------------- -------- --------------
BID NOT NULL NUMBER(10)
BUSINESSENTITYTYPEID NOT NULL NUMBER(10)
PARENTID NUMBER(10)
VERSION NOT NULL NUMBER(10)
FULLNAME VARCHAR2(255)
PHONE VARCHAR2(255)
FAX VARCHAR2(255)
[code]....
Whenever the BE table is read it gives more than 80K records from cust_id=1 and so on and becomes a bit slow ..
Now, when we copy the records from BE to CUSTPAYMETHOD (assume 30k). Next time when we read the table it should show us the customerid from 3001based on New Customer's(user created) Customerid.
CUSTID column is logically linked with BID column (FK not enforced) We don't have to pass parameter as we know the cust_id=1,flag for suspended='Y' and Eff_date between date1 and date2..We have accept from/to Period.Probably a loop to insert...This particular SP will run in all env and the id is going to be different how we are going to handle this...
We want to have a procedure. This is to purge BE table.
1) Insert a new parameter CUST_PMNT_MTHD_RETENTION_PERIOD into system_parameter table(which is only a parameter table) -- this can be done outside of Proc.
2)Create a new Customer(user id) through the Customer application and then find out the custID for the new customer -- this can be done outside of Proc.
SP should be for:
- Store Proc will Update the CustID in the CustPayMethod table to the new customer ID (that was created) when the EFFECTIVESTARTDATE older than CUST_PMNT_MTHD_RETENTION_PERIOD(from system_parameter table) days and suspended=’Y’.
- Copy records with custid=1(anonymous user) and startdata older than retention_period and suspended='Y' to Table CUSTPAYMETHOD
Eventually,delete the record from BE table.BusinessEntity(parent) table is information about customer and customerpaymentmethod(child) table is about payment mode(how payment is made by customer).
Assume If BID=1 then we can find Cust_id=1(which can have many records as the payment mode may change because the customer pay by different mode(credit,debit etc).We have to use loop and commit every 200 or 500 records.
FIrst we have to update the table with new cust_id and then insert...
View 1 Replies
View Related
Dec 13, 2012
i need to do a loop on a table and export the data in Excel format (so i need a procedure to do it).
write a java class that build this Excel.. oracle procedure loop around the data and every step my Java class write on Excel file.
So, for this i should initialize my java class for example
public class ExcelExporter{
private String fileName;
public ExcelExporter(String fileName){
this.fileName = fileName;
}
}
So, for this issuse i should call the ExcelExporter costructor from my PL/SQL so i can create an object of it and next i use this to populate my Excel.
How its possible to create Java object from PLSQL ? I've seen on the net all procedure call only Java static methods...
View 5 Replies
View Related
Jul 1, 2013
I am trying to create an anonymous PL/SQL block to output privilege information for each of the users listed in DBA_USERS In a loop. This is my block so far (not finished):
declare
v_usr varchar2(30);
v_out_header varchar2(100);
[Code]....
The output is as follows:
***User-Role Privilege report***
-----------------------------------
username: ANDREY , profile: DEFAULT
SYSTEM privileges granted directly to the user(not through ROLE) :
no_data_found
A problem I am encountering is that for some users I have no direct privileges that are not granted through roles, And when I have the expression v_qry (which is basically "'select grantee ||'',''|| privilege from DBA_SYS_PRIVS where grantee not in (select role from dba_roles) and grantee ='||'''' ||v_usr||''''") not initialized with values because the select statement retrieved 0 results, I have the process interfered by the no_data_found error/exception.
Questions: how I can preferrably simply, avoid/overcome my problem? Some way to make the loop go on in spite of no data found? maybe something similar to NVL?
View 14 Replies
View Related
Nov 19, 2012
CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;
can i use forall instead of 'for loop ..end loop'
View 10 Replies
View Related
May 25, 2010
I have a list of strings ( like a,b ,c) that I want to loop againts. I will be creating string to execute them as dynamic sql.I could have put the string in a table and loop againt the table but this is a deployment script so I do not want to create any table.I can also do
DECLARE
cursor c is SELECT 'a' FROM dual UNION SELECT 'b' FROM dual ;
BEGIN
for i in c
loop
.......
end loop.
END;
But I have many strings to loop over. What is the best way to achieve it. Can i use collection to achieve this?
View 4 Replies
View Related
Feb 17, 2010
I have a table which I will call 'fruit basket' which contains the following data:
basket_ref, fruit, fruit_serial
1, apple, 1
1, banana, 2
1, pear, 3
2, apple, 1
2, lemon, 2
What I want to produce is a table in the following format
basket_ref, all_fruit
1, apple banana pear
2, apple lemon
There could theoretically be any amount of fruit in a given basket, so I will need to set up some kind of loop in order to read each record from the original table. I am not sure how it would be best to do this.
View 2 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
Jun 27, 2011
[CODE]
DECLARE
D1 DATE:='&D1';
D2 DATE;
BEGIN
WHILE D1<=D2 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY'));
D1:=D1+1;
END LOOP;
END;
Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.
View 9 Replies
View Related
Aug 28, 2012
begin
for i in 1..10000 loop
update table1
set col1= col1+1
where type =1;
commit;
end loop;
end;
My question is, if a strored procedure contains the script above and the said procedure will be invoked by two or more sessions at the same time. Does it mean that the 1st session will lock the related rows and other sessions will have to wait for loop in session one to finish?
View 18 Replies
View Related
Sep 22, 2012
Declare
x number;
Begin
Loop
if NVL(x,1)>=1 then
[code].......
May be this code is going into an infinite loop. I'm unable to understand where the problem is?
View 6 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
Jul 20, 2012
I want to do something like this
-------------------------------------------------------------------------------------------------------
While Condition
LOOP
BEGIN
Insert into table1 values(......);
[Code]...
EXCEPTION
When OTHERS THEN <capture the error while inserting into an Error table>
END;
END LOOP;
-------------------------------------------------------------------------------------------------------
Now I want that If one of the insert statement within the loop fails (say table2) the exception should be captured and next insert statement (table3) should be executed.
How can I do this ? I guess I wont even need a loop
View 10 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
Jul 5, 2012
I have the following function:
CREATE OR REPLACE FUNCTION get_project_id(
schema_p IN VARCHAR2,
table_p IN VARCHAR2)
RETURN VARCHAR2
IS
projects_pred VARCHAR2 (400);
[code].......
I am trying to get the projects a user has from the works_on table (user_id, project_id). The user_id is retrieved from the context projects_ctx. I am getting the error Function created with compilations errors.
View 6 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
Jun 1, 2013
How the loop back entry in /etc/hosts relates to listener?
View 1 Replies
View Related
Feb 19, 2010
I want to update column in table 1 based on a substraction of two column, one from the same table and the other from different table. Then update the result of substraction in table 1. Number of rows in two tables are different.
--for r in (( select (table2.y - table1.y as x from table1, table2 where table1.x = c and table2,.x = m))
declare
i number := 1;
c number ;
m number;
[Code]....
View 8 Replies
View Related
Nov 8, 2011
How do I loop through a Input parameter (varchar_table) and pass the input value to a select query.
Procedure Test
(
param1 IN dbms_sql.varchar2_table
)
[Code]....
1. How do I define temp_tbl and is it a best practice to use temporary table - if not what is the best method to do the same.
View 5 Replies
View Related
Apr 5, 2010
Is it possible to have multiple variables in a for loop. For expample:
FOR i AND j in 1..10
I was wanting to increase one of the variable more than the other.
View 8 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
Nov 1, 2013
I am trying read a File to find out the error message through UTL_FILE.m. But the loop used inside is not getting closed.
CREATE OR REPLACE procedure alert_mail_A
as
v_flag varchar2(10);
mesg varchar2(100);
c1 utl_smtp.connection; -- passing the datatype to a variable.
c varchar2 (1000);
B utl_file.file_type;
[code]........
View 1 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
Apr 7, 2010
a project I'm working on. I normally work in SQL Server, so I'm a little stuck on this one.
I have a temp table (tmp_stack) with four columns:
Floor [varchar]
Unit [varchar]
Block [number]
BlockStart [number]
BlockEnd [number]
BlockStart and BlockEnd are currently NULL. What I need to do is loop through the table for each Floor and update BlockStart and BlockEnd for each Unit depending on how many blocks they use and how many have been used by prior units on that floor.
For example:
There are three units on Floor #1: 1A, 1B, and 1C.
1A = 5 blocks
1B = 3 blocks
1C = 2 blocks
For 1A, BlockStart should = 1 and BlockEnd should = 5
For 1B, BlockStart should = 6 and BlockEnd should = 8
For 1C, BlockStart should = 9 and BlockEnd should = 10
And everything should reset back to the beginning on successive floors.
In T-SQL, I would use a cursor, and I assume I need to do the same kind of thing in Oracle, but I can't figure out the syntax.
View 8 Replies
View Related
Jul 3, 2010
i want check some data from one process instead of last record
i.e
process data
uuu 1
2
3
4
ppp 1
2
3
4
[code].....
View 2 Replies
View Related
Jan 6, 2011
I have a dynamic query which i want to run till it return zero records.
I am using WHILE loop for that but it is giving compilation error:
The query is
execute immediate ' Delete from tbl_archive_trade_list
where deal_id in (
select deal_id from tbl_archive_trade_list where trade_id in (
select trade_id from ' || main_trade_group_table || ' where tradegroup_id in (
select tradegroup_id from ' || main_trade_group_table || ' a , tbl_archive_trade_list b
[Code]...
I want to run this Query in While loop till the above command return 0 records.
I tried giving the above statement inside WHILE loop but it is failing.
Without the WHILE loop the above statement works fine and executed properly.
View 5 Replies
View Related
Jun 26, 2012
I have one emp table, in which i have sal, ename,empno etc columns. Now i am trying to replace each digit of salary with '*'. like if salary is 10000(it has five digit) then it will be replace by *****, but unable to do so. I don't how to achieve this, can we use translate or replace function in this.
Well i have achieved this by using case statement but where hardcoding is done...see the case below:-
select substr(ename,1,8),case when length(sal)=2
then '**'
when length(sal)=3
then '***'
when length(sal) = 4
then'****'
when length(sal)=5 then
[code]....
View 27 Replies
View Related
