Solved

Oracle Select query

Posted on 2013-02-06
10
466 Views
Last Modified: 2013-02-07
Hi,

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!

Thanks
meetDinesh
seems-to-be-oracle-limitation.png
0
Comment
Question by:Dinesh Kumar
10 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 38858935
Oracle does not support that, you got to use INTO or use a variable:
oracle-select-statement-in-if-condition
0
 

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.
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 450 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:
DECLARE
  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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

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

but: perhaps I'm missing the point
0
 
LVL 42

Expert Comment

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

Expert Comment

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 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:
http://www.oracle.com/technetwork/articles/dotnet/williams-refcursors-092375.html

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

Author Closing Comment

by:Dinesh Kumar
ID: 38863474
Thanks
0
 

Author Comment

by:Dinesh Kumar
ID: 38863484
Hi slightwv,

Can you please help why on the following link,

http://www.experts-exchange.com/Software/Server_Software/Web_Servers/Microsoft_IIS/Q_28022154.html


any one is not posting any answer?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

839 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