ORA-1036: Table insertion error
Date: 1997/07/07
Message-ID: <33C12D46.452_at_usa.net>
RDBMS Version (if applicable): 7.1.6
Error Number (if applicable): 1036
Product and Version: Pro*C 2.0
[Quoted] Operating System: Sun Solaris V2 Sparc
We have been experiencing problems with doing an insert into a table. The usual way of doing a Method 2 Dynamic SQL insert with bind variables does not seem to work. We could not get past the EXEC SQL PREPARE portion. The following is the code that finally got through the PREPARE without getting error ORA-917, "Missing comma". Now we get ORA-1036, "illegal variable name/number".
The following is a cut down version of the program that tries to insert one record.
#include #include #include #include
/* #include "GCDMPrototypes.h"*/
#include "SAICGeoDefines.h" #include "ora_halflink.h" #include #include /*===========================================================================SQL routines to import marker data from OpenWorks into GCDM
=============================================================================*/
/* Initialize the SQLCA */
#ifndef SQLCA_INIT #define SQLCA_INIT #endif
/* Initialize the ORACA */
#ifndef ORACA_INIT #define ORACA_INIT #endif
/* #define DP_LEN 5*/
/* #define DATE_LEN 25*/
#define MAXMESSAGELEN 256
#define SUB_STRING 30
EXEC SQL INCLUDE ORACA;
EXEC ORACLE OPTION (ORACA=YES); /* Enable ORACA */
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
/* extern int parse_flag; */
long SQLCODE;
/* SQLDA *bind_dp;*/
EXEC SQL BEGIN DECLARE SECTION;
char sql_stmt[2500];
/* EXEC SQL VAR sql_stmt IS STRING (2500);
VARCHAR lock_stmt[512];
*/
char current_well_id[31];
EXEC SQL VAR current_well_id IS STRING (31);
char hor_dp_code[DP_LEN];
EXEC SQL VAR hor_dp_code IS STRING (DP_LEN);
char horizon_code[17];
EXEC SQL VAR horizon_code IS STRING (17);
char hor_dia_code[17];
EXEC SQL VAR hor_dia_code IS STRING (17);
int hor_seq_number;
char table_name[36] = ""; /* current table name */ EXEC SQL VAR table_name IS STRING (36);
char well_brid[31];
EXEC SQL VAR well_brid IS STRING (31);
char dp_code[DP_LEN]; /* data provider code */
EXEC SQL VAR dp_code IS STRING (DP_LEN);
char geol_provider_code[17];
EXEC SQL VAR geol_provider_code IS STRING (17);
char marker_code[17]; /* usually marker name */
EXEC SQL VAR marker_code IS STRING (17);
char dia_code[17]; /* horizon data interpreter analyst code */
EXEC SQL VAR dia_code IS STRING (17);
/* this number is usually generated by the database on inserts */
int seq_number;
float up_md_depth; /* upper measured depth */
float low_md_depth;
char up_comment_code[5];
EXEC SQL VAR up_comment_code IS STRING (5);
char low_comment_code[5];
EXEC SQL VAR low_comment_code IS STRING (5);
char elev_ref_cd[5];
EXEC SQL VAR elev_ref_cd IS STRING (5);
char source_table_code[5];
EXEC SQL VAR source_table_code IS STRING (5);
char eff_date[DATE_LEN];
EXEC SQL VAR eff_date IS STRING (DATE_LEN);
/* int sql_code = 0; */
/* char err_text[512] = "";
EXEC SQL VAR err_text IS STRING (512);
*/
/* null indicators */
short well_brid_ind;
short dp_code_ind;
short geol_provider_code_ind;
short marker_code_ind;
short dia_code_ind; /* horizon data interpreter analyst code */
short seq_number_ind;
short up_md_depth_ind;
short low_md_depth_ind;
short up_comment_code_ind;
short low_comment_code_ind;
short elev_ref_cd_ind;
short source_table_code_ind;
short eff_date_ind;
EXEC SQL END DECLARE SECTION;
/* Geoshare Rules:
Overwrite - if record does not exist, skip it or do nothing.
Insert - if record exists, skip it or do nothing.
Replace - as expected.
Update - as expected.
*/
/*==============================================================================
imp_marker_data: Place marker data from wellbore horizon into prospective tables.
RWB 9 JUN 97 Creation.
RWB 20 JUN 97 Added seq_number to insert and update of tables.
==================================================================================*/
int main ()
{
char *Module = "{import_only:main}";
char Message[MAXMESSAGELEN] = "";
char transaction_type[8] = "";
char old_table_name[36] = "";
char geol_prov[17] = "";
char pick_type[5] = "";
/* GCDM_WellMarker_T *current_well_marker = NULL; */
int return_code = 0;
int rows_updated;
int bind_count;
int db_error_code = 0;
int stmt_len = 0;
int date_length = 0;
int parse_flag = 1;
/*====================================================================*/
connect_oracle("elasseter", "a");
EXEC SQL COMMIT;
/* initialize the sql_stmt buffer */
memset ((void *) sql_stmt, 0, sizeof (sql_stmt));
strcpy(well_brid, "GEOSHARE_TEST_1"); strcpy(dp_code, "EXON"); strcpy(dia_code, "UNK"); strcpy(up_comment_code, "OKAY"); strcpy(eff_date, "1997-06-27:00:00:00");up_md_depth = 55555.0;
seq_number = 1;
strcpy(geol_provider_code, "KAGEAN");
strcpy(marker_code, "UNKNOWN");
well_brid_ind = strlen(well_brid);
dp_code_ind = strlen(dp_code);
dia_code_ind = strlen(dia_code);
up_comment_code_ind = strlen(up_comment_code);
eff_date_ind = strlen(eff_date);
up_md_depth_ind = 1;
seq_number_ind = 1;
geol_provider_code_ind = strlen(geol_provider_code);
marker_code_ind = strlen(marker_code);
parse_flag = 1;
EXEC SQL WHENEVER SQLERROR
DO return_code = sql_imp_error(Module);
date_length = strlen(eff_date);
/* strcpy ((char *) sql_stmt, "INSERT INTO WLBR_SEQ_BNDRY");*/
/* strcat ((char *) sql_stmt, table_name); */
strcpy ( sql_stmt,
"INSERT INTO WLBR_SEQ_BNDRY (WLBR_ID, SEQ_STRAT_DP_CD, GEOL_PROV_CD,
SEQ_BNDRY_ID,"
" SEQ_DIA_CD, SEQ_NBR,"
" BNDRY_MD_DPTH, PICK_CMNT_CD, INTRPT_EFV_DATE, OWNER_SECUR_CD)"/* );*/
/* strcat ((char *) sql_stmt,*/ " VALUES (':m1:m1ind', ':m2:m2ind',
':m3:m3ind', "
"':m4:m4ind', ':m5:m5ind', TO_NUMBER(':m6:m6ind'),
TO_NUMBER(':m7:m7ind'), "
"':m8:m8ind', TO_DATE(':m9','YYYY-MM-DD:HH24:MI:SS'), 'TST_D')");
/* sql_stmt.len = strlen(((char *) sql_stmt) + 1); */
sql_stmt[strlen(sql_stmt) + 1]='\0';
stmt_len = strlen(sql_stmt);
EXEC SQL PREPARE insert_stmt FROM :sql_stmt;
if (return_code == SUCCESS)
{
EXEC SQL EXECUTE insert_stmt USING
:well_brid:well_brid_ind, :dp_code:dp_code_ind, :geol_provider_code:geol_provider_code_ind, :marker_code:marker_code_ind, :dia_code:dia_code_ind, :seq_number:seq_number_ind, :up_md_depth:up_md_depth_ind, :up_comment_code:up_comment_code_ind, :eff_date; /* :eff_date_ind; */
/* */
db_error_code = SQLCODE;
}
EXEC SQL COMMIT;
disconnect_oracle();
exit (return_code);
}
/*==================================================================================*/int sql_imp_error (char *Module)
{
char Message[MAXMESSAGELEN];
int buf_len, msg_len;
int err_code = 0;
int return_code = 0;
extern long SQLCODE; /* local copy of SQLCODE */
int parse_flag = 1;
int offset = 0;
char sub_sql_string[SUB_STRING] = "";
err_code = SQLCODE;
buf_len = sizeof (Message);
/* NOT FOUND is not really an error condition */
if ( (err_code == 1403) || (err_code == NO_DATA_FOUND) || (err_code == NO_DATA_FOUND2) )
{
return_code = DATA_NOT_FOUND;
return (return_code);
}
/* ORACLE error handler */
EXEC SQL WHENEVER SQLERROR CONTINUE;
/* check for extents blowout problem */
return_code = FATAL_ERROR;
if ( err_code == LOGON_DENIED )
{
return_code = NO_LOGON;
}
if ( err_code == DUP_VAL_ON_INDEX )
{
return_code = DUPLICATE_VALUE;
}
GeoWriteMessage (INFO, Module, "An Oracle error occurred!"); sprintf (Message, "%.70s",sqlca.sqlerrm.sqlerrmc); GeoWriteMessage (INFO, "{sql_imp_error}", Message);
/* Print the text of the last SQL statement */
if (parse_flag)
{
sprintf (Message, "Last SQL statement at or near line number %d\n %.*s",
oraca.oraslnr, oraca.orastxt.orastxtl, oraca.orastxt.orastxtc);
GeoWriteMessage (INFO, Module, Message);
/* Get full text of error message. */
if (err_code < 0)
{
sqlglm (Message, &buf_len, &msg_len);
/* Null-terminate and print the text. */
Message[msg_len] = '\0';
GeoWriteMessage (0, Module, Message);
printf("SQL Statement: \n\n%s\n", (char *) sql_stmt);
}
offset = sqlca.sqlerrd[4];
sprintf (Message,
"Parse error at character offset %d in SQL statement.",
sqlca.sqlerrd[4]);
GeoWriteMessage (INFO, Module, Message);
if ( offset > 0 )
{
strncpy(sub_sql_string, sql_stmt[offset], SUB_STRING - 1);
sprintf (Message, "Error Begins at: %s ", sub_sql_string);
/* sql_stmt[offset],
sql_stmt[offset+1],sql_stmt[offset+2],sql_stmt[offset+3], sql_stmt[offset+4],sql_stmt[offset+5], sql_stmt[offset+6], sql_stmt[offset+7], sql_stmt[offset+8], sql_stmt[offset+9], sql_stmt[offset+10]); */GeoWriteMessage (INFO, Module, Message); }
}
EXEC SQL ROLLBACK WORK;
/* reset SQLCODE */
if ( SQLCODE == 0 )
SQLCODE = err_code;
return (return_code);
}
/*============================================================================*/
If anyone has a clue to what's going on, please help. In the Insert
string
we had to enclose the substitution variables within single quotes, which
I
have never seen it necessary to do before. When we tried the same query
with
SQL*Plus using substitution variables, we had to enclose the variables
in
single quotes, otherwise it complained of the variable being the name of
a column.
If anyone has any suggestions, please let me know.
Reginald Bailey
c/o
sryan_at_lgc.com or rwbailey_at_usa.net
-- -------------------------------------------------------------- Reginald W. Bailey Consultant c/o Landmark Graphics Corp. Houston, TX 281-368-8937 rbailey_at_lgc.com rwbailey_at_usa.net --------------------------------------------------------------Received on Mon Jul 07 1997 - 00:00:00 CEST