How to call a function from a PL/SQL file inside a package function of another PL/SQL file using PL/SQL (Oracle 10g)

Question

I have two sql file.one is "function.sql" and another is "donor.sql". "donor.sql" has a package "AddDonor" and "add_donor" function.This is the code :

create or replace package AddDonor as 
    function add_donor(id in Donation.id%TYPE,d_id in Donor.d_id%TYPE,p_id in Participant.p_id%TYPE,name in Donor.name%TYPE,age in Donor.age%TYPE,gender in Donor.gender%TYPE,bg in Donor.bg%TYPE,branch in Donor.branch%TYPE,contactno in Donor.contactno%TYPE,status in Donation.status%TYPE,c_id in Campaign.c_id%TYPE)
    return number;
end AddDonor;
/
create or replace package body AddDonor as 

    function add_donor(id in Donation.id%TYPE,d_id in Donor.d_id%TYPE,p_id in Participant.p_id%TYPE,name in Donor.name%TYPE,age in Donor.age%TYPE,gender in Donor.gender%TYPE,bg in Donor.bg%TYPE,branch in Donor.branch%TYPE,contactno in Donor.contactno%TYPE,status in Donation.status%TYPE,c_id in Campaign.c_id%TYPE)
    return number
    is
    flag number:=0;
    BEGIN
        
        flag := add_donor1(d_id, name, age,gender,bg,branch,contactno);
        return flag;
    END add_donor;
    
End AddDonor;
/

"function.sql" has "adddonor1" function.This function is not part of any package."adddonor1" is a stand alone function from "function.sql" file.This is the code:

create or replace function add_donor1(id in Donation.id%TYPE,d_id in Donor.d_id%TYPE,p_id in Participant.p_id%TYPE,name in Donor.name%TYPE,age in Donor.age%TYPE,gender in Donor.gender%TYPE,bg in Donor.bg%TYPE,branch in Donor.branch%TYPE,contactno in Donor.contactno%TYPE,status in Donation.status%TYPE,c_id in Campaign.c_id%TYPE)
    return number
    is
 
BEGIN
    insert into Donor values(d_id, name, age,gender,bg,branch,contactno);
    insert into Donation values(id,d_id,status);
    insert into Participant values(p_id,c_id, d_id,name,bg);
    insert into Donor@server_link values(d_id, name, age,gender,bg,branch,contactno);
    insert into Donation@server_link values(id,d_id,status);
    insert into Participant@server_link values(p_id,c_id, d_id,name,bg);
    return 1;
END add_donor1;
/
commit;

I want to call "add_donor1" from "function.sql" inside the package "AddDonor" and package function "add_donor" from "donor.sql" file

I have tried this :

flag := add_donor1(d_id, name, age,gender,bg,branch,contactno);

But it shows :

8/3 PL/SQL: SQL Statement ignored
8/10 PL/SQL: ORA-00904:"FUNCTIONS"."ADD_DONOR1": invalid identifier

How can I call a function from a pl/sql file inside a package function of another pl/sql file using PL/SQL (Oracle 10g) How to solve this?

APC · Accepted Answer · 2021-03-20T09:33:12.380

I have already tried this.It doesn't work.Check my code please. By .sql file I mean to say that I have written the code in a .sql file.Then I have used command line to execute the files.

You need to run both the scripts in the order of dependency. Programs which don't depend on anything else must be run first. You have a package which depends on a standalone function, so you need to run the function script first.

@function.sql
@donor.sql

There is a problem with your package: the call to add_donor1() has the wrong number of parameters. You need to pass all the arguments declared in the signature. Once you fix that your code will compile and run.

Here is a demo on db<>fiddle

Incidentally, using a function to change database state by issuing DML is widely understood to be bad practice. It should be a procedure (a program unit which doesn't return anything). This is underlined by the fact that your function returns a dummy value rather than anything meaningful. –

Littlefoot · Answer 2 · 2021-03-19T08:58:50.257

You've said some "strange" things. Files (.sql) is what confuses me. This is an Oracle database, you don't manipulate with its objects from files stored on your disk, but from within the database itself.

Therefore: connect to the database (using any tool you use; is it SQL*Plus, SQL Developer, TOAD, ... - doesn't matter)

first create a function (add_donor1) by running that create function statement
then create a package by running both create package and create package body
- package's function add_donor calls standalone function add_donor1; as it already exists, package should compile

As I don't have your tables (and don't feel like typing that much), I commented superfluous code, just to show you that it works if properly done.

function.sql

create or replace function add_donor1
  /*
  (id in Donation.id%TYPE,
   d_id in Donor.d_id%TYPE,
   p_id in Participant.p_id%TYPE,
   name in Donor.name%TYPE,
   age in Donor.age%TYPE,
   gender in Donor.gender%TYPE,
   bg in Donor.bg%TYPE,
   branch in Donor.branch%TYPE,
   contactno in Donor.contactno%TYPE,
   status in Donation.status%TYPE,
   c_id in Campaign.c_id%TYPE)
  */  
    return number
    is
BEGIN
   /*
    insert into Donor values(d_id, name, age,gender,bg,branch,contactno);
    insert into Donation values(id,d_id,status);
    insert into Participant values(p_id,c_id, d_id,name,bg);
    insert into Donor@server_link values(d_id, name, age,gender,bg,branch,contactno);
    insert into Donation@server_link values(id,d_id,status);
    insert into Participant@server_link values(p_id,c_id, d_id,name,bg);
   */   
    return 1;
END add_donor1;
/

donor.sql

create or replace package AddDonor as 
    function add_donor
      --(id in Donation.id%TYPE,d_id in Donor.d_id%TYPE,p_id in Participant.p_id%TYPE,name in Donor.name%TYPE,age in Donor.age%TYPE,gender in Donor.gender%TYPE,bg in Donor.bg%TYPE,branch in Donor.branch%TYPE,contactno in Donor.contactno%TYPE,status in Donation.status%TYPE,c_id in Campaign.c_id%TYPE)
    return number;
end AddDonor;
/
create or replace package body AddDonor as 

    function add_donor
      --(id in Donation.id%TYPE,d_id in Donor.d_id%TYPE,p_id in Participant.p_id%TYPE,name in Donor.name%TYPE,age in Donor.age%TYPE,gender in Donor.gender%TYPE,bg in Donor.bg%TYPE,branch in Donor.branch%TYPE,contactno in Donor.contactno%TYPE,status in Donation.status%TYPE,c_id in Campaign.c_id%TYPE)
    return number
    is
    flag number:=0;
    BEGIN
        
        flag := add_donor1; --(d_id, name, age,gender,bg,branch,contactno);
        return flag;
    END add_donor;
    
End AddDonor;
/

Execution:

SQL> @function

Function created.

SQL> @donor

Package created.


Package body created.

SQL>

See? No errors.

I have already tried this.It doesn't work.Check my code please. By .sql file I mean to say that I have written the code in a .sql file.Then I have used command line to execute the files. — Lb4340, Mar 19 '21 at 08:50
I have first executed "function.sql" then i have executed "donor.sql" — Lb4340, Mar 19 '21 at 08:52
Then you did something wrong. I ran your code (modified, though, as I don't have your tables) to show that it works OK. Have a look at the edited answer. — Littlefoot, Mar 19 '21 at 08:59

How to call a function from a PL/SQL file inside a package function of another PL/SQL file using PL/SQL (Oracle 10g)

2 Answers2