• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

Oracle Select query

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
Dinesh Kumar
Asked:
Dinesh Kumar
2 Solutions
 
Meir RivkinFull stack Software EngineerCommented:
Oracle does not support that, you got to use INTO or use a variable:
oracle-select-statement-in-if-condition
0
 
Dinesh KumarAuthor Commented:
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
 
Meir RivkinFull stack Software EngineerCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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

but: perhaps I'm missing the point
0
 
Meir RivkinFull stack Software EngineerCommented:
i didn't say stored procedure, check the link I've posted and it explains how to achieve what u want.
0
 
Meir RivkinFull stack Software EngineerCommented:
if you post the whole sql in here (rather than a picture) i'll modify it to make it work for you.
0
 
slightwv (䄆 Netminder) Commented:
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
 
Dinesh KumarAuthor Commented:
Thanks
0
 
Dinesh KumarAuthor Commented:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now