9 Replies Latest reply on Jun 19, 2012 11:49 AM by 943610

    calling a ref cursor from pro*c

    user629122
      I am getting problem when i am trying to comple this program in vax/vms system.
      It is a pro*c program.Can any one help.It is a reference cursor program.
      This perticular block will be called by pro*c program.


      pl/sql block;


      create or replace package emp_demo_pkg100 as
      type emp_cur_type is ref cursor return emp%rowtype;
      procedure open_cur(curs IN OUT emp_cur_type);
      end emp_demo_pkg100;
      /


      create or replace package body emp_demo_pkg100 as
      procedure open_cur(curs IN OUT emp_cur_type) is
      begin
      open curs for select * from emp order by empno asc;
      end;
      end emp_demo_pkg100;
      /




      And this is the pro*c program.


      #include <stdio.h>
      #include<stdlib.h>
      #include<string.h>

      EXEC SQL INCLUDE ORACA.H;
      EXEC ORACLE OPTION(ORACA=YES);
      EXEC SQL INCLUDE SQLCA.H;

      EXEC SQL BEGIN DECLARE SECTION;

      SQL_CURSOR emp_cursor;


      struct employee_information
      {
      int empno;
      varchar ename[100];
      int sal;
      int comm;
      int deptno;
      varchar doj[100];
      };

      EXEC SQL END DECLARE SECTION;
      void sql_error()

      {
      size_t clen, fc;
      char cbuf[128];

      clen = sizeof (cbuf);
      sqlgls((char *)cbuf, (size_t *)&clen, (size_t *)&fc);


      printf("Statement is--\n%s\n", cbuf);
      printf("Function code is %ld\n\n", fc);

      sqlglm((char *)cbuf, (size_t *) &clen, (size_t *) &clen);
      printf ("\n%.*s\n", clen, cbuf);

      EXEC SQL WHENEVER SQLERROR CONTINUE;
      EXEC SQL ROLLBACK WORK RELEASE;
      exit(0);
      }


      void main()
      {


      struct employee_information emp_info;
      EXEC SQL WHENEVER SQLERROR do sql_error();

      char *uid = "scott/tiger";
      EXEC SQL ALLOCATE :emp_cursor;
      /* EXEC SQL WHENEVER NOT FOUND DO break; */

      EXEC SQL CONNECT :uid;

      if(sqlca.sqlcode==0)
      {

      EXEC SQL CALL emp_demo_pkg100.open_cur(:emp_cursor) ;

      do
      {
      EXEC SQL FETCH :emp_cursor into :emp_info;

      if(sqlca.sqlcode==0)
      {
      emp_info.ename.arr[emp_info.ename.len]='\0';
      emp_info.doj.arr[emp_info.doj.len]='\0';
      printf("\n empno:: %d emp name:: %s emp sal::%d",emp_info.empno,emp_info.ename.arr,emp_info.sal);
      }
      }while(sqlca.sqlcode==0);



      }
      else
      {
      printf("\n Can not connect to oracle database");
      }

      /* EXEC SQL WHENEVER SQLERROR CONTINUE; */

      EXEC SQL CLOSE :emp_cursor;

      EXEC SQL COMMIT WORK RELEASE;
      exit(0);


      }


      The error which is comming


      Statement is--
      "
      Function code is 0


      ORA-01002: fetch out of sequence


      can any one please help.