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
Solved

execute immediate query

Posted on 2003-12-11
10
3,501 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

808 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