OraFAQ Forum: Precompilers, OCI & OCCI » Calling Stored Procedure from a C program

Home » Developer & Programmer » Precompilers, OCI & OCCI » Calling Stored Procedure from a C program

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calling Stored Procedure from a C program [message #448125]

Fri, 19 March 2010 11:47

saracooper
Messages: 15
Registered: February 2010

Junior Member

Hello, I need to call a PL/SQL stored procedure from a C program.
Something like this sample program provided by Oracle -

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.

Report message to a moderator

Re: Calling Stored Procedure from a C program [message #448127 is a reply to message #448125]

Fri, 19 March 2010 12:09

Michel Cadot
Messages: 68755
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

how is the Stored procedure get_employees declared

It is declared in Oracle server not in your program.

Regards
Michel

Report message to a moderator

Re: Calling Stored Procedure from a C program [message #448130 is a reply to message #448125]

Fri, 19 March 2010 12:27

saracooper
Messages: 15
Registered: February 2010

Junior Member

I will explain better. The example I put is just a Sample program from Oracle, but I am doing something similar. I have a similar C program that calls a stored procedure with a float array as parameter -

void main()
{

EXEC SQL BEGIN DECLARE SECTION;
char currCursor[1000]="currCursor1";
char SelectStr[1000]= "SELECT CVAL1 FROM CURVA1";
float varf[5];
EXEC SQL END DECLARE SECTION;

connect();

EXEC SQL EXECUTE
BEGIN
harshal.decl(:currCursor,:SelectStr);
END;
END-EXEC;

EXEC SQL EXECUTE
BEGIN
harshal.opcur(:currCursor);
END;
END-EXEC;

EXEC SQL EXECUTE
BEGIN
harshal.OL1.fetc(:currCursor,1,:varf);
END;
END-EXEC;
}

I am calling fetc routine with a float array - varf. And having problems with the parameter declaration of the fetc routine.
This is similar to the Sample program I have posted before. So I need help with the parameter declaration for float array in fetc or get_employees routines. Thanks

Report message to a moderator