9 Replies Latest reply: Jun 19, 2012 6:49 AM by 943610 RSS

    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.
        • 1. Re: calling a ref cursor from pro*c
          prajithparan
          You should exit the following loop when there is no data

          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);
          • 2. Re: calling a ref cursor from pro*c
            user629122
            hi prajit

            will you say it more clearly.It is not very clear to me because

            when it is getting data then only it is ntering into the if statement

            if(sqlca.sqlcode==0)

            the do while loop is there because when it will get not get any data then it will exit from the loop.

            do
            {
            ...
            ...
            }while(sqlca.sqlcode==0);

            please be a bit clear in it.
            • 3. Re: calling a ref cursor from pro*c
              prajithparan
              basic difference between a

              Do ............... while() Loop

              and

              For (,,,) Loop

              Should be clear to you to get the answer

              While loop iterates once again until it reaches the while condition.

              use For loop and see the difference
              • 4. Re: calling a ref cursor from pro*c
                user629122
                do not give such stupid answer if you do not have clear idea that what you are saying,it will be batter if you remain silent if you do not know the answer.
                • 5. Re: calling a ref cursor from pro*c
                  prajithparan
                  Thanks for the compliment
                  AND

                  READ THE BELOW ORACLE ERROR: BEFORE COMMENTING on people.

                  ORA-01002: fetch out of sequence

                  Cause: This error means that a fetch has been attempted from a cursor which is no longer valid. Note that a PL/SQL cursor loop implicitly does fetches, and thus may also cause this error. There are a number of possible causes for this error, including: 1) Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned. 2) If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error. 3) Rebinding any placeholders in the SQL statement, then issuing a fetch before reexecuting the statement.

                  Action: 1) Do not issue a fetch statement after the last row has been retrieved - there are no more rows to fetch. 2) Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE. 3) Reexecute the statement after rebinding, then attempt to fetch again.
                  • 6. Re: calling a ref cursor from pro*c
                    581299
                    It's Better U should be Answer Clearly.....Prajithparan
                    • 7. Re: calling a ref cursor from pro*c
                      David Spencer Rac Team-Oracle
                      The cursor is not valid, because you haven't opened it with EXEC SQL OPEN.
                      To fetch from a ref cursor, you'll need to pass :emp_cursor and :emp_info into another procedure so that PL/SQL can fetch the data for you.
                      • 8. Re: calling a ref cursor from pro*c
                        937267
                        the solution:
                        increment the counter before fetch cursor
                        when you call using the cursor index is based on starting or zero.
                        However when you use exec / execute cursor index starts or is based on 1.
                        Then in the cycle of your program do.

                        .
                        .
                        do
                        {
                        *emp_cursor.curocn + +*
                        EXEC SQL FETCH: emp_cursor into: emp_info;
                        .
                        .
                        }
                        • 9. Re: calling a ref cursor from pro*c
                          943610
                          Thanks 934264 .