Jdbc stored Procedure

, ,
1

hi ,
I am using Jdbc adapter services to call stored procedure ,i created stored procedure adapter service to call stored procedure in SQL server.
this is my first experience using jdbc service,
my stored procedure is taking one parameter as input
so i have defined one variable in
Parameter tab
>Param Jdbc type :Param name :param type: expression
and that stored procedure is returning me 10 values so for that i defined
call tab
Jdbc type & Return Field name
(i can define only one field i cant define 10 fields )

my question is if my adapter service is returning 10 fields
where i need to define those 10 fields.

any help will be highly appreciated
Thanks

2

Kitt,

you can define any number of out values depends on the SP returns OUT.

So under the parameter tab you define those 10 fields and set
Param Type to OUT,so this will automatically adds the OutputName and OutputType and ultimately you will see these fields in the Input/Output tab of the SP AdapterService.

HTH,
RMG

3

thanks RMG for the quick reply
I gave the 1 input value(IN) and 2 output value (OUT) (just to test)
and i can see 1 input and 2 output in input/output tab.
but when i run the service i can see the error ::

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service XXX.XXX.XXX:SimstoredProc.
[ADA.1.316] Cannot execute the SQL statement “{call
XXX.XX.XXXXXXXX(?, ?, ?) }”. "
(HY000/8144) [wm-cjdbc33-0009][SQLServer JDBC Driver][SQLServer]Procedure or function XXXXXXXXXXX has too many arguments specified."
[wm-cjdbc33-0009][SQLServer JDBC Driver][SQLServer]Procedure or function XXXXXXXXX has too many arguments specified.

thanks

4

Kitt,

Did you tested this SP call using any client TOAD or SQL plus…etc…if SP is returning different out values then the SP AdapterService should definetely work.

Since the error indicates too many arguments,test the SP using client and later debug the AS.

HTH,
RMG

5

RMG,
I tryed to run that procedure through SQL analyser
and i could not run that procedure I got the error message that

CREATE PROCEDURE permission denied in database ‘DatabaseSis’.

I hope that user dosen’t have execute permission to run that stored procedure.or it might be some other resons.I need to check with DB guys .

Thanks for the help RMG

6

Here is another thing to look for. I caused me problems. This information came from [url=“http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#34_05”]http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#34_05[/url]

Do Oracle’s JDBC drivers support PL/SQL tables/result sets/records/booleans?
No. It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL types TABLE (now known as indexed-by tables), RESULT SET, RECORD, or BOOLEAN. There are currently no plans to change this. Instead people are encouraged to use RefCursor, Oracle Collections and Structured Object Types.

As a workaround, you can create wrapper procedures that handle the data as types supported by JDBC.

For example, to wrap a stored procedure that uses PL/SQL booleans, you can create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN, or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, you can create a stored procedure that handles a record in its individual components (such as CHAR and NUMBER). To wrap a stored procedure that uses PL/SQL tables, you can break the data into components or perhaps use Oracle collection types.

Here is an example of a PL/SQL wrapper procedure MY_PROC for a stored procedure PROC that takes a BOOLEAN as input:

PROCEDURE MY_PROC (n NUMBER) IS
BEGIN
IF n=0
THEN proc(false);
ELSE proc(true);
END IF;
END;

PROCEDURE PROC (b BOOLEAN) IS
BEGIN
…
END;