CODEWITHSUNDEEP
.netoracle-databasestored-procedureswebmatrixasp.net-webpages
cpeele00
cpeele00

Reputation: 893

How do I call an Oracle stored procedure from WebMatrix?

I am trying to execute an oracle stored procedure using WebMatrix.Data from Microsoft's WebMatrix. I can get data just fine with normal select statements...but I can't for the life of me figure out how to execute an oracle proc. It's not executed like a T-SQL proc I know that. Please help!

I'm trying to get a single value back. I'm just trying to prove that I can access an oracle stored proc with webmatrix.data. It is a query so I'm not sure db.Execute would work since that is for non-queries.

here's the code:

 CREATE OR REPLACE PROCEDURE COUNT_JOB_HISTORY
 (
   reccount OUT NUMBER
 )
 IS
   BEGIN
     SELECT COUNT(*) INTO reccount
     FROM JOB_HISTORY;
   END COUNT_JOB_HISTORY;

Here's the .Net code

    public static dynamic GetJobHistoryCount()
    {
        var db = Database.OpenConnectionString(connectionString, providerName);

        var jobs = db.QueryValue("exec COUNT_JOB_HISTORY(:0)", "count");

        return jobs;
    }

It blows up as an ORA-00900 : invalid SQL statement

Thanks!

Upvotes: 0

Views: 761

Answers (1)

Yahia
Yahia

Reputation: 70379

you need to use db.Execute() (see http://msdn.microsoft.com/en-us/library/webmatrix.data.database.execute%28v=vs.99%29.aspx)...

Regardings the procedure itself and dealing with params/result it is hard to tell because you did not provide enough information like

What exactly does the procedure return ? one value ? one row ? a cursor ?

EDIT- after the OP showed the Stored Procedure:

Every Stored Procedure is a "Non-Query" - WebMatrix does not know anything about what happens inside the Stored Procedure... I never used WebMatrix... so this is only a guess:

int reccount = 0;
db.Execute ("COUNT_JOB_HISTORY", reccount);


or perhaps 

db.Execute ("COUNT_JOB_HISTORY", ref reccount);

EDIT 2 - another try:

db.Execute ("BEGIN COUNT_JOB_HISTORY(:0); END;", reccount);

Upvotes: 1

Related Questions