Re: c precompiler

fyang1_at_tiger.towson.edu (Fuzhen Yang) wrote:

> Hi, I got a serious problem with the following program which is used to
>create a table (DEPT_LOCATION) and insert two lines of values. I compiled the
>program using oracle C Precompiler and run it successfully. But when I went to
>my oracle system, I used statement "select Table_Name from USER_TABLES", the
>table I created using Pre C program didn't show up, which means the table
>"DEPT_LOCATION" was not created. I don't know does the table created using pre
>C program only exists during runtime of the program or should exist forever?
> I need idea for that.
> Good luck to everybody!!!
>
>fyang1_at_tiger.towson.eud
>
>
>
>#include <stdio.h>
>
>char *username = "fyang1";
>char *password = "fuzhen";
>char dlocation[15];
>int dnumber;
>
>void sql_error();
>#include <sqlca.h>
>
>main()
>{
> EXEC SQL WHENEVER SQLERROR do sql_error("oracle error");
>
> EXEC SQL CONNECT:username IDENTIFIED BY:password;
> printf("Connected.\n");
>
> EXEC SQL CREATE TABLE DEPT_LOCATION(DNUM int,
> DLOC varchar(15));
> EXEC SQL COMMIT;
> EXEC SQL INSERT INTO DEPT_LOCATION VALUES(1, "Baltimore");
> EXEC SQL INSERT INTO DEPT_LOCATION VALUES(2, "Houston");
> EXEC SQL COMMIT;
> EXEC SQL COMMIT WORK RELEASE;
> exit(0);
>}
>
>void sql_error(msg)
>char *msg;
>{
> char buf[500];
> int buflen, msglen;
>
> EXEC SQL WHENEVER SQLERROR CONTINUE;
> EXEC SQL ROLLBACK WORK RELEASE;
> buflen = sizeof (buf);
> sqlglm(buf, &buflen, &msglen);
> printf("%s\n", msg);
> printf("%*.s\n", msglen, buf);
> exit(1);
>}
>
>
Fyang,

You need to use dynamic sql, either the Pro*C style, or use the dbms_sql package to create the table. Just a note, it helps if the error messages are called and checked at times, after each sql statement.

Hope this helps.

---
Vikram Goel         vgoel_at_emi.net  (H)
Sr. Oracle DBA      vgoel_at_co.palm-beach.fl.us (O).
 & Oracle Performance Analyst. Consultant