SQL & PL/SQL

Hi all

Here is my test case

O:\OraHomeDS>sqlplus boch/boch@boch

SQL*Plus: Release 9.0.1.4.0 - Production on Jue Sep 7 11:32:25 2006

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Conectado a:
Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - Production

SQL> create table t (articuloid number, articulo varchar2(10));

Tabla creada.

SQL> insert into t values (1,'AA');

1 fila creada.

SQL> insert into t values (2,'BB');

1 fila creada.

SQL> select * from t;

ARTICULOID ARTICULO
---------- ----------
1 AA
2 BB

SQL> create or replace package mypkg is
2
3 TYPE t_record IS RECORD (articuloid t.articuloid%TYPE,
4 articulo t.articulo%TYPE);
5
6 TYPE t_ref_strong IS REF CURSOR RETURN t_record;
7
8
9 PROCEDURE query_st( prefstrong IN OUT t_ref_strong);
10
11 PROCEDURE query_wk( prefstrong IN OUT t_ref_strong);
12
13
14 end mypkg;
15 /

Paquete creado.

SQL> show errors
No hay errores.
SQL> create or replace package body mypkg is
2
3 PROCEDURE query_st( prefstrong IN OUT t_ref_strong) IS
4 BEGIN
5 OPEN prefstrong FOR SELECT articuloid,articulo FROM t
6 WHERE articulo LIKE 'B%';
7 END query_st;
8
9 PROCEDURE query_wk( prefstrong IN OUT t_ref_strong) IS
10 vquery VARCHAR2(2000) := 'SELECT articuloid,articulo FROM t
11 WHERE articulo LIKE ''B%''';
12 TYPE trc IS REF CURSOR;
13 vrefweak trc;
14 BEGIN
15 OPEN vrefweak FOR vquery;
16 prefstrong := vrefweak;
17 END query_wk;
18
19 END;
20 /

Cuerpo del paquete creado.

SQL> show errors
No hay errores.
SQL> set autoprint on
SQL> variable x refcursor
SQL> exec mypkg.query_st(:x);

Procedimiento PL/SQL terminado correctamente.


ARTICULOID ARTICULO
---------- ----------
2 BB

SQL> exec mypkg.query_wk(:x);
BEGIN mypkg.query_wk(:x); END;

*
ERROR en linea 1:
ORA-06504: PL/SQL: Los tipos de retorno de variables de juego de resultados o
la consulta no coinciden
ORA-06512: en "BOCH.MYPKG", linea 16
ORA-06512: en linea 1


SQL>

I can't see the problem when using the weak variable cursor to get the results and copyying to he strong variable cursor.

I need to do this way (weak cursor -> strong cursor) because I'm using in the real world dynamic query and I need to use weak cursor. On the other hand I'm going to return the cursor to Oracle Forms and only strong cursors are accepted by Forms.

Any help would be really apreciated.

Best regards

Cecilio.