Tech Rocks

Coldfusion
Java
JQuery

An online resource for latest web technologies like Coldfusion, JRun, Pro*C, JQuery, HTML5, PHP, W3C, Java, J2EE, C, C++, ORACLE, PL/SQL, MySql, Ajax, Coldbox, Fusebox, UNIX, JavaScript, NodeJS and much more...

Sunday, February 8, 2009

Inserting and Fetching Rows by Using the Array and loading into Stored Procedure in Pro*C

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-proc.html

http://www.lsbu.ac.uk/oracle/oracle7/api/doc/PC_22/ch03a.htm

http://forums.oracle.com/forums/thread.jspa?messageID=1661451

http://asktom.oracle.com/pls/asktom/
f?p=100:11:0::::P11_QUESTION_ID:208012348074


http://www.filibeto.org/sun/lib/nonsun/oracle/
11.1.0.6.0/B28359_01/appdev.111/b28427/pc_08arr.htm


how to compile?
&
int emp_number[50];
char name[50][11];
/* ... */
EXEC SQL INSERT INTO emp(emp_number, name)
VALUES (:emp_number, :emp_name);
which will insert all the 50 tuples in one go.
*/

#DEFINE SQL_LEN 300


EXEC SQL BEGIN DECLARE SECTION;

struct record {
VARCHAR npa[50][3];
VARCHAR npa[50][3];
...
}
typedef struct record recorddata;

VARCHAR host_string[SQL_LEN];

EXEC SQL END DECLARE SECTION;

strcpy((char*)host_string.arr, "SCHEMA.PROCEDURENAME(:records[5].npa, :records[5].npx, ...)");
host_string.len = strlen((char*)host_string.arr);

EXEC SQL PREPARE stmt FROM :host_string;


/*
EXEC SQL EXECUTE stmt USING :npa, :npx, ...;

OR
rows_to_insert = 25; /* set FOR-clause variable */
EXEC SQL FOR :rows_to_insert EXECUTE stmt USING :npa, :npx, ...;
===============================================================

void insertdata()
{
int i, cnt, file_cnt=0, temp_cnt=0;
char *str;
recorddata rec_in;

while(loopFile){
file_cnt = file_cnt + 1;

/* To store temporary strings */
str = (char *)malloc (25 * sizeof(char));

/* Fill the array elements to insert */
for (i = 0; i < 50; i++)
{
temp_cnt = tmp_cnt +1;

sprintf(str, "001");
strcpy (rec_in.npx[i].arr, str);
rec_in.npx[i].len = strlen (rec_in.npx[i].arr);
rec_in.npx[i].arr[rec_in.npx[i].len] = '\0';

sprintf(str, "001");
strcpy (rec_in.npa[i].arr, str);
rec_in.npa[i].len = strlen (rec_in.npa[i].arr);
rec_in.npa[i].arr[rec_in.npa[i].len] = '\0';

...
}
if (temp_cnt == 50){
free (str);

EXEC SQL EXECUTE SCHEMA.PROCEDURENAME(:npa, :npx, ...) USING :rec_in.npa, :rec_in.npx, ...;
temp_cnt=0;
}
}end while

0 comments :

Post a Comment