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!

Calling Stored Procedure with parameters from a C program

Discussion in 'SQL PL/SQL' started by saracooper, Mar 19, 2010.

  1. saracooper

    saracooper Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hello, I need to call a PL/SQL stored procedure from a C program.
    Something like this sample program provided by Oracle -


    Code (C++):
    main()
    {
       int i;
    EXEC SQL BEGIN DECLARE SECTION;
       /* Define type for null-terminated strings. */
       EXEC SQL TYPE asciz IS STRING(20);
       asciz  username[20];
       asciz  password[20];
       int    dept_no;    /* which department to query */
       char   emp_name[10][21];
       char   job[10][21];
       EXEC SQL VAR emp_name is STRING (21);
       EXEC SQL VAR job is STRING (21);
       float  salary[10];
       int    done_flag;
       int    array_size;
       int    num_ret;    /* number of rows returned */
       int    SQLCODE;
    EXEC SQL END DECLARE SECTION;

       /* Connect to Oracle. */
       strcpy(username, "SCOTT");
       strcpy(password, "TIGER");

       EXEC SQL WHENEVER SQLERROR DO sqlerror();

       EXEC SQL CONNECT :username IDENTIFIED BY :password;

       printf("Enter department number: ");
       scanf("%d", &dept_no);
       fflush(stdin);

       /* Set the array size. */
       array_size = 10;
       done_flag = 0;
       num_ret = 0;

       /* Array fetch loop - ends when NOT FOUND becomes true. */
          EXEC SQL EXECUTE
             BEGIN personnel.get_employees
                (:dept_no, :array_size, :num_ret, :done_flag,
                :emp_name, :job, :salary);
             END;
          END-EXEC;

    }
    The question is - how is the Stored procedure get_employees declared ? Or more specifically, how is the salary parameter declared in get_employees ? Any help is highly appreciated.
     
  2. UnixGuru

    UnixGuru Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    I suggest you find out more about the package PERSONNEL and the procedure GET_EMPLOYEES. You can do that by describing the package in oracle using sqlplus, like this:
    Code (SQL):
    DESC personnel
    You should see how each parameter for the procedure GET_EMPLOYEES is defined in ORACLE and then declare variables in C against each of the parameters in your C Program.
     
  3. saracooper

    saracooper Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Thank u very much for the help