SQL & PL/SQL

Hi
I have created a package wich reads header information from a comma separated text file and creates an external table at run time. The column names come from the header read from the file.

{color:#0000ff}[ The (pseudo) code looks like this:
start
drop existing ext table
create new ext table
code for accessing information from ext table (static code)
end
]
{color}
The table creation has to happen at run time becuase the header information in the text file is not static. The columns to be created in the table could change for every run.
The logic of reading the file and creating the external table works fine but the package gets invalidated after every run becuase the existing external table gets dropped before creating the new one.

Before the next run, I need to recompile the packge so that it is marked as valid.
One way of doing this is to write a dummy procedure in the package and invoke it before invoking the actual procedures. The dummy procedure call will force the db to recompile the package and mark it as valid.

This is a very crude way of doing things and I am sure there is a better approach.
I am evaluating the option of making the table access code dynamic (so that the package does not get invalidated when the tables get dropped) and would like to know if there are other better approaches to this problem.

Thanks!

Edited by: kedruwsky on Oct 16, 2008 5:12 AM

Edited by: kedruwsky on Oct 16, 2008 5:16 AM
Sorry, I typed GTT when I wanted to type External table.
I guess long work hours are taking their toll!