Home > .net 2.0, Oracle, SQL > Oracle vs. Microsoft SQL Stored Procedures

Oracle vs. Microsoft SQL Stored Procedures

July 28, 2006

For the past many years, I’ve worked with Microsoft SQL Server… stored procedures, functions, everything is beautiful. As a .NET developer, SqlConnection into DataSets == happiness.

We recently moved one of my databases over to Oracle 10g, meh, okay, so my SqlConnections become OleDbConnections. Big deal.

WRONG.

Tell me, someone, why I cannot implicitly create a SQL select in Oracle and output a resultset?

PROCEDURE myStoredProcedure (

  report_id IN NUMBER, language_id IN NUMBER, section_id IN NUMBER) IS
BEGIN
  SELECT
    q.section_id, q.question_id, q.question_label, q.display_order, r.report_id,
    CASE r.question_value
        when '0' then 'No'
        when '1' then 'Yes'
        when '2' then 'NA'
    END AS question_value
FROM table1 q, table2 r
WHERE
    report_id = report_id
    and q.question_id = r.question_id
    and q.language_id = language_id
    and q.section_id = section_id
    and q.valid = 1
ORDER BY display_order ASC;
 
END;


That looks good… but, Oracle says I need an INTO statement… because Procedures figure something, not output something. According to the sources around here, you cannot output resultsets using stored procedures in Oracle–you create a view, read the view, delete the view. Bullshit. I’ll just create a class in C# to handle this…

*grr*

Categories: .net 2.0, Oracle, SQL
  1. scott
    August 3, 2006 at 12:19 pm

    David, I’m running into the same situation. Have you figured out the problem?

  2. August 3, 2006 at 2:03 pm

    Scott-

    I’ve found a few various resources (http://support.microsoft.com/default.aspx?scid=kb;en-us;310101 is probalby the most explicit, though for C# 2003, not 2005’s grids), but I ended up just creating the code functions within the application and leaving none of the logic within the database (aka: SQL statement generation “functions” that we used to do ages ago before ADO was real keen at pulling back resultsets.) It seems like a huge step backwards, but I’ve yet to find a good way to do it.

    A coworker explained a long, complex way using counters to reiterate results into a temporary table, then query that temporary table, but… ack, even more calls to the database.😦

    I’m still open for any suggestions on this one!🙂 I never thought I’d appreciate the ‘freebies’ that MSSQL provides so much!

  3. Jaspaul Singh
    January 17, 2007 at 2:32 am

    use this code

    CREATE OR REPLACE PACKAGE sample AS

    TYPE REF_CURSOR IS REF CURSOR;
    PROCEDURE myStoredProcedure (
    report_id IN NUMBER, language_id IN NUMBER, section_id IN NUMBER, p_RecordSet out REF_CURSOR);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY sample AS

    PROCEDURE myStoredProcedure (
    report_id IN NUMBER, language_id IN NUMBER, section_id IN NUMBER, p_RecordSet out REF_CURSOR)

    AS

    BEGIN
    OPEN p_RecordSet FOR
    SELECT
    q.section_id, q.question_id, q.question_label, q.display_order, r.report_id,
    CASE r.question_value
    when ‘0′ then ‘No’
    when ‘1′ then ‘Yes’
    when ‘2′ then ‘NA’
    END AS question_value
    FROM table1 q, table2 r
    WHERE
    report_id = report_id
    and q.question_id = r.question_id
    and q.language_id = language_id
    and q.section_id = section_id
    and q.valid = 1
    ORDER BY display_order ASC;

    END;

  1. No trackbacks yet.
Comments are closed.
%d bloggers like this: