SELECT procedure requires INSERT INTO?

Question

I have this simple SELECT procedure:

create or replace PROCEDURE CST_FEEDBACK
(
  LineID IN Integer
) AS
BEGIN

SELECT 
    T1.*
--  ,   Mgr.UserName as UserName
FROM F_Feedback T1
--LEFT JOIN tblUsers Mgr
--ON T1.EmpID = Mgr.EmpID

WHERE
    T1.Line_ID = LineID

ORDER BY T1.Comments_TS DESC;

END CST_FEEDBACK;

I am an Oracle n00b, we were using SQL Server in our shop but were suddenly forced to switch to Oracle. I'm getting this message when I try to compile it:

Error(7,1): PLS-00428: an INTO clause is expected in this SELECT statement

Can anyone tell me why this is happening, and if there's some way to get around it?

Because that is the syntax for PL/SQL. What do you plan to do with the selected data? And which version of Oracle are you using (the database, not the SQL Developer client)? — Alex Poole, Nov 15 '16 at 17:20
I just want to bring back the data and then populate a repeater control in an ASP page (using C#). I guess I could just run the code in C#, but I was trying to replicate exactly what I did when we used SQL Server. We're using Oracle 12c. — Johnny Bones, Nov 15 '16 at 18:16
I have no idea how you'd call a procedure from there; quite likely you want a ref cursor out variable, or a function that returns a ref cursor, if your SQL Server code was just seeing the result of the query. [There's a 12c-specific method here](http://stackoverflow.com/a/40360471/266304). I think, from comments I've seen, that is supposed to be close to how it works in SQL Server. — Alex Poole, Nov 15 '16 at 18:20

score 0 · Answer 1 · edited May 23 '17 at 12:30

if you call a select inside the PL/SQL you normally do this to calculate with the results inside the procedure. You have two ways:

Select ... into ... from ...

if you want to select one row.

or you declare a cursor as select and loop through it.

But I think, you want a stored procedure, that calculates a result as table.

It seems you want to do something like

SELECT * FROM MyProcedure(12345);

So you probably should look here oracle procedure returning table of custom data type record

SELECT procedure requires INSERT INTO?

1 Answers1