Solved

execute immediate query

Posted on 2003-12-11
10
3,509 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
Independent Software Vendors: 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 100 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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…
Via a live example, show how to take different types of Oracle backups using RMAN.

691 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