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

Susan M. Ahlers (sma_at_icf.hrb.com) wrote:
: 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!

Susan,
 I feel you have compatibility problems here. The steps that you have  followed are correct. (I prefer to use varchar in Pro*C, I suggest that you  look at Figure 5-1 on page 5-14 of the Programmers's guide to the ORacle Pro*C,  Version (2.0) (I am assuming again!)

 Let me know how it goes. Thank you.

 Kannan
 skannan_at_fs.com Received on Mon Apr 17 1995 - 00:00:00 CEST