RE: [fme] Calling an Oracle Stored procedure

[Richard Wilkinson]

Title: Message

Peter, Thanks for your reply.  I am actively looking at tcl as an alternative but still having trouble with the Stored Procedure.  I have a script that works for excuting SQL commands (at least some commands) but fails when calling a strored procedure with the (unhelpful) error   MyTest Error: orasql: SQL execution failed.   The same call from SQL/Plus logged on with same user/password is ok:   SQL> SQL> SQL> exec chi_two.mytest;   PL/SQL procedure successfully completed.   SQL>     I would be pleased to receive comments on what I must be missing   Thanks   Richard Wilkinson Systems Analyst Resources - ICT Services Leicestershire County Council 0116 2657709 [EMAIL PROTECTED]         My TCL script is:   # for error handling set program "MyTest Error"   # ========================================================================= # # Test_FME_END.tcl # # This script records the execution of a translation using # the Oratcl package. This example includes the creation of the # XLATION_RESULTS table for storing the translation results. # One row for each translation run is inserted into the table. package require Oratcl # ========================================================================= # Login to the Oracle service set username chi_two set password gumb0 set service lgis set loginHandle [oralogon $username/[EMAIL PROTECTED] # ========================================================================= # Determine if the xlation_results table we wish to record results to exists set tableExists no set statementHandle [oraopen $loginHandle] orasql $statementHandle "select * from user_tables where table_name = 'TEMP4XL'" while {[orafetch $statementHandle -datavariable row] == 0} { set tableExists yes } #=====================THIS FAILS=========================================== set sql "EXEC CHI_TWO.MYTEST" if [catch {orasql $statementHandle $sql} ora_error] {    puts stderr "$program: $ora_error" }   #=====================THIS WORKS=========================================== orasql $statementHandle "TRUNCATE TABLE TEMP4XL" -commit orasql $statementHandle "INSERT INTO TEMP4XL        Select id_key, Locality from parkland_agg_new" -commit # ========================================================================= # Shut down the statement handle oraclose $statementHandle # ========================================================================= # Logout of our oracle service oralogoff $loginHandle       The called procedure, chi_two.mytest, has the single line of code for testing:   insert into test (id) values (999);               -----Original Message----- From: fme@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of peter laulund Sent: 13 November 2006 21:20 To: fme@yahoogroups.com Subject: RE: [fme] Calling an Oracle Stored procedure Hi Richard If the Begin_sql directive is not working, you could take a look at the oratcl extension to tcl (http://wiki.tcl.tk/204) it allowes you to access oracel and do all kind off stuff behind the 'back' off fme I use for logging the result off a translation, deleting tabels after a translation, getting values I then put into my mapping files as macroe's etc. I also think there is an exampel on the fmepedia under FME_END_TCL Peter >From: "Richard Wilkinson" <[EMAIL PROTECTED]gov.uk> >Reply-To: [EMAIL PROTECTED]com >To: <[EMAIL PROTECTED]com> >Subject: [fme] Calling an Oracle Stored procedure >Date: Mon, 13 Nov 2006 15:42:20 -0000 > >Hi, > >A similar question to this was posted last year but no solutions were >posted. > >I want to call an Oracle Stored procedure from my transform that will >execute before the transform starts. I have tried including the >statement [exec Proj.MyTest] in the SQL Statement to execute before but >I get no errors/warnings and the statement does not seem to work. > >Can anyone confirm that this should work and what the correct syntax is. >Can this be achieved another way? > >Thanks > >Richard Wilkinson >Systems Analyst >Resources - ICT Services >Leicestershire County Council >0116 2657709 >[EMAIL PROTECTED]gov.uk > > >__________________________________________________________ >Leicestershire County Council - rated a 'four-star' council by the Audit >Commission >__________________________________________________________ > > >This e-mail and any files transmitted with it are confidential. If you are >not the intended recipient, any reading, printing, storage, disclosure, >copying or any other action taken in respect of this e-mail is prohibited >and may be unlawful. If you are not the intended recipient, please notify >the sender immediately by using the reply function and then permanently >delete what you have received. > >Incoming and outgoing e-mail messages are routinely monitored for >compliance with Leicestershire County Council's policy on the use of >electronic communications. The contents of e-mails may have to be >disclosed to a request under the Data Protection Act 1998 and the Freedom >of Information Act 2000. > >The views expressed by the author may not necessarily reflect the views or >policies of the Leicestershire County Council. > >Attachments to e-mail messages may contain viruses that may damage your >system. Whilst Leicestershire County Council has taken every reasonable >precaution to minimise this risk, we cannot accept any liability for any >damage which you sustain as a result of these factors. You are advised to >carry out your own virus checks before opening any attachment. > > __________________________________________________________ Få de bedste søgeresultater med MSN Search: http://search.msn.dk __._,_.___ For insights into what's up at Safe Software and what's on the development horizon, visit Safe's blog at spatial-etl.blogspot.com. Safe Software has also made slides available that outline enhancements planned for FME 2007. The slides are from the "Road Ahead" presentation given on Day 2 of the FME Worldwide Users Conference. To view these slides, visit www.safe.com/2006uc. Your email settings: Individual Email|Traditional Change settings via the Web (Yahoo! ID required) Change settings via email: Switch delivery to Daily Digest | Switch to Fully Featured Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe __,_._,___