PL/SQL :: Possible To Use Forall Instead Of For Loop - End Loop
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;
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?
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.
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].....
[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.
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
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?
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,
------------------------------------------------------------------------------------------------------- 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
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,
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.
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;
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]........
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]....
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.
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.
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
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]..........
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 ..
PROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2, P_Txn_Ccy IN VARCHAR2, P_Allocation IN VARCHAR2, l_lty_id IN VARCHAR2 ) IS l_balance_fmt NUMBER := 0; BEGIN [code]....
it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..
