Archive for oracle

Oracle AQ

Getting Started

Create Users

 
CONNECT system/manager;
DROP USER aqadm CASCADE;
CREATE USER aqadm IDENTIFIED BY aqadm;
GRANT CONNECT, RESOURCE TO aqadm;
GRANT EXECUTE ON DBMS_AQADM TO aqadm;
GRANT Aq_administrator_role TO aqadm;
 
DROP USER aq CASCADE;
CREATE USER aq IDENTIFIED BY aq;
GRANT CONNECT, RESOURCE TO aq;
GRANT EXECUTE ON dbms_aq TO aq;
GRANT EXECUTE ON dbms_aqadm TO aq;

Create Q Table and Q of Object Type

 
connect aq/aq;
 
CREATE type aq.Message_typ AS object (
subject VARCHAR2(30),
text VARCHAR2(80));
/
 
begin
  DBMS_AQADM.CREATE_QUEUE_TABLE (
  queue_table => 'aq.objmsgs80_qtab',
  queue_payload_type => 'aq.Message_typ');
 
   DBMS_AQADM.CREATE_QUEUE (
  queue_name => 'msg_queue',
  queue_table => 'aq.objmsgs80_qtab');
 
   DBMS_AQADM.START_QUEUE (
  queue_name => 'msg_queue');
end;
/

Create a Q Table and Q of Raw Type

 
begin
  DBMS_AQADM.CREATE_QUEUE_TABLE (
  queue_table => 'aq.RawMsgs_qtab',
  queue_payload_type => 'RAW');
 
  DBMS_AQADM.CREATE_QUEUE (
  queue_name => 'raw_msg_queue',
  queue_table => 'aq.RawMsgs_qtab');
 
  DBMS_AQADM.START_QUEUE (
  queue_name => 'raw_msg_queue');
end;
/

Create a Prioritized Message Q Table and Q

 
begin
  DBMS_AQADM.CREATE_QUEUE_TABLE (
  queue_table => 'aq.priority_msg',
  sort_list => 'PRIORITY,ENQ_TIME',
  queue_payload_type => 'aq.Message_typ');
 
  DBMS_AQADM.CREATE_QUEUE (
  queue_name => 'priority_msg_queue',
  queue_table => 'aq.priority_msg');
 
  DBMS_AQADM.START_QUEUE (
  queue_name => 'priority_msg_queue');
end;
/

Drop Queue

 
begin
   DBMS_AQADM.STOP_QUEUE(
      queue_name => 'TEST_QUEUE');
 
   DBMS_AQADM.DROP_QUEUE(
      Queue_name => 'TEST_QUEUE');
 
   DBMS_AQADM.DROP_QUEUE_TABLE(
        Queue_table => 'TEST_QUEUE_TAB');
END;

References

* Oracle® Streams Advanced Queuing User's Guide and Reference Release 10.1 Part No. B10785-01
* http://www.akadia.com/services/ora_advanced_queueing.html
* AQ vs JMS
* Oracle DBMS_AQADM

Filed under: Uncategorized, oracle | |Comments off

Generate XML Schema from oracle tables

Here is a nice thread describing how to generate XML schema from Oracle tables. I tried the following function and it worked great.

* Compile the following PL/SQL function:

 
FUNCTION GEN_XML_SCHEMA(target_table varchar2) RETURN xmltype
AS
  xmlSchema XMLTYPE;
begin
  SELECT
    xmlElement(
      "xsd:schema",
      xmlAttributes(
        'http://www.w3.org/2001/XMLSchema' AS "xmlns:xsd",
        'http://xmlns.oracle.com/xdb' AS "xmlns:xdb",
        'http://' || target_table || '/xsd' AS "targetNamespace",
        'http://' || target_table || '/xsd' AS "xmlns:tns",
        'qualified' AS "elementFormDefault"
      ),
      xmlElement(
        "xsd:element",
        xmlAttributes(
          table_name || 'S' AS "name",
          'tns:' || table_name || 'S_T' AS "type"
        )
      ),
      xmlElement(
        "xsd:complexType",
        xmlAttributes (
          table_name || 'S_T' AS "name"
        ),
        xmlElement (
          "xsd:sequence",
          xmlElement (
            "xsd:element",
            xmlAttributes (
              table_name AS "name",
              'tns:' || table_name || '_T' AS "type",
              'unbounded' AS "maxOccurs"
            )
          )
        )
      ),
      xmlElement (
        "xsd:complexType",
        xmlAttributes (
          table_name || '_T' AS "name"
        ),
        xmlElement (
          "xsd:sequence",
          (
            xmlAgg(ELEMENT)
          )
        )
      )
    )
  INTO xmlSchema
  FROM (
    SELECT TABLE_NAME, INTERNAL_COLUMN_ID,
      case
        when DATA_TYPE IN ('VARCHAR2', 'CHAR') then
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            ),
            xmlElement (
              "xsd:simpleType",
              xmlElement (
                "xsd:restriction",
                xmlAttributes (
                  'xsd:string' AS "base"
                ),
                xmlElement (
                  "xsd:maxLength",
                  xmlAttributes (
                  DATA_LENGTH AS "value"
                  )
                )
              )
            )
          )
        when DATA_TYPE = 'DATE' then
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              --'xsd:dateTime' as "type",
              'xsd:date' AS "type",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            )
          )
        when DATA_TYPE = 'NUMBER' then
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              decode(DATA_SCALE, 0, 'xsd:integer', 'xsd:double') AS "type",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            )
          )
        else
          xmlElement (
            "xsd:element",
            xmlattributes (
              column_name AS "name",
              'xsd:anySimpleType' AS "type",
              decode(NULLABLE, 'Y', 0, 1) AS "minOccurs",
              column_name AS "xdb:SQLName",
              DATA_TYPE AS "xdb:SQLTYPE"
            )
          )
      end ELEMENT
    FROM user_tab_cols c
    WHERE TABLE_NAME = target_table
    ORDER BY internal_column_id
  )
  GROUP BY TABLE_NAME;
 
  RETURN xmlSchema;
