CODEWITHSUNDEEP
stored-proceduresoracle11g
sasi
sasi

Reputation: 534

Oracle 11g - Passing boolean to stored procedure

I am trying to create a stored procedure with boolean input parameter:-

Step 1: I have created a table like below

CREATE TABLE STOREBOOL ( BOOLVAL NUMBER(1));

Step 2: I created a stored procedure

Based on the following link: http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64685/tips3.htm#1005343 created a procedure

CREATE OR REPLACE PROCEDURE boolProc(x boolean)
AS
BEGIN
    INSERT INTO storebool("boolval") VALUES(x);
COMMIT;
END;

Output is:-

Warning: Procedure created with compilation errors.

Similarly created a second procedure:

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

Output is:-

Warning: Procedure created with compilation errors.

Step 3: Executing the code

BEGIN
boolWrap(1);
END;
/

It is showing the following error:

boolWrap(1);
*
ERROR at line 2:
ORA-06550: line 2, column 1
PLS-00905: object SCOTT.BOO
ORA-06550: line 2, column 1
PL/SQL: Statement ignored

How do i run it properly?

Upvotes: 0

Views: 3432

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

Did you notice the "created with compilation errors" messages? You can see what the errors were immediately after you see that with show errors, or by querying the user_errors view.

In the first procedure you're dong this:

INSERT INTO storebool("boolval") VALUES(x);

... which has two problems: (a) you don't have a column called "boolval"; it was created unquoted as BOOLVAL so either refer to it unquoted, or quoted but in uppercase as it appears in the data dictionary; and (b) you're trying to set a number column to a boolean value. Oracle SQL doesn't have a boolean type, but there is no implicit conversion available. You need to define what number represents TRUE, and what represents FALSE, and then insert that number instead of the boolean x argument. So you'd see errors like:

4/46           PLS-00382: expression is of wrong type
4/28           PL/SQL: ORA-00904: "boolval": invalid identifier

The boolWrap can't compile because boolProc isn't valid.

Your boolProc and boolWrap procedures are reversed really; you want the 'real' procedure to take a number argument, and the wrapper to take a boolean and convert that to a number to call the real one.

This might make more sense:

CREATE TABLE STOREBOOL (BOOLVAL NUMBER(1),
  CONSTRAINT BOOLCHECK CHECK (BOOLVAL IN (0,1))
);

CREATE OR REPLACE PROCEDURE boolProc(p_bool_num number)
AS
BEGIN
    INSERT INTO storebool(boolval) VALUES (p_bool_num);
END;
/

CREATE OR REPLACE PROCEDURE boolWrap(p_bool boolean)
AS
    l_bool_num number;
BEGIN
    IF p_bool THEN
        boolProc(1);
    ELSE
        boolProc(0);
    END IF;
END;
/

Then you can call boolProc with a number argument, or boolWrap with a boolean argument:

BEGIN
    boolProc(1);
END;
/

BEGIN
    boolWrap(false);
END;
/

select * from storebool;

   BOOLVAL
----------
         1 
         0

Upvotes: 2

Related Questions