1

I'm trying to create a PL SQL that returns a table but it doesn't work

CREATE PROCEDURE MyProcedure 
AS
BEGIN
SELECT * FROM MyTable
END

What is wrong here? Thanks!

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
jnoguerm
  • 147
  • 1
  • 1
  • 15

1 Answers1

0

The SELECT statement END procedure are need to end with ;.

CREATE OR REPLACE PROCEDURE MyProcedure 
(
   P_Column1 OUT INTEGER,
   P_Column2 OUT VARCHAR2,
   P_COlumn3 OUT FLOAT
)
AS
BEGIN
   SELECT Column1, Column2, Column3 
   INTO P_Column1, P_Column2, P_Column3 
   FROM MyTable
   WHERE <Condition>;
END;
/

with usage of CURSOR:

CREATE PROCEDURE GET_ONESET_CUSTOMERINFO
(
   CURSOR_ IN OUT TYPES.REF_CURSOR
)
AS
BEGIN
   OPEN CURSOR_ FOR
      SELECT * FROM MyTable
      WHERE <condition>;
END;

Please find some more examples in this page

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
  • LINE/COL ERROR --------- ------------------------------------------------------------- 6/4 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; Errors: check compiler log – jnoguerm May 16 '19 at 13:38
  • 5/5 PLS-00428: an INTO clause is expected in this SELECT statement Errors: check compiler log – jnoguerm May 16 '19 at 13:49
  • @jnoguerm I guess columns of select statements must be assigned to variables. Please refer [the answer](https://stackoverflow.com/a/25486681/2451726) – Arulkumar May 16 '19 at 13:55
  • Could you put a simple example please? I can't make it work. – jnoguerm May 16 '19 at 14:12
  • @jnoguerm I have updated the asnwer with `OUT` param of different data types and `SELECT ... INTO`. – Arulkumar May 16 '19 at 14:24
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193474/discussion-between-arulkumar-and-jnoguerm). – Arulkumar May 16 '19 at 14:29