stored procedures out parameter

[Maikit]

Hi, i need to return the result of the query from this procedure, where can i store this data

create or replace PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT ?????????????
) AS
 
  nom_atr_tbl RELACIONES.NOMBRE%TYPE;
  NOM CAT_ATRIBUTO_TBL.NOMBRE_ATRIBUTO%TYPE;
 
 
BEGIN
  select r.NOMBRE,
       (select NOMBRE_ATRIBUTO
        FROM CAT_ATRIBUTO_TBL  CATTR
        WHERE CATTR.CVE_ATRIBUTO = r.CVE_ATR_IND_REC) NOMBREBIO
  INTO nom_atr_tbl, NOM ????????????????????????????????
  FROM  CAT_ATRIBUTO_TBL c, RELACIONES r 
  WHERE CVE_TABLA = p_CVE_TABLA
  AND CVE_ATRIBUTO IN (SELECT CVE_ATRIBUTO
                     FROM DATOENT_ATRIBUTO 
                     WHERE CVE_DATOENT IN (SELECT CVE_DATOENT 
                                           FROM DATO_ENTRADA
                                           WHERE CVE_TIPO_DATOENT = p_CVE_TIPO_DATOENT)
                     ) 
AND c.CVE_ATRIBUTO = r.CVE_ATRIBUTO_TABLA_REQUERIDA;
END RELACIONA_DATOS;

what i get from this query is

NOMBRE                  | NOMBREBIO
 
tipoDocProbClave     CVE_DOCTO_PROB
curpDocumProbNum    | AN_NUM_ACTA
tipoDocProbClave    | AN_FECHA_REG
curpDocumProbNum    | AN_TOMO
tipoDocProbClave    | AN_LIBRO
curpDocumProbNum    | AN_FOJA
tipoDocProbClave    | AN_ENTIDAD_REG
curpDocumProbNum    | AN_ID_MUNICIPIO
curpDocumProbNum    | AN_CRIP

[Dave44]

you have to decide what you are doing with the data. You can insert the rows from the query into a table or you can use a ref cursor etc ... there are many things you can do to get at the data.

[Maikit]

you have to decide what you are doing with the data. You can insert the rows from the query into a table or you can use a ref cursor etc ... there are many things you can do to get at the data.

How can i use a ref cursor, im new to this and im not sure how to use it, ive tried several things:

create or replace PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT REL_EST%ROWTYPE
) AS

REL_EST is a table with the structure i want

I also tried this

PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT cursor_type
);

cursor type is a ref cursor declared in apackage but i cant get it to work...

any ideas?

[Dave44]

what will be receiving the out parameter of type ref cursor? is it another pl/sql package/procedure or is it another language which can automatically determine the columns involved with the ref cursor (such as vb.net)? I ask this because if you use weakly typed ref cursors then you need to make sure that the calling procedure with the IN parameter of sys_refcursor has all the correct columns accounted for in the fetch.

an example of a out ref cursor can be found here:
http://www.psoug.org/reference/ref_cursors.html

[amitpatel66]

How can i use a ref cursor, im new to this and im not sure how to use it, ive tried several things:

create or replace PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT REL_EST%ROWTYPE
) AS

REL_EST is a table with the structure i want

I also tried this

PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT cursor_type
);

cursor type is a ref cursor declared in apackage but i cant get it to work...

any ideas?

Can you POST your PACKAGE code for reference. Why is your cur_type not working:

check the below code for reference:

 
SQL> create or replace package tttt AS
  2  TYPE cur IS REF CURSOR;
  3  PROCEDURE tttt1(a NUMBER, b OUT cur);
  4  END tttt;
  5  /
 
Package created.
 
SQL> create or replace package body tttt AS
  2  PROCEDURE tttt1(a NUMBER, b OUT cur) IS
  3  BEGIN
  4  OPEN b FOR SELECT * FROM EMP where empno = a;
  5  END tttt1;
  6  end tttt;
  7  /
 
Package body created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  declare
  2  a SYS_REFCURSOR;
  3  emp_rec EMP%ROWTYPE;
  4  begin
  5  tttt.tttt1(1,a);
  6  LOOP
  7  FETCH a INTO emp_rec;
  8  EXIT WHEN a%NOTFOUND;
  9  dbms_output.put_line(emp_Rec.ename||','||emp_rec.empno);
 10  END LOOP;
 11* end;
SQL> /
BOND,1
 
PL/SQL procedure successfully completed.