Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mike.

Asked: January 24, 2001 - 5:58 am UTC

Last updated: February 07, 2007 - 5:25 pm UTC

Version: 8.0.5

Viewed 1000+ times

You Asked

Dear Tom,

pls tell me where can I read about DBMS_SYS_SQL package. It's pkg body and specification are wrapped and no any mention about it in documentation shipped with installation.

Thank you.

and we said...

It is an undocumented package used internally by Oracle services. It is called by DBMS_SQL. It is specifically used by replication and other products such as WebDB. It is not intended for use outside of Oracle.

It works the same as DBMS_SQL would however it has an additional entry point "parse as user" that allows you to parse a sql statement as any other user in the database. That statement will be executed with their privileges. That makes this package an EXTREMELY powerful package. Execute on this package should be strictly controlled as the accounts that can execute this can do virtually anything they want.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBMS_SYS_SQL (line 1120)

J, October 11, 2006 - 2:26 pm UTC

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

Tom Kyte
October 11, 2006 - 4:06 pm UTC

likely the place where your sql is being executed there, you don't have any more insight into it than that, you cannot change what it does, you can only avoid calling it.

DBMS_SYS_SQL

Carlos, February 07, 2007 - 3:10 am UTC

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


More to Explore

DBMS_SQL

More on PL/SQL routine DBMS_SQL here