ORA-00600: with arguments: [12811], [154970]

We encountered this bug on Windows 12.1.0.2.0 Enterprise Edition.

When dropping a table and you get a ORA-00 600 with arguments: [12811] and [154970], you will have to check your application for the following sequence of events:

– You created the table with an identity column GENERATED BY DEFAULT ON NULL AS IDENTITY
– you have code that tries to alter the value the sequence by an alter table statement in dynamic sql.

When the alter table results in an error, the sequence is dropped and you are stuck with a corrupted database.

Don’t do this in a production database, as we have 😦

CREATE TABLE edwin_test2
   (  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 
      MINVALUE 1 MAXVALUE 9999999999999999999999999999 
      INCREMENT BY 1 START WITH 179193 CACHE 20 NOORDER  
      NOCYCLE  CONSTRAINT edw_con2 NOT NULL ENABLE, 
      CREATION_DATE date, 
      CONSTRAINT edwin_test_pk2 PRIMARY KEY (ID)
      USING INDEX  ENABLE
   ) NO INMEMORY ;

declare
  l_max_id number:= 10;
begin
   insert into edwin_test2 
     ( creation_date
     ) 
   values 
     ( sysdate
     );
   commit;
   -- 
   l_max_id := -1;  -- force the error
   --
   -- Let's blow things up...
   execute immediate 
   'alter table edwin_test2  modify id number '
   ||'generated by default on null as identity '
   ||'( start with ' ||l_max_id || ' )';
end;
/

drop table edwin_test2;

You can check if you’re have this corruption by the following script:

declare
  cursor c_seq
    ( cp_name in varchar2
    )
  is
    select 'exists' bla
    from   dba_sequences seq
    where  seq.sequence_name = cp_name
  ;
  r_seq c_seq%rowtype;
begin
  for r_i in 
    ( select col.table_name
      ,      col.data_default as seq
      from   dba_tab_columns col
      where  col.identity_column = 'YES'
      and    not exists ( select 1
                          from   dba_views vw
                          where  vw.view_name = col.table_name
                        )
    )
  loop
    r_i.seq := substr( r_i.seq, instr(r_i.seq,'.')+2);
    r_i.seq := substr( r_i.seq, 1, instr(r_i.seq,'"')-1);   
    --      
    r_seq:= null;
    open c_seq(r_i.seq);
    fetch c_seq into r_seq;
    close c_seq;
    --- 
    dbms_output.put_line
      ( rpad(r_i.table_name,40,' ')
      ||r_i.seq
      ||' '
      ||nvl(r_seq.bla, ' ------ohoh!')
      );
  end loop;
end;               

To handle this corruption, just open a SR with Oracle. They should be able to help you.

No, recreating the sequence by hand doesn’t work. I tried. The sys.seq.FLAGS column has a value of 8 for a hand-created sequence and 40 for an identity column; I see no problem in selecting from the datadictionairy, but updating it is even for me a big NO.

 

Tables with row archiving in Oracle 12c … bit 47

This week I followed the Oracle 12c New Features course in the Netherlands by Harald van Breederode. In this course the new row archiving feature was mentioned. Very interesting feature, but if you want to look up which table has row archiving enabled, it seems that the only thing you can do is to look up all tables with an ORA_ARCHIVE_STATE column present.

That seems odd to me, because the feature is a property of the table, so CDB/DBA_TABLES should have a column indicating the feature. The following PL/SQL block, executed as sys in a 12.1.0.1.0 mirrors the investigation I did.

declare
  /* 
    Create a table in another schema.
    When you don't, the alter table will give an 
    ORA-12988: cannot drop column from table owned by SYS.
    and we are dropping the hidden column ORA_ARCHIVE_STATE 
    from our new table when we alter the row archival

    output:

    Version: 12.1.0.1.0
    Before : 140755205095424 Bits unset: 29 34 47
    After  :     17716740096 Bits unset: 29 34

    EDWIN           no archival
    EDWIN_BENCHMARK with archival    
  */
  cursor c_prop
  is
    select t.property 
    from   tab$ t
    ,      obj$ o
    where  o.obj# = t.obj#
    and    o.name = 'EDWIN'
  ;
  l_after   tab$.property%type;
  l_before  tab$.property%type;
  l_version v$instance.version%type;
  -- 
  function BITS_UNSET
    ( p_n number 
    ) return varchar2
  is
    l_power  number;
    l_return varchar2(4000);
  begin
    for l_i in 0..126
    loop
      l_power := power(2,l_i);
      -- 
      if ( l_power > p_n )
      then
        exit;
      end if;
      --
      if bitand(p_n,l_power) != 0
      then
        l_return := l_return||' '||l_i;
      end if;
    end loop;
    return trim(l_return);
  end;        
