SQL & PL/SQL :: Execute Immediate Inside A WHILE Loop
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.
I have below peice of SQL,Im trying to form the P_attribute1 value by using the Loop index(i) but the P_attribute1 value which i assigned in declaration not displaying in the output.
DECLARE P_attribute1 VARCHAR2(100) := '110000027'; P_attribute VARCHAR2(100); l_cnt NUMBER := 10; v_buffer VARCHAR2(500); v_buffer1 VARCHAR2(500); BEGIN FOR i IN 1 .. l_cnt LOOP v_buffer := v_buffer || '' || 'P_attribute' || i || '' || '!'; END LOOP; dbms_output.put_line('v_buffer :' || v_buffer); END; v_buffer :P_attribute1!P_attribute2!P_attribute3!P_attribute4!P_attribute5!P_attribute6!P_attribute7!P_attribute8!P_attribute9!P_attribute10!
I have a select..bulk collect into clause which is inside a for loop as the query gets a parameter from the loop, Then, how to extend the collection rows for each iteration.
set serveroutput on declare a int; begin execute immediate 'select employee_id from employees where first_name=:ab' into a using 'Donald' ; dbms_output.put_line(a); end;
but this one doesn't
set serveroutput on declare a int; begin execute immediate 'select employee_id into :1 from employees where first_name=:2' using a,'Donald'; dbms_output.put_line(a); end;
Am I not allowed to specify a bind variable with an into clause inside execute immediate ?
can we place insert statement in loop inside anonymous block?
CREATE TABLE DEP(DEPTID NUMBER(5) NOT NULL PRIMARY KEY,DNAME VARCHAR2(10),LOCID VARCHAR2(10)); DECLARE I NUMBER(5); BEGIN I := 0; LOOP INSERT INTO DEP VALUES(&DEPTID,'&DNAME',&LOCID); I := I+1; EXIT WHEN I = 5; END LOOP; END;
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):
***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?
Requirement is to build procedure where it has 10-12 input variables but some of them (input variables) may at times be NULL.Based on this, i thought of getting into EXECUTE IMMEDIATE but this would just return rows i mean DML stmt for EXECUTE IMMEDIATE.Also, on the requirment is all parameters are present then result set be based on range on start and end date.
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.
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS (SELECT ph.* FROM po_header ph WHERE 1 = 2), pf AS (SELECT DISTINCT pf.order_id, pf.fund FROM po_fau pf, ph WHERE 1 = 1 AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746 po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor; dbms_sql.parse(nDDL_Cursor, sSQLStr, 2); FOR x IN 1 .. nCols LOOP sCols(x) := ''; dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100); END LOOP; nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
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;
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
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?
I am having a view say name vw_mytable , i need to call this view inside the stored procedure it is saying as Grant execute on
usera.vw_mytable to userb;ORA-02204: ALTER, INDEX and EXECUTE not allowed for views
how do i create a store procedure to call a view from another user.Usera is having a view and i need to create procedure in userb and call usera.vw_table.
In the follow code example, is it possible to save the seeds that I generated into a table when I call this table function without expliciting doinginsert into <some_table>select select * from table(pkg_seed.getSeed(200)); I try the automonous_transaction clause but it does not work.
--drop package pkg_seed--drop type seed_tab CREATE or replace TYPE seed_rec AS OBJECT( id number,seed number); CREATE or replace TYPE seed_tab AS TABLE OF seed_rec; CREATE or replace PACKAGE pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED;END pkg_seed;/ CREATE or replace PACKAGE BODY pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED IS cursor cur_seed(vmaxrow number) is select rownum id, floor(dbms_random.value(1,1000) ) seed from dual connect by level <= vmaxrow; l_seed cur_seed%rowtype; BEGIN open cur_seed(maxrow); LOOP FETCH cur_seed into l_seed; pipe row(seed_rec(l_seed.id,l_seed.seed)); END LOOP; RETURN; -- the function returns a single result END getSeed;END pkg_seed;/ select * from table(pkg_seed.getSeed(200));
Below is the block which i am trying to test in scott schema. I dont want to substute IN clause values directly. So i have written cursor and have added in separate variable separeated by comma.But its not working.
declares varchar2(1000);s1 varchar2(1000);v number := 0;v1 varchar2(2000) := 'SCOTT';j number := 0;cursor hhis select ename from emp;beginselect count(*) into v from emp; for i in hh loops := s||''''||i.ename||''''; j := j+1;if j <> vthen s := s||',';end if;s1 := s1||s;s := null; end loop;dbms_output.put_line(S1); case when v1 in (s1) then dbms_output.put_line('Y'); else dbms_output.put_line('N'); end case;end;
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,
