Home » Developer & Programmer » Precompilers, OCI & OCCI » Calling Stored Procedure from a C program
Calling Stored Procedure from a C program [message #448125] Fri, 19 March 2010 11:47 Go to next message
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.
Re: Calling Stored Procedure from a C program [message #448127 is a reply to message #448125] Fri, 19 March 2010 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Calling Stored Procedure from a C program [message #448130 is a reply to message #448125] Fri, 19 March 2010 12:27 Go to previous messageGo to next message
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
Re: Calling Stored Procedure from a C program [message #448134 is a reply to message #448130] Fri, 19 March 2010 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Pro*C/C++ Programmer's Guide
Chapter 18 Collections

Regards
Michel
Re: Calling Stored Procedure from a C program [message #448223 is a reply to message #448134] Mon, 22 March 2010 03:53 Go to previous message
saracooper
Messages: 15
Registered: February 2010
Junior Member
Thanks a lot for the help. I will look into Collections.
Previous Topic: How to view SQLCODE value in Debuggin' Time
Next Topic: Pro*c file compilation error.
Goto Forum:
  


Current Time: Sat Sep 24 21:28:00 CDT 2016

Total time taken to generate the page: 0.10848 seconds