Re: Returning a REF cursor

<franmerar229_at_gmail.com> a écrit dans le message de news: cb4349c6-2b7d-4b7c-b9d0-19b298b69d6a_at_googlegroups.com...
| Hi,
|
| Can anyone help me with my issue on getting this code to work?
|
| CREATE OR REPLACE PACKAGE test_package AS
|
| TYPE ref_crs IS REF CURSOR;
|
| PROCEDURE test_procedure (
| p_score NUMBER,
| p_data OUT REF_CRS);
|
| END test_package;
| /
|
| CREATE OR REPLACE PACKAGE BODY test_package AS
|
| PROCEDURE test_procedure (
| p_score NUMBER,
| p_data OUT REF_CRS) AS
|
| BEGIN
| OPEN p_data FOR
| SELECT email FROM customer WHERE score = p_score;
| END test_procedure;
|
| END test_package;
|
|
|
|
| Ok, that compiled fine.........now..........
|
|
| DECLARE
| v_data SYS_REFCURSOR;
|
| BEGIN
| test_package.test_procedure (
| p_score => 5,
| p_data => v_data);
| END;
| /
|
| ERROR at line 5:
| ORA-06550: line 5, column 3:
| PLS-00306: wrong number or types of arguments in call to
| 'TEST_PROCEDURE'
| ORA-06550: line 5, column 3:
| PL/SQL: Statement ignored
|
|
| Can anyone help please?
|
| Thank you.
|

SQL> CREATE OR REPLACE PACKAGE test_package AS   2
  3 TYPE ref_crs IS REF CURSOR;
  4
  5 PROCEDURE test_procedure (
  6 p_score NUMBER,
  7 p_data OUT REF_CRS);
  8
  9 END test_package;
 10 /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY test_package AS   2
  3 PROCEDURE test_procedure (
  4 p_score NUMBER,
  5 p_data OUT REF_CRS) AS
  6
  7 BEGIN
  8 OPEN p_data FOR
  9 SELECT email FROM customer WHERE score = p_score;  10 END test_procedure;
 11
 12 END test_package;
 13
 14 /

Warning: Package Body created with compilation errors.

SQL> create table customer (score number, email varchar2(100));

Table created.

SQL> alter package TEST_PACKAGE compile body;

Package body altered.

SQL> DECLARE
  2 v_data SYS_REFCURSOR;
  3
  4 BEGIN
  5 test_package.test_procedure (

  6      p_score => 5,
  7      p_data  => v_data);

PL/SQL procedure successfully completed.

SQL> _at_v

Oracle version: 11.2.0.4.0

[Quoted] No problem for me.

Regards
Michel Received on Sun Jul 31 2016 - 19:07:43 CEST