What you need to do goes like this:
1. Write out a rough structure of all the queries you'll need to do on either database
2. Write a C programme that will allow you to edit files (I prefer to use a large buffer myself) maybe something like this:
Code:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define MAX_NO_STR 1000
#define MAX_LGT_STR 200
int main(int argc, char *argv[]) {
int i=0, j;
char **buffer;
FILE *fp, *fopen();
buffer = malloc( MAX_NO_STR * sizeof(char *) );
for(i=0; i < MAX_NO_STR; i++) {
buffer[i] = malloc( MAX_LGT_STR * sizeof(char) );
}
if(buffer == NULL) {
puts("Out of memory");
exit(1);
}
if( (fp = fopen(argv[1], "r")) == NULL) {
puts("File opening for reading failed");
exit(1);
}
while( (fgets(buffer[i], MAX_NO_STR, fp) ) != NULL ) {
/*Enter conditional statements to test where to insert information here
Use comments in the file you're going to edit to make finding these points easier
e.g.
if( strncmp(buffer[i], "//Insert new", 12) == 0 ){
}
*/
i++;
}
fclose(fp);
// Do any other editing here
if( (fp = fopen(argv[1], "w")) == NULL) {
puts("File opening for writing failed");
exit(1);
}
for(j=0; j<i; j++) fputs(buffer[j], fp);
fclose(fp)
for(i=0; i<MAX_NO_STR; i++) free(buffer[i]);
free(buffer);
return 0;
}
3. within your program use calls like this to run various sqlplus scripts USE PL/SQL IF YOU CAN!
Code:
system("sqlplus -s username/password @script.sql argument1 argument2 >file_to_save_output 2>&1");
The -s supresses server output to save you screens worth of info you don't need (DON'T USE DURING TESTING!). Use the following to display any input you require (requires PL/SQL):
Code:
DBMS_OUTPUT.PUT_LINE('Insert your message here' || variable_input || 'End of message');
the 2>&1 ensures that all error information is directed to the output file also, but if your system doesn't like it, it can be deleted.
4. Use the editing code from your C programming to get any info necessary from the output file generated by your script(I recommend strtok() for parsing, but that's just a personal preference http://www.rt.com/man/strtok.3.html)
5. Use the C program to insert any information retrieved from running the first script, into the second script.
6. Run the second script with the same syntax as the first.
7. Sit back and admire your handywork
NOTE: make sure to end your SQL or PL/SQL scripts with exit as the last line in the file- this ensures sqlplus will return control to the C program
If the sqlplus script appears to hang during testing, type control +C and then exit even if you don't see a prompt. That usually gets you out.
There's a little bit of work in this, but I hope it's of use to you. Just remember, as always, the key to writing a program that works without confusing yourself is to plan your structure before writing a single line of code!
Let me know if you need any more help or clarification.