Forum Stats

  • 3,816,037 Users
  • 2,259,133 Discussions
  • 7,893,366 Comments

Discussions

Procedure and Bind Variable

753617
753617 Member Posts: 5
edited Mar 3, 2010 12:45AM in SQL & PL/SQL
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?

Answers

  • 753617
    753617 Member Posts: 5
    edited Mar 2, 2010 10:21PM
    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
    009 Member Posts: 820
    edited Mar 2, 2010 11:05PM
    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
    753617 Member Posts: 5
    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
    Prazy Member Posts: 822 Silver Badge
    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
This discussion has been closed.