end;

* Run the function:

 
-- Complete schema
SELECT gen_xml_schema('MY_TABLE').extract('/*') FROM dual;
 
-- Select element and complexType
SELECT gen_xml_schema('RF_TAG_TXN').extract('/schema/element | /schema/complexType',
'xmlns="http://www.w3.org/2001/XMLSchema"') FROM dual;

* Generate schema from multiple tables:

 
CREATE OR REPLACE DIRECTORY dir_temp AS
    'C:\TEMP';
 
DECLARE
  TYPE tableNameArray IS varray(100) of VARCHAR2(64);
  tableNames tableNameArray;
 
  outputFileName VARCHAR2(100) := 'MY_SCHEMA.xsd';
  outputFileHandler UTL_FILE.file_type;
 
  tableXmlType xmlType;
  xmlRootElementBegin VARCHAR2(500) :=
    '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xdb="http://xmlns.oracle.com/xdb"
    targetNamespace="http://my.db/xsd"
    xmlns:tns="http://my.db/xsd"
    elementFormDefault="qualified">';
  xmlRootElementEnd VARCHAR2(500) := '</xsd:schema>';
BEGIN
  -- Database tables
  tableNames := tableNameArray(
    'TABLE1',
    'TABLE2');
 
  -- Open schema file to write
  outputFileHandler := UTL_FILE.fopen('DIR_TEMP', outputFileName, 'W');
  -- Write root element begin tag
  UTL_FILE.put_line(outputFileHandler, xmlRootElementBegin);
 
  -- Loop through all tables
  FOR tblName IN 1 .. tableNames.count loop
    DBMS_OUTPUT.put_line('Processing table: ' || tableNames(tblName));
 
    -- Generate xml schema from each table
    SELECT gen_xml_schema(tableNames(tblName)).extract(
        '/schema/element|/schema/complexType',
        'xmlns="http://www.w3.org/2001/XMLSchema"')
    INTO tableXmlType
    FROM dual;
 
    -- Write xml schema
    IF tableXmlType IS NOT NULL THEN
      UTL_FILE.put_line(outputFileHandler, tableXmlType.getStringVal());
    END IF;
  END loop;
 
  -- Write root element end tag
  UTL_FILE.put_line(outputFileHandler, xmlRootElementEnd);
  -- Close file
  UTL_FILE.fclose(outputFileHandler);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

Filed under: oracle | |No Comments

GoldenGate: Java Adapter Properties

Properties

* Two kinds of properties:
- User exit properties
- Java application properties
* Set property file locations in user exit extract parameter file

 
SetEnv ( GGS_USEREXIT_CONF = "dirprm/cuserexit.properties" )
SetEnv ( GGS_JAVAUSEREXIT_CONF = "dirprm/javaue.properties" )

User Exit Properties

Logging Properties

 
# log file name prefix
goldengate.log.logname=cuserexit
# log level
goldengate.log.level=ERROR | WARN | INFO | DEBUG
# where to log
goldengate.log.tostdout=false
goldengate.log.tofile=true
# advanced logging
goldengate.log.modules=GENUSEREXIT,LOGMALLOC,JAVAWRITER
goldengate.log.level.LOGMALLOC=ERROR

Generic Properties

 
# do not change this
goldengate.userexit.writers=javawriter
# user exit checkpoint file prefix
goldengate.userexit.chkptprefix=javaue_

JVM Boot Options

 
javawriter.bootoptions=-Djava.class.path=javaue/ggue.jar -Dlog4j.configuration=log4j.properties -Xmx256m -Xms128m

Statistics and Reporting

 
javawriter.stats.display=TRUE
# output both Java and C statistics
javawriter.stats.full=TRUE
# report statistics every 10 min (default is 1 hr)
javawriter.stats.time=600
# report statistics every 600 records (default is 10000 records)
javawriter.stats.numrecs=100

Java Application Properties

