Reputation: 534
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
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