?
Solved

execute immediate query

Posted on 2003-12-11
10
Medium Priority
?
3,514 Views
Last Modified: 2011-10-03
My code is

DECLARE
type snirec is record (abc_name  varchar2(200),
                               abc varchar2(100),
                               type  varchar2(4));
v_rec snirec;

v_final := 'select * from abc minus select * from dfg';
execute immediate v_final into v_rec;

Now i have v_rec which have some values. How do i read the values from this record?
After extracting each of the values in a loop, i need to do some more querying with each of these values.

Please help.

Regards,
Lakshmi
0
Comment
Question by:lakshminair
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 10

Expert Comment

by:Sys_Prog
ID: 9919345
Hi,

I do not think orcale would allow u to use the following

execute immediate v_final into v_rec;


if your SQL returns more than one rows

Amit
0
 

Author Comment

by:lakshminair
ID: 9919400
then is there some other way i can go about?

anyway i can store the output of my sql command and then loop through it?
0
 
LVL 10

Expert Comment

by:Sys_Prog
ID: 9919406
It would return u the following error

ORA-01422: exact fetch returns more than requested number of rows

Amit
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lakshminair
ID: 9919415
yes thats right. it does happen so.
can you pls suggest me something else?
0
 
LVL 15

Accepted Solution

by:
andrewst earned 300 total points
ID: 9919434
You can use BULK COLLECT:

DECLARE
type snirec is record (abc_name  varchar2(200),
                               abc varchar2(100),
                               type  varchar2(4));
type snitab is table of snirec index by binary_integer;
v_tab snitab;

v_final := 'select * from abc minus select * from dfg';
execute immediate v_final bulk collect into v_tab;
0
 

Author Comment

by:lakshminair
ID: 9919462
ok great. sorry for the next silly question.
now i have data in v_tab, how do i extract this data one by one?
wont v_tab.abc_name work?

say

LOOP
var1 := v_tab.abc_name

END LOOP;
0
 
LVL 4

Expert Comment

by:dasari
ID: 9919466
How abt declaring a cursor and loopin thru it........here's a sample procedure for a good start..

the following procedure loops through the records fetched through the query mentioned in the cursor and applies business logic.....

CREATE OR REPLACE PROCEDURE PPART.TRUNCATE_ALL
IS
TABNAME VARCHAR2(10);
SSQL      VARCHAR2(50);
EXP EXCEPTION;
CURSOR curTABLES IS
         SELECT TABNAME FROM TBDTABLES;
BEGIN

OPEN curTABLES;
LOOP
      FETCH curTABLES INTO TABNAME;
      EXIT WHEN curTABLES%NOTFOUND;

                IF TABNAME = "tblTest" THEN
                --Your Business Logic goes here......
                --SSQL := 'TRUNCATE TABLE ' || TABNAME;
      --EXECUTE IMMEDIATE SSQL;
      END IF;

      END LOOP;
EXCEPTION
      WHEN EXP THEN
             DBMS_OUTPUT.PUT_LINE('An unexpected error occurred');

END;
/
0
 
LVL 15

Expert Comment

by:andrewst
ID: 9919477
for i in 1..v_tab.count loop
 var1 := v_tab(i).abc_name;
end loop;
0
 
LVL 48

Expert Comment

by:schwertner
ID: 9919540
DECLARE


type ec is REF CURSOR RETURN abc%ROWTYPE;

h_rec abc%ROWTYPE;
par NUMBER := 1;

cr_cur ec;

begin

IF par = 1 THEN
     OPEN cr_cur FOR select * from abc minus select * from dfg;
ELSE
     OPEN cr_cur FOR ........;
END IF;

LOOP
    FETCH cr_cur INTO h_rec;
    EXIT WHEN cr_cur%NOTFOUND;
   



--h_rec.abc_name
--h_rec.abc
--h_rec.type

end loop;
end;

0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 9919587
I know you have several valid examples, but here's a very simple one for you, this will print out the values of all of your rows (you can change to do what you want, but this is a good quick example), just make sure that you have serveroutput set to on

DECLARE
BEGIN
      FOR row IN (SELECT * FROM abc MINUS SELECT * FROM dfg) LOOP
            DBMS_OUTPUT.put_line(row.abc_name || '; ' || row.abc || '; ' || row.type);
      END LOOP;
END;
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question