Oratcl Examples

Oratcl Examples demonstrates by example the usage of Oratcl.

Description

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 $ldh "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 $sth -datavariable row

    if {[oramsg $sth rc] != 0} break

    .b2 configure [lindex $row 0] [lindex $row 1]
}

The orafetch reads exactly one row from the result set in the variable 'row' (but 10 rows are pre-fetched by default - see oraconfig to change this behaviour). 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.

another way:

orasql $sth {SELECT KEY, VALUE FROM PROPERTY}

while {![orafetch $sth -datavariable row]} {
    .b2 configure [lindex $row 0] [lindex $row 1]
}

The orafetch command allows to bind variable to readable column names through an array (perfomances?):

orasql $sth {SELECT KEY, VALUE FROM PROPERTY}

while {![orafetch $sth -dataarray row -indexbyname]} {
    .b2 configure $row(KEY) $row(VALUE)
}

Binding

# code goes here

PL/SQL

This is a hot topic not very well documented everywhere... Here is just the simple example that everyone is looking for one day!

set plsql {BEGIN ORACLE_USER.ORACLE_PACKAGE.ORACLE_PROCEDURE(:in_out_Cursor, :user_id); END;}
set fetch_sth [oraopen $lda]

if {[catch {
  oraplexec $sth $plsql :user_id 121 :in_out_Cursor $fetch_sth
} msg]} {
  puts stderr "ERROR: $msg"
  puts stderr [oramsg $sth err]
  exit
}

while {![orafetch $fetch_sth -datavariable row]} {
    puts [list [lindex $row 0] [lindex $row 1]]
}

Note that we need a second statement handler to fetch the cursor opened into the Oracle procedure. The PL/SQL example:

CREATE OR REPLACE PACKAGE ORACLE_PACKAGE
AS
TYPE genCurType IS REF CURSOR;

PROCEDURE ORACLE_PROCEDURE (in_out_Cursor  OUT genCurType,
                        user_id      IN  TABLE_NAME.FIELD_NAME%TYPE);

END  ORACLE_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY ORACLE_PACKAGE

AS
PROCEDURE ORACLE_PROCEDURE (in_out_Cursor  OUT genCurType,
                        user_id      IN  TABLE_NAME.FIELD_NAME%TYPE);

IS
BEGIN
OPEN in_out_Cursor FOR
  SELECT  *
    FROM TABLE_NAME
    WHERE FIELD_NAME = user_id;

END ORACLE_PROCEDURE;

END ORACLE_PACKAGE;
/

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 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

Oracle stored-procedures cursors

set exec_cur [oraopen $lda]
set fetch_cur [oraopen $lda]

set plsql {begin
    open :fetchcur for select loginname, hostname
    from ac_account where loginname = :log;
    end;
}
oraplexec $exec_cur $plsql :log tmh :fetchcur $fetch_cur
orafetch $fetch_cur {puts $login:$host} {} login 1 host 2

RLH: I wonder if someone could post pulling data out of a DB and using TCOM to build an Excel spreadsheet with it? I currently do this with Perl but would like to see a Tcl version.

LV: What I'd love to find is an introspective database browser. What I'm dreaming of is something that would show me the tables to which I have access, then, when I click on the table, it would provide me a view of the data in that table, with some way to get the equivalent of the describe if I click on a button or whatever.

I'm not asking, yet, for a way to change the data - just to browse, it, and perhaps, wistfully, wishing for a way to "order by one of the columns"... and to have the ability to widen columns, and maybe even hide columns in which I'm not too interested.

And maybe a way to specify a filter so that I only see rows which meet a particular set of criteria...

thelfter - 2017-09-24 01:45:48

RLH Wrote: I wonder if someone could post pulling data out of a DB and using TCOM to build an Excel spreadsheet with it? I currently do this with Perl but would like to see a Tcl version.

TMH : Would a CSV format be sufficient?

I use this all the time.

package require Oratcl package require csv set user SCOTT set pass TIGER set dbname ORCL set lda oralogon ${user}/${pass}@${dbname} set stm oraopen $lda

# specify each table you want to generate a CSV file for. set tblList list USER_TABLES

proc csvjoin {values {sepChar ,} {delChar \"}} {

    set out ""
    set sep {}
    foreach val $values {
        append out $sep${delChar}[string map [list $delChar ${delChar}${delChar}] $val]${d                                                                                                                         elChar}
        set sep $sepChar
    }
    return $out

}

foreach tbl $tblList {

        puts $tbl
        set t [open ${tbl}.csv w+]
        oraparse $stm "select * from ${tbl} order by num_rows desc"
        puts $t [csvjoin [oracols $stm name]]
        oraexec $stm
        while {[orafetch $stm -datavariable row] == 0} {
                puts $t [csvjoin $row]
        }
        close $t

}

Category Example

Oratcl

SQL

Oratcl Examples

See Also

Description