Hi Peter, Yes, from Takusen you can call Oracle stored procedures, functions, packaged stored procedures or functions, or execute an arbitrary pl/sql block. In the Takusen software release there is a directory called "Database\Oracle\Test". There, Enumerator.lhs, among other code has these helpers you may want to use:
wrapPLSQLFunc funcname parms = let sqltext = "begin " ++ (head args) ++ " := " ++ funcname ++ "(" ++ placeholders ++ "); end;" placeholders = concat (intersperse "," (tail args)) args = take (length parms) (map (\n -> ":x" ++ show n) [1..]) in cmdbind sqltext parms
wrapPLSQLProc procname parms = let sqltext = "begin " ++ procname ++ "(" ++ placeholders ++ "); end;" placeholders = concat (intersperse "," args) args = take (length parms) (map (\n -> ":x" ++ show n) [1..]) in cmdbind sqltext parms
Please, be aware of the following points: 1) If the pl/sql code doesn't need parameters and has no results, you can use "execDDL". (execDML returns a counter of affected rows). 2) If the procedure/function receives parameter, you'll need to use "cmdbind" (or similar to "cmdbind") to pass the parameters. 3) If the pl/sql code returns values, you have this options: 3.a) The returned value is a reference (cursor): Takusen supports this very fine. Use "doQuery" or similar. 3.b) The return value is an scalar value: You can collect the result with an iteratee, even if it is a single value. 3.c) The return value is a complex oracle object: As of Takusen 0.8.5 there is no support for table of records of ... 3.d) The return value is Boolean. You'll get an error. Little examples: For case #1:
-- Example 1.a: We set nls_language to american english. set_NlsLang_Eng :: DBM mark Session () set_NlsLang_Eng = execDDL $ sql "alter session set nls_language='AMERICAN'"
-- Example #1.b: Now we set session language parameter to spanish. set_NlsLang_Esp :: DBM mark Session () set_NlsLang_Esp = execDDL $ sql "alter session set nls_language='LATIN AMERICAN SPANISH'"
For case #2:
-- Example 2.a: We use database string "concat" function concat' :: String -> String -> DBM mark Session String concat' a b = do let ite :: Monad m => String -> IterAct m String ite v _ = return $ Left v sqlcmd = wrapPLSQLFunc "concat" [bindP $ Out (""::String), bindP a, bindP b] doQuery sqlcmd ite undefined
-- later on the program, you'd have... some_string <- concat' "a" "b"
For case #3:
-- Case 3.b: We collect a single scalar value. qNlsLang :: DBM mark Session [String] qNlsLang = doQuery s ite [] where s = "select value from nls_session_parameters \ \ where parameter = 'NLS_LANGUAGE'" ite :: (Monad m) => String -> IterAct m [String] ite a acc = result' ( a:acc )
mostrar_NlsLang :: DBM mark Session () mostrar_NlsLang = qNlsLang >>= liftIO . print . head
-- Another example for Case 3.b -- This time we don't use a list to accumulate results. s1 = sql "select systimestamp from dual"
sysTSasCTQ :: DBM mark Session CalendarTime sysTSasCTQ = do
let ite :: (Monad m) => CalendarTime -> IterAct m CalendarTime ite x _ = result' x
t <- liftIO ( getClockTime >>= toCalendarTime) doQuery s1 ite t
-- Leonel Fonseca.
Hi Leonel
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.
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?
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.
Peter
On 9 September 2010 06:13, Leonel Fonseca
Hi Peter,
Yes, from Takusen you can call Oracle stored procedures, functions, packaged stored procedures or functions, or execute an arbitrary pl/sql block.
In the Takusen software release there is a directory called "Database\Oracle\Test". There, Enumerator.lhs, among other code has these helpers you may want to use:
wrapPLSQLFunc funcname parms = let sqltext = "begin " ++ (head args) ++ " := " ++ funcname ++ "(" ++ placeholders ++ "); end;" placeholders = concat (intersperse "," (tail args)) args = take (length parms) (map (\n -> ":x" ++ show n) [1..]) in cmdbind sqltext parms
wrapPLSQLProc procname parms = let sqltext = "begin " ++ procname ++ "(" ++ placeholders ++ "); end;" placeholders = concat (intersperse "," args) args = take (length parms) (map (\n -> ":x" ++ show n) [1..]) in cmdbind sqltext parms
Please, be aware of the following points:
1) If the pl/sql code doesn't need parameters and has no results, you can use "execDDL". (execDML returns a counter of affected rows). 2) If the procedure/function receives parameter, you'll need to use "cmdbind" (or similar to "cmdbind") to pass the parameters. 3) If the pl/sql code returns values, you have this options: 3.a) The returned value is a reference (cursor): Takusen supports this very fine. Use "doQuery" or similar. 3.b) The return value is an scalar value: You can collect the result with an iteratee, even if it is a single value. 3.c) The return value is a complex oracle object: As of Takusen 0.8.5 there is no support for table of records of ... 3.d) The return value is Boolean. You'll get an error.
Little examples:
For case #1:
-- Example 1.a: We set nls_language to american english. set_NlsLang_Eng :: DBM mark Session () set_NlsLang_Eng = execDDL $ sql "alter session set nls_language='AMERICAN'"
-- Example #1.b: Now we set session language parameter to spanish. set_NlsLang_Esp :: DBM mark Session () set_NlsLang_Esp = execDDL $ sql "alter session set nls_language='LATIN AMERICAN SPANISH'"
For case #2:
-- Example 2.a: We use database string "concat" function concat' :: String -> String -> DBM mark Session String concat' a b = do let ite :: Monad m => String -> IterAct m String ite v _ = return $ Left v sqlcmd = wrapPLSQLFunc "concat" [bindP $ Out (""::String), bindP a, bindP b] doQuery sqlcmd ite undefined
-- later on the program, you'd have... some_string <- concat' "a" "b"
For case #3:
-- Case 3.b: We collect a single scalar value. qNlsLang :: DBM mark Session [String] qNlsLang = doQuery s ite [] where s = "select value from nls_session_parameters \ \ where parameter = 'NLS_LANGUAGE'" ite :: (Monad m) => String -> IterAct m [String] ite a acc = result' ( a:acc )
mostrar_NlsLang :: DBM mark Session () mostrar_NlsLang = qNlsLang >>= liftIO . print . head
-- Another example for Case 3.b -- This time we don't use a list to accumulate results. s1 = sql "select systimestamp from dual"
sysTSasCTQ :: DBM mark Session CalendarTime sysTSasCTQ = do
let ite :: (Monad m) => CalendarTime -> IterAct m CalendarTime ite x _ = result' x
t <- liftIO ( getClockTime >>= toCalendarTime) doQuery s1 ite t
--
Leonel Fonseca. _______________________________________________ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Hi, Peter I did and uploaded some examples regarding Oracle and Takusen and the intended approach you would employ. Example 01: Create table and some tuples. http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29883#a29883 Example 02: Create a package with procedures to mantain the table. http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29884#a29884 Example 03: Optional. See how it works in Oracle sqlplus. http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29885#a29885 Example 04: The Takusen-Haskell program. It follows the scheme of example #03. http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29894#a29894 Some comments on the topic: This approach has limitations. Takusen (if not use in conjunction with Template Haskell) will be bound up to iteratees of eight values. I (and I'm pretty sure some other people) have successfully used Template Haskell to overcome this limitation. (I've been working in removing some boilerplate). The all stored procedure approach is not a limitation as you can see from example #04, unless these procedures return values these types: Record Of <something>, complex objects (PL/SQL tables, objects, etc), Boolean. Oracle has several programatic interfaces, some that I remember: JDBC, ODBC, OCI, OCCI and those for Windows .NET. Takusen is a wrapper for OCI (Oracle Call Interface) which seems to me pretty low level. Yet, I don't know if you can specify just the name of the procedure. Please note that if there is a procedure named close_accounts, by writing "begin close_accounts; end;" you are not wrapping it in a SQL statement. It actually is an anonymous PL/SQL block and it is needed in that form to be processed by the server. Oracle offers for Windows .NET: ODT (Oracle Developer Tools for Visual Studio), ODP (Oracle Data Provider) and ODE (Oracle Database Extensions). I did test (and taste) a bit of .NET tools with F#. It was nice. Maybe you wish to try hs-dotnet with these. A final remark, I have used Takusen both on Windows and Linux. The given example was developed and tested actually on Windows. Regards, Leonel Fonseca.
participants (2)
-
Leonel Fonseca -
Peter Marks