Re: I need help on: Stored procedure calls from a C++ program (using Rogue Wave DBTools.h++)

Jordi G-Ramallo wrote:
>
> Hi,
>
> I'm developing an Oracle Cartridge using Rogue Wave Tools.h++(v6.1) and
> DBTools.h++(v1.1).
> I execute stored procedures from the database that returns
> an string (maybe the result of a query or other stuff).
>
> Here is the code used to call them:
> ----------------------------------------------------------------------
> ...
> RWDBStoredProc dbFn = my_database_var.storedProc(my_fn_name);
> RWCString ret_val_str;
>
> if (!dbFn.isValid()) {
> ret_val_str = "??????????";
> }
> else {
> //dbFn << &ret_val_str;
> dbFn.execute(my_connection_var);
> //dbFn.fetchReturnParams();
> RWDBValue value = dbFn.returnValue();
> ret_val_str = value.asString();
> }
> ...
> ----------------------------------------------------------------------
>
> And here is an example of one of such procedures:
>
> ----------------------------------------------------------------------
> FUNCTION userTypeSelectOptions RETURN VARCHAR2 IS
> CURSOR userTypeC IS
> SELECT userTypeID, userType FROM userType;
> stro VARCHAR2(1000) := ''; -- Line 81
> BEGIN
> FOR u_type IN userTypeC LOOP
> stro := stro ||
> '<li value="' ||
> u_type.userTypeID ||
> '">' ||
> u_type.userType;
> END LOOP;
>
> RETURN stro;
> END userTypeSelectOptions;
> ----------------------------------------------------------------------
>
> The problem is that when I call the stored procedure from my cartridge
> using RogueWave DBTools API, I get an error from Oracle
> (reported by a DBTools error handler that I've installed).
>
> The error message reported is:
> Message: [SERVERERROR] Error from Server: ORA-06502: PL/SQL: numeric or
> value error ORA-06512: at "ADMCV.ACVHTMLTEMPLATE", line 81
> (ADMCV.ACVHTMLTEMPLATE is the name of the package and the line number
> is marked in the code above)
>
> The same is true if I try it with a procedure with an in/out parameter
> (using the proper calling method).
>

This sounds like the same problem that we had with powerbuilder 5. Before calling the proc, we had to initialize the out variables with at least as many spaces as the the length of the return string from the procedure.

-- 
Thomas Griffin
Project Leader
QUALITECH Systems, Inc.
tgriffin_at_qualitech.com