Home » SQL & PL/SQL » SQL & PL/SQL » retrieving data from pl sql table
retrieving data from pl sql table [message #580167] Thu, 21 March 2013 01:46 Go to next message
gonuguntla
Messages: 9
Registered: March 2012
Junior Member
hello all,

i have reqirement, that is to retrieve the data from pl/sql table.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.



SQL> DECLARE
CURSOR all_emp IS
SELECT *
FROM emp;
TYPE emp_tbl_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tbl emp_tbl_type;
BEGIN
OPEN all_emp;
FETCH all_emp BULK COLLECT INTO emp_tbl;
CLOSE all_emp;
FOR myindex IN 1..emp_tbl.count LOOP
DBMS_OUTPUT.PUT_LINE (emp_tbl(myindex).ename);
END LOOP;
END;
/

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.


now i want receive the data from particular x record to y record

please help me
Re: retrieving data from pl sql table [message #580172 is a reply to message #580167] Thu, 21 March 2013 02:25 Go to previous messageGo to next message
sss111ind
Messages: 502
Registered: April 2012
Location: India
Senior Member

It is not clear .What is exactly your requirement.
Re: retrieving data from pl sql table [message #580173 is a reply to message #580167] Thu, 21 March 2013 02:27 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
then add WHERE condition in your cursor SELECT.

regards,
Delna
Re: retrieving data from pl sql table [message #580179 is a reply to message #580172] Thu, 21 March 2013 03:03 Go to previous messageGo to next message
gonuguntla
Messages: 9
Registered: March 2012
Junior Member
sss111ind wrote on Thu, 21 March 2013 12:25
It is not clear .What is exactly your requirement.


i want 3,4,5,6 and 7 records of primary key(binary_intiger)

is it clear now?
Re: retrieving data from pl sql table [message #580182 is a reply to message #580179] Thu, 21 March 2013 03:08 Go to previous messageGo to next message
Littlefoot
Messages: 19884
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
To me, it is not clear. Could you "draw" a desired output (using any text editor)?
Re: retrieving data from pl sql table [message #580185 is a reply to message #580182] Thu, 21 March 2013 03:27 Go to previous messageGo to next message
gonuguntla
Messages: 9
Registered: March 2012
Junior Member
SMITH --0
ALLEN --1
WARD ---2
JONES --3
MARTIN--4
BLAKE --5
CLARK --6
SCOTT --7
KING  --8
TURNER--9
ADAMS --10
JAMES --11
FORD  --12
MILLER--13
i want the below output

the numbers are the Binary integer values only for reference, in the output i want only the ename
JONES --3
MARTIN--4
BLAKE --5
CLARK --6
SCOTT --7


sorry my english skills are very poor
Re: retrieving data from pl sql table [message #580187 is a reply to message #580179] Thu, 21 March 2013 03:38 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
gonuguntla wrote on Thu, 21 March 2013 08:03
sss111ind wrote on Thu, 21 March 2013 12:25
It is not clear .What is exactly your requirement.


i want 3,4,5,6 and 7 records of primary key(binary_intiger)

is it clear now?


This:
FOR myindex IN 1..emp_tbl.count LOOP

becomes
FOR myindex IN 3..7 LOOP

Replace the numbers with variables/parameters if you want something generic.

Seems fairly obvious really.
Re: retrieving data from pl sql table [message #580188 is a reply to message #580185] Thu, 21 March 2013 03:39 Go to previous messageGo to next message
Littlefoot
Messages: 19884
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see; but, what makes these boundaries (3 .. 7)? If you want to enter them manually, then modify the FOR statement to
FOR myindex IN 3 .. 7
Re: retrieving data from pl sql table [message #580195 is a reply to message #580167] Thu, 21 March 2013 03:54 Go to previous message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please format your code.
If you don't how to do it, learn it using SQL Formatter

From your previous topic:

Michel Cadot wrote on Wed, 21 March 2012 18:19
And the solution was?

Regards
Michel


Waiting for your answer.

Regards
Michel
Previous Topic: Ref Cursors for passing parameters
Next Topic: Exception: ORA-01438: value larger than specified precision allowed for this column
Goto Forum:
  


Current Time: Thu Dec 18 10:34:47 CST 2014

Total time taken to generate the page: 0.09197 seconds