begin
  select version into l_version from v$instance;
  dbms_output.put_line('Version: '||l_version);
  --
  begin execute immediate 'drop table scott.edwin'; exception when others then null; end;
  begin execute immediate 'drop table scott.edwin_benchmark'; exception when others then null; end;
  --
  execute immediate 'create table scott.edwin_benchmark (kol number) row archival'; 
  execute immediate 'create table scott.edwin (kol number) row archival'; 
  --
  open c_prop;
  fetch c_prop into l_before;
  close c_prop;
  dbms_output.put_line('Before : '||l_before||' Bits unset: '||BITS_UNSET(l_before));
  --
  execute immediate 'alter table scott.edwin no row archival';
  --
  open c_prop;
  fetch c_prop into l_after;
  close c_prop;
  dbms_output.put_line('After  : '||l_after||' Bits unset: '||BITS_UNSET(l_after));  
  --
  for r_check in 
    ( select o.name
      ||     decode( bitand(t.property, power(2,47))
                   , 0, ' no archival'
                   , ' with archival'
                   ) as text
      from   tab$ t
      ,      obj$ o
      where  o.obj# = t.obj#
      and    o.name in ('EDWIN','EDWIN_BENCHMARK')
    )
  loop
   dbms_output.put_line(r_check.text);      
  end loop;             
end;    
/

So, the flag is bit 47 in the property column of tab$.

I guess we can wait for a future upgrade of 12c where they unlock this information. In the mean time, you can use the information above for a custom view….or just look for the existence of ORA_ARCHIVE_STATE.

Kernel32.dll with Oracle 12c

In OS commands in Oracle using Kernel32.dll I explained a way to link the kernel32.dll library to execute host commands.

But what if you had to migrate to the latest 12c version (64 bits) and you don’t want to rebuild your application to use the new features at the same time.

Well, it seems that it is still working the way I explained, but with one addition:

In %ORACLE_HOME%\hs\admin\extproc.ora set EXTPROC_DLLS to ANY (or any value that is appropriate for you).

Without it you will get an ORA-28595: Extproc agent : Invalid DLL Path

Preventing the usage of partitioning in Oracle

In the situation that you have installed the Partitioning option and you don’t want your developers to use this feature, you could use the installer or “chopt” to remove the option.

However, with these utilities you need to shut down the database and that is not always desirable.

You could also use an INSTEAD OF CREATE on DATABASE to prevent your developers to create partitioned tables:

create trigger wi_prevent_partitioning
instead of create on database
when (ora_dict_obj_type = 'TABLE')
declare
  l_sql      varchar2(32767);
  l_sql_list ora_name_list_t;
begin
  for r_i in 1 .. ora_sql_txt(l_sql_list)
  loop
    l_sql := l_sql||l_sql_list(r_i);
  end loop;
  --   
  if ( instr(lower(l_sql),'partition') > 0 )
  then
    raise_application_error
      ( -20999
      , 'Partitioning is not allowed'
      );
  else
    l_sql := replace(l_sql,chr(10),' '); 
    -- 
    l_sql := replace( l_sql
                    , 'CREATE TABLE '
                    , 'CREATE TABLE '
                    ||ora_login_user||'.'
                    );
    execute immediate l_sql;
  end if;
end;

A word of caution: I have tested this with success in a 11GR2 database with APEX 4.2.
In 10.2.0.4 with APEX 4.1 I got an ORA-600 error when I went to the application section of the builder.
So of course test, test, test.

Rollback after commit using flashback

Normally when someone asks if it is possible to rollback a single, complete transaction after a commit I have to say no. But this time I was confronted myself with this requirement. We are migrating an Oracle Forms client-server application to Oracle Apex and in one of the forms the transaction-handling was as follows:

  1. Execute some complex pl/sql that updates/inserts into several tables
  2. After completion the user can switch between several screens within the same session to look at several parameters.
  3. If necessary the user can rollback the complete transaction.

Well, in C/S the third step is just a simple ROLLBACK command, but in Apex life is not that simple.

In Apex a commit is issued after every pl/sql block – in our case a Dynamic Action- so step 3 is not that trivial any more. As we saw it we had 2 options:

  • Write some complex pl/sql that reverses the complex logic of the first step
  • Something else.

