Hello Tom.

Not sure if this question goes here or under a dbms_profiler question, but here goes: Here's an edited snippet of a profiler test run that took 143 seconds to run. Out of that run, DBMS_SYS_SQL (line 1120) took 61.42 seconds of the run and was by far the #1 time-consumer.

PCT UNIT_OWNER UNIT_NAME LINE# TEXT
------ ----------- -------------- ------ -----------------
42.0 SYS DBMS_SYS_SQL 1120
29.7 SMS PKG_ABC 5114 blah blah blah
3.5 SMS PKG_DEF 124 blah blah blah

Hmmm... I can dive into PKG_ABC to look for tuning opportunities, but I'm really uninformed as to what is going on with DBMS_SYS_SQL - the associated text column is null (and of course the package is gibberish - DON'T want to touch it), so I'm at sea...

Any thoughts or ideas about what the slow-up might be would be most appreciated.

Regards,
- J

Hi Tom,

I'm playing with the dbms_sql package to use it in a future development and i've found a little problem. When i try to create a table with this kind of dinamyc sql (i know its a very bad approach and huge expensive) i get differente results depending on the way i execute it. See the example please:

SQL> DECLARE
2 cursorPointer INTEGER := DBMS_SQL.OPEN_CURSOR;
3 cursorQuery VARCHAR2(500) := 'CREATE TABLE fooTable (OID NUMBER(18))';
4 BEGIN
5 DBMS_SQL.PARSE(cursorPointer,cursorQuery,dbms_sql.native);
6 DBMS_OUTPUT.PUT_LINE(DBMS_SQL.EXECUTE(cursorPointer));
7 DBMS_SQL.CLOSE_CURSOR(cursorPointer);
8 END;
9 /

Procedimiento PL/SQL terminado correctamente.

SQL>select * from fooTable;

ninguna fila seleccionada

Transcurrido: 00:00:00.00
SQL>drop table fooTable;

Tabla borrada.

SQL>CREATE OR REPLACE PROCEDURE fooProcedure AS
2 cursorPointer INTEGER := DBMS_SQL.OPEN_CURSOR;
3 cursorQuery VARCHAR2(500) := 'CREATE TABLE fooTable (OID NUMBER(18))';
4 BEGIN
5 DBMS_SQL.PARSE(cursorPointer,cursorQuery,dbms_sql.native);
6 DBMS_OUTPUT.PUT_LINE(DBMS_SQL.EXECUTE(cursorPointer));
7 DBMS_SQL.CLOSE_CURSOR(cursorPointer);
8 END;
9 /

Procedimiento creado.

SQL>exec fooProcedure;
BEGIN fooProcedure; END;

*
ERROR en línea 1:
ORA-01031: privilegios insuficientes
ORA-06512: en "SYS.DBMS_SYS_SQL", línea 906
ORA-06512: en "SYS.DBMS_SQL", línea 39
ORA-06512: en "TESTUSER.FOOPROCEDURE", línea 5
ORA-06512: en línea 1

I use a 10.2.0.1.0 version of oracle.
Thanks in advance