A problem of oratcl orabindexec cmd
in [TCL]
|
From: frspace on 11 Dec 2008 05:33 Hello! I am working with tclsh8.2 & oratcl3.3. I have a table to insert lots of records,which has a primary key field ID.So I use "orasql -parseonly" and "orabindexec" method. I got a problem when I try to insert a reord, which could raise a primary key conflict. While using oratcl3.3 ,I can only catch an exception message: "orabindexec: SQL execution failed." But I can't tell what error it is .The oramsg(rc) is 0,and oramsg (errortxt) is null. Can I get something like ora-errcode?Because I have to distinguish from the error types. BTW: with oratcl2.5,no exception will caught,and the oramsg(rows) will be 0. This is my first post in this group,and any help would be greatly appreciated!
From: thelfter on 12 Dec 2008 08:46 On Dec 11, 5:33 am, frsp...(a)hotmail.com wrote: > Hello! > > I am working with tclsh8.2 & oratcl3.3. > > I have a table to insert lots of records,which has a primary key field > ID.So I use "orasql -parseonly" and "orabindexec" method. > > I got a problem when I try to insert a reord, which could raise a > primary key conflict. > While using oratcl3.3 ,I can only catch an exception message: > "orabindexec: SQL execution failed." > But I can't tell what error it is .The oramsg(rc) is 0,and oramsg > (errortxt) is null. > Can I get something like ora-errcode?Because I have to distinguish > from the error types. > > BTW: with oratcl2.5,no exception will caught,and the oramsg(rows) will > be 0. > > This is my first post in this group,and any help would be greatly > appreciated! Hello, What version of Oracle are you connecting too? Oratcl 3.3 is quite old and uses an older Oracle API (OCI7) that Oracle no longer supports. I'll try to help you as best as I can. Also, if you have a test case, please send that to me. (email is fine) -Todd
From: frspace on 16 Dec 2008 05:28 On 12ÔÂ12ÈÕ, ÏÂÎç9ʱ46·Ö, "thelf...(a)gmail.com" <thelf...(a)gmail.com> wrote: > On Dec 11, 5:33 am, frsp...(a)hotmail.com wrote: > > > Hello! > > > I am working with tclsh8.2 & oratcl3.3. > > > I have a table to insert lots of records,which has a primary key field > > ID.So I use "orasql -parseonly" and "orabindexec" method. > > > I got a problem when I try to insert a reord, which could raise a > > primary key conflict. > > While using oratcl3.3 ,I can only catch an exception message: > > "orabindexec: SQL execution failed." > > But I can't tell what error it is .The oramsg(rc) is 0,and oramsg > > (errortxt) is null. > > Can I get something like ora-errcode?Because I have to distinguish > > from the error types. > > > BTW: with oratcl2.5,no exception will caught,and the oramsg(rows) will > > be 0. > > > This is my first post in this group,and any help would be greatly > > appreciated! > > Hello, > > What version of Oracle are you connecting too? Oratcl 3.3 is quite > old and uses an older Oracle API (OCI7) that Oracle no longer > supports. > > I'll try to help you as best as I can. > > Also, if you have a test case, please send that to me. (email is > fine) > > -Todd- Òþ²Ø±»ÒýÓÃÎÄ×Ö - > > - ÏÔʾÒýÓõÄÎÄ×Ö - Thank you Todd, I'm using Oracle 8i/9i/10g and oratcl3.3 . As you said Oratcl 3.3 uses OCI7, so I used to modified the configfile to compile oratcl3.3 for Oracle8i (also 9i & 10g). Actually I made exception data designedly.I just want to catch the oracle error code,and then do switch according to the error code. But I just got "orabindexec: SQL execution failed." no matter what kind of exception data I have made. I have simplified my script as a test case: create table TEST_TABLE ( PKEY_ID NUMBER(8), COMMENTS VARCHAR2(512) ) alter table TEST_TABLE add constraint TEST_PK primary key (PKEY_ID) ##############script begin############## #!/bin/sh # the next line restarts using tclsh \ exec tclsh8.2 "$0" "$@" set exten [info sharedlibextension] load liboratcl3.3${exten} set dbconn "open/open(a)sesame" set logon [oralogon $dbconn] set cur [oraopen $logon] ##############1.make the sql############## set insert_table "test_table" set insert_fields "pkey_id,comments" set insert_value ":var_0,:var_1" set insert_sql "insert into $insert_table ($insert_fields) values (" append insert_sql "$insert_value)" orasql $cur $insert_sql -parseonly ##############2.read file & do insert############## #The content of ./testfile.txt is: #1|testok #1|primary key error ################################################### set data_file "./testfile.txt" set DELIMITER "|" set varlist [split "var_0 var_1"] set fd_in [open $data_file r] while {[gets $fd_in line_tmp] >= 0} { set line_fields [split $line_tmp $DELIMITER] if { [llength $line_fields] == 0 } { continue } set bind_field "" set field_index 0 foreach var_item $varlist { set line_field [lindex $line_fields $field_index] append bind_field " :$var_item [list $line_field]";#use list to escape special characters incr field_index } set cmd "orabindexec \$cur $bind_field" ##############3.bind & execute the sql############## ##############The problem is : I can't get the oracle error code in this catch!!! ##############How can I get "ORA-00001: unique constrant (open.TEST_PK) violated" ##############instead of "orabindexec: SQL execution failed." ? if { [catch "$cmd" res] } { close $fd_in oraclose $cur oralogoff $logon puts "-ERR {insert err.$res}" puts "$::oramsg(rc)==$::oramsg(errortxt)==" return } } close $fd_in oraclose $cur oralogoff $logon ##############script end############## -Toto
From: thelfter on 16 Dec 2008 23:14 On Dec 16, 5:28 am, frsp...(a)hotmail.com wrote: > On 12ÔÂ12ÈÕ, ÏÂÎç9ʱ46·Ö, "thelf...(a)gmail.com" <thelf...(a)gmail.com> wrote: > > > > > On Dec 11, 5:33 am, frsp...(a)hotmail.com wrote: > > > > Hello! > > > > I am working with tclsh8.2 & oratcl3.3. > > > > I have a table to insert lots of records,which has a primary key field > > > ID.So I use "orasql -parseonly" and "orabindexec" method. > > > > I got a problem when I try to insert a reord, which could raise a > > > primary key conflict. > > > While using oratcl3.3 ,I can only catch an exception message: > > > "orabindexec: SQL execution failed." > > > But I can't tell what error it is .The oramsg(rc) is 0,and oramsg > > > (errortxt) is null. > > > Can I get something like ora-errcode?Because I have to distinguish > > > from the error types. > > > > BTW: with oratcl2.5,no exception will caught,and the oramsg(rows) will > > > be 0. > > > > This is my first post in this group,and any help would be greatly > > > appreciated! > > > Hello, > > > What version of Oracle are you connecting too? Oratcl 3.3 is quite > > old and uses an older Oracle API (OCI7) that Oracle no longer > > supports. > > > I'll try to help you as best as I can. > > > Also, if you have a test case, please send that to me. (email is > > fine) > > > -Todd- Òþ²Ø±»ÒýÓÃÎÄ×Ö - > > > - ÏÔʾÒýÓõÄÎÄ×Ö - > > Thank you Todd, > I'm using Oracle 8i/9i/10g and oratcl3.3 . > As you said Oratcl 3.3 uses OCI7, so I used to modified the configfile > to compile oratcl3.3 for Oracle8i (also 9i & 10g). > > Actually I made exception data designedly.I just want to catch the > oracle error code,and then do switch according to the error code. > But I just got "orabindexec: SQL execution failed." no matter what > kind of exception data I have made. > > I have simplified my script as a test case: > > create table TEST_TABLE > ( > PKEY_ID NUMBER(8), > COMMENTS VARCHAR2(512) > ) > alter table TEST_TABLE add constraint TEST_PK primary key (PKEY_ID) > > ##############script begin############## > #!/bin/sh > # the next line restarts using tclsh \ > exec tclsh8.2 "$0" "$@" > > set exten [info sharedlibextension] > load liboratcl3.3${exten} > set dbconn "open/open(a)sesame" > set logon [oralogon $dbconn] > set cur [oraopen $logon] > > ##############1.make the sql############## > set insert_table "test_table" > set insert_fields "pkey_id,comments" > set insert_value ":var_0,:var_1" > > set insert_sql "insert into $insert_table ($insert_fields) values (" > append insert_sql "$insert_value)" > orasql $cur $insert_sql -parseonly > > ##############2.read file & do insert############## > #The content of ./testfile.txt is: > #1|testok > #1|primary key error > ################################################### > > set data_file "./testfile.txt" > set DELIMITER "|" > set varlist [split "var_0 var_1"] > set fd_in [open $data_file r] > > while {[gets $fd_in line_tmp] >= 0} { > set line_fields [split $line_tmp $DELIMITER] > if { [llength $line_fields] == 0 } { > continue > } > > set bind_field "" > set field_index 0 > foreach var_item $varlist { > set line_field [lindex $line_fields $field_index] > append bind_field " :$var_item [list $line_field]";#use list to > escape special characters > incr field_index > } > set cmd "orabindexec \$cur $bind_field" > > ##############3.bind & execute the sql############## > ##############The problem is : I can't get the oracle error code in > this catch!!! > ##############How can I get "ORA-00001: unique constrant > (open.TEST_PK) violated" ##############instead of "orabindexec: SQL > execution failed." ? > if { [catch "$cmd" res] } { > close $fd_in > oraclose $cur > oralogoff $logon > puts "-ERR {insert err.$res}" > puts "$::oramsg(rc)==$::oramsg(errortxt)==" > return > } > } > > close $fd_in > oraclose $cur > oralogoff $logon > ##############script end############## > > -Toto Hello Toto, I've emailed you a patch to the oratcl.c file that may do what you want. I am curious why you would use such an old version of Tcl and Oratcl. I would recommend Tcl 8.5 and Oratcl 4.4. Oratcl 4.4 is capable of connecting to Oracle 8i , 9i , 10g and 11g -Todd
From: frspace on 17 Dec 2008 20:50
On 12ÔÂ17ÈÕ, ÏÂÎç12ʱ14·Ö, "thelf...(a)gmail.com" <thelf...(a)gmail.com> wrote: > On Dec 16, 5:28 am, frsp...(a)hotmail.com wrote: > Hello Toto, > > I've emailed you a patch to the oratcl.c file that may do what you > want. I am curious why you would use such an old version of Tcl and > Oratcl. I would recommend Tcl 8.5 and Oratcl 4.4. > > Oratcl 4.4 is capable of connecting to Oracle 8i , 9i , 10g and 11g > > -Todd- Òþ²Ø±»ÒýÓÃÎÄ×Ö - > > - ÏÔʾÒýÓõÄÎÄ×Ö - Thanks Todd! I have recompiled Oratcl and it works! The old version of Tcl and Oratcl have been used in quite a lot of our script-products for years .And we worry about raising other big problems while updating Tcl and its tool libs. But you are right ,the latest version of Tcl and Oratcl would be better,so we'd better update them gradually. Here is the patch,it may be useful to someone else : [thelfter(a)dl320 generic]$ cvs diff -u oratcl.c Index: oratcl.c =================================================================== RCS file: /cvsroot/oratcl/oratcl/generic/oratcl.c,v retrieving revision 1.33.2.15 diff -u -r1.33.2.15 oratcl.c --- oratcl.c 3 Jul 2003 14:36:47 -0000 1.33.2.15 +++ oratcl.c 16 Dec 2008 14:07:45 -0000 @@ -2609,6 +2609,7 @@ (OCISnapshot *) NULL, oci_mode); +#if 0 Oratcl_Checkerr(interp, LogPtr->errhp, rc, 0); if (rc == OCI_ERROR || rc == OCI_INVALID_HANDLE) { @@ -2617,6 +2618,10 @@ ": SQL execution failed.", (Tcl_Obj *) NULL, (char *) NULL); +#endif + + Oratcl_Checkerr(interp, LogPtr->errhp, rc, 1); + if (rc == OCI_ERROR || rc == OCI_INVALID_HANDLE) { Oratcl_ColFree(StmPtr->col_list); StmPtr->col_list = NULL; Oratcl_ColFree(StmPtr->bind_list); -Toto |