I went for “Something else”. The database version is an Oracle 10g EE, so I didn’t have all the new flashback features at hand, but it was enough to make something that works.

I created the following package like this:

create or replace package asl_transaction
as
  procedure capture_transaction
    ( p_session_id number
    );
  procedure reverse_transaction
    ( p_session_id number
    );
  procedure confirm_transaction
    ( p_session_id number
    );    
  procedure purge_old_transactions ;
end;
/
create or replace package body asl_transaction
as

type t_cache 
is
  table of rowid
  index by varchar2(2000)
;
g_cache t_cache;

procedure capture_transaction
  ( p_session_id number
  )
is
begin
  insert into apx_transaction
    ( session_id
    , xid
    , creation_date
    )
  select p_session_id
  ,      xid
  ,      sysdate
  from  v$transaction
  where addr = ( select taddr
                 from v$session
                 where sid = ( select sid
                               from v$mystat
                               where rownum = 1  ));
end;

procedure push_rowid
  ( p_rowid rowid
  )
is
begin
  g_cache(p_rowid) := p_rowid;
end;

function check_rowid
  ( p_rowid in rowid
  ) return rowid
is
begin
  if g_cache.exists(p_rowid)
  then
    return g_cache(p_rowid);
  else
    return p_rowid;
  end if;
end;

procedure reverse_transaction
  ( p_session_id number
  )
is
  l_new_rowid rowid;
begin
  savepoint inlineTransaction;
  for r_transactie in ( select xid 
                        from   apx_transaction 
                        where  session_id = p_session_id
                      )
  loop
    for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql
                   ,      row_id
                   from   sys.flashback_transaction_query qry
                   where  qry.xid         = r_transactie.xid
                   and    qry.table_name != 'ASL_APX_TRANSACTION'
                 )
    loop
      if upper(r_dml.undo_sql) like '%INSERT INTO%'
      then
        push_rowid(r_dml.row_id);
        l_new_rowid := null;
        execute immediate r_dml.undo_sql
        ||' returning rowid into :b1' returning into l_new_rowid;        
        g_cache(r_dml.row_id) := l_new_rowid;
      else
        l_new_rowid := check_rowid(r_dml.row_id);
        if ( l_new_rowid != r_dml.row_id)
        then
          r_dml.undo_sql := replace(r_dml.undo_sql,r_dml.row_id, l_new_rowid);
        end if;
        execute immediate r_dml.undo_sql;        
      end if;
    end loop;
  end loop;
  confirm_transaction(p_session_id);
end;

procedure confirm_transaction
  ( p_session_id number
  )
is
begin
  delete from apx_transaction where session_id = p_session_id;  
end;

procedure purge_old_transactions
is
begin
  delete from apx_transaction where creation_date < sysdate - 1;  
end;

end;
/

I did not include the DDL for the table APX_TRANSATION. I think you will be able to reverse engineer the statement.
Before you commit your transaction, so in Apex before the end of your PL/SQL block, you call

ASL_TRANSACTION.CAPTURE_TRANSACTION(:SESSION_ID).

The code that is executed by the former Rollback-button now has to be

ASL_TRANSACTION.REVERSE_TRANSACTION(:SESSION_ID).

In my opinion there also has to be some Confirm button that has to call

ASL_TRANSACTION.CONFIRM_TRANSACTION(:SESSION_ID).

That’s all. I included a full  test script at the end of this post.

For this package to work you’ll have to have give grant select any transaction to the package owner. If you don’t you can’t see the view sys.flashback_transaction_query.

The logic behind the package is this:

  • Before the commit, capture all the XID’s from v$transaction of all the DML that are executed in the current transaction
  • Save the XID’s with some identifying number, e.g. the session_id
  • By reversal, get the undo_sql from flashback_transaction_query and play back the transaction.
  • If original transaction deleted the row he first updated c.q. inserted, the reversal has to catch the new rowid of the insert to play back the latter playback c.q. delete.

Some words of caution:

Don’t, really don’t try to access flashback_transaction_query with anything else than a XID.

One of the features you get with an ordinary rollback is locking. The above solution does not involve a locking mechanism, yet. Ghost updates are therefore a possibility: when transaction A updates a table to X, session B updates to Y and A reverses, the update to Y is lost. In the client-server situation session B was blocked for update until A commits or rollbacks. It’s simple Oracle 1:1