Properties for All Handlers

 
# what handler(s) to use
gg.handlerlist=handler1, handler2
# handler type
gg.handler.handler1.type={jms | jms_map | singlefile | rolling | stdout | stderr | log | com.foo.MyHandler}

Properties for Formatting Output

 
# formatting (default to built-in XML formatter)
# use Velocity template for formatting
gg.handler.handler1.format=path/to/sample.vm
# use custom Java class for formatting
gg.handler.handler1.format=com.mycompany.MyFormat
 
# include tables
gg.handler.handler1.includeTables=foo.customer, bar.orders
# exclude tables
gg.handler.handler1.excludeTables=dt_modifydate
 
# output one operation (op) or one transactioni (tx) per message
gg.handler.handler1.mode=op | tx
# use with custom formatter
gg.handler .handler1.format.mode

Properties for CSV and Fixed-format Output

 
gg.handler.my_jms_handler1.type=jms_text
gg.handler.my_jms_handler1.format=csv | fixed
gg.handler.my_jms_handler1.properties=/dirprm/my.properties
 
##############
# in my.properties  #
##############
delim=,
quote='
metacols=opcode, table, txind, position
 
missingColumnChar=M
presentColumnChar=P
nullColumnChar=N
 
beginTxChar=B
middleTxChar=M
endTxChar=E
wholeTxChar=W
 
insertChar=INS
updateChar=UPD
deleteChar=DEL
 
endOfLine=CR
justify=left
includeBefores=false

File Writer Properties

 
# name of the output file (default to output.xml)
gg.handler.filehandler.file=output.xml
# append upon restart
gg.handler.filehandler.append=true
# rollover when file size is reached (default to 10M)
gg.handler.filehandler.rollover.size=5M

Standard JMS Properties

 
# destination to use
gg.handler.jmshandler.destination=gg.myqueue
# jms user and password
gg.handler.jmshandler.user
gg.handler.jmshandler.password
# queue or topic
gg.handler.jmshandler.queueortopic=queue
# persistent destination or not
gg.handler.jmshandler.persistent=true | false
# JMS priority (default to 4)
gg.handler.jmshandler.priority=0 - 9
# timetolive (default to zero)
gg.handler.jmshandler.timetolive=0
# connection factory
gg.handler.jmshandler.connectionfactory
gg.handler.jmshandler.usejndi=true | false
# if usejndi=false
gg.handler.jmshandler.connectionUrl=tcp://host:61616?jms.useAsyncSend=true
gg.handler.jmshandler.connection.factoryclass
# for testing purpose, stop sending JMS messages
gg.handlerlist.nop=true
 
# include from a property file
gg.handler.jmshandler.properties=activemq-default.properties

General Properties

 
# set additional classpath
gg.classpath
# template for customizing the report format
gg.report.format

Filed under: oracle | |Comments off

Oracle ESB: AQ JMS Adapter Sample

Downloads

* Download Lesson07: OEMS: JMS (AQ) to DB PL/SQL
* Download ESBSamples.zip from oracle.

Configurations

OC4J: application.xml

* Located in j2ee\oc4j_soa\config directory.

 
<resource-provider class="oracle.jms.OjmsContext" name="OEMS">
  <description>Resource provider for the OEMS database</description>
<property name="url" value="jdbc:oracle:thin:jmsuser/jmsuser@localhost:1521:orcl" />
</resource-provider>

JMS Adapter: oc4j-ra.xml

* Located in j2ee\oc4j_soa\application-deployments\default\JmsAdapter directory.

 
<connector-factory location="eis/Jms/OEMS" connector-name="JmsAdapter">
  <config-property name="connectionFactoryLocation" value="java:comp/resource/OEMS/TopicConnectionFactories/myTCF"/>
  <config-property name="factoryProperties" value=""/>
  <config-property name="acknowledgeMode" value="AUTO_ACKNOWLEDGE"/>
  <config-property name="isTopic" value="true"/>
  <config-property name="isTransacted" value="true"/>
  <config-property name="username" value="jmsuser"/>
  <config-property name="password" value="jmsuser"/>
  <connection-pooling use="none">
  </connection-pooling>
  <security-config use="none">
  </security-config>
</connector-factory>

Database Adapter: oc4j-ra.xml

* Located in j2ee\oc4j_soa\application-deployments\default\DbAdapter directory.

 
<connector-factory location="eis/DB/EmployeeDB" connector-name="Database Adapter">
  <config-property name="xADataSourceName" value="jdbc/EmployeeDB-XA"/>
  <config-property name="dataSourceName" value="jdbc/EmployeeDB"/>
  <config-property name="platformClassName" value="oracle.toplink.platform.database.Oracle9Platform"/>
  <config-property name="usesNativeSequencing" value="true"/>
  <config-property name="sequencePreallocationSize" value="50"/>
  <config-property name="defaultNChar" value="false"/>
  <config-property name="usesBatchWriting" value="true"/>
  <connection-pooling use="none">
  </connection-pooling>
  <security-config use="none">
  </security-config>
</connector-factory>

Database Adapter: data-sources.xml