1

I have a problem with calling a stored procedure from ABAP. I use standard ABAP class cl_sql_statement and it's methods execute_procedure and set_param. Called procedure, for example, has a single Boolean input parameter.

CREATE OR REPLACE PROCEDURE print_boolean (
  p_in_flag   BOOLEAN
) IS

ABAP snippet

DATA: ld_e_bool type char5,
      ld_o_stat type ref to cl_sql_statement,
      ld_r_data type ref to data.
***************************
ld_e_bool = 'FALSE'.
get references of ld_e_bool into ld_r_data. 
ld_o_stat->set_param(DATA_REF = ld_r_data).
ld_o_stat->execute_procedure( 'print_boolean' ).
********************************************

After the call I catch an exception which says something like: 'wrong number or types of arguments'. Maybe I need another type than char5... Any help would be appreciated.

Some observation: The JDBC drivers do not support the passing of BOOLEAN parameters to PL/SQL stored procedures( suggest wrapping the PL/SQL procedure with a second PL/SQL procedure). But i dont want to use the above option becuase there are already lot of packages/SP's are available.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
IvanRyazanov
  • 23
  • 7
  • will the boolean value that youa re passing be the same every time? if so you could give it a default value which may or may not get around the error you are getting. – Jared Jul 24 '15 at 13:47
  • check this SCN post out, it may help https://scn.sap.com/thread/517601 – Jared Jul 24 '15 at 13:53
  • Unfortunately I can't use default value. That about your reference, i just viewed in direction of native sql statement, but i didn't find any way for to use execute procedure statement for my case. In sap doc say: "For actual parameters, you can use literals or host variables identified by a colon (:);" In case of literal There isn't for TRUE or FALSE special equivalents. – IvanRyazanov Jul 27 '15 at 06:10

2 Answers2

0

The Oracle documentation is unclear on how PL/SQL boolean values are actually represented. There is also a question that discusses the use of boolean types in Oracle database fields (not really relevant here, but provides some background).

From the PL/SQL documentation:

The BOOLEAN datatype takes no parameters. Only the values TRUE, FALSE, and NULL can be assigned to a BOOLEAN variable.

You cannot insert the values TRUE and FALSE into a database column. You cannot select or fetch column values into a BOOLEAN variable. Functions called from a SQL query cannot take any BOOLEAN parameters. Neither can built-in SQL functions such as TO_CHAR; to represent BOOLEAN values in output, you must use IF-THEN or CASE constructs to translate BOOLEAN values into some other type, such as 0 or 1, 'Y' or 'N', 'true' or 'false', and so on.

In light of that, and I know you don't want to, you may need to change the type of parameter you pass into the stored procedure (e.g., use a single character or integer) and then use logic to treat that as a boolean.

Community
  • 1
  • 1
mjturner
  • 1,035
  • 7
  • 16
  • Thx for your answer and I i apologize for some inaccuracy which exist in my post. I can't revise this procedure, because this is not only my unwillingness and point in license agreement. – IvanRyazanov Jul 27 '15 at 06:40
0

In SAP doc say:

Almost all SQL statements that are valid for the addressed database system can be included between EXEC and ENDEXEC

Maybe if i try put in this section native pl/sql code i get result...

Edit: I put this code snip and it's ok.

EXEC SQL.
   BEGIN
     print_boolean(TRUE); 
   END;
ENDEXEC.

But there is one problem. This sql statement have static form only.

IvanRyazanov
  • 23
  • 7