Home » Developer & Programmer » Precompilers, OCI & OCCI » Call Package Oracle from Pro*C
Call Package Oracle from Pro*C [message #37163] Mon, 21 January 2002 23:33 Go to next message
Edi Darmawan
Messages: 1
Registered: January 2002
Junior Member
I have a question and this is : how i can call package/procedure/function on
oracle from Pro*C ?Thank you very much.
Re: Call Package Oracle from Pro*C [message #37202 is a reply to message #37163] Thu, 24 January 2002 03:31 Go to previous messageGo to next message
MP
Messages: 6
Registered: January 2002
Junior Member
Here's a Sample Program :

/*************************************************************
Sample Program 9: Calling a stored procedure

This program connects to ORACLE using the SCOTT/TIGER
account. The program declares several host arrays, then
calls a PL/SQL stored procedure (GET_EMPLOYEES in the
CALLDEMO package) that fills the table OUT parameters. The
PL/SQL procedure returns up to ASIZE values.

Sample9 keeps calling GET_EMPLOYEES, getting ASIZE arrays
each time, and printing the values, until all rows have been
retrieved. GET_EMPLOYEES sets the done_flag to indicate "no
more data."

*************************************************************/

#include stdio.h
#include string.h
#include stdlib.h
#include sqlda.h
#include sqlcpr.h

typedef char asciz[[20]];
typedef char vc2_arr[[11]];

EXEC SQL BEGIN DECLARE SECTION;
/* User-defined type for null-terminated strings */
EXEC SQL TYPE asciz IS STRING(20) REFERENCE;

/* User-defined type for a VARCHAR array element. */
EXEC SQL TYPE vc2_arr IS VARCHAR2(11) REFERENCE;

asciz username;
asciz password;
int dept_no; /* which department to query? */
vc2_arr emp_name[[10]]; /* array of returned names */
vc2_arr job[[10]];
float salary[[10]];
int done_flag;
int array_size;
int num_ret; /* number of rows returned */
EXEC SQL END DECLARE SECTION;

long SQLCODE;

void print_rows(n)
int n;
{
int i;

if (n = = 0)
{
printf("No rows retrieved.n");
return;
}

for (i = 0; i < n; i++)
printf("%10.10s%10.10s%6.2fn",
emp_name[[i]], job[[i]], salary[[i]]);
}

/* Handle errors. Exit on any error. */
void sql_error()
{
char msg[[512]];
size_t buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;

buf_len = sizeof(msg);
sqlglm(msg, &buf_len, &msg_len);

printf("nORACLE error detected:");
printf("n%.*s n", msg_len, msg);

EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}

void main()
{
char temp_buf[[32]];

/* Connect to ORACLE. */
EXEC SQL WHENEVER SQLERROR DO sql_error();

strcpy(username, "scott");
strcpy(password, "tiger");
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("nConnected to ORACLE as user: %snn", username);

printf("Enter department number: ");
gets(temp_buf);
dept_no = atoi(temp_buf);

/* Print column headers. */
printf("nn");
printf("%-10.10s%-10.10s%sn", "Employee", "Job", "Salary");
printf("%-10.10s%-10.10s%sn", "--------", "---", "------");

/* Set the array size. */
array_size = 10;

done_flag = 0;
num_ret = 0;

/* Array fetch loop.
* The loop continues until the OUT parameter done_flag is set.
* Pass in the department number, and the array size--
* get names, jobs, and salaries back.
*/
for (;;)
{
EXEC SQL EXECUTE
BEGIN calldemo.get_employees
(:dept_no, :array_size, :num_ret, :done_flag,
:emp_name, :job, :salary);
END;
END-EXEC;

print_rows(num_ret);

if (done_flag)
break;
}

/* Disconnect from the database. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}

Hope it helps
icon8.gif  Re: Call Package Oracle from Pro*C [message #238573 is a reply to message #37163] Fri, 18 May 2007 10:07 Go to previous message
NizamUlMulk
Messages: 11
Registered: May 2007
Location: Paraguay
Junior Member
That code founds only for package functions.
Do you know how to call a package procedure whith in, out variables?.

Example:
....
EXEC SQL INCLUDE ORACLE_DECLARE_SECTION;
VARCHAR SQL_lage[100];
VARCHAR SQL_tageper[5];
VARCHAR SQL_tageaut[5];
VARCHAR SQL_matricula[16];
VARCHAR SQL_ruc[12];
VARCHAR SQL_cage[5];
VARCHAR SQL_date[20];

EXEC SQL INCLUDE ORACLE_SQL_SECTION;
....
ora_c_to_vc(SQL_ruc, "AAAA000000Q");
ora_c_to_vc(SQL_cage, "DESP");
....
EXEC SQL SELECT to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') INTO :SQL_date FROM DUAL;
....
/* Next sentence is in line 96 */
EXEC SQL CALL PA_AGE.P_OBTNR_AGEDAT_OTROS(:SQL_ruc, :SQL_cage,
to_date(:SQL_date,'dd/mm/yyyy hh24:mi:ss'),
:SQL_lage,:SQL_tageper, :SQL_tageaut, :SQL_matricula);

----------------------------------------------------------------
When execute this program, the error is:
LINE IN FILE: 96
SQL STMT: ORA-06576: not a valid function or procedure name

The package is public, have synonym.

Can Help me?



Previous Topic: problem with fopen in Pro*C
Next Topic: /usr/bin/ld: cannot find -lclntsh
Goto Forum:
  


Current Time: Thu Mar 28 15:44:28 CDT 2024