1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144
| /*make -f demo_proc.mk EXE=test_clob_dyn OBJS=test_clob_dyn.o build PROCFLAGS="sqlcheck=full userid=xxx/xxx@xxx code=ansi_c oraca=yes release_cursor=yes"*/
#include <stdio.h>
#include <string.h>
/* Include the SQL Communications Area, a structure through
* which ORACLE makes runtime status information such as error
* codes, warning flags, and diagnostic text available to the
* program.
*/
#include <sqlca.h>
/* Include the ORACLE Communications Area, a structure through
* which ORACLE makes additional runtime status information
* available to the program.
*/
#include <oraca.h>
void dyn_error(char *msg);
int main(int argc, char *argv[])
{
VARCHAR oracleid[100];
VARCHAR dynstmt3[2048];
EXEC SQL WHENEVER SQLERROR DO dyn_error("Oracle error:");
/* Save text of current SQL statement in the ORACA if an
* error occurs.
*/
oraca.orastxtf = ORASTFERR;
/*
* Connect to the database.
*/
strcpy( (char *)oracleid.arr, "xxx/xxx@xxx" );
oracleid.len = strlen((char *)oracleid.arr);
EXEC SQL CONNECT :oracleid;
printf("Connexion ... \n");
/* strcpy((char *)dynstmt3.arr, "declare i number; begin i := 0; end;"); */
char tcNomProc[80];
strcpy(tcNomProc, "clob_it.test_clob");
strcpy((char *)dynstmt3.arr, "\
declare \
cl_chaine clob; \
begin \
dbms_lob.createtemporary(lob_loc => cl_chaine, cache => true, dur => dbms_lob.session); \
dbms_lob.open(lob_loc => cl_chaine, open_mode => dbms_lob.lob_readwrite); \
dbms_lob.write(lob_loc => cl_chaine, amount => :1, offset => 1, buffer => :2); ");
strcat((char *)dynstmt3.arr, tcNomProc);
strcat((char *)dynstmt3.arr, "\
(cl_chaine); \
dbms_lob.close(lob_loc => cl_chaine); \
dbms_lob.freetemporary(lob_loc => cl_chaine); \
end;");
dynstmt3.len = strlen((char *)dynstmt3.arr);
printf("%d -%s- \n", dynstmt3.len, (char *)dynstmt3.arr);
/*EXEC SQL EXECUTE
declare
cl_chaine clob;
begin
dbms_lob.createtemporary(
lob_loc => cl_chaine,
cache => true,
dur => dbms_lob.session
);
dbms_lob.open(
lob_loc => cl_chaine,
open_mode => dbms_lob.lob_readwrite
);
dbms_lob.write(
lob_loc => cl_chaine,
amount => 25,
offset => 1,
buffer => :buffer
);
clob_it.test_clob(cl_chaine);
dbms_lob.close(lob_loc => cl_chaine);
dbms_lob.freetemporary(lob_loc => cl_chaine);
END;
END-EXEC;*/
/* Méthode 1: EXEC SQL EXECUTE IMMEDIATE :dynstmt3; */
/* Méthode 2 */
EXEC SQL PREPARE S FROM :dynstmt3;
/* 25, abcdefghijklmnopqrtuvwxyz */
int iLgBuffer = 25;
char tcChaine[80];
strcpy(tcChaine, "abcdefghijklmnopqrtuvwxyz");
EXEC SQL EXECUTE S USING :iLgBuffer, :tcChaine;
printf("Traitement ... \n");
return 0;
}
void dyn_error(char *msg)
{
/* This is the Oracle error handler.
* Print diagnostic text containing the error message,
* current SQL statement, and location of error.
*/
printf("\n%.*s\n",sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
printf("in \"%.*s...\'\n",oraca.orastxt.orastxtl, oraca.orastxt.orastxtc);
printf("on line %d of %.*s.\n\n",oraca.oraslnr, oraca.orasfnm.orasfnml,oraca.orasfnm.orasfnmc);
/* Disable Oracle error checking to avoid an infinite loop
* should another error occur within this routine as a
* result of the rollback.
*/
EXEC SQL WHENEVER SQLERROR CONTINUE;
/* Roll back any pending changes and disconnect from Oracle. */
EXEC SQL ROLLBACK RELEASE;
exit(1);
} |
Partager