• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Callable Statement

 
Danish Araquei
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All

i have to set boolean value in my procedure.the third parameter of Procedure is of BOOLEAN type

Here is my Code

1: ZUSC_DataBaseConnection objDBConn = null;
2: CallableStatement cs =null;
3: objDBConn = ZUSC_DataBaseConnection.elxGetInstance();
4: m_cnConnection = objDBConn.elxGetConnection();
5: szProcCall="{call DYNAMICARY(?,?,?)}";
6: cs = m_cnConnection.prepareCall(szProcCall);
7: cs.setString(1,szUserId);
8: cs.setString(2,ReportName);
9: cs.setBoolean(3,true);
10: cs.executeQuery();
11: cs.close();

when i put System out between 9-10 and 10-11,it dont show any msg for line 10-11,so i thing the boolean value is properly set but my procedure is no executed.

For test purpose i also remove my third parameter,in that case it properly work.Also i change my third parameter as Int ,in that case its also working fine ....


please help me out
thanks in advance...
 
Danish Araquei
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All

please refer

http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/tips.htm

Boolean Parameters in PL/SQL Stored Procedures
Due to a restriction in the OCI layer, the JDBC drivers do not support the passing of BOOLEAN parameters to PL/SQL stored procedures. If a PL/SQL procedure contains BOOLEAN values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as an INT and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion from INT to BOOLEAN.

The following is an example of a stored procedure, BOOLPROC, that attempts to pass a BOOLEAN parameter, and a second procedure, BOOLWRAP, that performs the substitution of an INT value for the BOOLEAN.

CREATE OR REPLACE PROCEDURE boolproc(x boolean)
AS
BEGIN
[...]
END;

CREATE OR REPLACE PROCEDURE boolwrap(x int)
AS
BEGIN
IF (x=1) THEN
boolproc(TRUE);
ELSE
boolproc(FALSE);
END IF;
END;

// Create the database connection
Connection conn = DriverManager.getConnection
("jdbcracleci:@<...hoststring...>", "scott", "tiger");
CallableStatement cs = conn.prepareCall ("begin boolwrap(?); end;");
cs.setInt(1, 1);
cs.execute ();
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic