| Result not showing after execution of this block [message #20933] |
Tue, 02 July 2002 05:35  |
pk
Messages: 12
Registered: September 2000
|
Junior Member |
|
|
1 declare
2 str varchar2(100);
3 begin
4 str:='select * from emp';
5 execute immediate str;
6* end;
SQL> /
PL/SQL procedure successfully completed.
Why the result of this query is not shown ?? Can anyone help me..
pk
|
|
|
|
| Re: Result not showing after execution of this block [message #20941 is a reply to message #20933] |
Tue, 02 July 2002 09:02  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
In the context of PL/SQL, a select statement must return data INTO something (variable, PL/SQL table). There is no auto-display of the data from the result set. One option would be to loop through a cursor and display the results using dbms_output:
First, in SQL*Plus:
and then:
begin
for r in (select * from emp) loop
dbms_output.put_line('Employee #: ' || r.emp_no);
end loop;
end;
/ |
|
|
|
| Re: Result not showing after execution of this block [message #20945 is a reply to message #20933] |
Tue, 02 July 2002 09:29 |
inmembr
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
If your interest was to form a native dynamic sql statement, then you should probably investigate cursor variables. As mentioned in an earlier reply, all select statements must select into something. Cursors, on the other hand, can allow you access a number of rows through a single query. Try something like this:
DECLARE
v_record emp%rowtype;
TYPE t_EMPREC is REF CURSOR;
c_cursor t_EMPREC;
BEGIN
IF #1 THEN --this is a condition to choose a cursor
OPEN c_cursor FOR
SELECT * FROM EMP;
IF 2# THEN --this is a condition to choose a cursor
.....
LOOP
IF #1 THEN
FETCH c_cursor into v_record;
EXIT WHEN c_cursor%NOTFOUND;
(Do what you want with the records here)
END IF;
IF #2 THEN
.....
END IF;
END LOOP;
close c_Cursor;
END;
You may need to initally declare a number of datatypes to account for all of the possibilities (Not just for #1 like in the above)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
