Hi, I was wondering if it is posible to create a datasource wich sql query contains a function that returns a cursor variable. For Example select my_function(:var1, :var2) as cursor_result from dual. If it is possible, can you please give us a litlle demo. Thanks in advance.

Claudio Arriagada.

asked 06 Nov '12, 10:46

carriagada's gravatar image

carriagada
7235
accept rate: 0%

Hi Claudio,

What data type does my_function return? Do you mean ref_cursor?

(06 Nov '12, 10:52) Ugur Kocak ♦

exactly....

(06 Nov '12, 15:07) carriagada

It is possible to create a Datasource Definition from a ref cursor in a slightly different way than in Oracle Forms.

You can do it with Oracle object/collection types. Define a function returning collection type. In the function populate the collection from a query, ref cursor or whatever. Then, use this function in the query of your Datasource Definition.

PS. With version 1.2, it will also be possible to set the query of the Datasource Definition in runtime.


Here is a sample of creating Datasource Definition from object types (using ref cursor):

1-First create neccessary object types in your schema:

CREATE OR REPLACE 
TYPE employee_ot
AS OBJECT(
  employee_id number,
  first_name varchar2(200),
  last_name varchar2(200)
)
/

CREATE OR REPLACE 
TYPE employee_ct IS TABLE OF employee_ot
/

2-Create the function in your schema returning the rowset object:

CREATE OR REPLACE 
PACKAGE objecttype_pkg IS

function getEmployees return employee_ct;

END;
/

CREATE OR REPLACE 
PACKAGE BODY objecttype_pkg IS

function getEmployees return employee_ct is
  v_employee_ct employee_ct := employee_ct();
  v_ref_cur sys_refcursor;
begin

  --You can populate collection one by one
  /*v_employee_ct.extend;
  v_employee_ct(v_employee_ct.count) := employee_ot(1,'Steven','King');

  v_employee_ct.extend;
  v_employee_ct(v_employee_ct.count) := employee_ot(2,'Neena','Kochhar');*/

  --Or you can populate it with a query
  /*select employee_ot(employee_id, first_name, last_name)
  bulk collect into v_employee_ct
  from (select 1 employee_id, 'Steven' first_name, 'King' last_name
        from dual
        union all
        select 2, 'Neena', 'Kochhar'
        from dual);*/

  --You can also use a ref cursor here
  open v_ref_cur for 
    'select employee_ot(employee_id, first_name, last_name)
     from (select 1 employee_id, ''Steven'' first_name, ''King'' last_name
           from dual
           union all
           select 2, ''Neena'', ''Kochhar''
           from dual)';

  fetch v_ref_cur bulk collect into v_employee_ct;
  close v_ref_cur;

  return v_employee_ct;
end;

END;
/

3-Then, create your Datasource Definition with the following query:

select * 
from table(objectType_pkg.getemployees)
link

answered 07 Nov '12, 01:03

Ugur%20Kocak's gravatar image

Ugur Kocak ♦
5616
accept rate: 23%

edited 07 Nov '12, 01:54

Hi Ugur. I' ll give you example of the function that i'm trying to query:

t_cursor is defined in the package specification in the following way:

type t_cursor is ref cursor;

In the body the that i need to query is:

function contribuyente_por_denominacion(p_d_denominacion in contribuyentes.d_denominacion%type) 
                                        return t_cursor is
/* 
   Permite recuperar los datos de un contribuyente en base a una  
   denominación o razón social. 
   Parametros:
   Entrada:  
      p_d_denominacion: Parte inicial de la denominación del contribuyente a recuperar.
   Salida(Cursor): 
      id_contribuyente --> Identificador de Contribuyente (PK)   
      n_cuit --> CUIT del contribuyente.
      d_tipo_documento --> Tipo de Documento del contribuyente 
      n_documento --> Número de documento del contribuyente 
      d_denominacion --> Denominación o Razón Social del contribuyente.
      f_nacimiento --> Fecha de nacimiento del contribuyente en caso de ser Persona Física.   
*/                                        
   --
   l_patron_busqueda varchar2(500);
   l_retorno t_cursor;        
   l_error_num number;      
begin
   --
   l_patron_busqueda := pkg_contribuyentes.devuelve_patron_busqueda(p_d_denominacion);

   --
   begin   
      open l_retorno for    
         select c.id_contribuyente, 
                c.n_cuit,
                c.c_tipo_documento,                 
                pkg_utiles.dato_tabla_general(c.c_tipo_documento, c.n_tabla_tipo_doc) d_tipo_documento,
                c.n_documento,
                c.d_denominacion,
                case 
                   when c.m_persona = 'F' 
                   then(select pf.f_nacimiento 
                        from   personas_fisicas pf 
                        where  pf.id_contribuyente = c.id_contribuyente)
                   else null     
                end f_nacimiento                        
         from   contribuyentes c
         where  contains(d_denominacion, l_patron_busqueda,1)>0
         order by c.d_denominacion asc;
   exception
      when others then
          l_error_num := sqlcode;
          if l_error_num = -29902 then
             raise_application_error(-20000, 'Por Favor, sea mas específico al ingresar los términos de búsqueda.');
          else   
             raise;
          end if;   
   end;

   --
   return l_retorno;
end;

As i undesrtand from your answer it is not possible to use this kind of function directly form Formspider, at least not yet. I need to modify it or create a wrapper function that returns a collection type. Thanks very much and best regards.

Claudio Arriagada.

link

answered 07 Nov '12, 04:59

carriagada's gravatar image

carriagada
7235
accept rate: 0%

Hi Claudio, Yes that's correct. You'll need to code a wrapper around your function.

(07 Nov '12, 14:17) Yalim K. Gerger ♦♦
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×63
×6
×3

Asked: 06 Nov '12, 10:46

Seen: 25,721 times

Last updated: 07 Nov '12, 14:17


© Copyright Gerger 2017