Hi Leonel<div><br></div><div>Thanks for your response. I don't know much about Oracle, but it has been suggested that this approach of calling a stored procedure via SQL won't work on a database that has security locked down to ensure all database access is via stored procedures. All our production databases are locked down in this way.</div>
<div><br></div><div>I'm told there is a different API call to call a stored procedure directly rather than compile a SQL statement that calls the procedure. I'm guessing, from your suggestion below, that Takusen does not expose this call?</div>
<div><br></div><div>I've taken an alternative route. We have our own Haskell to COM bridge (that we hope to release at some point) and I'm using that to talk to ADO, Microsoft's database API - yes, we are constrained to Windows for this. Initial signs are positive, but I haven't finished it yet.</div>
<div><br></div><div><br></div><div>Peter<br><br><div class="gmail_quote">On 9 September 2010 06:13, Leonel Fonseca <span dir="ltr"><<a href="mailto:leonelfl@gmail.com">leonelfl@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Hi Peter,<br>
<br>
Yes, from Takusen you can call Oracle stored procedures, functions,<br>
packaged stored procedures or functions, or execute an arbitrary<br>
pl/sql block.<br>
<br>
In the Takusen software release there is a directory called<br>
"Database\Oracle\Test". There, Enumerator.lhs, among other code has<br>
these helpers you may want to use:<br>
<br>
<br>
>wrapPLSQLFunc funcname parms =<br>
> let sqltext = "begin " ++ (head args) ++ " := " ++ funcname<br>
> ++ "(" ++ placeholders ++ "); end;"<br>
> placeholders = concat (intersperse "," (tail args))<br>
> args = take (length parms) (map (\n -> ":x" ++ show n) [1..])<br>
> in cmdbind sqltext parms<br>
<br>
>wrapPLSQLProc procname parms =<br>
> let sqltext = "begin " ++ procname<br>
> ++ "(" ++ placeholders ++ "); end;"<br>
> placeholders = concat (intersperse "," args)<br>
> args = take (length parms) (map (\n -> ":x" ++ show n) [1..])<br>
> in cmdbind sqltext parms<br>
<br>
<br>
Please, be aware of the following points:<br>
<br>
1) If the pl/sql code doesn't need parameters and has no results, you<br>
can use "execDDL". (execDML returns a counter of affected rows).<br>
2) If the procedure/function receives parameter, you'll need to use<br>
"cmdbind" (or similar to "cmdbind") to pass the parameters.<br>
3) If the pl/sql code returns values, you have this options:<br>
3.a) The returned value is a reference (cursor): Takusen supports<br>
this very fine. Use "doQuery" or similar.<br>
3.b) The return value is an scalar value: You can collect the<br>
result with an iteratee, even if it is a single value.<br>
3.c) The return value is a complex oracle object: As of Takusen<br>
0.8.5 there is no support for table of records of ...<br>
3.d) The return value is Boolean. You'll get an error.<br>
<br>
Little examples:<br>
<br>
For case #1:<br>
<br>
> -- Example 1.a: We set nls_language to american english.<br>
> set_NlsLang_Eng :: DBM mark Session ()<br>
> set_NlsLang_Eng = execDDL $ sql<br>
> "alter session set nls_language='AMERICAN'"<br>
<br>
> -- Example #1.b: Now we set session language parameter to spanish.<br>
> set_NlsLang_Esp :: DBM mark Session ()<br>
> set_NlsLang_Esp = execDDL $ sql<br>
> "alter session set nls_language='LATIN AMERICAN SPANISH'"<br>
<br>
For case #2:<br>
<br>
> -- Example 2.a: We use database string "concat" function<br>
>concat' :: String -> String -> DBM mark Session String<br>
>concat' a b = do<br>
> let ite :: Monad m => String -> IterAct m String<br>
> ite v _ = return $ Left v<br>
> sqlcmd = wrapPLSQLFunc "concat"<br>
> [bindP $ Out (""::String), bindP a, bindP b]<br>
> doQuery sqlcmd ite undefined<br>
><br>
> -- later on the program, you'd have...<br>
> some_string <- concat' "a" "b"<br>
<br>
For case #3:<br>
<br>
> -- Case 3.b: We collect a single scalar value.<br>
> qNlsLang :: DBM mark Session [String]<br>
> qNlsLang = doQuery s ite []<br>
> where<br>
> s = "select value from nls_session_parameters \<br>
> \ where parameter = 'NLS_LANGUAGE'"<br>
> ite :: (Monad m) => String -> IterAct m [String]<br>
> ite a acc = result' ( a:acc )<br>
<br>
> mostrar_NlsLang :: DBM mark Session ()<br>
> mostrar_NlsLang = qNlsLang >>= liftIO . print . head<br>
<br>
> -- Another example for Case 3.b<br>
> -- This time we don't use a list to accumulate results.<br>
> s1 = sql "select systimestamp from dual"<br>
><br>
> sysTSasCTQ :: DBM mark Session CalendarTime<br>
> sysTSasCTQ = do<br>
><br>
> let ite :: (Monad m) => CalendarTime -> IterAct m CalendarTime<br>
> ite x _ = result' x<br>
><br>
> t <- liftIO ( getClockTime >>= toCalendarTime)<br>
> doQuery s1 ite t<br>
<font color="#888888"><br>
<br>
--<br>
<br>
Leonel Fonseca.<br>
_______________________________________________<br>
Haskell-Cafe mailing list<br>
<a href="mailto:Haskell-Cafe@haskell.org">Haskell-Cafe@haskell.org</a><br>
<a href="http://www.haskell.org/mailman/listinfo/haskell-cafe" target="_blank">http://www.haskell.org/mailman/listinfo/haskell-cafe</a><br>
</font></blockquote></div><br></div>