Solved

execute immediate query

Posted on 2003-12-11
10
3,503 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 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

Technology Partners: 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!

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

749 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