select stored proc plsql

Question

I'm a bit confused by the stored procedure syntax in Oracle.

I started with a simple:

select * from test_table;

It works, then I put it in a proc:

CREATE OR REPLACE PROCEDURE example
IS
BEGIN
   select * from test_table;
END;

Doesn't work. Expected "INTO" is the error message I get. Now, I've seen syntax examples of SQL Server code that just shoves a select statement into a proc and it works instantly, but that doesn't seem to be the case here.

Solution 1

T-SQL and PL/SQL are completely different languages. In particular, for PL/SQL you have to select the result into some variable or cursor. Depending on what you plan to do with the record data - process in the procedure - or return to the caller, will drive what you have to do.

In your example, if you want to return the record set, you would do something like this:

CREATE OR REPLACE PROCEDURE example (
                      p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    select * from test_table;
END example ;

See this link for examples.

Solution 2

select * from test_table;

SQL and PL/SQL are nor the same. To execute a SQL in a procedure, the parser expects an INTO clause to store the value returned by the sql statement. In PL/SQL, there is a reason to execute a SQL statement. You want to use the result set later to process. Not just retrieve and do nothing.

Also, it is a bad idea to use select * in any production system. You don't want to dump all the columns data of the table on an application screen. There are many other reasons, however, not in the scope of this question.

You need to modify your SQL like following -

SELECT column_name INTO variable FROM table_name

There are several ways to fetch the data via SQL statement in PL/SQL. You need to elaborate your requirement and narrow down to specific steps here.

If you are learning about these concepts, I would recommend you to start reading the Oracle documentation first. Try and understand the concepts, and if you find any issues, then prepare a test case, explain your issue in words and then post a question. Too broad questions are difficult to answer, and are mostly considered out of scope.

Solution 3

Hello and welcome to SO.
I assume the full error you're seeing would be PLS-00428: an INTO clause is expected in this SELECT statement and it is correct, you must have an INTO statement in a stored procedure.
I recommend this link for syntax relating to the SELECT INTO statement.
For your code I recommend this (I've changed from your test_table example to dba_user):

CREATE OR REPLACE PROCEDURE example
IS
l_username VARCHAR(25);
BEGIN
   select username INTO l_username from dba_users where user_id=1;
END;
/

Note: The INTO clause works with 1 column from 1 row. You cannot select multiple records or columns into this. You would need to reference the BULK COLLECT feature to do that. For examples of that feel free to read here.

Comments

[user3503891]

So it would put the result into a record set, then how do you execute the proc so it displays the record set? I don't mind seeming really naive about Oracle, because I am, but SQL Server just seems to DO this as a given, not need a work around. It's just a bit confusing.

[OldProgrammer]

Depends what client language you are calling this from. You did not say. You will need to do some more research on your own at this point.

[user3503891]

I'm using PL/SQL and Oracle 11.