Hello Tom,
Can you please tell me if there is a way to only parse ddl without executing it. My need comes from the fact that one of our prod envs has very outdated testing envs and there is absolutely no time to bring these testing envs in syns with prod.
I would like to parse long ddl scripts without executing them in the prod environment. I know that dbms_sql's parse procedure also executes. Is there any other way?

Thank you very much

If one is the other then why do we have a distinction between the parsing step and the execution step. True they don't do the same thing but they are the same thing. Instead of spliting in all the books the ddl execution in two steps parsing/execution we should have just one, I think.

<quote>
DDL is performed in the following way:

o a commit is issued
o the DDL is performed
o a commit is issued
</quote>

I understand that if session 1 is querying a table, and session 2 tries to alter it, session 2 will block on a data dictionary lock until session 1's query completes.

What if session 1 starts a serializable transaction, queries the table, then session 2 alters the table, then session 1 queries the table again. Will it see the table alter or not altered, or get "can't serialize this transaction".

Thanks and apologies if you can't understand the question.

ops$tkyte@ORA817DEV> create table t ( x int );
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> set transaction isolation level serializable;
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t select rownum from all_users where rownum < 5;
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> variable x refcursor
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> begin
  2    open :x for select * from t;
  3  end;
  4  /
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          pragma autonomous_transaction;
  3  begin
  4          execute immediate 'alter table t add y number default 55';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4


ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> print x

         X
----------
         1
         2
         3
         4
ops$tkyte@ORA817DEV> 

I forgot I could answer my own question!

Check out these interesting results... 

==== session 1 ====
SQL> set transaction isolation level serializable;

Transaction set.

SQL> select * from test;

         X          Y          Z
---------- ---------- ----------
         1          2          0
         2          4          0
         3          6          0
         4          8          0

==== session 2 ====
SQL>alter table test add (q number default 1);

Table altered.

==== session 1 ====
SQL> select * from test;

         X          Y          Z          Q
---------- ---------- ---------- ----------
         1          2          0
         2          4          0
         3          6          0
         4          8          0

SQL> commit;

Commit complete.

SQL> select * from test;

         X          Y          Z          Q
---------- ---------- ---------- ----------
         1          2          0          1
         2          4          0          1
         3          6          0          1
         4          8          0          1

So the changes to the table structure in the data dictionary appear in session 1, but the value of the column doesn't appear until after the transaction ends. Could you explain how this is happening? Thanks Tom. 

the above example shows what happens in 8i, in 9i, this is what happens:

ps$tkyte@ORA920> create table t ( x ) as select rownum from all_users where rownum < 5;

Table created.

ops$tkyte@ORA920> set transaction isolation level serializable;

Transaction set.

ops$tkyte@ORA920> select * from t;

         X
----------
         1
         2
         3
         4

ops$tkyte@ORA920> declare
  2          pragma autonomous_transaction;
  3  begin
  4          execute immediate 'alter table t add y number default 55';
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from t;

         X          Y
---------- ----------
         1
         2
         3
         4

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920> select * from t;

         X          Y
---------- ----------
         1         55
         2         55
         3         55
         4         55



the reason Y is "NULL" until we commit is because the alter table really does an "update t set y = 55" (sql_trace will show you that).  We cannot see the effects of updates in a serializable transaction that happened AFTER our transaction begin -- we have to see dictionary updates (SYS -- recursive -- SQL is never done in "flashback" style) so we see the column -- but not the values therein 

Sorry Tom, did you make a typo? I am running 9i.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
PL/SQL Release 9.2.0.2.0 - Production
CORE    9.2.0.2.0       Production
TNS for HPUX: Version 9.2.0.2.0 - Production
NLSRTL Version 9.2.0.2.0 - Production

What happens in 8i?
 

Hi Tom I'm having problems executin long ddls like this for example

