HELP:Oracle Stored Procedures using Tcl

Hi,

I'm trying to use Tcl to run some stored procedures on an Oracle database. The code is shown below:

   package require odbc
   odbc sqlallochandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv    odbc sqlsetenvattr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0    odbc sqlallochandle SQL_HANDLE_DBC $henv hdbc    odbc sqlconnect $hdbc RisFilters SQL_NTS "" SQL_NTS "" SQL_NTS    odbc sqlallochandle SQL_HANDLE_STMT $hdbc hstmt   

   odbc SQLBindParameter $hstmt 1 SQL_PARAM_INPUT SQL_C_CHAR SQL_C_CHAR 0 10 chi 10 NULL

   odbc SQLBindParameter $hstmt 2 SQL_PARAM_OUTPUT SQL_C_CHAR SQL_C_CHAR 0 500 res1 500 NULL

   set chi 1234567890

   set SQLComLine "{call get_by_sf(?,?)}"

   if {[odbc SQLExecDirect $hstmt $SQLComLine SQL_NTS] == "SQL_SUCCESS"} {
      if {[odbc SQLFetch $hstmt] == "SQL_NO_DATA_FOUND"} {echo $res1}    } else {
   echo "SQL PROCEDURE CALL FAILED - $SQLComLine"    }

#-

# Tidy up : Release all the handles and disconnect from the database
#-

   odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt    odbc SQLDisconnect $hdbc
   odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc    odbc SQLFreeHandle SQL_HANDLE_ENV $henv

When I run this it connects to the database but then reports a SQL_ERROR when running the Execute command.

The chi is a 10 digit number and the return value is a string. I read somewhere that I had to use a Fetch before anything appeared in the return value.

Can anybody offer any assistance?

Thanks

Garry Received on Wed Jun 04 2003 - 14:40:48 CEST