Explanation needed

[nahom]

Hi, can any one explain me what function of this for loop and the 'execute immediate command line' . C_ALTER_INDEXES is a cursor

FOR L_ALTER_INDEXES IN C_ALTER_INDEXES
LOOP
DBMS_OUTPUT.PUT _LINE(L_ALTER_I NDEXES.INDEX_AL TER);
EXECUTE IMMEDIATE L_ALTER_INDEXES .INDEX_ALTER;
END LOOP;

[debasisdas]

What Is The Problem With The Code ?

Is That Your Code ?

[nahom]

What Is The Problem With The Code ?

Is That Your Code ?

no it is by some one else and i am unable to understand the usage of the Execute Immediate function.

[amitpatel66]

no it is by some one else and i am unable to understand the usage of the Execute Immediate function.

Let me brief you on what is the use of EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE is used to execute dynamic sql statements (queries). The sql statements which are not known till runtime are dynamic sql statements which are constructed dynamically and executed using EXECUTE IMMEDIATE statement.

The piece of code that you have provided is not enough for our experts to explain you in detail.

Please provide full source snippet, what is the cursor all about, and what data the cursor handle holds etc.

[nahom]

Thanks very much for this explanation I am able to make sense of the remaining code.

[amitpatel66]

Thanks very much for this explanation I am able to make sense of the remaining code.

You are welcome :) .

[subashsavji]

Hi, can any one explain me what function of this for loop and the 'execute immediate command line' . C_ALTER_INDEXES is a cursor

FOR L_ALTER_INDEXES IN C_ALTER_INDEXES
LOOP
DBMS_OUTPUT.PUT _LINE(L_ALTER_I NDEXES.INDEX_AL TER);
EXECUTE IMMEDIATE L_ALTER_INDEXES .INDEX_ALTER;
END LOOP;

some of example of execute immediate and cursor.

 
DECLARE
2 MY_CURSOR NUMBER;
3 RESULT NUMBER;
4 BEGIN
5 MY_CURSOR := DBMS_SQL.OPEN_CURSOR;
6 DBMS_SQL.PARSE(MY_CURSOR,'SELECT 1 FROM DUAL',0);
7 RESULT := DBMS_SQL.EXECUTE(MY_CURSOR);
8 DBMS_SQL.CLOSE_CURSOR(MY_CURSOR);
9 END;
10 /
 
both compilation(parsing) and execution happens at same time.
//
create table Test
(
Id integer,
SQLString varchar2(4000)
);
 
insert into Test values (1, 'select count(*) from Emp');
insert into Test values (2, 'select count(*) from Dept');
insert into Test values (3, 'select count(*) from User_Tables');
 
commit;
 
Now, actual PL/SQL block:
 
declare
 
vCount integer;
 
cursor csrSQLToExecute (pId Test.Id%type)
is
select *
from Test
where Id > pId;
 
begin
 
for rec in csrSQLToExecute(0)
loop
execute immediate rec.SQLString into vCount;
dbms_output.put_line('String: ' || rec.SQLString ||
'. Result: ' || to_char(vCount));
end loop;
 
end;
//
 

[amitpatel66]

Duplicate posts deleted for better management of the forum

MODERATOR