ORA-19110: unsupported XQuery expression

One of the frustrations I have found with XML functions in the latest Oracle releases is when things that used to be straightforward are now more difficult to achieve.

Consider the following simple script that extracts some fields from a small XHTML document:

set serveroutput on

DECLARE
  k_ns1 CONSTANT VARCHAR2(100) := 'http://www.w3.org/1999/xhtml';
  k_ns2 CONSTANT VARCHAR2(100) := 'xmlns="'||k_ns1||'"';
  k_xml CONSTANT XMLTYPE := XMLTYPE('<html '||k_ns2||'>
<head>
  <title>Test Page</title>
</head>
<body>
  <div id="wrapper">
    <div id="main">
      <h1>Test Heading</h1>
      <p>Para 1</p>
      <p>Para 2</p>
      <h2>List Heading</h2>
      <ul>
        <li><a href="/link1.html">Item 1</a></li>
        <li>Item 3</li>
        <li><a href="/link2.html">Item 3</a></li>
      </ul>
    </div>
  </div>
</body>
</html>');
BEGIN
  FOR r IN
  ( SELECT extractvalue(x.column_value, 'div/h1/text()', k_ns2) h1
         , extractvalue(x.column_value, '(div//a)[2]/@href', k_ns2) link2
    FROM   table(xmlsequence(extract(k_xml, '//div[@id="main"]', k_ns2))) x
  )
  LOOP
    dbms_output.put_line('H1: '||r.h1);
    dbms_output.put_line('LINK2: '||r.link2);
  END LOOP;
END;
/

The intention is to select the text of <h1> and the “href” attribute of the second <a>, both within the <div> with id “main”. The output is:

H1: Test Heading
LINK2: /link2.html

This test uses the extractvalue() and extract() functions, which have been deprecated since 11gR2. So let’s change to use the supported xmltable() function (for brevity I’ve only shown the part of the script that has changed):

  ( SELECT x.h1
         , x.link2
    FROM   xmltable( XMLNAMESPACES(DEFAULT 'http://www.w3.org/1999/xhtml')
                   , '//div[@id="main"]'
                     PASSING k_xml
                     COLUMNS h1    VARCHAR2(4000) PATH 'h1/text()'
                           , link2 VARCHAR2(4000) PATH '(//a)[2]/@href'
                   ) x
  )

This raises: ORA-19110: unsupported XQuery expression. Commenting out a couple of lines demonstrates that this is owing to the “(//a)[2]” XPath.

Note that I have also changed the path for the “h1″ column so that the “div” is not referenced. Using “div/h1/text()” gives a NULL value, whereas “/div/h1/text()” works fine. Documented examples seem to omit the root element, hence I use just “h1/text()”

In the past I have found a workaround to this is to use “string-join” in the PATH expression to capture all the “a/@href” attributes, and then in the SELECT to use “substr” to extract the one that is required.

However a better solution seems to be to avoid simply re-formulating the XPaths used with the deprecated functions, and instead embrace the full power of XQuery:

  ( SELECT x.h1
         , x.link2
    FROM   xmltable( XMLNAMESPACES(DEFAULT 'http://www.w3.org/1999/xhtml')
                   , 'for $d in //div
                      where $d/@id = "main"
                      return <result>
                               {$d/h1}
                               {($d//a)[2]}
                             </result>
                     '
                     PASSING k_xml
                     COLUMNS h1    VARCHAR2(4000) PATH 'h1/text()'
                           , link2 VARCHAR2(4000) PATH 'a/@href'
                   ) x
  )

This uses a FLWOR expression to select the containing <div> with the required “id” attribute. It then constructs a <result> element containing the elements of interest. In particular, the “($d//a)[2]” works fine, and its “href” attribute can be selected by the PATH expression.

A verbose solution definitely, but at least it is using supported functions and does not require an inelegant workaround.

Platform Information

The material in this article was most recently tested against Oracle 12.1.0.1.0 on 64-bit Linux.

Posted in XML | Tagged , | Leave a comment

ORA-29024: Certificate validation failure

This post is in response to a couple of comments to my earlier post about working with UTL_SMTP.STARTTLS. It describes in detail how to resolve the issue for this scenario, but the principles should apply equally to other applications where ORA-29024 is raised. The demonstration is from a Linux environment, however the tools should be applicable to other environments as well.

The first step is to connect to the server and see the certificate that it is presenting, and more especially the Certificate Authority that issued the root certificate:

$ openssl s_client -connect podNNNNN.outlook.com:587 -starttls smtp
CONNECTED(00000003)
depth=3 /C=IE/O=Baltimore/OU=CyberTrust/CN=Baltimore CyberTrust Root
verify return:1
depth=2 /CN=Microsoft Internet Authority
verify return:1
depth=1 /DC=com/DC=microsoft/DC=corp/DC=redmond/CN=MSIT Machine Auth CA 2
verify return:1
depth=0 /C=US/ST=Washington/L=Redmond/O=Microsoft Corporation/OU=Exchange/CN=*.outlook.com
verify return:1
---
Certificate chain
 0 s:/C=US/ST=Washington/L=Redmond/O=Microsoft Corporation/OU=Exchange/CN=*.outlook.com
   i:/DC=com/DC=microsoft/DC=corp/DC=redmond/CN=MSIT Machine Auth CA 2
 1 s:/DC=com/DC=microsoft/DC=corp/DC=redmond/CN=MSIT Machine Auth CA 2
   i:/CN=Microsoft Internet Authority
 2 s:/CN=Microsoft Internet Authority
   i:/C=IE/O=Baltimore/OU=CyberTrust/CN=Baltimore CyberTrust Root
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIGqTCCBZGgAwIBAgIKHwZHMwABAABLlzANBgkqhkiG9w0BAQUFADCBgDETMBEG
CgmSJomT8ixkARkWA2NvbTEZMBcGCgmSJomT8ixkARkWCW1pY3Jvc29mdDEUMBIG
CgmSJomT8ixkARkWBGNvcnAxFzAVBgoJkiaJk/IsZAEZFgdyZWRtb25kMR8wHQYD
VQQDExZNU0lUIE1hY2hpbmUgQXV0aCBDQSAyMB4XDTEzMDMwNzAwMjEyOFoXDTE1
MDMwNzAwMjEyOFowfzELMAkGA1UEBhMCVVMxEzARBgNVBAgTCldhc2hpbmd0b24x
EDAOBgNVBAcTB1JlZG1vbmQxHjAcBgNVBAoTFU1pY3Jvc29mdCBDb3Jwb3JhdGlv
bjERMA8GA1UECxMIRXhjaGFuZ2UxFjAUBgNVBAMMDSoub3V0bG9vay5jb20wggEi
MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCnRuQFhPkgI5QZZgxG0zb5BETO
8R+9fjyek60Apw09xDhV4eGUOKeZmrt+YwF1rVWFSsOB2FAkgV90MAKCxCPbyNyZ
oiqHbyuoXyCJMTPR6n+iI/TuJvcRiMZ8KNvX60gpCL+Ox++cN6+ZNJs0kflF09/R
9EDdr2GqJKg6GKHSZANsx2Xst/ThRyYVszImmz7r+r8JlGyH6uC8b5DlnNyofpF8
aPTg5IQFn81SVvLSmlXpe6TsdHki71a8n7Z/0D4iTI54ft6NyXDf5NcmsYNAZowS
viUkSpUtXq17pYUQBDiU0F7Fl+gUgLGKrccpLQ+0pg5y79rvSU1MbVrCDrEjAgMB
AAGjggMjMIIDHzALBgNVHQ8EBAMCBLAwHQYDVR0lBBYwFAYIKwYBBQUHAwIGCCsG
AQUFBwMBMHgGCSqGSIb3DQEJDwRrMGkwDgYIKoZIhvcNAwICAgCAMA4GCCqGSIb3
DQMEAgIAgDALBglghkgBZQMEASowCwYJYIZIAWUDBAEtMAsGCWCGSAFlAwQBAjAL
BglghkgBZQMEAQUwBwYFKw4DAgcwCgYIKoZIhvcNAwcwHQYDVR0OBBYEFExxJMC8
b1ziMFlE9bOTDsxdV/shMB8GA1UdIwQYMBaAFOvbEV74CZ7Y1mKc/WKd44RKKOEn
MIHuBgNVHR8EgeYwgeMwgeCggd2ggdqGT2h0dHA6Ly9tc2NybC5taWNyb3NvZnQu
Y29tL3BraS9tc2NvcnAvY3JsL01TSVQlMjBNYWNoaW5lJTIwQXV0aCUyMENBJTIw
MigxKS5jcmyGTWh0dHA6Ly9jcmwubWljcm9zb2Z0LmNvbS9wa2kvbXNjb3JwL2Ny
bC9NU0lUJTIwTWFjaGluZSUyMEF1dGglMjBDQSUyMDIoMSkuY3JshjhodHRwOi8v
Y29ycHBraS9jcmwvTVNJVCUyME1hY2hpbmUlMjBBdXRoJTIwQ0ElMjAyKDEpLmNy
bDCBrQYIKwYBBQUHAQEEgaAwgZ0wVQYIKwYBBQUHMAKGSWh0dHA6Ly93d3cubWlj
cm9zb2Z0LmNvbS9wa2kvbXNjb3JwL01TSVQlMjBNYWNoaW5lJTIwQXV0aCUyMENB
JTIwMigxKS5jcnQwRAYIKwYBBQUHMAKGOGh0dHA6Ly9jb3JwcGtpL2FpYS9NU0lU
JTIwTWFjaGluZSUyMEF1dGglMjBDQSUyMDIoMSkuY3J0MD8GCSsGAQQBgjcVBwQy
MDAGKCsGAQQBgjcVCIPPiU2t8gKFoZ8MgvrKfYHh+3SBT4PC7YUIjqnShWMCAWQC
AQowJwYJKwYBBAGCNxUKBBowGDAKBggrBgEFBQcDAjAKBggrBgEFBQcDATAsBgNV
HREEJTAjgg0qLm91dGxvb2suY29tghIqLmV4Y2hhbmdlbGFicy5jb20wDQYJKoZI
hvcNAQEFBQADggEBAJX0wAFlJXc+hpRBt3+hIzSgQ4bGNfjm2lUxigvi0snP0F4m
RKzdgYb2OFFdsnaQOsOXQchw9hwEikTG46ts1OKKCeO7JZEHzKk5g7tIRUlGY2k0
fhXu2Cs9iew1OHi3Y34S1NF6EBdds9eSstfeVtFooE4K7l5Z3pK3LJlVCY5VNtWg
WtC7IZwirC2xzIe7BBlOuuDKyzUV+d8gPi4gsas+JT71WnAw2pFbxS+goOL8ZyyS
g/nont9VXbuuiLOXX98490t6lo9V/eu5vxShpXpJjp991DPPuMZsQJGUeKUMUFrZ
pGg2TVVnj44unjC0XjhDU/+U6hSc6XDzSSP5kcI=
-----END CERTIFICATE-----
subject=/C=US/ST=Washington/L=Redmond/O=Microsoft Corporation/OU=Exchange/CN=*.outlook.com
issuer=/DC=com/DC=microsoft/DC=corp/DC=redmond/CN=MSIT Machine Auth CA 2
---
No client certificate CA names sent
---
SSL handshake has read 5018 bytes and written 482 bytes
---
New, TLSv1/SSLv3, Cipher is AES128-SHA
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
SSL-Session:
    Protocol  : TLSv1
    Cipher    : AES128-SHA
    Session-ID: CF30000024B17E1F4EA1C727541A3D73ABAD244E3487418DE34040E96AB1B537
    Session-ID-ctx:
    Master-Key: BBC9FF815105D9E6E577CD80C2C1F32AD6170DE35E57B34074E82518774CFCF765076D730A5F1E1B93E9F40B1CEF6742
    Key-Arg   : None
    Krb5 Principal: None
    Start Time: 1365602158
    Timeout   : 300 (sec)
    Verify return code: 0 (ok)
---
250 CHUNKING

^C to cancel

From this we can see that the signer is /C=IE/O=Baltimore/OU=CyberTrust/CN=Baltimore CyberTrust Root.

The next step is to see if this is in our wallet:

$ orapki wallet display -wallet wallets/ewallet.p12
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: 
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

We can see that the required certificate is not in the list, there’s no mention of Baltimore CyberTrust Root.

You can find the root certificate in several places. Here’s one way to do it from Internet Explorer:

  • Internet Options => Content => Certificates => Trusted Root Certification Authorities
  • Highlight “Baltimore CyberTrust Root” => Export… => Base-64 encoded X.509 (.CER)
  • Select a filename (we’ll use baltimore.cer) and export it

Now copy the file to the database server and add it into the wallet:

$ orapki wallet add -wallet wallets/ewallet.p12 -trusted_cert -cert baltimore.cer
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

And now check it’s there:

$ orapki wallet display -wallet wallets/ewallet.p12
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:  le
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=Baltimore CyberTrust Root,OU=CyberTrust,O=Baltimore,C=IE
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Posted in Network | Tagged | 1 Comment

Collection cardinality in Oracle 12c

I’ve written in the past (see 1 and 2) about some of the limitations Oracle has when dealing with the cardinality of table functions and collections, especially when these are used in PL/SQL and bound in as variables. So I thought it would be interesting to see how things have changed in Oracle 12c.

Collections

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000)
/

DECLARE
  x varchar2_ntt := varchar2_ntt('A','B','C');
  y varchar2_ntt;
BEGIN
  SELECT /*+ Q1 */ t.*
  BULK COLLECT INTO y
  FROM   TABLE(x) t;
END;
/

A simple table type, with a collection of 3 elements passed in as a bind variable. Previously this would have given a cardinality of 8168 (for a database with 8KB default block size). Here’s the plan from executing this in 12c:

SQL_ID  874d5mfj85gk2, child number 0
-------------------------------------
SELECT /*+ Q1 */ T.* FROM TABLE(:B1 ) T

Plan hash value: 4118387986

-------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |        |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH|      |      3 |     6 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

It can be seen that the optimizer has estimated 3 rows, and it wasn’t necessary to request dynamic sampling or work with the extensible optimizer!

Table Functions

DECLARE
  l_xml XMLTYPE := XMLTYPE('<a><b>1</b><b>2</b></a>');
  l_cnt BINARY_INTEGER;
BEGIN
  SELECT /*+ Q2 */ count(*)
  INTO   l_cnt
  FROM   XMLTABLE('/A/B' PASSING l_xml COLUMNS b_val INTEGER PATH '/B/text()');
END;
/

This example uses the XMLTABLE function, and we would expect a cardinality of 2:

SQL_ID  4ngjhs2nh731u, child number 0
-------------------------------------
SELECT /*+ Q2 */ COUNT(*) FROM XMLTABLE('/A/B' PASSING :B1 COLUMNS
B_VAL INTEGER PATH '/B/text()')

Plan hash value: 1487578152

-----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | E-Rows | Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |        |    29 (100)|          |
|   1 |  SORT AGGREGATE                    |                       |      1 |            |          |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |   8168 |    29   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

What a shame, nothing has changed! However there is hope with the introduction of cardinality feedback. I had intended to demonstrate this here, but although the cardinality feedback seems to work, the query I tested with would not change plan. I shall investigate further and hopefully the results will be the subject of a future blog.

Platform Information

The material in this article was most recently tested against Oracle 12.1.0.1.0 on 64-bit Linux.

Posted in Optimizer | Tagged | Leave a comment

ALTER TABLE … MOVE with LOBs and XML

This week I was asked a question about ALTER TABLE … MOVE in relation to LOB and XML segments. I thought I knew the answer, but wasn’t 100% confident. After some testing I found that it didn’t behave as I thought. Here’s a summary of how this command works.

Begin by creating some tablespaces to work with, and a table with a normal data segment, a LOB segment and an XML segment.

CREATE TABLESPACE ts1
/

CREATE TABLESPACE ts2
/

CREATE TABLESPACE ts3
/

CREATE TABLESPACE ts4
/

CREATE TABLE t_test
( col_vc2  VARCHAR2(4000)
, col_clob CLOB
, col_xml  XMLTYPE
)
TABLESPACE ts1
LOB (col_clob) STORE AS SECUREFILE t_test_clob (TABLESPACE ts2 DISABLE STORAGE IN ROW RETENTION CACHE)
XMLTYPE COLUMN col_xml STORE AS SECUREFILE BINARY XML t_test_xml (TABLESPACE ts3 DISABLE STORAGE IN ROW RETENTION CACHE)
/

INSERT INTO t_test VALUES
( lpad('X', 4000, 'X')
, lpad('X', 4000, 'X')
, xmlelement("X", lpad('X', 4000, 'X'))
)
/

COMMIT
/

Check where the segments have been created:

SELECT   segment_name
       , tablespace_name
       , header_file
       , header_block
FROM     dba_segments
WHERE    segment_name LIKE 'T_TEST%'
ORDER BY 1
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS1                                      9          130
T_TEST_CLOB                    TS2                                     10          129
T_TEST_XML                     TS3                                     11          129

Now let’s move the table, without giving any further specification:

ALTER TABLE t_test MOVE
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS1                                      9          162
T_TEST_CLOB                    TS2                                     10          129
T_TEST_XML                     TS1                                      9          137

From this it can be seen that the table data segment has moved within TS1 as you would expect. However the LOB segment has not moved; it is still in TS2 at the same position. What is more interesting is that the XML segment has moved to TS1, the same as the table data segment!

The next test is to specify the tablespace for the move:

ALTER TABLE t_test MOVE
TABLESPACE ts4
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS4                                     12          154
T_TEST_CLOB                    TS2                                     10          129
T_TEST_XML                     TS4                                     12          129

Again, the XML segment has followed the table data segment and the LOB segment is untouched. Leaving the XML for now, let’s make sure the LOB segment moves:

ALTER TABLE t_test MOVE
TABLESPACE ts4
LOB (col_clob) STORE AS SECUREFILE (TABLESPACE ts4)
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS4                                     12          178
T_TEST_CLOB                    TS4                                     12          129
T_TEST_XML                     TS4                                     12          153

So to move the LOB segment(s) associated with a table, you must specify them explicitly. Let’s see if we can control the XML segment too. Here we will try to move all the segments within their original tablespaces:

ALTER TABLE t_test MOVE
TABLESPACE ts1
LOB (col_clob) STORE AS SECUREFILE (TABLESPACE ts2)
LOB (col_xml) STORE AS SECUREFILE (TABLESPACE ts3)
/

ORA-00904: "COL_XML": invalid identifier

As might be expected, we cannot refer to the XML LOB segment using its column name, we need to use the internal column name (for example, by querying DBA_LOBS).

ALTER TABLE t_test MOVE
TABLESPACE ts1
LOB (col_clob) STORE AS SECUREFILE (TABLESPACE ts2)
LOB (SYS_NC00004$) STORE AS SECUREFILE (TABLESPACE ts3)
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS1                                      9          138
T_TEST_CLOB                    TS2                                     10          161
T_TEST_XML                     TS3                                     11          161

So in summary:

  • LOB segments will not move by default; you need to be explicit if you want to include them in the move.
  • XML segments are inconsistent in this respect, by default they move to the table data segment’s tablespace (whether specified or not); you must be explicit to ensure they will remain in the correct tablespace (but they will always move).

Platform Information

The material in this article was most recently tested against Oracle 11.2.0.3.6 on 64-bit Linux.

Posted in XML | Tagged , , | Leave a comment

Error Stack and PLSQL_OPTIMIZE_LEVEL

PL/SQL developers will be familiar with Oracle’s reporting of the error stack when an exception is raised. Consider the following example that creates a package containing three procedures:

CREATE OR REPLACE PACKAGE test_package
IS
  PROCEDURE proc1;

END test_package;
/

CREATE OR REPLACE PACKAGE BODY test_package
IS
  PROCEDURE proc3
  IS
  BEGIN
    RAISE PROGRAM_ERROR;

  END proc3;

  PROCEDURE proc2
  IS
  BEGIN
    proc3;

  END proc2;

  PROCEDURE proc1
  IS
  BEGIN
    proc2;

  END proc1;

END test_package;
/

Now we call the public procedure:

BEGIN
  test_package.proc1;
EXCEPTION
  WHEN OTHERS
  THEN
    dbms_output.put_line(dbms_utility.format_error_stack);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
/

This gives us the expected output, showing the original exception with its line number, the line numbers of the procedure calls where it was not caught, and finally the line number in the calling anonymous block.

ORA-06501: PL/SQL: program error

ORA-06512: at "TEST_USER.TEST_PACKAGE", line 6
ORA-06512: at "TEST_USER.TEST_PACKAGE", line 13
ORA-06512: at "TEST_USER.TEST_PACKAGE", line 20
ORA-06512: at line 2

However recently I came across a case where this was not happening as expected. After some investigation I found that the cause was setting the PLSQL_OPTIMIZE_LEVEL from the default value of 2 up to 3:

DROP PACKAGE test_package
/

ALTER SESSION SET plsql_optimize_level=3
/

Now repeat the package creation and test script from above, and the output is:

ORA-06501: PL/SQL: program error

ORA-06512: at "TEST_USER.TEST_PACKAGE", line 20
ORA-06512: at line 2

Notice how the error stack has been lost, apart from the final call within the package and the anonymous block.

A little bit of further investigation has shown that this only happens with subprograms calling one another within the same package. Calls to standalone procedures and functions, and other packages, are still present in the error stack. Also, it makes no difference whether the package subprograms are public or private, the error stack is lost either way.

Platform Information

The material in this article was most recently tested against Oracle 11.2.0.3.6 on 64-bit Linux.

Posted in PL/SQL | Tagged , , | Leave a comment

Data Pump Export of Public Synonyms

Oracle’s Data Pump Export has lots of ways to filter the objects you would like to include in an export. My requirement was to export a subset of a database’s schemas, including all public synonyms created for objects in those schemas. I thought this would be easy, but it turned out not to be straightforward. I found a few articles on the internet with clues to a solution, but nothing that covered exactly what I needed to do. This article presents my solution to the problem for a much-simplified test case.

Test Setup

Create a new schema, an object within it, and a public synonym:

CREATE USER test_user IDENTIFIED BY xxx
/
CREATE TABLE test_user.test_table (id INTEGER)
/
CREATE PUBLIC SYNONYM test_table_syn FOR test_user.test_table
/

Schema Export

First, let’s try a simple schema export. For brevity I’ll only give the pertinent parameters to Data Pump Export for each example.

SCHEMAS=TEST_USER

Now if we use the SQLFILE parameter of Data Pump Import to view what is in the dump file, we see only the CREATE TABLE; the public synonym is not included.

Full Export #1

My first clue was that I should be doing a FULL mode export, and filtering the schemas. Let’s begin with a very simple test to check we can export the public synonyms in isolation:

FULL=YES
INCLUDE=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner = 'TEST_USER')"

Note use of the embedded SELECT statement to generate the list of synonyms. That’s not necessary for this trivial example, but demonstrates the syntax I used to restrict the export to the subset of schemas I needed, without actually listing them all.

Viewing the SQLFILE output shows that the public synonym has been exported.

Full Export #2

Thinking I’d solved the problem, I then added in a filter to include my schemas.

FULL=YES
INCLUDE=SCHEMA:"IN (SELECT username FROM dba_users WHERE username = 'TEST_USER')"
INCLUDE=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner = 'TEST_USER')"

So I was surprised to find that this included the schema objects but not the public synonyms!

Solution

The key to the problem is that the public synonyms are owner by PUBLIC, and by filtering on schemas these objects are excluded. So the solution is to include PUBLIC in the list of schemas. Note that as PUBLIC is role not a user, it doesn’t exist in DBA_USERS so needs to be included with a UNION.

FULL=YES
INCLUDE=SCHEMA:"IN (SELECT 'PUBLIC' FROM DUAL UNION SELECT username FROM dba_users WHERE username = 'TEST_USER')"
INCLUDE=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner = 'TEST_USER')"

Now the SQLFILE output shows the public synonym and the table have been exported.

Platform Information

The material in this article was most recently tested against Oracle 11.2.0.3.6 on 64-bit Linux.

Posted in Data Pump | Tagged , , | Leave a comment

Setting ODCIEnv Debug Level

Oracle’s “Extensible Indexing” feature provides a framework to create domain indexes to meet particular indexing requirements. Oracle Text and Oracle Spatial are examples provided as options for the database, but the framework allows us to create indexes for efficient access to any user-defined type.

When creating the object type to support a new indextype, many of the methods pass a parameter for the environment, of type ODCIEnv. This includes attributes for whether debugging is on, and if so the debug level. However the documentation does not say how to set this debug level when developing the code! The 10g documentation provides a clue in the “What’s New in Data Cartridges” chapter:

Passing debugging information to the index implementation methods: Based on system level settings, the index type code sometimes needs to dump debugging information. A new system level event is introduced, which if set is passed in to the index type methods and the methods can then selectively dump the debugging information.

To cut a long story short, I eventually found the event to set by searching the messages file $ORACLE_HOME/rdbms/mesg/oraus.msg

29891, 00000, "enable debugging in ODCI user-defined routines"
// *Document: NO
// *Cause: For internal use only
// *Action: THIS IS NOT A USER ERROR NUMBER/MESSAGE. THIS DOES NOT NEED TO BE
// TRANSLATED OR DOCUMENTED. IT IS USED FOR SETTING A NEW EVENT FOR
// DEBUGGING SUPPORT IN ODCI USER-DEFINED ROUTINES

Now, if I set this and call ODCIEnvDump() in my methods I can see the debug level is being set:

ALTER SESSION SET EVENTS '29891 trace name context forever, level 1';
...
ODCIEnv
      Debugging is ON
      DebugLevel is 1

Setting the level to 0, or using “context off” switches off debugging.

Platform Information

The material in this article was most recently tested against Oracle 11.2.0.3.6 on 64-bit Linux.

Posted in Extensibility | Tagged , , | Leave a comment