1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Embedded SQL: Pro*C

Discussion in 'SQL PL/SQL' started by tyro, Oct 15, 2008.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Embedded SQL: Pro*C

    Pro*C has been around since the early days of Oracle PL/SQL. It is a method of integrating stored procedures in PL/SQL and includes the flexible benefits and speed of writing code in C. Since Oracle 8, C++ has been included. Therefore it is now known as Oracle C/C++.

    The combined features of both languages prove beneficial in certain systems, where the stored procedures can be used to extract data from a server database, and the C languages used to manipulate data on the client machine, making processing time substantially reduced.

    Pro*C programs have to be compiled before they are used. It is not an interpreted language. The precompiler recognizes code which forms SQL statements. These are then replaced with calls to function within the SQL runtime library. The output is raw C/C++ code. It is then the place of a regular C compiler to generate the necessary binary executable file.

    An example of Pro*C code is shown below:
    Code (Text):

    {
            int id;
            /* ... */
            EXEC SQL SELECT id INTO :id
                     FROM Employee
                     WHERE EMPNO=37482389;
            /* ... */
            printf("The employee id is %d\n", a);
            /* ... */
        }
     
    As you can see from this example, C variables can have values assigned to them from the Oracle PL/SQL code. The C variable is called a bind variable and is distinguished from other variables (which could be oracle variables) by the prefix colon ‘:’.

    In this case, an employee id is being retrieved from a database and is then assigned to the c variable :id.

    There are a limited number of proprocessors included, such as #include and #if.

    However, there is a limitation on the preprocessors that can be used, and therefore some items may not be usable, such as #define.

    SQL statements are called with EXEC, as shown in the previous example. One of the connections to be made in the C code is the use of labels to determine direction. Some of these are shown below:

    Code (Text):

    EXEC SQL WHENEVER SQLERROR GOTO error_in_SQL;
        /* ... */
    error_in_SQL:
        /* do error handling */
     
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Some more things to say about writing code in Pro*C :):

    Writing code in Pro*C is both interesting and powerful. We have all the advantages of deciding the most appropriate course of action for a given situation. For example I can decide to use the C functions for calculations rather than waste time and effort using SQL code, which will hit the network and slow things down.

    The ability to write code at a bit level is available by using the C functionality. This could be useful when reading an integer from the database and using C bit-level functionality on the integer to determine logical courses of action.

    This kind of coding can lead to a more efficient database as you would only need to store data which was absolutely essential to your business logic. Why stores 10 integers to indicate something when one will do the same job? However, the downside of this is that 10 integers stored in the database would be a lot more user friendly. In the end the way you design your system for Pro*C usage depends on the type of application you are involved with.

    Many engineering type applications use Pro*C, but a business type application is best served by pure database functionality.