Read and monitor Oracle related blogs and news sources, all in one place.
Contractors Network is pleased to announce the latest free of charge release into our extensive Oracle Apps related White Paper Library of:
“A Structured Approach to SQL Query Design”
Authored By Brendan Furey
About Brendan;
Brendan is an Oracle Applications Consultant with over nineteen years Oracle experience, across a wide range of projects and industries. He has worked with many Oracle modules, including Financials, Supply Chain, and CRM, up to 11.5.10. He is technically oriented, and has expert level knowledge of most of the Oracle development tools, as well as Unix and other tools, and also has good functional knowledge.
Recent projects have included: implementation of multi-org and intercompany invoicing within an existing single-org implementation; development of complex interfaces between Oracle instances using different CRM modules, with PO, AP AR; integration design (using middleware) for a European implementation of Oracle ERP (back-office) along with Siebel (front-office) and other systems.
The Purpose of Brendan’s Paper;
The purpose of this document is to describe a structured, graphical approach to the design of SQL queries that may be a useful way of handling complexity without reverting to procedural design. It focuses on sub query structure and join orders, rather than on other areas such as grouping and aggregation, or design patterns. The author has used it to design complex queries with up to 46 table instances, and the approach is demonstrated using a real (rather simpler) example of a custom report within Oracle’s Order Management and Inventory modules (see REF-2 for Oracle’s table specifications).
If you would like request a copy please email myself on rebecca.bragg@oraclecontractors.com or register via the White Paper library and request your copy today.
La première release d'Oracle SQL Developer Data Modeler est enfin disponible en téléchargement sur OTN !
| Note: 824414.1 R12.1: Frequently Asked Questions related to the R12.1 Release(Financials) And from Chris Warticki's blog we have a link to FREE R12.1 Advisor Webcast |
The UKOUG has chosen articles/ blogs from our Website for the up and coming release of Oracle Scene Magazine.
Well Done to Andy Noble and Rory Dwyer for being selected in the up and coming issue for your Articles.
Rory Dwyer wrote Its Not What You Know Its Who You Know
http://blog.oraclecontractors.com/?p=543
Andy Noble wrote How to quickly identify a missing join
http://blog.oraclecontractors.com/?p=454
Well done guys I look forward to reading your Articles in printed form 
The last time we looked at Oracle’s SaaS strategy, Oracle chairman Larry Ellison had largely rejected the idea of software-as-a-service, saying it wasn’t profitable enough. He said this, despite the fact that more than a few of his competitors have enthusiastically embraced the on-demand industry.
So has Ellison had a change of heart?
A year later, it looks that way. Oracle announced today SaaS for ISVs, a new pricing model that allows Independent Software Vendors (ISVs) to pay for its Oracle SaaS platform by the month rather than make an upfront investment. This also allows ISVs to adjust their SaaS offerings to meet customer demand, according to Oracle.
Analyst China Martens calls this new licensing option part of the “continued gradual easing of Oracle into the SaaS arena,” according to the IDG’s Chris Kanaracus in this article.
Oracle has over 10,000 partners that are active ISVs in its partner program, said Judson Althoff, senior vice president, worldwide alliances and channels, in his video post on the Oracle site. Althoff says that while Oracle has mostly serviced the “higher end of the SaaS market,” the new model will “allow us to reach a much broader base of ISVs, and better cater to you, the partner who wants to roll out a SaaS offering.”
Althoff also said the monthly cost structure will help the ISV “better manage cash flow” and “only pay for just the actual elements for the Oracle platform that (it) used in the previous month.”
Oracle has also begun promoting SaaS for ISVs on its SaaS Knowledge Zone and a new ISV and SaaS blog by Oracle’s Kevin O’Brien, Senior Director of ISV and SaaS Strategy for Oracle’s Worldwide Alliances and Channels organization. According to an Oracle data sheet, the new pricing model will apply to the Oracle SaaS Platform from the Oracle Database, Oracle Fusion Middleware, Oracle Enterprise Manager and Oracle VM.
Oracle isn’t the only vendor to have made recent on-demand developments. SAP recently released its latest SaaS strategy, which includes on-demand applications for Business Suite customers that use a multi-tenant architecture rather than an isolated tenant model.
Where do you think Oracle stands in the SaaS market? Will this latest SaaS for ISVs offer help Oracle? Or, as some are saying, despite the new pricing model, is Oracle software just too expensive in the first place?
The previous blog post described a way to upload and (in theory at least) download text files to/from a remote Windows machine using WS-Management. In practice, the applicability of the method is limited for upload (text files only, slow for large files) and almost nonexistent for download. Here is a much improved version.
This is another example of something that was too obvious for me to see last weekend when I was in the thick of fighting with WS-Management SOAP messages and learning about WMI classes. It just took a day of not thinking about it to have the solution pop in my mind: use ftp.exe. For the longest time (at least since Windows NT) Windows has been shipping with this FTP client. And the documentation shows that you can call it from the command line and provide it with the name of a text file containing the commands to execute. Bingo.
Specifically, here are the steps. Let’s say that I want to run a program called task.exe on a remote Windows machine and that program takes a large binary file (data.bin) as input. I want to transfer both to the remote machine and then run the program. This can be done in 3 simple steps:
Step 1: upload the FTP command file to the remote Windows machine. The content of the command file is below. mgmtserver.myco.com is the name of the machine from which the two files can be retrieved over FTP. I use anonymous FTP here, but you could just as well provide a username and password.
open mgmtserver.myco.com anonymous binary get task.exe get data.bin quit
Step 2: execute the FTP commands above. This downloads task.exe and data.bin from mgmtserver.myco.com onto the remote Windows machine.
Step 3: execute the program on the remote Windows machine (”task.exe data.bin”).
Here are the on-the-wire messages corresponding to each step:
Step 1: upload the FTP command file to the remote Windows machine
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:w="http://schemas.dmtf.org/wbem/wsman/1/wsman.xsd"> <s:Header> <a:To>http://server:80/wsman</a:To> <w:ResourceURI s:mustUnderstand="true">http://schemas.microsoft.com/wbem/wsman/1/wmi/root/cimv2/Win32_Process </w:ResourceURI> <a:ReplyTo> <a:Address s:mustUnderstand="true">http://schemas.xmlsoap.org/ws/2004/08/addressing/role/anonymous</a:Address> </a:ReplyTo> <a:Action s:mustUnderstand="true">http://schemas.microsoft.com/wbem/wsman/1/wmi/root/cimv2/Win32_Process/Create</a:Action> <a:MessageID>uuid:9A989269-283B-4624-BAC5-BC291F72E854</a:MessageID> </s:Header> <s:Body> <p:Create_INPUT xmlns:p="http://schemas.microsoft.com/wbem/wsman/1/wmi/root/cimv2/Win32_Process"> <p:CommandLine>cmd /c echo open mgmtserver.myco.com>ftpscript&&echo anonymous>>ftpscript&&echo binary>>ftpscript&&echo get task.exe>>ftpscript&&echo get data.bin>>ftpscript&&echo quit>>ftpscript</p:CommandLine> <p:CurrentDirectory>C:\data\winrm-test\</p:CurrentDirectory> </p:Create_INPUT> </s:Body> </s:Envelope>
As before, you need to set the Content-Type HTTP header to “application/soap+xml;charset=UTF-8? (or UTF-16).
Step 2: execute the FTP commands to download the files from your server
It’s the same message, except the <p:CommandLine> element now has this value:
<p:CommandLine>ftp -s:ftpscript</p:CommandLine>
Step 3: execute the task.exe program on the remote Windows machine
Again, the same message except that the command line is simply:
<p:CommandLine>C:\data\winrm-test\task.exe data.bin</p:CommandLine>
Note that I have broken this down in three messages for clarity, but you can easily bundle all three steps in one SOAP message. Just use this command line:
<p:CommandLine>cmd /c echo open mgmtserver.myco.com>ftpscript&&echo anonymous>>ftpscript&&echo binary>>ftpscript&&echo get task.exe>>ftpscript&&echo get data.bin>>ftpscript&&echo quit>>ftpscript&&ftp -s:ftpscript&&C:\data\winrm-test\task.exe data.bin</p:CommandLine>
Of course this can also be used in reverse, to download files from the remote Windows machine rather than upload files to it. Just use PUT or MPUT as FTP commands instead of GET or MGET.
This mechanism is a major improvement, for many use cases, over what I originally described. I feel a bit like someone who just changed a flat tire by loosening the lug nuts with his teeth and then found the lug wrench under the spare tire.
Stage 4: Automating Data Refreshes.
In part 1 of this blog, I demonstrated the initial configuration of the Oracle Database Gateway for ODBC (DG4ODBC).
Part 2 completed the configuration and also showed how to run some basic queries.
Part 3 showed a method of resolving issues encountered when dealing with LONG data type columns, using a combination of external tables and materialized views.
In the fourth and final part, I’ll demonstrate one method of automating the data refreshes for this solution. There are many solutions available to automate procesess, but for this example, I’ll use the database scheduler.
Step L: Check the objects we have created previously
——-
58) Run the query below to confirm the list of objects owned by NWIND_ACCESS, that were created in parts 1,2 and 3 of this blog:
conn nwind_access/zuggy4
select table_name from user_tables union select view_name from user_views;
The information below shows how the tables and views map to the Microsoft Office Access 2007 database tables from the northwind database.
ORCL11 Object ORCL11 objects referenced Microsoft Office Access tables
========== ====================== ====================
MV_NWIND_CUSTOMERS (materialized view) NWIND_CUSTOMERS(external table) Customers
MV_NWIND_ORDERS (materialized view) NWIND_ORDERS (external table) Orders
V_ORDER_DETAILS(View) Order Details
V_ORDER_STATUS(View) Order Status
T_ORDER_DETAILS(Table) Order Details
T_ORDER_STATUS(Table) Orders Status
Step M: Create Additional Materialized Views and re-create existing materialized views
——-
59) We created materialized views based on the Customers table and the Orders table from the northwind database. We now also need to create them for information stored within the Order Details and Orders Status tables.
As demonstrated previously, we aren’t able to base these on selects directly from the table. (If we try to create the materialized view as a “select * from v_order_details” or “select * from “Order Details”@Northwind” then you just see the error: ORA-00942: table or view does not exist)
60) Create the text files that the external tables will be based on.
conn nwind_access/zuggy4
set head off feedback off trimspool on lines 32766 buffer 32767 pages 0 space 0 colsep ‘~’
alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
spool
C:\app\Administrator\admin\ORCL11\nwind_dump\order_details.txt
select * from “Order Details”@Northwind;
spool off
spool
C:\app\Administrator\admin\ORCL11\nwind_dump\order_status.txt
select * from “Orders Status”@Northwind;
spool off
Then re-set your SQLPLUS* environment:
set head on feedback on trimspool off lines 132 buffer 132 pages 1000 space 1 colsep ” “
Note: Throughout all of this work, ensure that the permissions of the directory which contains the output text files (i.e. the nwind_dir database directory location) is restricted according to your company’s security policies.
61) Create the new external tables nwind_order_details and nwind_order_status and re-create the existing external tables:
conn nwind_access/zuggy4
create table nwind_order_details
(
ID NUMBER(10),
ORDER_ID NUMBER(10),
PRODUCT_ID NUMBER(10),
QUANTITY NUMBER(18,4),
UNIT_PRICE NUMBER(19,4),
DISCOUNT FLOAT(53),
STATUS_ID NUMBER(10),
DATE_ALLOCATED DATE,
PURCHASE_ORDER_ID NUMBER(10),
INVENTORY_ID NUMBER(10)
)
organization external
(type oracle_loader
default directory nwind_dir
access parameters
(
records delimited by newline
badfile NWIND_DIR:’order_details.bad’
discardfile NWIND_DIR:’order_details.dsc’
logfile NWIND_DIR:’order_details.log’
fields terminated by ‘~’ optionally enclosed by ‘”‘ LRTRIM
missing field values are null
reject rows with all null fields
(
ID,
ORDER_ID,
PRODUCT_ID,
QUANTITY,
UNIT_PRICE,
DISCOUNT,
STATUS_ID,
DATE_ALLOCATED CHAR(20) date_format DATE mask “dd-mon-yyyy hh24:mi:ss”,
PURCHASE_ORDER_ID,
INVENTORY_ID
)
)
location (NWIND_DIR:’order_details.txt’)
)
reject limit unlimited
/
create table nwind_order_status
(
STATUS_ID NUMBER(3),
STATUS_NAME VARCHAR2(100)
)
organization external
(type oracle_loader
default directory nwind_dir
access parameters
(
records delimited by newline
badfile NWIND_DIR:’order_status.bad’
discardfile NWIND_DIR:’order_status.dsc’
logfile NWIND_DIR:’order_status.log’
fields terminated by ‘~’ optionally enclosed by ‘”‘ LRTRIM
missing field values are null
reject rows with all null fields
(
STATUS_ID,
STATUS_NAME
)
)
location (NWIND_DIR:’order_status.txt’)
)
reject limit unlimited
/
Note: In the above, you’ll notice that we’ve left out the ” skip 1” clause that was included in the create table statements in earlier parts of this blog. As commands will not be echoed to the spool file, as we are using the database scheduler, we need to remove this clause, otherwise we’ll lose the first row of data. Because of this issue, we’ll also need to drop and re-create the existing Customers and Order external tables also. (We don’t need to re-create the materialized views that were based on these tables). We also don’t need to include the ” load when ( != “off”) ” commands in the access parameters section, as the SQL commands will not appear in the output file.
This week in the Database Programmer we look at something called an "UPSERT", the strange trick where an insert command may magically convert itself into an update if a row already exists with the provided key. This trick is very useful in a variety of cases. This week we will see its basic use, and next week we will see how the same idea can be used to materialize summary tables efficiently.
The idea behind an UPSERT is simple. The client issues an INSERT command. If a row already exists with the given primary key, then instead of throwing a key violation error, it takes the non-key values and updates the row.
This is one of those strange (and very unusual) cases where MySQL actually supports something you will not find in all of the other more mature databases. So if you are using MySQL, you do not need to do anything special to make an UPSERT. You just add the term "ON DUPLICATE KEY UPDATE" to the INSERT statement:
Retrouvez le 
insert into table (a,c,b) values (1,2,3)
on duplicate key update
b = 2,
c = 3
The MySQL command gives you the flexibility to specify different operation on UPDATE versus INSERT, but with that flexibility comes the requirement that the UPDATE clause completely restates the operation.
With the MySQL command there are also various considerations for AUTO_INCREMENT columns and multiple unique keys. You can read more at the MySQL page for the INSERT ... ON DUPLICATE KEY UPDATE feature.
MS SQL Server introduced something like UPSERT in SQL Server 2008. It uses the MERGE command, which is a bit hairy, check it out in this nice tutorial.
Let us say that we want a simpler UPSERT, where you do not have to mess with SQL Server's MERGE or rewrite the entire command as in MySQL. This can be done with triggers.
To illustrate, consider a shopping cart with a natural key of ORDER_ID and SKU. I want simple application code that does not have to figure out if it needs to do an INSERT or UPDATE, and can always happily do INSERTs, knowing they will be converted to updates if the line is already there. In other words, I want simple application code that just keeps issuing commands like this:
INSERT INTO ORDERLINES
(order_id,sku,qty)
VALUES
(1234,'ABC',5)
We can accomplish this by a trigger. The trigger must occur before the action, and it must redirect the action to an UPDATE if necessary. Let us look at examples for MySQL, Postgres, and SQL Server.
Alas, MySQL giveth, and MySQL taketh away. You cannot code your own UPSERT in MySQL because of an extremely severe limitation in MySQL trigger rules. A MySQL trigger may not affect a row in a table different from the row originally affected by the command that fired the trigger. A MySQL trigger attempting to create a new row may not affect a different row.
Note: I may be wrong about this. This limitation has bitten me on several features that I would like to provide for MySQL. I am actually hoping this limitation will not apply for UPSERTs because the new row does not yet exist, but I have not had a chance yet to try.
The Postgres trigger example is pretty simple, hopefully the logic is self-explanatory. As with all code samples, I did this off the top of my head, you may need to fix a syntax error or two.
CREATE OR REPLACE FUNCTION orderlines_insert_before_F()
RETURNS TRIGGER
AS $BODY$
DECLARE
result INTEGER;
BEGIN
SET SEARCH_PATH TO PUBLIC;
-- Find out if there is a row
result = (select count(*) from orderlines
where order_id = new.order_id
and sku = new.sku
)
-- On the update branch, perform the update
-- and then return NULL to prevent the
-- original insert from occurring
IF result = 1 THEN
UPDATE orderlines
SET qty = new.qty
WHERE order_id = new.order_id
AND sku = new.sku;
RETURN null;
END IF;
-- The default branch is to return "NEW" which
-- causes the original INSERT to go forward
RETURN new;
END; $BODY$
LANGUAGE 'plpgsql' SECURITY DEFINER;
-- That extremely annoying second command you always
-- need for Postgres triggers.
CREATE TRIGGER orderlines_insert_before_T
before insert
ON ORDERLINES
FOR EACH ROW
EXECUTE PROCEDURE orderlines_insert_before_F();
SQL Server BEFORE INSERT triggers are significantly different from Postgres triggers. First of all, they operate at the statement level, so that you have a set of new rows instead of just one. Secondly, the trigger must itself contain an explicit INSERT command, or the INSERT never happens. All of this means our SQL Server example is quite a bit more verbose.
The basic logic of the SQL Server example is the same as the Postgres, with two additional complications. First, we must use a CURSOR to loop through the incoming rows. Second, we must explicitly code the INSERT operation for the case where it occurs. But if you can see past the cruft we get for all of that, the SQL Server exmple is doing the same thing:
CREATE TRIGGER upsource_insert_before
ON orderlines
INSTEAD OF insert
AS
BEGIN
SET NOCOUNT ON;
DECLARE @new_order_id int;
DECLARE @new_sku varchar(15);
DECLARE @new_qty int;
DECLARE @result int;
DECLARE trig_ins_orderlines CURSOR FOR
SELECT * FROM inserted;
OPEN trig_ins_orderlines;
FETCH NEXT FROM trig_ins_orderlines
INTO @new_order_id
,@new_sku
,@new_qty;
WHILE @@Fetch_status = 0
BEGIN
-- Find out if there is a row now
SET @result = (SELECT count(*) from orderlines
WHERE order_id = @new_order_id
AND sku = @new_sku
)
IF @result = 1
BEGIN
-- Since there is already a row, do an
-- update
UPDATE orderlines
SET qty = @new_qty
WHERE order_id = @new_order_id
AND sku = @new_sku;
END
ELSE
BEGIN
-- When there is no row, we insert it
INSERT INTO orderlines
(order_id,sku,qty)
VALUES
(@new_order_id,@new_sku,@new_qty)
UPDATE orderlines
-- Pull the next row
FETCH NEXT FROM trig_ins_orderlines
INTO @new_order_id
,@new_sku
,@new_qty;
END -- Cursor iteration
CLOSE trig_ins_orderlines;
DEALLOCATE trig_ins_orderlines;
END
While the examples above are definitely cool and nifty, they ought to leave a certain nagging doubt in many programmers' minds. This doubt comes from the fact that an insert is not necessarily an insert anymore, which can lead to confusion. Just imagine the new programmer who has joined the team an is banging his head on his desk because he cannot figure out why his INSERTS are not working!
We can add a refinement to the process by making the function optional. Here is how we do it.
First, add a column to the ORDERLINES table called _UPSERT that is a char(1). Then modify the trigger so that the UPSERT behavior only occurs if the this column holds 'Y'. It is also extremely import to always set this value back to 'N' or NULL in the trigger, otherwise it will appear as 'Y' on subsequent INSERTS and it won't work properly.
So our new modified explicit upsert requires a SQL statement like this:
INSERT INTO ORDERLINES
(_upsert,order_id,sku,qty)
VALUES
('Y',1234,'ABC',5)
Our trigger code needs only a very slight modification. Here is the Postgres example, the SQL Server example should be very easy to update as well:
...trigger declration and definition above
IF new._upsert = 'Y'
result = (SELECT.....);
_upsert = 'N';
ELSE
result = 0;
END IF;
...rest of trigger is the same
The UPSERT feature gives us simplified code and fewer round trips to the server. Without the UPSERT there are times when the application may have to query the server to find out if a row exists, and then issue either an UPDATE or an INSERT. With the UPSERT, one round trip is eliminated, and the check occurs much more efficiently inside of the server itself.
The downside to UPSERTs is that they can be confusing if some type of explicit control is not put onto them such as the _UPSERT column.
Next week we will see a concept similar to UPSERT used to efficiently create summary tables.
RSS
Email