Re:Using OCI to interface to Oracle

From: <dgoulet_at_vicr.com>
Date: Fri, 3 Nov 2000 10:29:34 -0500
Message-Id: <10669.121078@fatcity.com>

Ronald,

    Being an old C hack and having done a whole lot of all of the subject matter. I would vote for a mixed bag. Where you put the SQL should be dependent on what your trying to do. The name of the game is to minimize the number of round trips to and from the database. Therefore is you have an amount of data that you simply need to validate or run a calculation on and the result is used by the library or other local program then placing that in the library does not create additional overhead. But if your doing that calculation & then inserting all of the data into a table that is probably a better use of a stored procedure. Maybe these examples will be helpful (pardon my use of plain C, OH BTW, I'm a proponent of PRO*C vs OCI as it's easier to maintain at least I believe it is):

This I would call a library thing

    EXEC SQL SELECT SALARY + :increase

           INTO :new_salary
               FROM EMP
             WHERE ENAME = :ename;

    printf("New Salary for %s is %d\n", ename.arr, new_salary);

This I would Not

    EXEC SQL SELECT SALARY + :increase

           INTO :new_salary
               FROM EMP
             WHERE ENAME = :ename;
      EXEC SQL UPDATE EMP
               SET SALARY = :new_salary
             WHERE ENAME = :ename;
      print("New Salary for %s is %d\n", ename.arr, new_salary);

Better would be

    EXEC SQL EXECUTE

        BEGIN
          :new_salary := UPDATE_SALARY(:increase, :ename);
          END;
      END-EXEC;

    print("New Salary for %s is %d\n", ename.arr, new_salary);

Dick Goulet
Senior Oracle DBA & Officially OCP certified.

(Yeah, the pin is great, but the certificate is better, Received on Fri Nov 03 2000 - 09:29:34 CST