Home » SQL & PL/SQL » SQL & PL/SQL » Display ResultSet
Display ResultSet [message #212299] Thu, 04 January 2007 10:43 Go to next message
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 #212305 is a reply to message #212299] Thu, 04 January 2007 11:09 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, PL/SQL can not offer such a functionality. You can not interact with it.

However, it is possible to "delay" it using the DBMS_LOCK.SLEEP procedure which would "simulate" user's intervention.
Re: Display ResultSet [message #212312 is a reply to message #212305] Thu, 04 January 2007 11:41 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
I know that now. But can it be done in SQL*Plus?
Re: Display ResultSet [message #212313 is a reply to message #212299] Thu, 04 January 2007 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>But can it be done in SQL*Plus?
NO
IMO, you are using the wrong tool for the job.
Re: Display ResultSet [message #212424 is a reply to message #212299] Fri, 05 January 2007 04:50 Go to previous messageGo to next message
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.
Re: Display ResultSet [message #212495 is a reply to message #212424] Fri, 05 January 2007 09:49 Go to previous message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Giraffe,

That is super! I can live w/ something like that.
I 'll let you know how it pans out.
Many thanks.
Previous Topic: keyboard input related
Next Topic: NOT EQUAL QUESTION
Goto Forum:
  


Current Time: Sat Dec 10 18:16:02 CST 2016

Total time taken to generate the page: 0.27003 seconds