CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
flamingcheeto5
flamingcheeto5

Reputation: 11

Calling a Procedure inside a Function PL/SQL

I am trying to figure out how to call the following procedure from a function using Oracle 11g 11.2.0.2.0. Does anyone have some ideas? : 

-- CREATES or REPLACES insert_into_table_a Procedure. 
CREATE OR REPLACE PROCEDURE insert_into_table_a
-- Declares variable to be inserted into tables. 
    (test_insert_a   VARCHAR2)
IS
-- Begins Procedure. 
BEGIN
-- Creates a savepoint. 
SAVEPOINT all_or_none; 

-- Inserts test_insert_a into the CONTACT_ID column of the CONTACT table. 
INSERT INTO CONTACT (CONTACT_ID)
VALUES (test_insert_a);

-- Inserts test_insert_a into the ADDRESS_ID column of the ADDRESS table. 
INSERT INTO ADDRESS (ADDRESS_ID)
VALUES (test_insert_a);

-- Inserts test_insert_a int the TELEPHONE_ID column of the TELEPHONE table. 
INSERT INTO TELEPHONE (TELEPHONE_ID)
VALUES (test_insert_a);

--Commits inserts. 
COMMIT; 
-- Creates exception, incase any errors occur, all changes are rolled back. 
EXCEPTION 
   WHEN OTHERS THEN
    ROLLBACK TO all_or_none; 
-- Ends procedure.
END;
/
-- Shows any errors created. 
SHOW ERRORS

Upvotes: 0

Views: 4123

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

You can call the procedure from a function just as you'd call any other PL/SQL block

CREATE OR REPLACE FUNCTION my_function
  RETURN integer
IS
  l_parameter VARCHAR2(100) := 'foo';
BEGIN
  insert_into_table_a( l_parameter );
  RETURN 1
END;

That being said, it doesn't make sense to call this procedure from a function. Procedures should manipulate data. Functions should return values. If you call a procedure that manipulates data from a function, you can no longer use that function in a SQL statement, which is one of the primary reasons to create a function in the first place. You also make managing security more complicated-- if you use functions and procedures properly, DBAs can give read-only users execute privileges on the functions without worrying that they'll be giving them the ability to manipulate data.

The procedure itself seems highly suspicious as well. A column named address_id in the address table should be the primary key and the name implies that it is a number. The same applies for the contact_id column in the contact table and the telephone_id column in the telephone table. The fact that you are inserting a string rather than a number and the fact that you are inserting the same value in the three tables implies that neither of these implications are actually true. That's going to be very confusing for whoever has to work with your system in the future.

Upvotes: 3

Related Questions