Link to home
Create AccountLog in
Avatar of Billy Ma
Billy MaFlag for Hong Kong

asked on

C# ASP.NET Oracle Stored Procedure

1. How to create a stored procedure in Oracle?

2. Are there any Temporary Table in Oracle?

3. If I write SELECT COMPANY_ID, COMPANY_NAME FROM COMPANY in my stored procedure, is it possible to get the query result in C# ASP.NET?

Thank you!
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Billy Ma

ASKER

I use Olb in # ASP.NET
sorry, should be oledb
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I know how to connect to Oracle using OleDB.
However, how to get the value from the stored procedure?

My Stored Procedure called MyStoredProc
and it only have one SELECT statement in it which is
SELECT COMPANY_NAME FROM COMPANY;

I wanna know how to get the result of this SELECT statement if this SELECT statement is in a STORED PROCEDURE?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
My Procedure have NO parameter.

How can I change the following code to ODP.NET?

Is it possible one page us Ole and another page us ODP.NET?
string connectionString = "Provider=MSDAORA;Data Source=xe;User ID=test; Password=test";
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            string query = "SELECT C.COMPANY_ID, C.COMPANY_NAME, P.PROCESS_ID, P.PROCESS_NAME " +
                           "FROM COMPANY C JOIN PROCESS P " +
                           "ON C.COMPANY_ID = P.COMPANY_ID";

            OleDbCommand command = new OleDbCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            command.Connection = connection;

            OleDbDataReader reader = command.ExecuteReader();

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You know, what I wanna do it
Get everything from different tables (which may require a stored proc) into a temp table first.
and then I wanna get the result from the temp table then bind it into a grid view.

Database view won't help because I got some logic behind when gathering data from different tables.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks