Home » Other » General » PLSQL with Java (merged) (Oracle 10G R2 on UNIX)
| PLSQL with Java (merged) [message #468889] |
Mon, 02 August 2010 07:22  |
kris_kaza
Messages: 11
Registered: August 2010
Location: UK
|
Junior Member |
|
|
Hi Guys
Can any of you help in answering simple questions please?
This is a daily batch job which captures end of the day changes from a set of Oracle 10G "Source" tables in (.exp) file and another overnight batch job will read from (.exp) files and Insert into Oracle 10G "Target" Tables. The .exp files are created by Oracle 10G Data Pump utilities.
In future we will be migrating Target to Teradata. All the tables will be migrated. No change in table names or structure.
The plan of action to write the later bit of PLSQL (which read from .exp files and loads it into Target tables) into Java so that the programe can be re-used on teradata.
Questions
1) Can Java read the contents with in .exp files and insert into Oracle tables?
2) Can Teradata read the contents with in .exp files and insert into Teradata tables?
3) Will we be achieving any benefit if we execute DDL (Insert) using Java rather than PLSQL. Obvious one being Platform independent.
Please note that there is no transformation of data in flight between .exp to Target tables.
Please advice
Kris
|
|
|
|
|
|
|
|
| Re: PLSQL with Java (merged) [message #468905 is a reply to message #468902] |
Mon, 02 August 2010 08:16  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:can it store output in any other file format (i.e. as a .txt file) rather than .exp file?
No.
Quote:Can Java procedural programs read the contents on .txt file and load data into Oracle 10G tables?
Sure.
Quote:Question: Is the proposed solution is not practical then what could be the alternatives please?
Extract the data into 500 CSV files and then you can load it with SQL*Loader with Oracle and similar tools with Teradata.
Or you can buy Golden Gate to transfer the data changes in both cases.
Regards
Michel
[Updated on: Mon, 02 August 2010 08:20] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: PLSQL with Java (merged) [message #468933 is a reply to message #468927] |
Mon, 02 August 2010 09:37 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
kris_kaza wrote on Mon, 02 August 2010 15:11
1) If there are 500 source tables and new rows are inserted in 200 source tables very day, then the creating a .dmp or .exp file using Oracle 10G Data pump with external tables will it create 200 individual files or 1 file (with all changed records for 200 tables)?
Datapump doesn't give changed records, it gives all records. Unless you can supply it with a query to identify changed records.
You'd get one file by default but you can change that.
kris_kaza wrote on Mon, 02 August 2010 15:11
2) If 200 (.dmp or .exp) files are created (above using Oracle Data pump and external tables), then what is the proposed Oracle inbuilt utility to create (200 .csv) files as you mentioned? Can it be PLSQL?
PL/SQL would be the obvious way.
kris_kaza wrote on Mon, 02 August 2010 15:11
3) Will the performance of PLSQL to extract overnight changed data from 200 tables be slower than Oracle Data pump? Any other disadvantages of using PLSQL to create 200 .csv files?
Probably, but if you can't use datapump that's a bit of a moot point.
kris_kaza wrote on Mon, 02 August 2010 15:11
4) If data is extracted in .csv file then can Java read it? I can use SQL loader but please note I have to attach Batch IT and Load_Time_Stamp to every record loaded by SQL loader or Java is it possible?
.csv is plain text, anything can read it but you will have to write code to do so or use sqlloader.
kris_kaza wrote on Mon, 02 August 2010 15:11
6) Can Java extract data from source Oracle 10G tables and populate a .csv or .txt file and again can Java load data into Target Oracle 10G tables and later could be re-used to load data into Teradata?
Of course, but again you'd have to code it.
|
|
|
|
| Re: PLSQL with Java (merged) [message #469087 is a reply to message #468889] |
Tue, 03 August 2010 04:14 |
kris_kaza
Messages: 11
Registered: August 2010
Location: UK
|
Junior Member |
|
|
Dear Michel, Mr Cook
Thanks for your replies and support yesterday.
One of the venueswe are exploring as an alternative to Data Pump (since .bat or .dmp) file cannot be read from Java or Teradata is, XML output.
I have created a block of programme that can create SQL statements which will generate an XML script. The example is based on simple (EMP , DEPT table example).
DECLARE
XML_TABLE_STRING VARCHAR2(5000) := ' ';
XML_COLUMN_STRING VARCHAR2(5000) ;
CURSOR A1 IS
SELECT DISTINCT TABLE_NAME
FROM ALL_TAB_COLS
WHERE OWNER = 'SYS' AND TRIM(TABLE_NAME) IN ('EMP','DEPT','BONUS','SALGRADE');
CURSOR B1 (A VARCHAR2) IS
SELECT DISTINCT COLUMN_NAME
FROM ALL_TAB_COLS
WHERE TABLE_NAME =A;
BEGIN
FOR G1 IN A1
LOOP
XML_TABLE_STRING := 'SELECT ';
FOR H1 IN B1(G1.TABLE_NAME)
LOOP
XML_COLUMN_STRING := XML_COLUMN_STRING || 'XMLELEMENT ('||H1.COLUMN_NAME||','||H1.COLUMN_NAME||'), ';
END LOOP;
XML_COLUMN_STRING := SUBSTR(XML_COLUMN_STRING, 1, LENGTH(XML_COLUMN_STRING) - 2);
XML_TABLE_STRING := XML_TABLE_STRING || ' '|| XML_COLUMN_STRING ||' FROM '|| G1.TABLE_NAME ||';';
DBMS_OUTPUT.PUT_LINE (XML_TABLE_STRING );
XML_COLUMN_STRING := ' ';
XML_TABLE_STRING:= ' ';
END LOOP;
END;
The PLSQL block above will generate an SQL Script.
Quick questions
1) When I execute the script generated above (few more modifications needed) and it generates a single XML output, (i.e. XML output that data details of data for all 4 tables Emp, Dept, Bonus, Salgrade)
Can PLSQL read this single XML file (with data from 4 tables) and load data into these 4 tables?
Please advise
Kris
CM: added code tags, please do so yourself next time.
[Updated on: Tue, 03 August 2010 10:22] by Moderator Report message to a moderator |
|
|
|
| Re: PLSQL with Java (merged) [message #469088 is a reply to message #469087] |
Tue, 03 August 2010 04:21 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Suggest you read up on xml in the documentation.
Oracle has xml integration but I suspect you'll need to have seperate xml files for each table - then you can treat the xml file as a table in a sql statement.
Otherwise you can read it as plain text, but obviously, again, you'd have to code that.
Oh - and have you really got tables in SYS?
If you have - move them, you should never put anything in sys.
Can you also please read the orafaq forum guide and in future follow its instructions on gow to format posts.
|
|
|
|
| Re: PLSQL with Java (merged) [message #469163 is a reply to message #469087] |
Tue, 03 August 2010 10:30 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
1/ You learn to indent the code then a) it is more readable b) it is more understandable c) it is more maintanable
2/ Are you sure you get an XML data? For this is not XML:
SQL> DECLARE
2 XML_TABLE_STRING VARCHAR2(5000) := ' ';
3 XML_COLUMN_STRING VARCHAR2(5000) ;
4 CURSOR A1 IS
5 SELECT DISTINCT TABLE_NAME
6 FROM ALL_TAB_COLS
7 WHERE OWNER = 'MICHEL' AND TRIM(TABLE_NAME) IN ('EMP','DEPT');
8 CURSOR B1 (A VARCHAR2) IS
9 SELECT DISTINCT COLUMN_NAME
10 FROM ALL_TAB_COLS
11 WHERE TABLE_NAME =A;
12
13 BEGIN
14 FOR G1 IN A1
15 LOOP
16
17 XML_TABLE_STRING := 'SELECT ';
18
19 FOR H1 IN B1(G1.TABLE_NAME)
20 LOOP
21
22 XML_COLUMN_STRING := XML_COLUMN_STRING || 'XMLELEMENT ('||H1.COLUMN_NAME||','||H1.COLUMN_NAME||'), ';
23
24 END LOOP;
25
26 XML_COLUMN_STRING := SUBSTR(XML_COLUMN_STRING, 1, LENGTH(XML_COLUMN_STRING) - 2);
27
28 XML_TABLE_STRING := XML_TABLE_STRING || ' '|| XML_COLUMN_STRING ||' FROM '|| G1.TABLE_NAME ||';';
29
30 DBMS_OUTPUT.PUT_LINE (XML_TABLE_STRING );
31
32 XML_COLUMN_STRING := ' ';
33 XML_TABLE_STRING:= ' ';
34
35 END LOOP;
36 END;
37 /
SELECT XMLELEMENT (DNAME,DNAME), XMLELEMENT (DEPTNO,DEPTNO), XMLELEMENT (LOC,LOC) FROM DEPT;
SELECT XMLELEMENT (SAL,SAL), XMLELEMENT (HIREDATE,HIREDATE), XMLELEMENT (MGR,MGR), XMLELEMENT (COMM,COMM), XMLELEMENT
(DEPTNO,DEPTNO), XMLELEMENT (ENAME,ENAME), XMLELEMENT (JOB,JOB), XMLELEMENT (EMPNO,EMPNO) FROM EMP;
SQL> SELECT XMLELEMENT (DNAME,DNAME), XMLELEMENT (DEPTNO,DEPTNO), XMLELEMENT (LOC,LOC) FROM DEPT;
XMLELEMENT(DNAME,DNAME)
---------------------------------------------------------------------------------------------------
XMLELEMENT(DEPTNO,DEPTNO)
---------------------------------------------------------------------------------------------------
XMLELEMENT(LOC,LOC)
---------------------------------------------------------------------------------------------------
<DNAME>ACCOUNTING</DNAME>
<DEPTNO>10</DEPTNO>
<LOC>NEW YORK</LOC>
<DNAME>RESEARCH</DNAME>
<DEPTNO>20</DEPTNO>
<LOC>DALLAS</LOC>
<DNAME>SALES</DNAME>
<DEPTNO>30</DEPTNO>
<LOC>CHICAGO</LOC>
<DNAME>OPERATIONS</DNAME>
<DEPTNO>40</DEPTNO>
<LOC>BOSTON</LOC>
But you can use (I limit the output to 3 rows to save space):
SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980 0:0:0</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="2">
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/20/1981 0:0:0</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="3">
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/22/1981 0:0:0</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
</ROWSET>
This is a real XML output of the table.
Regards
Michel
|
|
|
|
|
|
|
|
|
|
| Re: PLSQL with Java (merged) [message #472890 is a reply to message #472849] |
Tue, 24 August 2010 10:22 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Will this work for CLOB Objects?
Why don't you try it?
Quote:Can the query extract CLOB Information because Line size is limited to 32767 characters in SQL.
I don't know which line size you are talking about but you can return a CLOB (of any size) from SQL.
SQL> select length(col) from tt;
LENGTH(COL)
-----------
50000
SQL> select dbms_xmlquery.getxml('select col from tt') from dual;
DBMS_XMLQUERY.GETXML('SELECTCOLFROMTT')
------------------------------------------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<COL><TABLE id="tabelaTec1"> <THEAD> <TR> <TD> <DIV>Dionièki fondovi - Equ
ity funds</DIV> <DIV>Proširena teèajnica</DIV> <DIV>Grafièki prikaz</DIV></TD></T
R> <TR> <TH> Fond</TH> <TH> </TH> <TH> </TH> <TH> </TH> <TH&
gt;Datum</TH> <TH>Vrijednost</TH> <TH>Val.</TH> <TH>Promj. %</TH> <TH>'1
0 %</TH> <TH>3mj %</TH> <TH>1g %</TH> <TH>Info</TH> <TH>Pristup</TH&g
t;</TR></THEAD> <TBODY> <TR> <TD>KD Victoria</TD> <TD></TD> <TD>&l
t;/TD> <TD></TD> <TD>23.07.</TD> <TD>14,6417</TD> <TD>kn</TD> <TD&
gt; <DIV>1,83</DIV></TD> <TD>-3,88</TD> <TD>-7,22</TD> <TD>-5,94</TD&
gt; <TD></TD> <TD>Pristup </TD></TR> <TR> <TD>ST Global Equity</TD> <
TD></TD> <TD></TD> <TD></TD> <TD>23.07.</TD> <TD>48,7616</TD> &
lt;TD>kn</TD> <TD> <DIV>-0,22</DIV></TD> <TD>-9,21</TD> <TD>-5,77<
/TD> <TD>-20,33</TD> <TD></TD> <TD>Pristup </TD></TR> <TR> <TD>
HI-growth</TD> <TD></TD> <TD></TD> <TD></TD> <TD>23.07.</TD> <T
D>8,0705</TD> <TD>€</TD> <TD> <DIV>0,31</DIV></TD> <TD>-3,35</TD&g
t; <TD>-7,55</TD> <TD>6,33</TD> <TD></TD> <TD>Pristup </TD></TR> &
lt;TR> <TD>ZB trend</TD> <TD></TD> <TD></TD> <TD></TD> <TD>23.0
7.</TD> <TD>127,2600</TD> <TD>€</TD> <TD> <DIV>0,79</DIV></TD> <
;TD>4,59</TD> <TD>-3,83</TD> <TD>18,22</TD> <TD></TD> <TD>Pristup <
;/TD></TR> <TR> <TD>KD Prvi izbor </TD> <TD></TD> <TD></TD> <TD>
;</TD> <TD>23.07.</TD> <TD>11,9794</TD> <TD>kn</TD> <TD> <DIV>0,86
</DIV></TD> <TD>4,01</TD> <TD>-3,13</TD> <TD>6,14</TD> <TD></TD
> <TD>Pristup </TD></TR> <TR> <TD>Raiffeisen World</TD> <TD></TD> <
;TD></TD> <TD></TD> <TD>23.07.</TD> <TD>97,7200</TD> <TD>€</TD>
<TD> <DIV>0,56</DIV></TD> <TD>1,42</TD> <TD>-6,43</TD> <TD>10,58&
lt;/TD> <TD></TD> <TD><FONT>Pristup</FONT> </TD></TR> <TR> <TD>
ZB euroaktiv</TD> <TD></TD> <TD></TD> <TD></TD> <TD>23.07.</TD> &l
t;TD>100,8200</TD> <TD>€</TD> <TD> <DIV>0,38</DIV></TD> <TD>-0,52<
/TD> <TD>-4,99</TD> <TD>12,23</TD> <TD></TD> <TD>Pristup </TD></TR
> <TR> <TD>FIMA Equity</TD> <TD></TD> <TD></TD> <TD></TD> <T
D>23.07.</TD> <TD>79,1031</TD> <TD>kn</TD> <TD> <DIV>0,03</DIV></T
D> <TD>-12,56</TD> <TD>-11,56</TD> <TD>-20,68</TD> <TD></TD> <TD&g
t;Pristup </TD></TR> <TR> <TD>ZB BRIC+</TD> <TD></TD> <TD></TD> &l
t;TD></TD> <TD>23.07.</TD> <TD>97,6900</TD> <TD>€</TD> <TD> <DIV&g
t;0,89</DIV></TD> <TD>-2,31</TD> <TD>-0,64</TD> <TD>N/A</TD> <TD>&
lt;/TD> <TD>Pristup </TD></TR></TBODY></TABLE>#########################################
########################################################################################################################
########################################################################################################################
...<output sniped>...
########################################################################################################################
########################################################################################################################
########################################################################################################################
#############################################################################</COL>
</ROW>
</ROWSET>
1 row selected.
Regards
Michel
[Updated on: Tue, 24 August 2010 10:25] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Nov 26 04:02:56 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