However, when we look back at our options we also would have had the same problem with option 1.

Test, test and test if the given solution is appropriate in your case.

drop table emp_2
/

drop table dept_2
/

delete from apx_transaction
where session_id = -1
/

CREATE TABLE emp_2 
   (empno   NUMBER PRIMARY KEY, 
    empname VARCHAR2(16), 
    salary  NUMBER
    )
/
CREATE TABLE dept_2 
   (deptno   NUMBER, 
    deptname VARCHAR2(32)
   )
/

Prompt Insert in EMP_2 111,mike,555
INSERT INTO emp_2 VALUES (111, 'Mike', 555);
COMMIT;

prompt insert in dept_2 10,accounting
INSERT INTO dept_2 VALUES (10, 'Accounting');
COMMIT;

Prompt
Prompt Start an erroneous transaction
Prompt

UPDATE emp_2 SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept_2 VALUES (20, 'Finance'); 
DELETE FROM emp_2 WHERE empno = 111;

Prompt
Prompt Capture the transactiondata and commit
Prompt
execute asl_transaction.capture_transaction(-1);
COMMIT;

Prompt New data in the tables
Prompt EMP_2
select * from emp_2
/
Prompt DEPT_2
select * from dept_2
/
prompt
Prompt The undo-statements
begin
  for r_transactie in ( select xid from apx_transaction)
  loop
    for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql
                   ,      row_id
                   from   sys.flashback_transaction_query qry
                   where  qry.xid  = r_transactie.xid
                   and    qry.table_name != 'ASL_GTT_TRANSACTION'
                 )
    loop               
      dbms_output.put_line( r_transactie.xid||'...'||r_dml.undo_sql);
    end loop;
  end loop;
end;  
/
Prompt
Prompt Reverse the transaction
exec asl_transaction.reverse_transaction(-1)
Prompt
Prompt Check 
Prompt Has to be: 111,mike,555
select * from emp_2
/

prompt Has to be:  10,accounting
select * from dept_2
/

Oracle Native web services and SOAPUI

You can find several examples on the net to enable native webservices in an Oracle 11g Database. E.g. http://www.liberidu.com/blog/2008/07/08/howto-create-a-native-database-webservice/ is a good starting point to accomplish this.

I tried to test my webservice in SOAPUI and it didn’t work… at first.

I used the testscript from Mark Drake that can be found on OTN

clear screen
clear buffer

connect / AS sysdba

-- call dbms_xdb.sethttpport(80);
-- alter system register;

grant dba, xdbadmin to scott;

alter user scott identified by tiger account unlock;

set echo on
set termout on
set feed on
cle scre
---------------------------------------------------------
-- Show User and "orawsv" new xdbconfig.xml entry
----------------------------------------------------------

show user
--
DECLARE
  SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
  DBMS_XDB.deleteServletMapping(SERVLET_NAME);
  DBMS_XDB.deleteServlet(SERVLET_NAME);
  DBMS_XDB.addServlet
    ( NAME => SERVLET_NAME
    , LANGUAGE => 'C'
    , DISPNAME => 'Oracle Query Web Service'
    , DESCRIPT => 'Servlet for issuing queries as a Web Service'
    , SCHEMA   => 'XDB'
    );
  DBMS_XDB.addServletSecRole
    ( SERVNAME => SERVLET_NAME
    , ROLENAME => 'XDB_WEBSERVICES'
    , ROLELINK => 'XDB_WEBSERVICES'
    );
  DBMS_XDB.addServletMapping
    ( PATTERN => '/orawsv/*'
    , NAME => SERVLET_NAME
    );
END;
/

pause
--
def USERNAME=SCOTT
def PASSWORD=tiger
--
pause
clear screen
----------------------------------------------------------
-- Drop the Access Control List XML file if it exists
----------------------------------------------------------
begin
  dbms_network_acl_admin.drop_acl('/public/localhost.xml');
end;
/
commit;
pause
clear screen
----------------------------------------------------------
-- Create and Assign ACL's to localhost
----------------------------------------------------------
begin
  dbms_network_acl_admin.create_acl
    ( '/public/localhost.xml'
    , 'ACL for 127.0.0.1'
    , '&USERNAME'
    , true
    , 'connect'
    );
  dbms_network_acl_admin.assign_acl
    ( '/public/localhost.xml'
    , '127.0.0.1'
    );
end;
/
COMMIT
/
pause
clear screen
----------------------------------------------------------
-- Show and grant NDWS Roles to a user account
----------------------------------------------------------

