By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,083 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,083 IT Pros & Developers. It's quick & easy.

How to retrieve single column values from table

P: 37
Hi

I have a table
SQL> select * from nodes;

NODE_ID NODE_NAME
-------------------- ------------------------------
N1 Kothhapet
N2 Nagole
N3 Uppal
N4 NGRI
N5 Habsiguda
N6 IGSA Labs

I want to get all the data from the nodes table(i.e out put is cursor type)
For that i did like this

Expand|Select|Wrap|Line Numbers
  1. create or replace package nodespkg
  2. is
  3. TYPE nodes_type IS REF CURSOR RETURN nodes%ROWTYPE;
  4. end nodespkg;
/

Expand|Select|Wrap|Line Numbers
  1. create or replace procedure viewNodes(Nodes out nodespkg.nodes_type,
  2.       status out varchar2)
  3. is
  4. begin
  5. open Nodes for select * from nodes;
  6. status:='0';
  7. commit;
  8. exception
  9. when others then
  10. status:='7';
  11. end viewNodes;
/

It gives the output correctly.

Now i want to retreive only one column from the table.
For that i created a package like this

Expand|Select|Wrap|Line Numbers
  1. create or replace package nodeidpkg
  2. is
  3. TYPE nodeid_type IS REF CURSOR RETURN nodes.node_id%TYPE;
  4. end nodeidpkg;
/
but it gives the error
PLS-00362: invalid cursor return type; 'NODES.NODE_ID%TYPE' must
be a record type

Please tell me, How can i get one particular column values from table(nodes)?
(My ultimate aim is to get the single column values from table using procedure by declaring out parameter)

Thank You
sandeep
Oct 11 '07 #1
Share this Question
Share on Google+
1 Reply


amitpatel66
Expert 100+
P: 2,367
Sandeep,

In package, dont specify the RETURN TYPE for the REF CURSOR.

Then in the procedure, you can OPEN ref cursor as SELECT nodeid from table_name will work!!

Try and do post back!!
Oct 11 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.