Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Oracle Select query

Posted on 2013-02-06
Medium Priority
Last Modified: 2013-02-07

I was trying to get the records of select statement inside the procedure of Oracle (not sql server), the image is attached and then get the data in say Data Reader like

DataReader dr=ObjOracleHelper.ExecuteNonQuery("procedureName", oPara, CommandType.StoredProcedure);

but the procedure return error and don't allow me this. I don't want to use the query in the Data Access Layer.

The select statement return around 100 records.

Can anyone help!

Question by:Dinesh Kumar
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 42

Expert Comment

by:Meir Rivkin
ID: 38858935
Oracle does not support that, you got to use INTO or use a variable:

Author Comment

by:Dinesh Kumar
ID: 38858946
BUT I have to return 100 records having 10 columns from Oracle Proecedure. There must be some way out!

By using INTO variable I can not select/retrieve say 100 records from the table.
LVL 42

Accepted Solution

Meir Rivkin earned 1800 total points
ID: 38858975
you can declare new type which consists of the fields in your select statement.
for instance,
the following won't work as explained:
if(lastid != 0 then
select a,b,c,d from table
end if;

Open in new window

so what you wanna do is to declare new type:
  TYPE SomeNewType IS RECORD (
    tmp_a table.a%TYPE,
    tmp_b table.b%TYPE,
    tmp_c table.b%TYPE
  recNewType SomeNewType;

Open in new window

and use it in your if expression:
if(lastid != 0 then
SELECT a, b,c  INTO recNewType 
  FROM table
end if;

Open in new window

check here for example:
SELECT INTO Assigns Values to Record Variable
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

LVL 49

Expert Comment

ID: 38859095
must it be a stored procedure? why not use a table function?

but: perhaps I'm missing the point
LVL 42

Expert Comment

by:Meir Rivkin
ID: 38859114
i didn't say stored procedure, check the link I've posted and it explains how to achieve what u want.
LVL 42

Expert Comment

by:Meir Rivkin
ID: 38859120
if you post the whole sql in here (rather than a picture) i'll modify it to make it work for you.
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 38859216
You can declare a record and use an associative array but I would suggest you return a ref cursor.

There are a ton of examples out there if you look around:

Also, if you aren't using ODP.Net, I strongly encourage you to start.

Author Closing Comment

by:Dinesh Kumar
ID: 38863474

Author Comment

by:Dinesh Kumar
ID: 38863484
Hi slightwv,

Can you please help why on the following link,


any one is not posting any answer?

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question