We help IT Professionals succeed at work.

Making a package and calling API to one PLSQL Block

Swadhin Ray
Swadhin Ray used Ask the Experts™
on
Hello Experts,

I have a package which have two procedures. Finally i use a simple PLSQL block to call the package so that it calls the package.

the flow is like :

PLSQL Block --> Package.first_proceudre --> internally calls the second_procedure .

And finally the second procedure inserts the data into a temp table.

Attached here with the package which includes two procedure , the PLSQL block which is calling the package and finally the temp table structure.

I want to make this package and my block into one block , means I dont have to use any package any more .  What ever the package is doing just to put them into one block and from the main plsql block it should call like the second block, and finally should insert the records.

Can any one help me in achieving this process.
API.txt
package.sql
Temp-table.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Are you saying you want to move hte logic form the procedures into one long pl/sql block so you no longer have stored code?

If so, why would you want to do that?

If you do not want the code stored inside the database, since you already have the procedures created, I would declare the procedures in the anonymous pl/sql block.

I'm not going to go through all your code and attempt to do this for you but the concept goes like:

declare
	somevar varchar2(20) := 'Bob';

	procedure proc2(invar in varchar2) is
	begin
		dbms_output.put_line('Hello ' || invar);
	end proc2;
	
	procedure proc1(invar in varchar2) is
	begin
		proc2(invar);
	end proc1;
	
begin
	proc1(somevar);
end;
/

Open in new window

thanks...