Re: Returning a REF cursor

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 2 Aug 2016 21:36:18 +0200
Message-ID: <57a0f63d$0$7096$426a74cc_at_news.free.fr>

<franmerar229_at_gmail.com> a écrit dans le message de news: bbbe29aa-a04a-4650-846f-2ce9aa05fa51_at_googlegroups.com...
| Something like this:
|
| CREATE OR REPLACE PACKAGE test_package AS
|
| TYPE ref_crs IS REF CURSOR;
|
| PROCEDURE test_procedure (
| p_score NUMBER,
| p_email VARCHAR2,
| p_address VARCHAR2
| p_data OUT REF_CRS);
|
| END test_package;
| /
|
| Package created.
|
| CREATE OR REPLACE PACKAGE BODY test_package AS
|
| PROCEDURE test_procedure (
| p_score NUMBER,
| p_email VARCHAR2,
| p_address VARCHAR2
| p_data OUT REF_CRS) AS
|
| BEGIN
| OPEN p_data FOR
| SELECT email FROM emails WHERE score = p_score;
| END test_procedure;
|
| END test_package;
| /
|
|
| Then to call it:
|
| var data refcursor;
|
| exec test_package.test_procedure (
| p_score => 5,
| p_data => :data);
|
| BEGIN test_package.test_procedure (; END;
| *
| ERROR at line 1:
| ORA-06550: line 1, column 36:
| PLS-00103: Encountered the symbol ";" when expecting one of the following:
| ( ) - + case mod new not null <an identifier>
|
|
| Any thoughts on this one?
|
| Thanks!!

EXEC is a one line SQL*Plus short cut for BEGIN/END. Either use BEGIN/END or - line continuation character.

SQL> var data refcursor;
SQL>
SQL> BEGIN

  2 test_package.test_procedure (
  3 p_score => 5,
  4 p_data => :data);
  5
  6 END;
  7 /

PL/SQL procedure successfully completed.

SQL> exec test_package.test_procedure ( - > p_score => 5, -
> p_data => :data);

PL/SQL procedure successfully completed.

Regards
Michel Received on Tue Aug 02 2016 - 21:36:18 CEST