2-dim array, Pro*C, stored PL/SQL proc?

From: Susan M. Ahlers <sma_at_icf.hrb.com>
Date: 1995/04/07
Message-ID: <1995Apr7.155120.23083_at_hrbicf>#1/1

How do you send a mutidimensional string array to a stored PL/SQL procedure using Pro*C 2.0? I have a 2 dimensional array in C (char scenarios[10][40]) which I want to send to a stored PL/SQL procedure.

The scenario table is as follows:

   create table at_scenarios (

      experiment_id          varchar2(40) not null,
      scenario_name          varchar2(40) not null);

The stored procedure is as follows:

   PROCEDURE add_at_scenarios(

      n_scenarios  IN  BINARY_INTEGER,
      scenarios    IN  ScenarioTabTyp) IS
   BEGIN
      for i in 1 .. n_scenarios loop
         INSERT INTO at_scenarios VALUES ("experiment 1", scenarios(i) );
      end loop;
      COMMIT WORK;    
   EXCEPTION 
      WHEN OTHERS THEN
         ROLLBACK;

   END add_at_scenarios;

The Pro*C subprogram is as follows:

#include <stdio.h> /* for stderr */
#include <stdlib.h> /* for free() prototype */
#include <sqlca.h> /* SQL Communications Area */
#include "ei.h"

/*  
 * #define  EI_MAX_SCEN_USED  10      
 * #define  EI_MAX_ID_LEN     40      
 * typedef  char              EI_descriptor[EI_MAX_ID_LEN]; 
 */

#include "db_prototypes.h"

int db_at_scenarios(int n_scenarios, EI_scenario_list scenarios ) {

   int db_status;

   /* Declare local variable and equivalence char to string */    typedef char scen_name[EI_MAX_ID_LEN];    EXEC SQL TYPE scen_name IS STRING(EI_MAX_ID_LEN);

   scen_name                  local_scenarios[EI_MAX_SCEN_USED]; 

   db_status = 0;

   /* Assign the local scenario array to the array of scenarios passed in. */    memcpy (local_scenarios, scenarios, sizeof(local_scenarios) );

   /* Setup error handler */
   EXEC SQL WHENEVER SQLERROR DO
      db_status = db_sql_error("at_scenarios");    EXEC SQL WHENEVER SQLWARNING DO
      db_status = db_sql_warning("at_scenarios");

   /* Insert audit trail scenario data in the database. */    EXEC SQL EXECUTE

      BEGIN
         db_process_audit_trail.add_at_scenarios (
            :n_scenarios, :local_scenarios);
      END;

   END-EXEC;        return db_status;
}

I'm calling the Pro*C subprogram with the following:

   EI_scenario_list scenario_names;

   strncpy(scenario_names[0],"scenario_4",11);
   strncpy(scenario_names[1],"scenario_5",11);
   strncpy(scenario_names[2],"scenario_6",11);

   status = db_at_scenarios(3, scenario_names);

I'm getting the following error:

   ORACLE Error occurred in at_scenarios    ORA-01480: trailing null missing from STR bind value

Any help on the correct way to send this data to a stored procedure would be greatly appreciated.

Thanks! Received on Fri Apr 07 1995 - 00:00:00 CEST