Just to bring this thread to some conclusion.
starting from the initial question:
libspatialite calls just once sqlite3_enable_load_extension(),
and this happens in the stored_precedure.c source.
if you comment out that call you'll then obviously risk to
get some unexpected malfunctions when using a stored
procedure. if you aren't interested in stored procedure
it's absolutely harmless.
a more detailed reply:
libsqlite3 has two subtly distinct modes for supporting an
extension module (as SpatiaLite is).
a) by calling SELECT load_extension('module-name');
this in an universal mode that you can use on any
possibile language, from SQL itself to Java, Python
and so on.
this SQL call works in two steps:
a.1) first libsqlite3 will attempt to locate and
load the extension module
a.2) then it will attempt to find a conventional
EntryPoint (a C routine) containing all the
code to be executed in order to correctly
register and initialize the extension.
b) by statically or dinamically linking the library
implementing the extension. this second option is
only avaible for C or C++ programs.
in this second case there isn't any need to locate
and load the extesione code, because it's already
linked to your program (statically or dynamically
is absolutely the same, it makes no difference).
what is required is just executing the EntryPoint
routine so to register and initialize the extension,
and exactly this is the intended scope of calling
spatialite_init_ex() immediately after establishing
a db connection.
final remark: using a libsqlite3 not supporting the
capability to dynamically load extensions never is
a good idea. any possible effort should always be
done in order to replace this defective component
with a "genuine" unconstrained libsqlite3.