Oracle vs. Microsoft SQL Stored Procedures
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.
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
q.section_id, q.question_id, q.question_label, q.display_order, r.report_id,
when '0' then 'No'
when '1' then 'Yes'
when '2' then 'NA'
END AS question_value
FROM table1 q, table2 r
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;
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…