Purpose: '''Accumulate simple examples demonstrating the use of the [[ [Oratcl] ]] database library''' ---- * [ora2txt] performs a select and outputs the results as tab delimited text from a command-line * [Oratcl Logon Dialog] is a program extract showing a logon routine ---- The following examples are using a simple option table called PROPERTY. The table contains only two columns: KEY and VALUE, each of them are VARCHAR2 types. The variables 'lda' and 'sth' contain the connection to the database and the statement handle for executing queries, inserts etc. '''A simple logon routine''' package require Oratcl set lda [oralogon user/pass@dbname -async] set sth [oraopen $lda] if {[oramsg $sth rc] == 0} { puts "Successfully connected" } else { puts "Unable to connect to the database." } oralogoff $lda '''Inserting into a database''' Not that big problem. Let's assume we have a button called .b and want to save all of its options in our database table. We use foreach and configure to get 'em and put them in the PROPERTY table using simple insert statements. foreach opt [.b configure] { orasql $sth "INSERT INTO PROPERTY KEY, VALUE VALUES ('[lindex $opt 0]', '[lindex $opt 4]')" } oracommit $lda As we know that both key and value are VARCHAR2 types, we use single quotes in the value section. This can be avoided using bindings (explained later). Note also that no semicolon is used at the end of the SQL command. This is different to using SQLPlus, for example, where a semicolon terminates the command and forces the SQL code to be executed. Using Oratcl a simple parameter is assumed and parsing rules are only applied by the Tcl interpreter. Don't forget to commit the data, otherwise nobody else would be able to read the contents in the table and you will lose your work after ending your session. '''Deleting from a database''' It wouldn't make sense to save all of the options, e.g. -textvariable will be set by the application itself when it creates the button. Let's delete it. orasql $lda "DELETE FROM PROPERTY WHERE KEY = '-textvariable'" oracommit $lda '''Changing the values''' We want to change some of the options of our button directly in the database. No problem using an update statement. orasql $sth "UPDATE PROPERTY SET VALUE = 'red' WHERE KEY = '-foreground'" oracommit $lda '''Retrieving data from a database''' Now the interesting part. We try to reload our settings from the database into another button '.b2'. orasql $sth "SELECT KEY, VALUE FROM PROPERTY" while {1} { orafetch $stmt -datavariable row if {[oramsg $stmt rc] != 0} break .b2 configure [lindex $row 0] [lindex $row 1] } The orafetch reads exactly ten rows by default (see ''oraconfig'' to change this behaviour) from the result set in the variable 'row'. This variable contains a list of column contents in the order specified by the SELECT statement. If no more rows are available we will get an ORACLE error (return code = 1403 - No more data) and we break the statement. ''or'' orasql $sth "SELECT KEY, VALUE FROM PROPERTY" while { ![orafetch $stmt -datavariable row] } { .b2 configure [lindex $row 0] [lindex $row 1] } '''Binding''' # code goes here '''PL/SQL''' # code goes here '''Cleanup''' "Be tidy" my mother said. So do I and I gracefully close the connection to the database after work. oraclose $sth oralogoff $lda '''Pitfalls''' Oh gosh - how often did this thing come up - regional settings. I work in Austria, so my PC is setup with a german version of ORACLE. "No problems" I thought - until I selected some rows from a table and tried to insert it into another one afterwards. All double values were dumped using a comma instead of a decimal point. BUT a comma is also the field separator in SQL so ORACLE got confused when trying to use the information in an insert statement. So I recommend changing the language settings after oralogon. This can be done using a simple SQL statement: orasql $sth {alter session set NLS_NUMERIC_CHARACTERS =". "} orasql $sth {alter session set NLS_DATE_FORMAT ="YYYY.MM.DD HH24:MI:SS"} The second statement also alters the default date format which is also very convenient for me. I put these two statements just after my logon routine to prevent confusion later. If you have a RAC with TAF you will lose these settings after a failover to another RAC node. Since oratcl43 you can register a TAF callback procedure that is automaticly called, everytime you are reconnected to another RAC node. proc tafCallback {lda fo_type fo_event} { # lda: the oratcl logon handle of this connect # fo_type: 1 none; 2 session; 4 select # fo_event: 1 end; 2 abort; 4 reauth; 8 begin; 10 error if {$fo_event == 1} { set sth [oraopen $lda] orasql $sth {alter session set NLS_NUMERIC_CHARACTERS =". "} orasql $sth {alter session set NLS_DATE_FORMAT ="YYYY.MM.DD HH24:MI:SS"} oraclose $sth } } # connect with TAF callback set lda [oralogon user/pass@dbname -failovercallback tafCallback] # execute it once manually to set up the session after connect tafCallback $lda 4 1 '''Anything else you want to see regarding Oratcl...''' [RLH] - Besides the above, I would like to see some stuff about pulling data out and displaying it via [CGI]. [LV] I seem to recall issues regarding [Unicode] and Oratcl/Oracle; but I cannot recall the details. Anyone aware of issues either getting unicode into and out of Oracle, or perhaps having to do with stuff from Tcl being Unicode when the field isn't expecting it, or something? Sorry to be so vague... ---- ''[Todd Helfter] wrote on comp.lang.tcl:'' Here are two examples. package require Oratcl # should see '4.3' # open logon handle set lda [oralogon tmh/password@db] # should see 'oratcl0' # open statement handle % set cur [oraopen $lda] # should see 'oratcl0.0' # parse sql statement oraparse $cur {select table_name from user_tables} # should see '0' :: 0 == OCI_SUCCESS # execute the statement handle oraexec $cur # should see '0' # fetch the results (loop until return code != OCI_SUCCESS) while {[orafetch $cur -datavariable row] == 0} { puts $row } # I got back table name 'STACKS' # describe a table oradesc $lda stacks # I got back {SID 22 NUMBER 0 -127 1} {EBE 3 CHAR 0 0 1} {PART 20 CHAR 0 0 1} # example with a bind. Only return rows with sid = 6 oraparse $cur {select * from stacks where sid = :sid} orabind $cur :sid 6 oraexec $cur oracols $cur # I got back SID EBE PART oracols $cur all # I got back {SID 22 NUMBER 0 -127 1} {EBE 3 CHAR {} {} 1} {PART 20 CHAR {} {} 1} while {[orafetch $cur -datavariable row] == 0} { puts $row } # 1st row: 6 TMH {} # 2nd row: 6 TMH {} oraclose $cur oralogoff $lda ---- '''DSNless connections''' [RLH]: Per a posting on '''c.l.tcl''': # you need to pass in the whole DSN string if you want to bypass the tsnames.ora file # set dsn "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test)))" oralogon scott/tiger@$dsn ---- [[[Category Example]|[Category Database]]]