DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Fetch out of sequence problem - ORA 8

  1. #1
    Join Date
    Jul 2000
    Posts
    3
    ANY SOLUTIONS WELCOME!!!!
    /*
    This 'C' program demonstrates a difference in behaviour between ORACLE 7
    and ORACLE 8 when turning on autocommit and fetching from a cursor that
    is doing a select for update.

    This behaviour was demonstrated on Windows 2000 running ORACLE
    version 8.1.6.0.0 and shown to not be present on Windows NT4.0 running
    ORACLE 7.3.2.2.1 The problem also exists on various UNIX platforms.

    The example:
    connects to a database
    turns on autocommit
    creates a cursor
    parses a statement that does a 'select for update'
    binds a variable
    executes the statement
    attempts to fetch on the cursor

    The end result on ORACLE 8 is that an ORA-01002 error is produced on
    the fetch.
    The output is:
    Connecting to ORACLE as scott/tiger ...OK
    Turning on autocommit... OK
    Creating cursor... OK
    Parsing statement ...OK
    Defining output variable...OK
    Executing query...OK
    Fetching result...

    ORA-01002: fetch out of sequence
    Processing OCI function OFETCH, OFEN

    When run on ORACLE 7 the fetch succeeds

    To run this example you will need to:
    1. make the binary
    On ORACLE 8 issue the following -
    cl -I%ORACLE_HOME%\oci\include -I. -D_DLL -D_MT eg.c /link /LIBPATH:%ORACLE_HOME%\oci\lib\msvc oci.lib kernel32.lib msvcrt.lib /nod:libc

    On ORACLE 7 issue the following -
    cl /c -I%ORACLE_HOME%/oci73/include /I. eg.c
    link eg.obj libc.lib kernel32.lib %ORACLE_HOME%\oci73\lib\msvc\ociw32.lib /out:eg.exe
    2. create the ORACLE table and populate it with one row
    SQLPLUS scott/tiger
    CREATE TABLE TEST (FIELD1 CHAR(10));
    INSERT INTO TEST VALUES ('ABCDEFGHIJ');
    COMMIT;
    3. run the program
    .\eg.exe

    */

    #include <stdio.h>
    #include <oratypes.h>
    #include <ocidfn.h>
    #include <ocidem.h>

    Cda_Def cda;
    Lda_Def lda;
    char orc_result[100];

    char logon_user_password[] = "scott/tiger";
    char select_statement[] = "select * from test for update";


    dvoid oci_error(void);

    main()
    {
    printf("Connecting to ORACLE as %s ...", logon_user_password);
    if (olon(&lda, logon_user_password, -1, NULL, -1, -1)) {
    printf("Cannot logon as scott/tiger. Exiting...\n");
    return(-1);
    }
    printf("OK\n");

    printf("Turning on autocommit...");
    if (ocon(&lda))
    {
    printf("Failed to turn on autocommit\n");
    return(-1);
    }
    printf("OK\n");

    printf("Creating cursor...");
    if (oopen(&cda, &lda, NULL, -1, -1, NULL, -1)) {
    printf("Cannot open cursor, exiting...\n");
    return(-1);
    }
    printf("OK\n");

    printf("Parsing statement...");
    if (oparse(&cda, select_statement, -1, 0, 2))
    {
    oci_error();
    }
    printf("OK\n");

    printf("Defining output variable...");
    (void)memset(orc_result, 0, sizeof(orc_result));
    if (odefin(&cda, 1, (ub1 *) orc_result, sizeof(orc_result) - 1,
    (sword) STRING_TYPE,
    (sword) -1, (sb2 *) 0, (text *) 0, -1, -1,
    (ub2 *) 0, (ub2 *) 0))
    {
    oci_error();
    }
    printf("OK\n");


    printf("Executing query...");
    if (oexec(&cda))
    {
    oci_error();
    }
    printf("OK\n");

    printf("Fetching result...");
    if (ofetch(&cda))
    {
    oci_error();
    }
    printf("OK\n");

    printf("Result is: %s\n", orc_result);

    /*
    ** Clean up
    */
    if (oclose(&cda))
    {
    printf("Error closing cursor!\n");
    return -1;
    }

    if (ologof(&lda))
    {
    printf("Error logging off!\n");
    return -1;
    }

    return(0);
    }


    dvoid
    oci_error(void)
    {
    text msg[600];
    sword rv;

    rv = oerhms(&lda, cda.rc, msg, 600);

    printf("\n\n%.*s", rv, msg);
    printf("Processing OCI function %s\n", oci_func_tab[cda.fc]);
    if (oclose(&cda))
    printf("Error closing cursor!\n");
    if (ologof(&lda))
    printf("Error logging off!\n");
    exit(1);
    }

    autouser

  2. #2
    Join Date
    Oct 2000
    Posts
    80
    Not being conversant with Oracle 7, I cannot comment on why you would not get a fecth out of sequence error there. As to O8.1.6, when you select for update, Oracle obtains locks on all rows returned in the result set. When you issue a commit (BTW I'd like to know more about autocommit...can't find it in the documentation) the aforementioned locks are released and your place in the cursor is lost. Any subsequent attempts to fetch from the cursor will bomb.
    See further: Oracle PL/SQL Programming by S. Feuerstein, published by O'Reilly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width