Database stored procedure parameters
Sven
I would like to run a stored procedure from MATLAB, and provide a java object (in this case a CLOB-equivalent) as one of the parameters. Consider the following "hello world" Oracle procedure:
CREATE OR REPLACE PROCEDURE testProc (inClob IN VARCHAR2, outChar OUT VARCHAR2) IS
BEGIN
outChar := 'Hello World!';
END testProc;
/
Now the following MATLAB command runs successfully:
>> out1 = runstoredprocedure(conn, 'testProc',{'''blah'''},{java.sql.Types.VARCHAR});
The following, however, fails:
>> javaStr = java.lang.String('blah');
>> out2 = runstoredprocedure(conn, 'testProc',{javaStr},{java.sql.Types.VARCHAR});
??? Error using ==> horzcat
The following error occurred converting from char to opaque:
Error using ==> horzcat
Undefined function or method 'opaque' for input arguments of type 'char'.
Error in ==> database.runstoredprocedure at 56
spcall = [spcall inarg{i} ',']; %#ok, not sure how long spcall will be
Can somebody help me to upload a java object directly? I understand that this the above case my javaStr actually gets converted to a MATLAB char so that placing quotes around it will get it to upload as a string, but I would eventually like to create a CLOB object and upload that directly. If I simply wrap my string in quotes, it is uploaded as a VARCHAR object, and the following error occurs:
??? Java exception occurred:
java.sql.SQLException: ORA-06550: line 1, column 16:
PLS-00172: string literal too long
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:744)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:217)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:968)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1189)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3369)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3453)
at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4421)
Error in ==> database.runstoredprocedure at 81
csmt.executeUpdate;
So in summary, my question is:
Can I provide a java object as one of the parameters to runstoredprocedure, and if so, how??
Thanks,
Sven.
Yair Altman
Re your general question about passing Java objects, I have never tried this but perhaps you can try passing them as a BLOB.
Yair Altman
http://UndocumentedMatlab.com
Sven
> Re this specific case, your SP expects a VARCHAR2, which Matlab converts into an expectation for a Matlab string (char) object. Matlab's DB toolbox is not smart enough to understand that your java.lang.String object can easily be converted to a char using the char() function, so you should help it by char'ing yourself.
OOPS! In various testing and boiling down my problem to newsgroup-ready code, I made a typo with the procedure definition.
It was supposed to be "(inClob IN CLOB, outChar OUT VARCHAR2)" rather than "(inClob IN VARCHAR2..."
My problem basically boils down to the fact that I don't actually *want* to pass my argument as a MATLAB char() or any other native MATLAB format. My choice of CLOB is due to the fact that the text I want to pass is very long (above the size limit for Oracle's VARCHAR2). The act of char'ing my java.lang.String object means that it gets passed as a VARCHAR2 to the stored procedure and I run over this limit. I will make another reply to this post that addresses this question directly with accompanying code.
> Re your general question about passing Java objects, I have never tried this but perhaps you can try passing them as a BLOB.
This is essentially what I'm trying to do. Can you give an example of MATLAB code that "passes arguments as a BLOB" (or in fact, *any* non MATLAB-native variable) to a stored procedure or an insert/update statement?
Thanks,
Sven.
Sven
Firstly, a simple oracle stored procedure that takes a CLOB as input:
------------------
CREATE OR REPLACE PROCEDURE testClob (inClob IN CLOB, clobSize OUT NUMBER) IS
BEGIN
SELECT length(inClob) into clobSize from dual;
END testClob;
/
--------------------
Next, to show that I can run this procedure from matlab passing a VARCHAR2-length string:
>> myout = runstoredprocedure(conn,'testClob',{['''' sprintf('%d',ones(1,32767)) '''']},{java.sql.Types.NUMERIC});
>> myout{1}
ans =
32767
As you can see, the length of the string is returned. Note that 32767 is the maximum length for a VARCHAR2 in oracle. Note however that the stored procedure takes in a CLOB, not a VARCHAR2. When calling directly from Oracle PL/SQL, I can send any size CLOB to this function without error. If I exceed this size with my input from MATLAB however, an error occurs:
>> myout = runstoredprocedure(conn,'testClob',{['''' sprintf('%d',ones(1,32768)) '''']},{java.sql.Types.NUMERIC})
??? Java exception occurred:
java.sql.SQLException: ORA-06550: line 1, column 16:
PLS-00172: string literal too long
Can anybody please show me how to get my test procedure to return when called from MATLAB with a return value greater than 32767?
As a step towards a solution, here is a simple stored procedure which BUILDS a CLOB:
---------------
CREATE OR REPLACE PROCEDURE buildClob (clobSize NUMBER, outClob OUT CLOB) IS
BEGIN
outClob := '';
FOR Lcntr IN 1..clobSize LOOP outClob := outClob || '1'; END LOOP;
END
/
-----------------
I can get a large CLOB *into* MATLAB quite simply as follows:
>> myoutCLOB = runstoredprocedure(conn,'buildClob',{50000},{java.sql.Types.CLOB})
myoutCLOB =
[1x1 oracle.sql.CLOB]
So now I have a CLOB ready to send to a stored procedure that accepts a CLOB. Much to my dismay, however, I simply can't find the syntax to call it. The following causes error:
>> myout = runstoredprocedure(conn,'testClob',myoutCLOB,{java.sql.Types.NUMERIC})
??? Error using ==> horzcat
The following error occurred converting from char to opaque:
Error using ==> horzcat
Undefined function or method 'opaque' for input arguments of type 'char'.
Error in ==> database.runstoredprocedure at 56
spcall = [spcall inarg{i} ',']; %#ok, not sure how long spcall will be
So... can anyone tell me whether what I'm trying to do is even possible? I've really been banging my head against a wall with this one and I'd appreciate any help you can give.
Thanks,
Sven.
Sven
The problem with runstoredprocedure is that it builds a string to include all input parameters rather than binding them (as it does for output parameters). So that a call from runstoredprocedure looks something like:
'{call testClob(''param1 input string'',?)}'
Rather than the more generic
'{call testClob(?,?)}'
Note that with the first format, ALL input parameters are forced into a string before being sent. With the second format, you can use the "set" methods of the callable statement to individually bind different parameters.
So, I wrote a function that mimics runstoredprocedure but with a bit more generic input. I didn't have time to work out how to get "Ordinal binding" working, so I had to get a further input parameter containing the specific names of the stored procedure parameters, and use "Named binding" instead. If any Mathworks people are following this thread and have input, it would be great to hear from you either in this thread or by email as I have some more questions and problems I would like to overcome.
Anyway, the following now works for a stored procedure:
-------------
CREATE OR REPLACE PROCEDURE UMPIRE.testClob (inClob IN CLOB, clobSize OUT NUMBER) IS
BEGIN
SELECT length(inClob) into clobSize from dual;
END testClob;
/
------------
Can now be successfully called from MATLAB with a large (CLOB-sized) string as follows:
>> x = runstoredprocedure_generic(c,'testClob',{repmat('1',1,50000)},{java.sql.Types.VARCHAR,java.sql.Types.NUMERIC},{'inClob','clobSize'})
>> x{1}
ans =
50000
So without further ado, here's the runstoredprocedure_generic function:
function x = runstoredprocedure_generic(c,spcall,inarg,typeinout,namesinout)
%RUNSTOREDPROCEDURE_GENERIC Stored procedures with input and output parameters.
% X = RUNSTOREDPROCEDURE_GENERIC(C,SPCALL,INARG,TYPEINOUT,NAMESINOUT) calls a stored
% procedure given input parameters and returns output parameters. C is the database
% connection handle, SPCALL is the stored procedure to be run, INARG is a
% cell array containing the stored procedure's input parameters. TYPEINOUT
% is a list of data types of the input/output parameters, in the order of the stored procedure
% definition. Likewise namesinout are the procedure parameter names corresponding to the typeinout
% list.
%
% For example, the call syntax may appear as
%
% x = runstoredprocedure(c,'myproc',{2500,'Jones'},{java.sql.Types.NUMERIC,java.sql.Types.VARCHAR,java.sql.Types.NUMERIC},{'param1','param2'})
%
% which means that the stored procedure myproc will be run given the
% input parameters 2500 and 'Jones', and the stored procedure expects numeric
% and string input respectively. It will return an output parameter
% of type java.sql.Types.NUMERIC which could be any numeric Java data
% type.
% NOTE: The valid input types are currently limited to:
% - java.sql.Types.NUMERIC (can be used to indicate INTEGER/NUMBER/BOOLEAN/ETC). It will be
% passed to the database via setDouble(), and the database will deal with typing from there
% - java.sql.Types.VARCHAR (can be used to indicate CHAR/VARCHAR2/CLOB/ETC). It will be passed
% to the database as toString()
% ALSO NOTE: This function has ONLY been tested with Oracle
%
% If the TYPEIN parameter is unused, this function will simply shadow MATLAB'S database toolbox
% RUNSTOREDPROCEDURE, passing ONLY the name of the spcall, and the INARG/TYPEOUT parameters if
% they are set.
%
% For stored procedures that return resultsets, use the methods
% DATABASE/EXEC and CURSOR/FETCH to process the return data.
%
% See also RUNSTOREDPROCEDURE, EXEC, FETCH.
% Modified by Sven Holcombe.
%Ensure all parameters are set
assert(nargin==5,'runstoredprocedure_generic expects all 5 arguments to be set')
assert(length(typeinout)==length(namesinout),'Number of input/output arguments and their names must be consistent')
%Build input/output pairs of types and names
numin = length(inarg);
numout = length(typeinout) - numin;
typein = typeinout(1:numin);
typeout = typeinout(numin+1:end);
namesin = namesinout(1:numin);
namesout = namesinout(numin+1:end);
%Build the full stored procedure call based on the number of arguments provided
numParams = numin + numout;
paramsStr = repmat('?,',1,numParams);
paramsStr = paramsStr(1:end-1);
spcallFullStr = sprintf('{call %s(%s)}',spcall,paramsStr);
%Get JDBC connection Handle
h = c.Handle;
%Create callable statement
csmt = h.prepareCall(spcallFullStr);
%Register input parameters
for i = 1:numin
switch(typein{i})
case 12
csmt.setString(namesin{i},inarg{i})
case 2
csmt.setDouble(namesin{i},inarg{i})
otherwise
error('runstoredprocedure_generic:badInputType','Current implementation can only handle java.sql.Types.VARCHAR/NUMERIC')
end
end
%Register output parameters
for i = 1:numout
csmt.registerOutParameter(namesout{i},typeout{i});
end
%Execute callable statement, method depends on output parameters
if ~isempty(typeout)
csmt.executeUpdate;
else
try
x = csmt.execute;
catch exception
error('database:runstoredprocedure:returnedResultSet',...
'Procedure may return resultset. Use EXEC and FETCH');
end
return
end
%Return output parameters as native data types
x = cell(numout,1);
for i = 1:numout
x{i} = csmt.getObject(namesout{i});
end
%Close callable statement
close(csmt)
Andreas Ronneberg
I currently have the same problem and need to pass large CLOBs to an Oracle db. I tried your function but my java complains about setString being able to hadle no more than 32766 characters. How exactly have you solved this problem?
Thanks in advance,
Andreas
Sven
Hmmm... so even the simple example fails? I assume you can run it on less than a clob-length string but it errors when you try a string over 32766 characters? What's the error message? You say that your java complains... when I was testing, it was Oracle that threw the first error when the input string was too large.
Anyway, for reference I'm running oracle 11g, although the JDBC lite client that I point MATLAB to is 10.0.2. Perhaps there's a difference in our setups there. To be honest, I haven't tested the above function on other setups, and tried only to get it to work for my specific case.
One thing to note here is that it actually *is* using setString(), which (for a CLOB input) I think is wrong (although it was working, so I didn't bother tinkering) as there are other set() methods available such as setClob(). If you look in the guts of the database/fastinsert.m file in the DB toolbox, you'll see a more generic implementation of variable binding which uses setObject() and a whole host of other set() calls depending on the input type. You might be able to bring in that code to the runstoredprocedure_generic function above and make it work. It would be interesting to first find out why my version works for the example above and not yours, but it's another option for you.
Cheers,
Sven.
Andreas Ronneberg
"Sven" <sven.h...@gmail.deleteme.com> wrote in message <hsrjcg$7sq$1...@fred.mathworks.com>...
> Hmmm... so even the simple example fails? I assume you can run it on less than a clob-length string but it errors when you try a string over 32766 characters? What's the error message? You say that your java complains... when I was testing, it was Oracle that threw the first error when the input string was too large.
For the record: the error message was
> java.sql.SQLException: setString can only process strings of less than 32766 chararacters
and your example worked with shorter strings. (I had to change my systems locale to get the english message and didn't want to post the localised one.)
> Anyway, for reference I'm running oracle 11g, although the JDBC lite client that I point MATLAB to is 10.0.2. Perhaps there's a difference in our setups there. To be honest, I haven't tested the above function on other setups, and tried only to get it to work for my specific case.
I'm running 10g (database 10.2.0.1, jdbc: 10.2.0.4), now there is a second test case. ;-)
> One thing to note here is that it actually *is* using setString(), which (for a CLOB input) I think is wrong (although it was working, so I didn't bother tinkering) as there are other set() methods available such as setClob(). If you look in the guts of the database/fastinsert.m file in the DB toolbox, you'll see a more generic implementation of variable binding which uses setObject() and a whole host of other set() calls depending on the input type. You might be able to bring in that code to the runstoredprocedure_generic function above and make it work. It would be interesting to first find out why my version works for the example above and not yours, but it's another option for you.
Thanks for the hint concerning .setString() and .setClob. After changing the select case to:
switch(typein{i})
case 2
csmt.setDouble(i,inarg{i})
case 2005
csmt.setClob(i,inarg{i})
otherwise
error('runstoredprocedure_generic:badInputType','Current implementation can only handle java.sql.Types.CLOB/NUMERIC')
end
I won't use it for varchars and didn't test if setString() would work with i instead of namesin{i}, thus i ommited the line. setClob() does not work with the parameter's name, only its index number.