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

I have a table
SQL> select * from nodes;

-------------------- ------------------------------
N1 Kothhapet
N2 Nagole
N3 Uppal
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
Oct 11 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 2,367

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.