Folks,

I just started working on Oracle. I am learning and porting our applicaiton
from DB2 to Oracle. I have worked for several years with DB2.

I wrote a simple program that inserts data in to a test table I created.
My application and test program are written in C using "EXEC SQL" calls.
When I try to insert data I get ORA-00913 error. I have checked my code and
the number of parameters matches the columns in the table. I am running
the test program on Sun running Solaris 8 and Oracle 9.2.

$ uname -a
SunOS uplyapp06 5.8 Generic_108528-19 sun4u sparc SUNW,Ultra-Enterprise

Here is how my table is defined:


CREATE TABLE LIDPOWN.MYTABLE
(EMPNO INTEGER NOT NULL,
LNAME VARCHAR2 (35) NOT NULL,
REC VARCHAR2(1024) NOT NULL)
TABLESPACE LIDP_DATA01
PCTFREE 10 PCTUSED 70
INITRANS 1 MAXTRANS 255
LOGGING
;



Here is my sample program:

/************************************************** ******************
* Sample program
************************************************** ******************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

void ConnectToDatabase(void);
void AddRecord(void);
void RollBack(void);
void Commit(void);
void DisconnectFromDatabase(void);
int CheckForSQLError(void);

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;

int EmpNo;

static struct Record
{
short RecLength;
char Record[1024];
} Record;

static struct LastName
{
short LNameLen;
char LName[35];
} LastName;


char *UserIdPasswd = "user/passwd";

EXEC SQL END DECLARE SECTION;

void Commit(void)
{
EXEC SQL COMMIT WORK;
CheckForSQLError();
atexit(RollBack);
}

void RollBack(void)
{
EXEC SQL ROLLBACK;
CheckForSQLError();
}

void AddRecord(void)
{

printf("Enter EmpNo: ");
fscanf(stdin, "%d", &EmpNo);
getchar();
printf("Enter LName: ");
fgets(LastName.LName, 35, stdin);
LastName.LName[strlen(LastName.LName)-1] = 0;
LastName.LNameLen = strlen(LastName.LName);
printf("Enter Record: ");
fgets(Record.Record, 1024, stdin);
Record.Record[strlen(Record.Record)-1] = 0;
Record.RecLength = strlen(Record.Record);

EXEC SQL INSERT INTO lidpown.mytable
(empno,
lname,
rec)
VALUES
(:EmpNo,
:LastName,
:Record);

CheckForSQLError();
}

void ConnectToDatabase(void)
{
printf("**** Connecting to the Server. ****\n");
EXEC SQL CONNECT :UserIdPasswd;
CheckForSQLError();
}

int CheckForSQLError(void)
{
char ErrBuf[2048];
int MsgLength;
int BufLen = 2048;

if (SQLCODE == 0)
{
return(SQLCODE);
}
sqlglm(ErrBuf, &BufLen, &MsgLength);

printf("%s\n", ErrBuf);

return(SQLCODE);
}

void DisconnectFromDatabase(void)
{
printf("**** Disconnecting from database ****\n");
}

int main(void)
{
ConnectToDatabase();
AddRecord();
DisconnectFromDatabase();
}



I compile the program as follows:

$ proc PREFETCH=10 DEF_SQLCODE=YES MODE=ANSI CODE=ANSI_C AUTO_CONNECT=YES insert.pc

$ cc -g -o insert insert.c -L $ORACLE_HOME/lib -l clntsh -lposix4 -lm


When I execute the program I get SQLCODE -913.


I am able to insert data via sqlplus.

Similarly when I try to read data from the table using cursor I get
ORA-01007: variable not in select list error.

I declare cursor as follows:

EXEC SQL BEGIN DECLARE SECTION;

int EmpNo;
int MyEmpNo;

static struct Record
{
short RecLength;
char Record[1024];
} Record;

char *UserIdPasswd = "user/passwd";

EXEC SQL END DECLARE SECTION;


EXEC SQL DECLARE EmpNoNext CURSOR FOR
SELECT empno, rec FROM lidpown.mytable WHERE empno >= :MyEmpNo
ORDER BY empno ASC;

EXEC SQL OPEN EmpNoNext;

EXEC SQL FETCH EmpNoNext
INTO :EmpNo, :Record;

EXEC SQL CLOSE EmpNoNext;



What am I doing wrong?

Thanks in advance.

-
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah (AT) xnet (DOT) com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

Please, don't crosspost.

Hemant Shah <shah (AT) typhoon (DOT) xnet.com> writes:

It looks that you should use

varchar Record[1024];
varchar LastName[35];

Harald