Get data from stored procedure in oracle

Question

I have this table

CREATE TABLE "TB_JOB_LOG"
  (
     "CODIGO"      NUMBER,
     "DESCRIPCION" VARCHAR2(200 BYTE),
     "FECHA"       DATE
  )

I have this stored procedure

CREATE OR replace PROCEDURE Dummy_test(
    v_mes  IN NUMBER DEFAULT NULL,
    v_anho IN NUMBER DEFAULT NULL,
    cv_1   IN OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN cv_1 FOR
      SELECT codigo,
             fecha
      FROM   tb_job_log
      WHERE  codigo > 5
             AND codigo < 10;
END dummy_test;

I want to get the results from dummy_test with this other stored procedure

CREATE OR replace PROCEDURE Job_fill_ce
IS
  cc SYS_REFCURSOR;
  l  tb_job_log%ROWTYPE;
BEGIN
    Dummy_test(NULL, NULL, cc);

    LOOP
        FETCH cc INTO l;

        exit WHEN cc%NOTFOUND;

        dbms_output.Put_line(l.codigo);
    END LOOP;

    CLOSE cc;
END job_fill_ce;

But I get this error when I execute job_fill_ce

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: en "JOB_FILL_CE", line 7
ORA-06512: en line 2

Am I missing something?

Check this [SO Solution](http://stackoverflow.com/a/5822771/125551) for similar question, your are returning only 2 columns from SP and you are setting your `l` variable to `table%rowtype` which can have more than 2 columns — rs., Jul 31 '13 at 20:07

score 1 · Accepted Answer · answered Jul 31 '13 at 20:15

1

  SELECT codigo,
         fecha
  FROM   tb_job_log

In this cursor you are selecting two columns

and later declaring l of rowtype of tb_job_log which will contain three columns

   l  tb_job_log%ROWTYPE;

Probably because of this you are getting this error

Please create a record type of type codigo, fecha and check

answered Jul 31 '13 at 20:15

Harshit

560
1
5
15

you're right, I created two types for codigo and fecha and worked fine. Thank you – Naty Bizz Jul 31 '13 at 20:31

Get data from stored procedure in oracle

1 Answers1