-- Only HTTPS allowed
GRANT XDB_WEBSERVICES TO &USERNAME
/
-- HTTP also allowed
GRANT XDB_WEBSERVICES_OVER_HTTP TO &USERNAME
/
-- Access also allowed that are accessible to PUBLIC.
GRANT XDB_WEBSERVICES_WITH_PUBLIC TO &USERNAME
/
pause
clear screen

connect &USERNAME/&PASSWORD

set echo on
set termout on
set feed on
SET long 10000
clear screen

----------------------------------------------------------
-- Show the now active WSDL URL
----------------------------------------------------------

show user

var url varchar2(700)

BEGIN
  :url := 'http://&USERNAME:&PASSWORD@localhost:'|| dbms_xdb.getHttpPort()|| '/orawsv?wsdl';
end;
/

print url

pause
SET long 10000 pages 0
cle scre

----------------------------------------------------------
-- Show the output via PL/SQL when WSDL URL called
----------------------------------------------------------
SELECT httpuritype( :url ).getXML()
FROM dual
/
pause
cle scre

----------------------------------------------------------
-- Create simple function that can be called via the NDWS
----------------------------------------------------------
create or replace function GET_SQRT
  ( INPUT_VALUE number
  ) return number
as
begin
  return SQRT(INPUT_VALUE);
end;
/

pause
SET long 10000 pages 0
cle scre
----------------------------------------------------------
-- Setting the URL as input for consumption
----------------------------------------------------------
BEGIN
  :url := 'http://&USERNAME:&PASSWORD@localhost:'||dbms_xdb.getHttpPort()||'/orawsv/SCOTT/GET_SQRT';
end;
/
print url

SELECT httpuritype( :url||'?wsdl' ).getXML()
FROM   dual
/
--cle scre

----------------------------------------------------------
-- Consume NDWS function call via PL/SQL
----------------------------------------------------------
set serveroutput on
--
DECLARE
  V_SOAP_REQUEST XMLTYPE := XMLTYPE(
    '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                           xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
                           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                           xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <SOAP-ENV:Body>
        <m:SNUMBER-GET_SQRTInput xmlns:m="http://xmlns.oracle.com/orawsv/&USERNAME/GET_SQRT">
        <m:INPUT_VALUE-NUMBER-IN>2>/m:INPUT_VALUE-NUMBER-IN>
        </m:SNUMBER-GET_SQRTInput> 
      </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>'
  );
  V_SOAP_REQUEST_TEXT CLOB := V_SOAP_REQUEST.getClobVal();
  V_REQUEST           UTL_HTTP.REQ;
  V_RESPONSE          UTL_HTTP.RESP;
  V_BUFFER            VARCHAR2(1024);
BEGIN
  V_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => :URL, METHOD => 'POST');
  UTL_HTTP.SET_HEADER(V_REQUEST, 'User-Agent', 'Mozilla/4.0');
  V_REQUEST.METHOD := 'POST';
  UTL_HTTP.SET_HEADER 
    ( R     => V_REQUEST
    , NAME  => 'Content-Length'
    , VALUE => DBMS_LOB.GETLENGTH(V_SOAP_REQUEST_TEXT)
    );
  UTL_HTTP.WRITE_TEXT 
    ( R    => V_REQUEST
    , DATA => V_SOAP_REQUEST_TEXT
    );
  --
  V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST);
  LOOP
    UTL_HTTP.READ_LINE(V_RESPONSE, V_BUFFER, TRUE);
    DBMS_OUTPUT.PUT_LINE(V_BUFFER);
  END LOOP;
  UTL_HTTP.END_RESPONSE(V_RESPONSE);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY 
  THEN
    UTL_HTTP.END_RESPONSE(V_RESPONSE);
END;
/

So in SOAPUI I created a new project. As as base-WSDL I used the new http://SCOTT:TIGER@localhost:8080/orawsv/SCOTT/GET_SQRT.
In the properties of the request I entered scott and tiger (lowercase)

And it didn’t work.

Analyzing the http-log of the request I found out that two requests where send. The first one lacked “Authorization: Basic …..” key in the HTTP-header. The response from the server was a 401 HTTP-error indicating the need for a username/pwd. A second request was send with the Authorization Basic key and the server didn’t respond.

The solution is to include the key in the first request.
In the SOAPUI preference in thet HTTP section you have to check the “Adds authentication information to outgoing request”.

Restart SOAPUI and it works.