CODEWITHSUNDEEP
oracle-databaseoracle10g
Christa L
Christa L

Reputation: 11

Calling a function within a procedure

I apologize ahead of time for the probably basic question. I am student and it is crunch time!

I am using Oracle 10g Express.

I created a function:

create or replace FUNCTION test_glaccounts_description
(
account_description_param VARCHAR2
) 
RETURN NUMBER
AS
  description_dup_var NUMBER;
BEGIN
  SELECT 1
  INTO description_dup_var
  FROM general_ledger_accounts
  WHERE account_description = account_description_param;

  RETURN description_dup_var;  
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;

And would like to use that function in a procedure. I've tried:

PROCEDURE insert_gla_with_test
(
  account_number_param  NUMBER,
  account_description_param VARCHAR2
)
 AS
BEGIN 
  IF test_glaccounts_description = 1 THEN
  INSERT INTO  general_ledger_accounts
  VALUES (account_number_param, account_description_param);
  ELSE raise_application_error (-20001, 'Duplicate account description');
  END IF;
END;

But it doesn't like the "test_gla_accounts" line.....what am I doing wrong? To my understanding, the function returns a value of 1 or 0, in the procedure, if the function returned a 1, I would like the param fields added to the table. If the function returned a 0, I would like the procedure to raise the error.

Upvotes: 1

Views: 22071

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

test_glaccounts_description takes a parameter (account_description_param) and returns a NUMBER. In order to call the function, therefore, you need to pass in a parameter. Assuming that you want to pass in the account_description_param that is passed in to the insert_gla_with_test procedure

CREATE OR REPLACE PROCEDURE insert_gla_with_test
(
  account_number_param  NUMBER,
  account_description_param VARCHAR2
)
 AS
BEGIN 
  IF test_glaccounts_description( account_description_param ) = 1 THEN
    INSERT INTO  general_ledger_accounts
      VALUES (account_number_param, account_description_param);
  ELSE 
    raise_application_error (-20001, 'Duplicate account description');
  END IF;
END;

Upvotes: 4

Related Questions