30. Oktober 2009
In der täglichen Arbeit kommt es immer wieder vor, dass das Ergebnis eines Select-Abfrage in eine “comma separated list” umgewandelt werden soll. Während viele Programmiersprachen wie php hierzu Funktionen wie explode/ implode bereitstellen, fehlt eine solche Funktion in Oracle.
Im folgenden zeigen wir, wie mittels zweier simpler PL/SQL Funktionen ein “Concatinate” und “Split” realisierbar ist.
f_concat
Der Funktion f_concat wird das Select-Statement als REF CURSOR übergeben, ein Trennzeichen (Delimiter) ist optional. Der Rückgabewert ist ein String mit der maximale Länge von 32767 Zeichen.
CREATE OR REPLACE FUNCTION
f_concat (p_cur sys_refcursor, p_del varchar2:= ',')
RETURN VARCHAR2 IS
l_value VARCHAR2 (32767);
l_result VARCHAR2 (32767);
BEGIN
LOOP
FETCH p_cur
INTO l_value;
EXIT WHEN p_cur%NOTFOUND;
IF l_result IS NOT NULL THEN
l_result := l_result || p_del;
END IF;
l_result := l_result || l_value;
END LOOP;
RETURN l_result;
END f_concat;
Somit lassen sich zum Beispiel die Attribute einer Tabelle als CSV-Liste ausgeben:
SELECT f_concat (cursor (SELECT column_name FROM dba_tab_columns WHERE table_name = 'CCOL$'), ', ') cols FROM DUAL;
Liefert folgende Ausgabe:
CON#, OBJ#, COL#, POS#, INTCOL#, SPARE1, SPARE2, ...
f_split
Das Gegenstück zur oben gezeigen Funktion ist f_split. Die Funktion extrahiert die einzelnen Tokens in einem String und gibt sie jeweils in einer Zeile zurück. Die Funktion benötigt den Typ “table of varchar”. Die Argumente werden extrahiert und in einer Pipeline zurückgegeben.
create or replace type t_split as table of varchar2(32767); / create or replace function f_split(p_list varchar2, p_del varchar2 := ';') return t_split pipelined is l_idx pls_integer; l_list varchar2(32767) := p_list; l_value varchar2(32767); begin loop l_idx := instr(l_list,p_del); if l_idx > 0 then pipe row(substr(l_list,1,l_idx-1)); l_list := substr(l_list,l_idx + length(p_del)); else pipe row(l_list); exit; end if; end loop; return; end f_split;
Durch den type cast “TABLE” kann die Funktion wie eine Tabelle verwendet werden:
select * from table(f_split('eis;zwoi;drü;vier;foif'));
eis zwoi drü vier foif
Oliver Stock, Werner Jacobs
Co-CEOs, Database-Experts
July 2009
Our company name reflects very well what we do, Basically, we’re a data warehouse Business Intelligence company, providing consulting and reporting services for our customers. When we wanted to expand our basic SAP solution, we knew that we needed the right tool.
We had already established ballpark parameters. The tool had to work well and reliably; connectivity was critical; and it needed to integrate heterogeneous environments. Cost, of course, was a factor. Our big customers, like international banks, have no problem spending money, but for small and medium-size businesses, you need a solution that’s more cost-effective than the proprietary market leaders.
In the proprietary arena, we looked at Informatica, but it was prohibitively expensive and had a very complicated user interface. Oracle Warehouse Builder was a contender, but we found that it only supports the ELT approach, not ETL, which made it unsuitable in heterogeneous environments. Add limited connectivity outside of the Oracle world and we took it off the table. The desire to avoid vendor lock-in was a major factor bringing us to open source.
It was actually word-of-mouth that led us to Talend. A friend of ours is the IT Manager at a telco that’s already using Talend and he was tremendously enthusiastic about it. We talked to a few other users and responses were uniformly enthusiastic. We downloaded Talend Open Source for evaluation and our findings supported all the positive feedback we’d gotten from the field.
There were several factors which tipped the scales in Talend’s favor. First and foremost, of course, was the functionality and the performance that Talend offers. Another major consideration was the variety of connectors already available. We need to connect to different systems (SAP, CRM) and having connectors is a huge advantage. We’re very impressed with the give and take that an open source community offers.
We’ve just finished setting up our pilot project, which extends our basic SAP solution. We pull data from two different sources – one is data from a DWH, and the other is directly connected to the SAP system database. We’re also directly accessing SAP tables.. Once extracted, we transform the information, write it into a database, and then generate reports on a daily or weekly basis from that database. These can be fairly large. For example, we’re processing HR reports for a health company and maintain information on 5,000 employees. In this arena, of course, there’s always the confidentiality aspect to consider.
We’ve found other benefits with Talend. The tool is easy to work with; just install it and you’re virtually ready to go. Reusing code is another plus. Our customers like being able to look at the Java source code and verify the processing.
From our standpoint, though, the biggest benefit is that the customer has a stable working tool with good functionality that easily integrates into his system, and customers can opt for either Talend Open Studio or Talend Integration Suite depending on need. The open source approach has been a huge success for us.
The result of a recent merger, Database-Experts is an information services and consulting company, providing solutions to the German market in the data warehousing, business intelligence, and system integration application areas
At the end of 2007 the company expanded its business to Switzerland and now comprises a team of 15 employees who assist the customers in all phases of their projects. In Switzerland and in Germany their client list comprises top 10 DAX, SMI listed companies as well as small and medium-sized businesses.
26. Oktober 2009
Die Talend Roadshow findet am 10. November in Zürich statt:
Klicken Sie hier, für die Anmeldung und Details.
Verstärkung im Back Office
Wir freuen uns Frau Nuding in unserem Team begrüssen zu dürfen. Sie unterstützt uns ab dem 21.September im Back Office.