Open RajendraPingte opened 7 years ago
Before we can tackle this in DBI we need a survey of stored procedure APIs across the major databases (mysql, postgres, sqlite, oracle, sql server).
I suspect most SQL databases offer an SQL way to execute a stored procedure, so that it can be called via dbExecute()
.
DBI specification had dbCallProc procedure but has been deprecated. ROracle added its own custom method to call PLSQL stored procedures in version 1.3-1. One can provide input, output or input and output arguments to stored procedure. There is not way to specify it in DBI. There is no dbBind functionality to specify that the value should be bound as a BLOB or some other data type in the DB. We have added oracle-specific attributes like the following: name <- attr(obj, "ora.parameter_name") # Used to bind using name instead of position mode <- attr(obj, "ora.parameter_mode") # this where one specifies IN/OUT or INOUT arguments typ <- attr(col, "ora.type") # data types(blob/bfile/date/raw/char/cursor...) enc <- attr(col, "ora.encoding") # UTF8 or something encoded data in mxl <- attr(col, "ora.maxlength") # Maximum length of IN/OUT arguemnst or bind variable
I am attaching a document describing PLSQL procedures and its usage in Oracle: Parameter_to_SP.pdf
It would be good to add dbBind and/or dbCallProc.
I had discussed this with Hadley Wickham and he suggested that I implement oracleProc for now.