ORA-4030 and huge PL/SQL block

Lately we have got for execution a quite huge anonymous PL/SQL block (~10M). When it went for execution it failed with ORA-4030. There are limits to the size of a PL/SQL block. They may be rised at the OS level or in the database (look at "ORA-4030 (PLSQL Opt Pool,pdziM01_Create: New Set), ORA-4030 (PLS CGA hp,pdzgM64_New_Link)" (Doc ID 1551115.1) on the Metalink).

For example on the OS level the /proc/sys/vm/max_map_count parameter sets the number of the maximum number of memory map areas a process may have for Linux.
But before You will mess with those settings, rethink if it is really necessary - most possibly there is another way to run the equivalent code, which makes a better use of the memory in disposal.
In this very case the 10M block consists of 1mln of calls to a procedure with different parameters provided as literals. Much better way in this case would be to load all the parameter values to a table (by sqlldr to persistent table or by simple inserts in one session to a global temporary table with rows preserved on commits) and then call a procedure by much, much smaller script on values from a cursor or get rid of PL/SQL block, turn procedure to function and call it in simple SELECT.

The fun with names and PLS-00302

Quite often it turns out that the more mysterious the problem is the more trivial is the solution.
This is a short note about such a situation.

There is a package, which prepares a report. The package has the VALID status, no problems with the compilation or so. However, the call to the procedure ended with throwing the exception PLS-00302 (component zzz must be declared). The call from the owner schema, so not problems with the visibility of the package, the DESCRIBE command works as expected.
To make the long story short I found out that the exception had been risen, when the call was fully qualified with the schema name. If there was no schema specification, the code had been working properly. The reason was the existence of another package with the same name as the schema name, and the problem was with the context resolution - in the first place there was a search within the package and not within the schema namespace.

The workaround is simple - not to call the package prefixed with the schema name. The true solution is clear (though not necessarily simple due to possible dependencies) - to change the package name to different than the schema name and of course to avoid naming the packages with names of schemas.

The listing of file names in a directory through PL/SQL

There is no straightforward way to enlist file names in a file system directory from PL/SQL level. The UTL_FILE package allows for many operations on the files, yet there is no equivalent to command ls. There is, however, a way to do it from within the database engine.

The first idea is to use Java or extproc. Tom Kyte describes such solution on AskTom.
The second idea is to use the DBMS_BACKUP_RESTORE package. This way is a little tricky and may change between different versions of the Oracle RDBMS binaries, so one has to be careful, when using it. As this is quite interesting I place here a short description.

connect / as sysdba
declare
p_dir varchar2(64) := '/oracle';
p_nul varchar2(1024);
begin
sys.dbms_backup_restore.searchFiles(p_dir, p_null);
dbms_output.put_line(p_nul);
for f in (select fname_krbmsft fname from x$krbmsft) loop
dbms_output.put_line(f.fname);
end loop;
end;
/

I run it on the 11.2.0.2 version and the whole thing works as advertised, however:

• does not follow symlinks
• returns all the file names recursively, so enlists also subdirectories' files

Possibly there are other features, I did not spot in the short test.

A very good article on the subject one may find at http://www.morganslibrary.com/hci/hci002.html. The author suggests some security measures, which in this case are obligatory like wrapping the x$krbmsft in a read only view and the searchFiles call in a procedure. Of course this is not only for security - possible changes in the DBMS_BACKUP_RESTORE package may bring an end to this procedure and then it is easier to change the code in custom procedure.