Devart Forums

Posted: **Thu 22 May 2008 12:31**

The below script is executed as a whole instead of 3 statements:

create or replace procedure cursor_test1( p_deptno IN number
                                , p_cursor OUT 
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
as
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end cursor_test1;



/** From Oracle 9 */
create or replace procedure cursor_test2( p_deptno IN number
                                , p_cursor OUT SYS_REFCURSOR)
as
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end cursor_test2;


/* Strong type */

create or replace procedure cursor_test3( p_deptno IN number
                                , p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR)
as
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end cursor_test3;

I started out with a CREATE PACKAGE on top of the script as well, and this also failed. Previous code:

Code: Select all

create or replace package REFCURSOR_PKG as
  TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
  TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;

/** until Oracle 9 */
create or replace procedure test( p_deptno IN number
                                , p_cursor OUT 
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end test;

/** From Oracle 9 */
create or replace procedure test( p_deptno IN number
                                , p_cursor OUT SYS_REFCURSOR)
is
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end test;


/* Strong type */

create or replace procedure test( p_deptno IN number
                                , p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR)
is
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end test;

Got a clue why this fails?

--
Martijn Tonies
Upscene Productions

Posted: **Fri 23 May 2008 06:55**

You should separate your statements by the line with '/'. For example:

Code: Select all

CREATE OR REPLACE PROCEDURE P1
AS
BEGIN
  NULL;
END;
/

CREATE OR REPLACE PROCEDURE P2
AS
BEGIN
  NULL;
END;
/

Posted: **Fri 23 May 2008 07:25**

Oh right, silly me

I copied this example from the internet, forgot to insert the / characters...

Sorry for this.

--
Martijn Tonies
Upscene Productions

Devart Forums

Script fails to be parsed properly

Script fails to be parsed properly