Procedure and Bind Variable

753617

I'm trying to write a procedure for an exercise I'm working on. I got an error that I needed to use a "Bind Variable," so now I'm trying to pass a bind variable to the procedure. I am supposed to get user input.


CREATE OR REPLACE PROCEDURE insert_glaccount
(
account_num_pram general_ledger_accounts.account_number%TYPE,
account_desc_pram general_ledger_accounts.account_description%TYPE
)
AS

BEGIN

INSERT INTO general_ledger_accounts
VALUES (account_num_pram, account_desc_pram);

/*Error handling to coming soon*/

END;
/



VARIABLE account_num_var general_ledger_accounts.account_number%TYPE;
VARIABLE account_desc_var general_ledger_accounts.account_description%TYPE;

BEGIN
:account_num_var := &account_num;
:account_desc_var := &account_desc;

CALL insert_glaccount(:account_num_var, :account_desc_var);


END;


Now I'm getting an error: "Bind Variable "account_num_var" is NOT DECLARED"

Can someone please explain how I'm messing this up?

753617

Also, can I SET SERVEROUTPUT ON in a procedure? I read in one place I can't, but after I get this procedure working, it says I'm supposed to add error handling using DBMS_OUTPUT.PUT_LINE

I haven't been able to find an example of using SET SERVEROUTPUT ON in a procedure.

If I put SET SERVEROUTPUT ON in a script will it work in a procedure that's called from the script?

Edited by: user8523912 on Mar 2, 2010 7:20 PM added tags

009

DECLARE
	account_num_var VARCHAR2(20) := &account_num;
BEGIN
	DBMS_OUTPUT.PUT_LINE(account_num_var);
END;

I hope the above given example helps you understand the error (hint: it's the VARCHAR2(20) you missed out )
And I'm not yet aware of the way to put serveroutput on ON mode with the help of PL/SQL
*009*

753617

I don't know if that helps. Now I have this, but it still doesn't work. It looks like the procedure it's self is working, but not the script where I call the procedure:


CREATE OR REPLACE PROCEDURE insert_glaccount
(
account_num_pram general_ledger_accounts.account_number%TYPE,
account_desc_pram general_ledger_accounts.account_description%TYPE
)
AS

BEGIN

INSERT INTO general_ledger_accounts
VALUES (account_num_pram, account_desc_pram);
COMMIT;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('A dup val on index error occurred.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unknown exception has occurred.');
END;
/

DECLARE
account_num_var general_ledger_accounts.account_number%TYPE;
account_desc_var general_ledger_accounts.account_description%TYPE;

BEGIN
account_num_var := &account_num; -- Get user input for account number
account_desc_var := &account_desc; -- Get user input for account description

CALL insert_glaccount(account_num_var, account_desc_var);
END;
/

Prazy

you do not have to use CALL keyword inside a PL/SQL block and do not by-pass the err message in exception block at any cost.

Test this

CREATE OR REPLACE PROCEDURE insert_glaccount
(
account_num_pram general_ledger_accounts.account_number%TYPE,
account_desc_pram general_ledger_accounts.account_description%TYPE
)
AS

BEGIN

INSERT INTO general_ledger_accounts
VALUES (account_num_pram, account_desc_pram);
COMMIT;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('A dup val on index error occurred.'||SQLCODE||' - '||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unknown exception has occurred.'||SQLCODE||' - '||SQLERRM);
END;
/

DECLARE
account_num_var general_ledger_accounts.account_number%TYPE;
account_desc_var general_ledger_accounts.account_description%TYPE;

BEGIN
account_num_var := &account_num; -- Get user input for account number
account_desc_var := &account_desc; -- Get user input for account description

insert_glaccount(account_num_var, account_desc_var);
END;
/

Regards,
Prazy