| Display ResultSet [message #212299] |
Thu, 04 January 2007 10:43  |
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
|
Member |
|
|
I have this test proc below running in SQL*plus . Maybe using
Array may do the trick.
SQL > set serveroutput on
declare
n int := 15;
begin
for i in 1..n loop
-- how can I get 3 rows at a time ?
-- for every 3 prompt to continue up to max = 15
dbms_output.put_line(i);
end loop;
end;
output:
SQL> /
1
2
3 -- I would like 1st 3 then pause i.e hit enter to continue...
4
5
6 -- next 3
7
8
9 -- next 3
10
11
12 -- last 3
13
14
15
|
|
|
|
|
|
|
|
|
|
| Re: Display ResultSet [message #212424 is a reply to message #212299] |
Fri, 05 January 2007 04:50  |
Giraffe
Messages: 10
Registered: January 2007
Location: Suffolk, UK
|
Junior Member |
|
|
Whilst you cannot get get user input into a PL/SQL function, you could do something similar by asking them to call a function multiple times, a bit like this:
CREATE OR REPLACE PACKAGE test
IS
PROCEDURE printnext;
END test;
/
CREATE OR REPLACE PACKAGE BODY test
IS
maxval NUMBER := 15;
startval NUMBER := 1;
currentval NUMBER := startval;
increment NUMBER := 3;
PROCEDURE printnext
IS
v_counter NUMBER := 0;
BEGIN
WHILE ( currentval <= maxval
AND v_counter < increment
)
LOOP
dbms_output.put_line ( currentval );
currentval := currentval + 1;
v_counter := v_counter + 1;
END LOOP;
END printnext;
END test;
This gave me:
SQL> @printnext
Package created
Package body created
SQL> exec test.printnext;
1
2
3
PL/SQL procedure successfully completed
SQL> exec test.printnext;
4
5
6
PL/SQL procedure successfully completed
SQL> exec test.printnext;
7
8
9
PL/SQL procedure successfully completed
SQL> exec test.printnext;
10
11
12
PL/SQL procedure successfully completed
SQL> exec test.printnext;
13
14
15
PL/SQL procedure successfully completed
SQL> exec test.printnext;
PL/SQL procedure successfully completed
The state is stored for the session within the package, so two different sessions will not affect each other. You'd want to write an initialisation procedure that would run the query and set up the package state, rather than have it in the package initialisation, so that you can run it more than once in a session.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
