I'm utterly lost as to what the actual problem is.
If the file name and structure are constant, all you need is a basic INSERT ... SELECT (with appropriate logic to handle retries, errors, etc.). See below for an example.
How else could you run your sample code in a named procedure?Just call it! The code is in the procedure P access it in another procedure, anonymous block, OS script, scheduler job, Java app, ...; it really depends on how this process works.
Like I asked, please provide pseudo code showing what you're trying to achieve and exactly what you're struggling with.
declare
f utl_file.file_type;
begin
f := utl_file.fopen ( 'TMP', 'file.txt', 'w' );
utl_file.put_line ( f, 'first' );
utl_file.fclose ( f );
end;
/
create table ext (
c1 varchar2(10)
) organization external (
default directory tmp
location ( 'file.txt' )
);
create table stage (
c1 varchar2(10)
);
create or replace procedure p as
begin
insert into stage
select * from ext;
end p;
/
exec p ();
select * from stage;
C1
----------
first
declare
f utl_file.file_type;
begin
f := utl_file.fopen ( 'TMP', 'file.txt', 'w' );
utl_file.put_line ( f, 'second' );
utl_file.fclose ( f );
end;
/
begin
p ();
end;
/
select * from stage;
C1
----------
first
second