iAdvise
ApEx: HTTP-400 Too many arguments passed
We got this error message because there are to many page items generated on the page. We could discuss why a page would need so many aguments but I leave that in the middle.
After some browsing on the oracle apex forum, I came accross the tip to set the PlsqlMaxParameters parameter to a higher value. It would have a default value of 2000.
You can set this parameter in your modplsql conf file, wich you can possibly find under Apache\modplsql\conf. For example, I added
I restarted the Apache server and the page submitted without any problem.
How to enlarge the disk size of a VM drive?
It is rather simple if you know it. In fact it is just an .exe file that you will have to run.
The steps:
Look in your VM for the VM that you want to enlarge.
Select it and double click on the disk that you want to enlarge.
Now you get the properties of this disk, remember the disk file and go to your explorer window and go to the directory of this VM, just to make sure that this file really exists.
If you are working on windows open a dos cmd(Start -> Run)
For the solution read more…
Now go to the VMWARE program directory in windows this is something like C:\Program Files\VMware\VMware Workstation
In this directory you will have to start the script that will enlarge the disk space of your VM-drive.
The command will look like: vmware-vdiskmanager.exe -x 19GB "F:\VMWare\Webcenter\test.vmdk" where 19GB will be the new size of your VM-drive.
You will see an indicator with the percentage of growth.
With this exe file it is also possible to shrink, create, defragment, rename, ... VM-disks
APEX versus ADF
When you're looking at Frameworks and tooling and vendor-specific solutions you could think of APEX, Application Development Framework (ADF), ADF Rich Client Components, Trinidad, ...
To be able to compare these products, we've tried to build a matrix of features and how these features are managed in APEX and ADF.
We're talking about 10.1.3 features, not yet about 11g because these aren't production ready yet. 11g is very promissing about web 2.0 functionality and more service oriented approach, but for now let's stick to what we have.
Feel free to comment, add features, add comparisons ...
Proxy Authentication Failure in BPEL
A patchset is made available to resolve the HTTP-407 issue in your BPEL Environment. For more detailed information :
1) Download and review the readme for Patch.6869621 ( MLR # 7 of 10.1.3.3.1 )
2) Apply Patch.6869621 in a test environment.
ESB error [TOPLINK-3001]
And in fact it has a very easy solution.
I was building my application that I already mentioned in a previous post.
When I tested the xsl file(in the xsl file right click and choose test), everything looked fine and no problem occurred.
But when I deployed the application into my application server and I wanted to upload the content of the csv file into the database, I always received an error.
For the solution read more…
This was the error I received:
nested exception is: ORABPEL-11616 DBWriteInteractionSpec Execute Failed Exception. insert failed. Descriptor name: [DBTank.PersonFuelCosts]. Caused by Uitzondering [TOPLINK-3001] (Oracle TopLink - 10g Release 3 (10.1.3.3.0) (Build 070608)): oracle.toplink.exceptions.ConversionException Omschrijving uitzondering: Het object [50,1], van klasse [class java.lang.String], kon niet worden geconverteerd naar [class java.math.BigDecimal]. Interne uitzondering: java.lang.NumberFormatException. at oracle.tip.adapter.fw.wsif.jca.WSIFOperation_JCA.executeRequestResponseOperation
Reading this error message I believed that there was something wrong with the translation from my csv content into the database, and that this was caused by a declaration in one of the xsd files. The type of the variable in the xsd file in the csv file was a ‘String’, but for the xsd file for the database adapter the type was ‘decimal’.
I replaced the types in the xsd files with different kinds of other types, but this didn’t changed a thing, I still received the error message.
After a lot of searching and testing I found out that, when I changed the value in the csv from 56,2 into 56.1 everything worked fine.
Strange thing is that my DB reads ‘,’ and not ‘.’ .
Ones I found this, the workaround was rather easy to find.
On my xsl file I used a function to replace the ‘,’ by a ‘.’
This function can be found in the ‘Component Palette’ where you have to choose ‘String Functions’ and select the ‘translate’ function.
Ones this was done I managed to get the data correct into my database.
Empty error message for instance in ESB
I was using a DB schema that I exported from a DB that was used in one of our internal applications.
The ESB itself was in fact a very simple example with just a file adapter to get the content of a csv file and a database adapter for inserting the content retrieved from this csv into the database.
I also checked the option to remove the csv when it was successfully retrieved.
Everything looked fine and I registered the application onto the application server.
So far, so good…
The application was registered without any problems and I wanted to test my application.
So I created a test csv file and copied it in the directory, waited 5 seconds until the file was removed, so I tought that everything worked correctly. I looked in the database, but my records where not inserted in the table.
Time to take a look at the ESB controller…
And indeed the status of my instance was invalid, so the next step to do is to take a look at the error message….
Strange, the error message was completely empty, no error, no trace, nothing…
This made it off course a bit more difficult to find out what the problem was.
So I took a closer look on this and tried to find out what I missed. I changed some things here and there, but did not found a solution, I always got the empty error.
Then I looked a bit closer to the table where I wanted to insert the data into and compared it with the content of the csv file. All the values of my csv for the primary, foreign, not nullable columns looked ok.
So why did he gave me that empty error message?
Then I tried to insert a record manually into the table, and I found out that this didn’t work either. Seemed that there existed some insert triggers on this table that would insert some data in a column when this field was empty and that these triggers used a packaged that was invalid(because it was using another schema that I didn't imported). So just for testing I enabled all these triggers and tried to execute my esb process again. And it worked, so the problem I had was caused by a trigger on the table who was calling a procedure in an invalid package...
So, if you ever get an empty error message, the best thing to do is to check your database first…
Bpel 4 people / Bpel 4 newbees
In other words, guide and coach the development teams of each customer in getting familiar with SOA concepts and getting their hands dirty ;o))
The developers themselves are tought BPEL, Web Services, XML and XSD through basic hands on session, workshops in a practical way and then they start designing bpel processes.
What's very important is this process is that each person needs to do the brainwork, in other words always start from scratch, don't define a synchronous or asynchronous process, no define an empty process and build it up yourself.
What’s very interesting when guiding and coaching people in becoming a SOA expert, is understanding the way they think and handle functional requests and problems in a SOA Technology. This gives me the opportunity and possibility to focus more on how to better advice and guide people in learning SOA, e.g. BPEL.
The most interesting points to consider and think about when being a teacher in SOA or being a student are the following:
Being a teacher:
- be passionate, patient, non technical, use metaphors and most don't use abbreviations or buzz words, this ain't cool ;o)
Basic knowledge before starting BPEL/ESB/OWSM/Mediators … Integration:
- Web services (synchronous versus asynchronous)
- Xml and xsd (how to define, how to work with xml data (xquery, xpath, …)
You need to be able to understand following principles:
- Fault handling
- Compensation handling
- Transaction handling
Following abbreviations mean something to you:
- JCA, JNDI, EDA, SOA, WSDL, WSIF, SOAP, JMS, RPC
How to get started when designing bpel processes:
One of the interesting tricks I’ve learned when guiding a customers’ developer in learning bpel was that he defined an empty bpel process where all needed activities where pseudo-coded.
In other words when you need to define your first bpel process using the use cases defined and described by your analysts, you just design the bpel process using empty activities which describe each needed task/action in your process.
This empty process is deployable and can then be iteratively implemented by the designer when the visual flow is checked upon with the functional developers. => THIS IS A GREAT INSIGHT and WAY OF THINKING and a very visual approach to enabling business processen (thanks to Yves for the tip !!!)
The most interesting part when starting with SOA development is that everyone that’s involved in this process is learning; it’s a learning organization where every person’s knowledge and expertise is augmented!
Playing around with 11g
Native PL/SQL WebServices (11g Feature):
- Procedures or functions need to have in- and output-parameters when using 11g webservices in 10.1.3.1 bpel processes
- You can’t work with rowtypes as return variables => not supported
- XDB Username & Password authentication is needed for native pl/sql web service calls, when invoking native pl/sql webservices from BPEL. Add following properties to your partnerlink:
Jdeveloper 11g Tech Preview:
- You can’t connect to AppServer 10.1.3 (it will be applicable in the production release)
- You can run existing 10.1.3.1 bpel processes in Jdev 11g when you've installed bpel in the embedded OC4J (nice to know)
You need less clicks to get to view audit data in your bpel console and it's all using one single source of truth, one console, one management environment. Finally you're used to working in bpel, esb, Jdeveloper ... well now you need to get used to the new, improved environment.
minoccurs=0; nillable="true not interpreted in Web Services
Because in bpel you're not able to define the xsi:nil-attribute at design time, you need to define this manually.
The xsi:nil=true-attribute is the solution to tell web services that this attribute, if empty, needs to be ignored. In this way serialization and deserialization will work, otherwise you will always run into NullPointerException.
What's the cause of our problem when talking about bpel [metalink Note:403586.1]:
The XSLT transformer used by BPEL was not originally designed to pass xsi:nil attributes.
What's the proposed solution [metalink Note:403586.1]:
To transfer xsi:nil values from source and target schemas in a transformation, make use of xsl:choose and xsl:when as shown below:
What you could also do is in the web service proxy wizard, which is generated for a given wsdl-document change the serializer-classes. Though this isn't really the approach you woudl wan't to consider because now you're changing generated code, which will be changed when the wizard is run again.
The following important message is shown in your proxy generated classes that urges you to not change the generated code ;o)
// !DO NOT EDIT THIS FILE!// This source file is generated by Oracle tools//Contents may be subject to change// For reporting problems, use the following//
Version = Oracle WebServices (10.1.3.3.0, build 070610.1800.23513)
What you can do, on your own responsibility, is to change the literal_serializer classes which were generated for each object used in your webservices payload. You need to change the setNullable-method for each attribute from 'false' to 'true':
if (
matchQName(elementName, ns1_adresId_QNAME) )
{
myns2_string__java_lang_String_String_Serializer.setNullable( false );
if (
matchQName(elementName, ns1_adresId_QNAME) )
{
myns2_string__java_lang_String_String_Serializer.setNullable( true);
Another possibility is to create a Service Request and define an Enhancement Request so this manual tweaking isn't necessary anymore in the future ... that's what I'm doing as well/
BPEL Process Invocation from different UI's - Best Practices
One of these tips was tips & tricks on invocation of BPEL Processes when using different UI approaches.
A very understable explanation given by 'Hajo Normann' about this:
It is best practice to use the default ways to invoke a BPEL process –
create a WSDL that maps to WSIF binding in a controlled environment and to a
SOAP/HTTP binding in a more B2B type of scenario. A call to the BPEL API would
be a “custom” solution that needs way more governance to communicate with fellow
developers and to maintain properly, when compared to the straightforward
standard way.
So, I suggest to use the API only if you desperately need
to optimize and found the default implementations violating your SLAs.
Let’s have a look at the technical implications:
If you run your presentation logic in the same Java VM as your BPEL process, you would use the default mechanism of invoking the process with the default WSDL. Under the hood, the middleware creates WSIF bindings that
- are extremely memory friendly, because no redundant memory is allocated (the middleware passes a reference to the Java object)
- share transactions
In this scenario I see no advantage of calling the API.
If you run your presentation logic in an other Java VM as your BPEL process in a controlled environment, you would still benefit from using the default mechanism of invoking the process with his default WSDL. Under the hood, the middleware creates WSIF bindings that
- serialize the passed Java objects and, on the BPEL side, create the BPEL DOM
implementations that make up the BPEL instance variables. This causes a performance and memory penalty you have to pay
- share transactions
I haven't seen any benchmarks regarding this or haven't received more information of the Oracle Development Team so hopefully this will start up a big discussion ;o)
Soa Suite 10.1.3.3 - ESB, BPEL - Nice-to-knows, pitfalls
I've been checking out the different capabilities and new features of the Adapter-framework in ESB and BPEL for some weeks now and came across some nasty pitfalls, nice-to-knows, ... which I would like to share with you.
Of course I would like to share thoughts, opinions and start discussions on these topics.
ESB:
- How to define xsd-validation on file-adapter (validate payload at runtime-option isn't available in file adapter) : In the ESB console, select the routing service which is invoked after the inbound file adapter. See the "Definition" tab. The validation option is in the "Operation Details" section. (with thanks to Ronald)
- Define error-handling on file-adapter for erroneous files: use the 'rejectedMessageHandlers' endpoint property which you need to define manually in the esb-file (for more information have a look at the technical note about error handling http://www.oracle.com/technology/products/integration/adapters/pdf/Adapter_TN_004_Adapter_ErrorManagement.pdf
BPEL:
- Inserting master-detail data using DB Adapter functionality : Referring to my experiences so far it's best best way to make use of stored procedures instead of the toplink mappings file. I am mainly using the stored procedures because the tooling support in Jdeveloper (wizards, toplink ui), I still miss a good ui for the toplink support. Also it is easy to give the task to create an PL/SQL api to the PL/SQL developers that are working on a certain application. (with thanks to Orjan)
- [Error ORABPEL-10007]: unresolved messageType for "{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage": When you've defined an empty bpel process (which is best practice to do the brain-work) you will face this issue when defining fault-handling inside your bpel-process. To solve this error you need to import the RuntimeFault.wsdl inside the adapter you're using. Following import statement needs to be added:
- Best practices when invoking Bpel Processes from different UI's (Flex, JSF, ...) : Many thanks to Hajo for his explanation: It is best practice to use the default ways to invoke a BPEL process – create a WSDL that maps to WSIF binding in a controlled environment and to a SOAP/HTTP binding in a more B2B type of scenario. A call to the BPEL API would be a “custom” solution that needs way more governance to communicate with fellow developers and to maintain properly, when compared to the straightforward standard way. . For more details see OTN Thread: http://forums.oracle.com/forums/thread.jspa?messageID=2329384�
- Use multiple sources in transform-activity: In 11g a new feature has been added to be able to use multiple sources using bpel 2.0 (bpel:doXslTransform(string, node-set, (string, object)*)). The workaround in 10.1.3.3 is by using the params-approach => http://blogs.oracle.com/rammenon/2007/05/07. Or by using an assign-activity with append-functionality to add the variable inside your source-target and in the same assign-activity add the process-xslt functionality to call your xsl to populate the source with the target-information.
Invoking Web Services from Database:
- Call an esb service using the UTL_HTTP package => ORA-29266: end-of-body reached => make sure to pass variables using String-notation instead of Character-notation
Interesting New Features in 10.1.3.3 :
- Controlling the Size of a Rejected Message (10133technotes.pdf):
You can now control the size of a rejected message by specifying the following
endpoint property for the inbound File/FTP adapter partner link.
In this example, you reject 100 lines from the file since the actual file is too large.
oracle.tip.adapter.file.debatching.rejection.quantum=”100”
- ESB Endpoint Properties : e.g. ability to add RejectedMessageHandler to file adapter services
Enhancement Requests:
- Ability to validate xml payload at runtime on Adapter-level instead of on domain level or routing service level
- Ability to add xsi:nil attribute using xsl-functionality in transform-activity
- File-adapter: Ability to skip columns besides skipping rows + ability to use special characters in column headers
Well that's it for now ... feel free to share thoughts, comments, etc.
Column Aliases in BI Publisher
Problem:
If you create a dataset and build your SQL-query by Query Builder or type it manually, you have to watch out that you do not use column aliases like ‘FEED’.
See example below:
SQL-query:
select BI_TABLE.FEED as FEED,
BI_TABLE.FEEDBACK as FEEDBACK,
BI_TABLE.APPLICATION_ID as APPLICATION_ID,
BI_TABLE.NAME as TYPE_NAME,
BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;
XML-file:
Internet Explorer seems to perform a strict XML validation. He gives you following error:
Solution:
Replace all column aliases with names like ‘FEED’ and your problem is solved. See solution below:
SQL-query:
select BI_TABLE.FEED as COLUMN_01,
BI_TABLE.FEEDBACK as COLUMN_02,
BI_TABLE.APPLICATION_ID as APPLICATION_ID,
BI_TABLE.NAME as TYPE_NAME,
BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;
XML-file:
Note: You can use ‘FEED’ as name for database tables but remember, not for column aliases!!!
Oracle BI Publisher Date-type
You have two options:
1. adapt the XML generation process to generate the date in the canonical format and use the format date functions of BI Publisher in the template.
2. adapt the XML generation process to generate the date in the format of the client (example: DD-MM-YYYY) and print this directly on your report.
Solution 1:
First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.
We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.
Note: XML-Date is in the canonical format and can be imported directly in the template. Example below is based on a ‘sysdate’ and stored in a field called ‘FULL_DATE’.
DS_Query:
select BI_DATE.FULL_DATE as FULL_DATE
from HRM.BI_DATE BI_DATE
After we create a dataset, we generate an XML to build our RTF-template.
XML-type:
<?xml version="1.0" encoding="UTF-8" ?>
<ROWSET>
<ROW>
<FULL_DATE>2008-02-19T13:24:37.000+01:00</FULL_DATE>
</ROW>
</ROWSET>
Properties:
Load XML into MS Word (with Oracle BI Publisher Desktop). Import the field ‘FULL_DATE’ and edit the properties. We can choose different format-types in the drop-down box. Choose one for example: ‘d-MMM-yy’ or type your own format-type like ‘dd-mm-yyyy’.
Note: Field ‘FULL_DATE’ is a canonical format.
Results in Word:
Solution 2:
First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.
We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.
DS_Query:
select BI_DATE.FULL_DATE as FULL_DATE,
to_char(BI_DATE.FULL_DATE) as CHAR_DATE,
to_char(BI_DATE.FULL_DATE,'dd-mm-yyyy') as FORMAT_DATE,
to_char(BI_DATE.DAY_DATE,'DD') as DAY_DATE,
to_char(BI_DATE.MONTH_DATE,'MM') as MONTH_DATE,
to_char(BI_DATE.YEAR_DATE,'YYYY') as YEAR_DATE
from HRM.BI_DATE BI_DATE
After we create a dataset, we generate an XML to build our RTF-template.
XML-type:
<?xml version="1.0" encoding="UTF-8" ?>
<ROWSET>
<ROW>
<FULL_DATE>2008-02-19T13:24:37.000+01:00</FULL_DATE>
<CHAR_DATE>19-FEB-08</CHAR_DATE>
<FORMAT_DATE>19-02-2008</FORMAT_DATE>
<DAY_DATE>19</DAY_DATE>
<MONTH_DATE>02</MONTH_DATE>
<YEAR_DATE>2008</YEAR_DATE>
</ROW>
</ROWSET>
Load XML into MS Word (with Oracle BI Publisher Desktop). Because we have set our date types in the query, we just can import the available fields without editing the properties.
Results in Word:
Solution 3:
XML-Date is not a canonical date format. Second solution for this problem is similar to solution 2. Except that we don’t edit the SQL-query and generate XML.
Load XML into MS Word (with Oracle BI Publisher Desktop). Import necessary fields and edit properties.
Example field ‘Full Date’:
Choose ‘Word Properties’:
Then ‘Add Help Text…’
Type following statement by ‘Help Key (F1)’: <?format-date: FULL_DATE;’dd-MMM-yyyy’;’Europe/Brussels’?>
Note: ‘Europe/Brussels’ is the time-zone of your region.
And click ‘OK’ -> ‘OK’.
Disadvantage: you have to apply this solution to every field on the report. If you do it on query-level, you can import fields without editing them in the template.
Flex - Rules of Thumb
When you need to take a decision in which technology to use to build your new application with, you need to be able to deliver each rule of tumb with this technology.
Rules of thumb that need to tackled:
- Isolation
- Simplicity of UI and Model
- Clear separation of concerns
- Responsive & intuitive User Interface
Delivering Rules of Thumb using FDMS
The rules of thumb mentioned for Adobe Flex Integration, can be delivered using Flex Data Management Services.
1. Isolation :
Isolate handling of state, presentation, action and communication
State:
- Assembler Class holds all necessary information regarding state-handling
- Pull-mechanism of Data Services assures all clients have up-to-date information
Presentation:
- Java DTO (Data Transfer Objects) are automatically mapped to Action Script DTO’s
Action:
- Actions are immediately called on the Assembler Class that holds all needed business logic and CRUD-methods
Communication:
- Communication between client and server are handled on server-side using the Assembler Class
2. Simplicity of UI and Data Model:
Keep UI and data model code as simple as possible
- The User Interface only defines the lay-out of the different objects and data management is performed by the Data Services
- Data Model is provided using Web Service Stubs created on the deployed Bpel Processes and Data Transfer Objects
We still need to manually create an Action Script DTO object for each POJO we’re using in our Web Services.
3. Clear separation of concerns:
Decouple and make programming tasks parallel so they can be carried out by different people
- Assembler Class will be defined for each designated web service stub
- Data Transfer Objects will be defined in ActionScript for each used object-definition in the Assembler-class
4. Responsive & intuitive User Interface:
Provide a very responsive user experience with clear feedback and zero latency
- Provided using Data Services Management using push-mechanism
- Use managed DTO-components so each event is known to the Data Services
- Use validation-framework for client-side validation
- Use conflict resolution API for exception handling
Configure Proxy Authentication for Web Services
We were able to create a web service proxy client inside Jdeveloper 10.1.3.3 but when deploying the web service we were always getting the HTTP Proxy Exception 407, meaning problems with username and password authentication.
This was the first time I ran into this issue, and it's quite cumbersome to get around this one.
Steps to create a web service client for an external web service using proxy authentication:
1. Create a new application and new project
2. Choose to create a new Web Services, from the Business Tier Categorie and choose to create a Java Web Service from WSDL
3. Click 'OK'
4. Click ‘Next’, if you don’t want to see this page in the future, check the checkbox ‘skip this page next time’ 5. Copy/Paste the following URL inside the WSDL URL, the first field http://www.oorsprong.org/websamples.countryinfo/CountryInfoService.wso?WSDL
6. Now press enter. The wsdl document will be fetched and read, and the next step in the wizard will be shown. Define a package name and a root package name for the types that will be generated for this webservice.
7. Go through the next steps and at the last screen you can see which operations are being provided in the web service and which classes will be generated, using the package names you’ve defined earlier.
The different classes are now being generated and you’re ready to deploy this web service to your application server 10.1.3.3 installation.
You can already deploy and run this web-service on your application server by performing the following steps:
1. Create a new Application Server Connection in the ‘Connections’-tab
2. Go back to the Applications Navigator-tab and choose to deploy your web service to this connection:
3. The next screen shown, is the deployment plan-screen where you can make adjustments before deploying the application:
4. In the deployment log window you can follow the different steps which are performed to deploy the web service client on our application server instance:
In the deployment log window you can follow the different steps which are performed to deploy the web service client on our application server instance
After succesfull deployment you can test your web service using Enterprise Manager:
1. Go to WebServices-tab in your Enterprise Manager console:
2.Click on the ‘CountryInfoServiceSoap’, the service we’ve deployed earlier:
3.Click the ‘Test Service’ link to test your service:
4.Click ‘invoke’ in the test window
As you can see the following exception is thrown ‘unexpected null value for literal data’
As you can see the following exception is thrown ‘unexpected null value for literal data’, this is due to proxy authentication failure. OK, this isn’t very clear in the exception message, but let’s configure proxy authentication.
To configure proxy authentication we need to go back to our jdeveloper environment and configure authentication for our web service.
1. Right-click on the web-service proxy client in your ‘Application Navigator’ and choose ‘properties’
2. Go to the Security-categorie in the tree structure and enable global security settings, by checking the checkbox. Only choose the ‘text password’ as authentication options:
3. Authentication: Define the authentication settings, as in the screen below (nonce and creation time aren’t required)
Click OK and deploy the web-service again.
When testing the web service you just need to fill in the ws-security headers, and no exceptions will be thrown anymore:
Connect from RHEL5 to a MSSQLServer Database using ODBC
For a data warehouse project I'm working on, I like to add a MS SQLServer database as a datasource to OWB. So the first thing I need is an ODBC Datasource. No problem, but there just one small issue which makes the creation of this ODBC datasource a bit less "default": My database is running on a linux machine. J
So, A few more steps are needed:
- Check the unixodbc packages are installed
- Install the KDE driver manager components for ODBC
- Install and configure FreeTDS
- Add the FreeTDS Driver to the ODDBC Configuration Files
- Add the MSSQLServer Datasource
STEP 1: Check the unixodbc-packages are installed
[oracle@lnx-srv-dwh-dev ~]$ odbcinst -j
unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/oracle/.odbc.ini
[oracle@lnx-srv-dwh-dev ~]$
If the unixODBC packages aren't installed, you can install them using yum or using the "rpms" located at the RHEL5 installation disks.
STEP 2: Install the KDE driver manager component for ODBC
This package will install the graphical "ODBCConfig" tool which we can use to add ODBC drivers and Data Sources. You can compare this tool with the "ODBC Data Source Administrator" in MS Windows.
[root@lnx-srv-dwh-dev rpm]# yum install unixODBC-kde-2.2.11-7.1.x86_64
Loading "installonlyn" plugin
Loading "rhnplugin" plugin
Setting up Install Process
Setting up repositories
rhel-x86_64-server-vt-5 100% |=========================| 1.4 kB 00:00
rhn-tools-rhel-x86_64-ser 100% |=========================| 1.2 kB 00:00
rhel-x86_64-server-5 100% |=========================| 1.4 kB 00:00
Reading repository metadata in from local files
primary.xml.gz 100% |=========================| 1.3 MB 00:02
################################################## 4235/4235
Parsing package install arguments
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for unixODBC-kde to pack into transaction set.
unixODBC-kde-2.2.11-7.1.x 100% |=========================| 12 kB 00:00
---> Package unixODBC-kde.x86_64 0:2.2.11-7.1 set to be updated
--> Running transaction check
Dependencies Resolved
=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
unixODBC-kde x86_64 2.2.11-7.1 rhel-x86_64-server-5 594 k
Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 594 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): unixODBC-kde-2.2.1 100% |=========================| 594 kB 00:02
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: unixODBC-kde ######################### [1/1]
Installed: unixODBC-kde.x86_64 0:2.2.11-7.1
Complete!
[root@lnx-srv-dwh-dev rpm]#
STEP 3: Install and configure FreeTDS
FreeTDS is a set of libraries that allows your programs to natively talk to Microsoft SQL Server and Sybase databases. Those libraries will used to create an ODBC driver used to connect to our SQLServer database. You can download the software (freetds-stable.tgz) from ftp://ibiblio.unc.edu/pub/Linux/ALPHA/freetds/stable/
Following log shows the install and configuration steps:
[oracle@lnx-srv-dwh-dev freetds]$ pwd
/tmp/freetds
[oracle@lnx-srv-dwh-dev freetds]$ ls
freetds-stable.tgz
[oracle@lnx-srv-dwh-dev freetds]$tar zxvf freetds-stable.tgz
...
[oracle@lnx-srv-dwh-dev freetds]$ cd freetds-0.64/
[oracle@lnx-srv-dwh-dev freetds-0.64]$
[oracle@lnx-srv-dwh-dev freetds-0.64]$ ./configure --with-tdsver=7.0 --prefix=/usr/local/freetds
...
config.status: creating include/config.h
config.status: executing depfiles commands
[oracle@lnx-srv-dwh-dev freetds-0.64]$ make
...
make[1]: Entering directory `/tmp/freetds/freetds-0.64'
make[1]: Nothing to be done for `all-am'.
make[1]: Leaving directory `/tmp/freetds/freetds-0.64'
if test ! -f PWD; then cp ./PWD.in PWD; fi
[oracle@lnx-srv-dwh-dev freetds-0.64]$ su
Password:
[root@lnx-srv-dwh-dev freetds-0.64]# make install
...
make[2]: Nothing to be done for `install-exec-am'.
/bin/sh ./mkinstalldirs /usr/local/freetds/etc
if test ! -f /usr/local/freetds/etc/freetds.conf; then \
/usr/bin/install -c -m 644 ./freetds.conf /usr/local/freetds/etc/freetds.conf; \
fi
if test ! -f /usr/local/freetds/etc/locales.conf; then \
/usr/bin/install -c -m 644 ./locales.conf /usr/local/freetds/etc/locales.conf; \
fi
make[2]: Leaving directory `/tmp/freetds/freetds-0.64'
make[1]: Leaving directory `/tmp/freetds/freetds-0.64'
STEP 4: add the FreeTDS driver to the ODBC Drivers-file
Edit the "/ect/odbcinst.ini" file and add following lines:
[FreeTDS]
Description = v0.64 with protocol v8.0
Driver = /usr/local/freetds/lib/libtdsodbc.so
Setup = /usr/local/freetds/lib/libtdsodbc.so
FileUsage = 1
[root@lnx-srv-dwh-dev etc]# pwd
/etc
[root@lnx-srv-dwh-dev etc]# cp odbcinst.ini odbcinst.ini.ori
[root@lnx-srv-dwh-dev etc]# vi odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
[FreeTDS]
Description = v0.64 with protocol v8.0
Driver = /usr/local/freetds/lib/libtdsodbc.so
Setup = /usr/local/freetds/lib/libtdsodbc.so
FileUsage = 1
As an alternative you can add the driver using the "ODBCConfig"-tool;
STEP 5: Add an MSSQLServer Datasource:
Start de" ODBCConfig" Administration Tool
Click the "System DSN"-add
Click the "Add"-button
Select the "FreeTDS"-driver
Click "OK"
Fill in the datasource properties
As an alternative, you can add the datasources to the "/etc/odbc.ini"-file
[oracle@lnx-srv-dwh-dev etc]$ pwd
/etc
[oracle@lnx-srv-dwh-dev etc]$ cat odbc.ini
[mySQLTest]
Description = my SQLServer Test Database
Driver = FreeTDS
Servername = mySQLTestServer
Server =
Address =
Port = 1433
Database =
TDS_Version = 8.0
Language = us_english
TextSize =
Domain =
PacketSize =
STEP 6: Test the brand new datasource!
Use the "isql"-tool to connect to our database.
[oracle@lnx-srv-dwh-dev etc]$ isql -v mySQLTest myusername mypassword
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
STEP 7: What's next
From now on we can use this ODBC datasource in an Oracle Heterogeneous Service which we need when adding the SQL Server as a Source in Oracle Warehouse Builder.
SYSMAN Account is Locked
This morning I opened Enterprise Manager and instead of the regular "login"-screen, I found following screen:
Everything is up and running but Enterprise Manager is not able to connect to the database instance. Sounds like a connection problem, not?
These are the steps I followed to solve this issue and to reset the "sysman"-account:
STEP1: Check EM log file:
[oracle@myserver log]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD
/sysman/log
[oracle@ myserver log]$ tail -50 emoms.log
…
2008-02-11 23:12:04,968 [ApplicationServerThread-11]
ERROR app.SessionObjectManager sessionDestroyed.128 -
java.sql.SQLException: ORA-28000: the account is locked
java.sql.SQLException: ORA-28000: the account is locked
…
STEP 2: Check database users:
SYSTEM> select username, account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ -------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN LOCKED(TIMED)
STEP 3: Stop the Database Console
[oracle@myserver dbascripts]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://myserver.mydomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.
[oracle@myserver dbascripts]$
STEP 4: Reset the SYSMAN-account
[oracle@myserver log]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 12 09:51:59 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS> alter user sysman identified by <new_password> account unlock;
User altered.
STEP 5: Reset the SYSMAN-account in the "targets.xml"-file ($ORACLE_HOME/myserver.mydomain _mySID/sysman/emd) and modify the "Username"-Property and the "Password"-property as follows:
[oracle@myserver emd]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/emd
[oracle@myserver emd]$ cp targets.xml targets.xml.ori
[oracle@myserver emd]$ vi targets.xml
…
<Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
<Property NAME="password" VALUE="<new_password>" ENCRYPTED="FALSE"/>
…
STEP 6: Reset the SYSMAN-account in the "emoms.properties"-file ($ORACLE_HOME/myserver.my_domain_mySID/sysman.config) and modify the "oracle.sysman.eml.mntr.emdRepPwd"-parameter and the "oracle.sysman.eml.mntr.emdRepPwdEncrypted" in the following way:
[oracle@myserver config]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/config
[oracle@myserver config]$ cp emoms.properties emoms.properties.ori
[oracle@myserver config]$ vi emoms.properties
…
oracle.sysman.eml.mntr.emdRepPwd=<new_password>
…
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE
…
STEP 7: Start the Database Console
[oracle@myserver dbascripts]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://myserver.mydomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...... started.
------------------------------------------------------------------
Logs are generated in directory
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/log
STEP 8: Test
ODTUG Kaleidoscope 2008
Thursday night I got this fantastic news:
"Dear Karen:
Congratulations! Your abstract,
Use the Power of Apex Dictionary Views to Increase the Quality of Your Apex Applications,
has been accepted for presentation at ODTUG Kaleidoscope 2008, June 15-19, in New Orleans, Louisiana."
Wauw!! A range of emotions ran through me. I go to New Orleans, the city from the gospel and the jazz!
It's the first time I go to a foreign country to give a presentation, so you understand that I'm very excited to be one of the selected speakers at ODTUG! This is a wonderful opportunity!
The Apex-core team at iAdvise has developed their own QA-tool on top of the Apex Repository to guard the quality of our application guidelines and conventions. I will bring this case together with my colleague Jan Huyzentruyt. We already did a lot of presentations together in Belgium, but it is the first time we are doing it abroad ...
So, we are looking forward going to New Orleans and we are honoured being on the list of speakers together with big names like Tom Kyte, Steven Feuerstein, Mark Rittman, Carl Backstrom, Patrick Wolf, ...
Hope to see you there!
ODI - Troubleshooting
I staged such a scenario to test the robustness of ODI and following troubleshooting hints & tips could be of help to anyone facing this situation in a real-time environment:
- 'The primary key is not unique, PK Errors': I couldn't figure out what this error was about because my sequences were defined correctly on the target datastore and the invocation in my ODI interface was defined correct as well. => Solution: delete the working tables in your sunopsis working repository, the snpw-schema. When you run the interface again it'll work smoothly.
- ''Join error between the table 'x' and the table 'y'' => This error was resolved by deleting all constraints defined on my target-model and by reverse engineering the model-object again. => New attributes, constraint are reversed as expected, but existing constraints and attributes aren't automatically deleted, this is a manual job you'll have to perform.
- You're getting 'privileges'-exceptions when trying to execute your interfaces => make sure to grant the target-datasource dba-privileges this is needed to be able to access the snpw- and snpm-datastores.
- Working with Excel Data : '-1305 : S0002 : java.sql.SQLException: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'MO'. Make sure the object exists and that you spell its name and the path name correctly.' => This error is thrown when the Excel you're pointing to is opened up whilst running ODI interfaces. When you close the Excel file no, this exception will be resolved.
Other usefull hints & tips are always welcome.
Oracle BPA Suite - Tips & Tricks regarding usability
This week I attended the SOA Forum in Lisbon for which Oracle had organized 2 days regarding overall presentations and case studies and then 2 days of expert camps.
These expert camps where divided in Advanced SOA and Oracle BPA Suite.
I suscribed for the BPA-one because I'm interested in the full development life cycle that is offered now, starting from analysis until development.
The Bpel Blueprint that got my attention, especially the ability to forward-engineer changes from BPA into your BPEL and vice versa.
The BPA-workshop started from scratch and gave us, the attendees, the possibility to get in toch with the different features within BPA as well for analysts as for developers through the usage of BPEL Blueprints.
The tips & tricks that can be of use for anyone interested in getting started with BPA, are the most important part to remember. The rest is basic workshop/tutorial stuff top get acquanted with the tool/environment.
Tips & Tricks:
- Installation-possibilities: When you want to install BPA you have different options depending on your requirements. If you just want to get acquanted with the tool and don't want to start using the tool for production purposes you can use the following set-up: Architect + local repository (Oracle Lite or Oracle DB). If you want to get acquanted and use the tool for real implementations, use the following set-up: Architect + Repository Server. If you want to use your existing installation of Oracle XE or Oracle DB you can install Architect with a local repository pointing to XE or your DB => make sure you're using the UTF8-character set, otherwise you will need to install a separate database instance
- You can run BPA as a Java Application or an Applet
- You need to use the same version for your bpa suite and your IDE, e.g. BPA 10.1.3.3 and Jdeveloper 10.1.3.X, and SOA Suite 10.1.3.x
- You can only add access rights on group-level in Business Architect, not on model- or object-level => in other words, you need to organize the folders properly
- When your working within Business Architect and with models and objects you need to keep in mind you're working in an object-oriented fashion, meaning= when an object is displayed on multiple models, you are referring to object occurences, not to the object itself. Each object occurence can have its own specified properties, independend from the other object occurrences
- You can't integrate Business Rules in the release 10.1.3.3 of BPA Suite
- Swimlanes and pools aren't generated into the BPEL Blue print, these aren't part of the generation functionality
Hopefully these tips & tricks, comments can help you in your first steps using BPA Suite.