I need to rund ddls dinamically of packages with more than 20,000 characters, any idea. (they packages are only one copmlex process and I can't divide in smaller packages)

execute immediate 'CREATE OR REPLACE
PACKAGE adm.pck_captura2
IS
FUNCTION clt ( cCodigo VARCHAR2, dFecha DATE, nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mco ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mde ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION men ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mfa ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mpg ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mru ( cCodigo VARCHAR2, dFecha DATE, nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION msf ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mtc ( cCodigo VARCHAR2, dFecha DATE,nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
FUNCTION mtr ( cCodigo VARCHAR2, dFecha DATE, nNoProceso NUMBER,cPath VARCHAR2 )
RETURN VARCHAR2;
END; -- Package spec
/';

you'll have to use dbms_sql which can parse arrays:

array(1) := 'create or replace ...'
array(2) := 'package adm.....'
array(3) := 'is'
......


of course, you could just put the code into a script and run the script -- be much easier than stuffing code into strings.....

(to put into array "easily", you can load it in your database and select it back out:

  1  select 'array('||line||') := ''' ||
  2  replace( replace( text, chr(10), '' ), '''', '''''' ) || ''';' from
  3  user_source
  4* where type = 'PROCEDURE' and name = 'P'
ops$tkyte@ORA9IR2> /
 
'ARRAY('||LINE||'):='''||REPLACE(REPLACE(TEXT,CHR(10),''),'''','''''')||''';'
-------------------------------------------------------------------------------
array(1) := 'procedure p ( p_str in varchar2 )';
array(2) := 'is';
array(3) := '   l_str   long := p_str;';
array(4) := 'begin';
array(5) := '   loop';
array(6) := '      exit when l_str is null;';
array(7) := '      dbms_output.put_line( substr( l_str, 1, 250 ) );';
array(8) := '      l_str := substr( l_str, 251 );';
array(9) := '   end loop;';
array(10) := 'end;';
 
10 rows selected.
 






 

Thanks Tom

didn't work with array, worked with variable
declare
type varchar2_aArray is table of varchar2(1000) index by binary_integer;
aArray varchar2_aArray;

nCursor NUMBER;
csql VARCHAR2(4000);
nRows NUMBER;
begin

aArray(1) := 'CREATE OR REPLACE '||CHR(10);
aArray(2) := 'PACKAGE ctb.pck_cco2'||CHR(10);
aArray(3) := ' IS'||CHR(10);
aArray(4) := ' cLUsaCostoParcial VARCHAR2(1);'||CHR(10);
aArray(5) := ' CURSOR uEstCosto IS SELECT ECC_NIVEL, ECC_LONG FROM ESTcCosto ORDER BY ECC_NIVEL;'||CHR(10);
aArray(6) := ''||CHR(10);
aArray(7) := ' FUNCTION VALIDCCOSTO (cCuenta VARCHAR2, cCosto IN OUT VARCHAR2, cCtaMayor VARCHAR2) RETURN VARCHAR2 ;'||CHR(10);
aArray(8) := ' FUNCTION getnomcosto( cCosto VARCHAR2 ) RETURN VARCHAR2;'||CHR(10);
aArray(9) := ' FUNCTION CuentaUsaCcosto( cCuenta VARCHAR2 ) RETURN BOOLEAN;'||CHR(10);
aArray(10) := ' FUNCTION UsaCcosto( cTratamiento VARCHAR2 ) RETURN BOOLEAN;'||CHR(10);
aArray(11) := ' FUNCTION UsaCcosto RETURN BOOLEAN;'||CHR(10);
aArray(12) := ' FUNCTION GetCcostoLike( cCosto VARCHAR2, cParte1 VARCHAR2, cParte2 VARCHAR2,'||CHR(10);
aArray(13) := ' cParte3 VARCHAR2, cParte4 VARCHAR2, cParte5 VARCHAR2)'||CHR(10);
aArray(14) := ' RETURN VARCHAR2;'||CHR(10);
aArray(15) := ' FUNCTION GetCostoCodigos( cCosto VARCHAR2, cParte1 OUT VARCHAR2,'||CHR(10);
aArray(16) := ' cParte2 OUT VARCHAR2, cParte3 OUT VARCHAR2,'||CHR(10);
aArray(17) := ' cParte4 OUT VARCHAR2, cParte5 OUT VARCHAR2) RETURN VARCHAR2;'||CHR(10);
aArray(18) := ' PROCEDURE GetcCostoCodigos( cCosto VARCHAR2, cAlerta VARCHAR2, cParte1 OUT VARCHAR2, cParte2 OUT VARCHAR2,'||CHR(10);
aArray(19) := ' cParte3 OUT VARCHAR2, cParte4 OUT VARCHAR2, cParte5 OUT VARCHAR2);'||CHR(10);
aArray(20) := ' FUNCTION GETCCOSTOQUERY( cCosto VARCHAR2 ) RETURN VARCHAR2;'||CHR(10);
aArray(21) := 'FUNCTION FUT_CCO_IMPUTACION( cCta VARCHAR2,nMonto NUMBER, cCCO_COD2 VARCHAR2 )'||CHR(10);
aArray(22) := ' RETURN TYP_CCO_IMPUTACION;'||CHR(10);
aArray(23) := 'END; -- Package spec'||CHR(10);
--aArray(24) := '/'||CHR(10);

FOR I IN 1..23 LOOP
csql := csql ||aArray(I);
END LOOP;
nCursor := dbms_sql.open_cursor;
dbms_sql.parse( nCursor, csql,DBMS_SQL.NATIVE);
--dbms_sql.parse( nCursor, aArray,DBMS_SQL.NATIVE); *** failed
nRows := dbms_sql.execute( nCursor);
dbms_output.put_line( nCursor );

exception when others then
dbms_output.put_line(sqlerrm);
end;

....
and using clob(50801 characters) gives a 6502 error

declare
ELLOB clob;
nCursor NUMBER;
csql VARCHAR2(4000);
nRows NUMBER;
begin
/*
aArray(1) := 'CREATE OR REPLACE '||CHR(10);
aArray(2) := 'PACKAGE ctb.pck_cco2'||CHR(10);
aArray(3) := ' IS'||CHR(10);
aArray(4) := ' cLUsaCostoParcial VARCHAR2(1);'||CHR(10);
aArray(5) := ' CURSOR uEstCosto IS SELECT ECC_NIVEL, ECC_LONG FROM ESTcCosto ORDER BY ECC_NIVEL;'||CHR(10);
aArray(6) := ''||CHR(10);
aArray(7) := ' FUNCTION VALIDCCOSTO (cCuenta VARCHAR2, cCosto IN OUT VARCHAR2, cCtaMayor VARCHAR2) RETURN VARCHAR2 ;'||CHR(10);
aArray(8) := ' FUNCTION getnomcosto( cCosto VARCHAR2 ) RETURN VARCHAR2;'||CHR(10);
aArray(9) := ' FUNCTION CuentaUsaCcosto( cCuenta VARCHAR2 ) RETURN BOOLEAN;'||CHR(10);
aArray(10) := ' FUNCTION UsaCcosto( cTratamiento VARCHAR2 ) RETURN BOOLEAN;'||CHR(10);
aArray(11) := ' FUNCTION UsaCcosto RETURN BOOLEAN;'||CHR(10);
aArray(12) := ' FUNCTION GetCcostoLike( cCosto VARCHAR2, cParte1 VARCHAR2, cParte2 VARCHAR2,'||CHR(10);
aArray(13) := ' cParte3 VARCHAR2, cParte4 VARCHAR2, cParte5 VARCHAR2)'||CHR(10);
aArray(14) := ' RETURN VARCHAR2;'||CHR(10);
aArray(15) := ' FUNCTION GetCostoCodigos( cCosto VARCHAR2, cParte1 OUT VARCHAR2,'||CHR(10);
aArray(16) := ' cParte2 OUT VARCHAR2, cParte3 OUT VARCHAR2,'||CHR(10);
aArray(17) := ' cParte4 OUT VARCHAR2, cParte5 OUT VARCHAR2) RETURN VARCHAR2;'||CHR(10);
aArray(18) := ' PROCEDURE GetcCostoCodigos( cCosto VARCHAR2, cAlerta VARCHAR2, cParte1 OUT VARCHAR2, cParte2 OUT VARCHAR2,'||CHR(10);
aArray(19) := ' cParte3 OUT VARCHAR2, cParte4 OUT VARCHAR2, cParte5 OUT VARCHAR2);'||CHR(10);
aArray(20) := ' FUNCTION GETCCOSTOQUERY( cCosto VARCHAR2 ) RETURN VARCHAR2;'||CHR(10);
aArray(21) := 'FUNCTION FUT_CCO_IMPUTACION( cCta VARCHAR2,nMonto NUMBER, cCCO_COD2 VARCHAR2 )'||CHR(10);
aArray(22) := ' RETURN TYP_CCO_IMPUTACION;'||CHR(10);
aArray(23) := 'END; -- Package spec'||CHR(10);
--aArray(24) := '/'||CHR(10);

FOR I IN 1..23 LOOP
csql := csql ||aArray(I);
END LOOP;
nCursor := dbms_sql.open_cursor;
dbms_sql.parse( nCursor, csql,DBMS_SQL.NATIVE);
--dbms_sql.parse( nCursor, aArray,DBMS_SQL.NATIVE);
nRows := dbms_sql.execute( nCursor);
dbms_output.put_line( nCursor );
*/

SELECT A INTO ELLOB FROM CTB.TESTE;
nCursor := dbms_sql.open_cursor;
dbms_output.put_line( ELLOB);
dbms_sql.parse( nCursor, ELLOB,DBMS_SQL.NATIVE);
--dbms_sql.parse( nCursor, aArray,DBMS_SQL.NATIVE);
nRows := dbms_sql.execute( nCursor);
dbms_output.put_line( nCursor );


exception when others then
dbms_output.put_line(sqlerrm);
end;

if you have some idea please

Thanks Tom I got it
In </code> http://asktom.oracle.com/pls/ask/f?p=4950:9:7337978121515501587::NO:9:F4950_P9_DISPLAYID:6161200355268 <code>

Here is applied to a clob

declare
dbms_sql.varchar2s;
l_cursor integer default dbms_sql.open_cursor;
l_rows number default 0;
ELLOB clob;
nPos number ;
I number := 1;
nPosAnt number := 1;
begin
SELECT A INTO ELLOB FROM CTB.TESTE;

/* WHILE TRUE LOOP
DBMS_OUTPUT.PUT_LINE(i||'-'||substr(ellob,i,1)||'-'||ascII(substr(ellob,i,1)));
i:=i+1;
if i>50 then
exit;
end if;
END LOOP;*/

WHILE TRUE LOOP
DBMS_OUTPUT.PUT_LINE(1);
nPos := instr( ELLOB,CHR(10),1,i);
-- DBMS_OUTPUT.PUT_LINE(nPos||':pos-i:'||i);
DBMS_OUTPUT.PUT_LINE(2);
if nPos = 0 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(substr(ellob,nPosAnt,nPos-nPosAnt));

DBMS_OUTPUT.PUT_LINE(3);
l_stmt(i) := substr(ellob,nPosAnt,nPos-nPosAnt);
DBMS_OUTPUT.PUT_LINE(4);
i:=i+1;
DBMS_OUTPUT.PUT_LINE(5);
nPosAnt := nPos;
END LOOP;


dbms_sql.parse( c => l_cursor,
statement => l_stmt,
lb => l_stmt.first,
ub => l_stmt.last,
lfflg => TRUE,
language_flag => dbms_sql.native );
l_rows := dbms_sql.execute(l_cursor);






dbms_sql.close_cursor( l_cursor );
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end;
/

Hi,

DDL triggers are evil, and my code is quite scratchy; but looks like we can cancel (and revert back) DDL by raising an exception in a DDL trigger.

create or replace trigger sys.check_only_alter_ddl_trg
after alter on database
declare
 n number;
 sqltext ora_name_list_t;
begin
    n := ora_sql_txt(sqltext);
    if upper(sqltext(1)) like '%/*%+%CHECKONLY%*/%' then
        raise_application_error(-20001,'It is a check only ALTER DDL.');
    end if;
end;
/

create or replace
procedure check_exchange_statement(p_table_name in varchar2,
    p_partition_name in varchar2 default null,
    p_exchange_table_name in varchar2 default null)
--------------------------------------------------------------------------------
-- Check exchange partition statement for the given table.
-- If the partition name is not specified peeks the 1st partition of the table.
-- If the exchange table name is not specified forms it by adding _EXC to the
-- table name.
is
    e_check_only exception;
    pragma exception_init(e_check_only, -04088);
-- ORA-04088: error during execution of trigger 'SYS.CHECK_ONLY_ALTER_DDL_TRG'
    v_partition_name user_tab_partitions.partition_name%type;
    v_exchange_table_name user_tables.table_name%type;
    v_cursor_id integer;
begin
    if p_partition_name is null then
       select partition_name into v_partition_name from user_tab_partitions
        where table_name = p_table_name and partition_position = 1;
    else
        v_partition_name := p_partition_name;
    end if;
    if p_exchange_table_name is null then
        v_exchange_table_name := p_table_name||'_EXC';
    else
        v_exchange_table_name := p_exchange_table_name;
    end if;
    begin
        v_cursor_id := dbms_sql.open_cursor;
        dbms_sql.parse(v_cursor_id,
            'alter /*+CHECKONLY */ table '||p_table_name||' exchange partition '
            ||v_partition_name||' with table '||v_exchange_table_name||
            ' including indexes update indexes',
            dbms_sql.native);
        dbms_sql.close_cursor(v_cursor_id);
    exception
        when e_check_only then dbms_sql.close_cursor(v_cursor_id);
    end;
end;
/

SQL> exec check_exchange_statement('CWMILESTONE');

PL/SQL procedure successfully completed.

SQL> exec check_exchange_statement('CWPC_BASKETITEM');
BEGIN check_exchange_statement('CWPC_BASKETITEM'); END;

*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at "CW_EOC_UI.CHECK_EXCHANGE_STATEMENT", line 29
ORA-06512: at line 1

SQL> select count(*) from CWMILESTONE_EXC;

  COUNT(*)
----------
         0

SQL> select 'select count(*) from CWMILESTONE partition ('||partition_name||');' from user_tab_partitions where table_name='CWMILESTONE' order by partition_position;

'SELECTCOUNT(*)FROMCWMILESTONEPARTITION('||PARTITION_NAME||');'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select count(*) from CWMILESTONE partition (SYS_P4790);
select count(*) from CWMILESTONE partition (SYS_P4011);
select count(*) from CWMILESTONE partition (SYS_P4012);

SQL> select count(*) from CWMILESTONE partition (SYS_P4790);

  COUNT(*)
----------
     24941

SQL> select count(*) from CWMILESTONE partition (SYS_P4011);

  COUNT(*)
----------
      6314

SQL> select count(*) from CWMILESTONE partition (SYS_P4012);

  COUNT(*)
----------
      8116