execute immediate query

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
lakshminairAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sys_ProgCommented:
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
lakshminairAuthor Commented:
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
Sys_ProgCommented:
It would return u the following error

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

Amit
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

lakshminairAuthor Commented:
yes thats right. it does happen so.
can you pls suggest me something else?
0
andrewstCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lakshminairAuthor Commented:
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
dasariCommented:
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
andrewstCommented:
for i in 1..v_tab.count loop
 var1 := v_tab(i).abc_name;
end loop;
0
schwertnerCommented:
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
grim_toasterCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.