SQL & PL/SQL

Greetings!

We are currently developing an update automation system that will transfer database changes (and other changes not relevant for this question) from a master system to around 100 database servers (all being 10g), where the transferred statements contained in SQL scripts (both DDL and DML code, PL/SQL packages and so on) will be stored inside a BLOB field on the remote system and can be "installed" later-on.

Now if I try to load the content from BLOB, convert it through UTL_RAW.CAST_TO_VARCHAR2 to character data and execute it thru DBMS_SQL.PARSE(content), I get an error because the uploaded file contains several commands, each command being separated by semi-colons and it's impossible to put each SQL command into separate files. As I have read on the "Coding Dynamic SQL" pages, using Native SQL (EXECUTE IMMEDIATE) should be used instead of DBMS_SQL, because it'll allow running multiple statements at once, but the 32K limit doesn't allow us to use this way.

Any ideas of a possible solution? How does SQLDeveloper handle this, i.e. if packages are compiled, it's basically the same procedure I guess (text data send from SQLDeveloper to the server, parsed by the server and committed to the database)?

A big thanks in advance for your expert comments!

Best regards,

Sascha

------------

procedure stepExecute(stepRef in integer, pIsError out number, pErrorMsg out varchar2)
as
   lSqlListIdx integer default 1;
   lStrPos     integer default 1;
   lSqlStmt    LOG_CHANGEDOBJECT.STOREDOBJECT%type;
   lSqlCursor  integer;
   lSqlOut     integer;
   sqlCommand  dbms_sql.varchar2a;

begin
   pIsError := 0;
   pErrorMsg := '';

   begin
      select STOREDOBJECT into lSqlStmt
        from LOG_CHANGEDOBJECT
       where log_taskstep_ref = stepRef;
   exception
      when no_data_found then
         pIsError := 1;
         pErrorMsg := 'Invalid SQL ID ' || stepRef;
   end;

   if pIsError = 0
   then 
      lSqlCursor := dbms_sql.open_cursor;

      loop
         sqlCommand(lSqlListIdx) := UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(lSqlStmt, 32767, lStrPos));
         exit when sqlCommand(lSqlListIdx) is null;
         lStrPos := lStrPos+32767;
         lSqlListIdx := lSqlListIdx+1;
      end loop;

      dbms_sql.parse(lSqlCursor, sqlCommand, 1, lSqlListIdx, null, dbms_sql.native);

      lSqlOut := dbms_sql.execute(lSqlCursor);
      dbms_sql.close_cursor(lSqlCursor);
   end if;
end;