Looking for something? Try here..

Loading...

Thursday, December 29, 2016

Inserting > 4000 chars in a CLOB column

For a small testing involving CLOB column, I need to create a test table with clob column containing more than 4000 chars.

In order to achieve this, I did the following.
SQL> create table clob_test (name varchar2(20), details clob) tablespace tools;

Table created.

SQL> insert into clob_test values ('AAA', 'Geography is a systematic study of earth and its features. Traditionally, geography has been associated with
  2  cartography and place names. Although many
  3  geographers are trained in toponymy and cartology, this is not their main preoccupation. Geographers study the space and the temporal database distribution of
phenomena, processes, and features as well as the interaction of humans and their environment.[7] Because space and place affect a variety of topics, such as
  4    5  economics, health, climate, plants and animals, geography is highly interdisciplinary. The interdisciplinary nature of the geographical approach depends on an
  6  attentiveness to the relationship between physical and human phenomena and its spatial patterns.[8]
  7  Names of places...are not geography...know by heart a whole gazetteer full of them would not, in itself, constitute anyone a geographer. Geography has higher
  8  aims than this: it seeks to classify phenomena (alike of the natural and of the political world, in so far as it treats of the latter), to compare, to
  9  generalize, to ascend from effects to causes, and, in doing so, to trace out the laws of nature and to mark their influences upon man. This is a description of
 10  the world—that is Geography. In a word Geography is a Science—a thing not of mere names but of argument and reason, of cause and effect.[9]
 11  —William Hughes, 1863
 12  Just as all phenomena exist in time and thus have a history, they also exist in space and have a geography.[10]
 13  —United States National Research Council, 1997
 14  Geography as a discipline can be split broadly into two main subsidiary fields: human geography and physical geography. The former largely focuses on the built
 15  environment and how humans create, view, manage, and influence space. The latter examines the natural environment, and how organisms, climate, soil, water, and
 16  landforms produce and interact.[11] The difference between these approaches led to a third field, environmental geography, which combines the physical and the
 17  human geography, and looks at the interactions between the environment and humans.[7]
 18  The oldest known world maps date back to ancient Babylon from the 9th century BC.[12] The best known Babylonian world map, however, is the Imago Mundi of 600
 19  BC.[13] The map as reconstructed by Eckhard Unger shows Babylon on the Euphrates, surrounded by a circular landmass showing Assyria, Urartu[14] and several
 20  cities, in turn surrounded by a "bitter river" (Oceanus), with seven islands arranged around it so as to form a seven-pointed star. The accompanying text
 21  mentions seven outer regions beyond the encircling ocean. The descriptions of five of them have survived.[15] In contrast to the Imago Mundi, an earlier
 22  Babylonian world map dating back to the 9th century BC depicted Babylon as being further north from the center of the world, though it is not certain what
 23  that center was supposed to represent.[12]
The ideas of Anaximander (c. 610 BC-c. 545 BC): considered by later Greek writers to be the true founder of geography, come to us through fragments quoted by
 24   25  his successors. Anaximander is credited with the invention of the gnomon, the simple, yet efficient Greek instrument that allowed the early measurement of
 26  latitude. Thales is also credited with the prediction of eclipses. The foundations of geography can be traced to the ancient cultures, such as the ancient,
 27  medieval, and early modern Chinese. The Greeks, who were the first to explore geography as both art and science, achieved this through Cartography,
Philosophy, and Literature, or through Mathematics. There is some debate about who was the first person to assert that the Earth is spherical in shape, with
 28   29  the credit going either to Parmenides or Pythagoras. Anaxagoras was able to demonstrate that the profile of the Earth was circular by explaining eclipses.
 30  However, he still believed that the Earth was a flat disk, as did many of his contemporaries. One of the first estimates of the radius of the Earth was made
 31  by Eratosthenes.[16]
 32  The first rigorous system of latitude and longitude lines is credited to Hipparchus. He employed a sexagesimal system that was derived from Babylonian
 33  mathematics. The meridians were sub-divided into 360°, with each degree further subdivided 60 (minutes). To measure the longitude at different location on
 34  Earth, he suggested using eclipses to determine the relative difference in time.[17] The extensive mapping by the Romans as they explored new lands would
 35  later provide a high level of information for Ptolemy to construct detailed atlases. He extended the work of Hipparchus, using a grid system on his maps and
 36  adopting a length of 56.5 miles for a degree.[18]
 37  From the 3rd century onwards, Chinese methods of geographical study and writing of geographical literature became much more complex than what was found in
 38  Europe at the time (until the 13th century).[19] Chinese geographers such as Liu An, Pei Xiu, Jia Dan, Shen Kuo, Fan Chengda, Zhou Daguan, and Xu Xiake wrote
important treatises, yet by the 17th century advanced ideas and methods of Western-style geography were adopted in China.' 39  );
insert into clob_test values ('AAA', 'Geography is a systematic study of earth and its features. Traditionally, geography has been associated with
                                     *
ERROR at line 1:
ORA-01704: string literal too long
 
 
You can see that the insert statement erred out with ORA-01704 as the length of the field that I tried to insert is of 5147 characters.
So to over come this situation, we can make use of bind variables and a pl/sql block which can handle data upto 32k characters as shown below.

The code is very simple:

declare
clobdata varchar2(32767) := <data to be inserted>;
begin
execute immediate 'insert into <table> values(col1, :1)' using clobdata;
end;
/

Eg: as below.
SQL> set serverout on
declare
SQL>   2  clobdata varchar2(32767) := 'Geography is a systematic study of earth and its features. Traditionally, geography has been associated with
  3  cartography and place names. Although many
geographers are trained in toponymy and cartology, this is not their main preoccupation. Geographers study the space and the temporal database distribution of
  4    5  phenomena, processes, and features as well as the interaction of humans and their environment.[7] Because space and place affect a variety of topics, such as
  6  economics, health, climate, plants and animals, geography is highly interdisciplinary. The interdisciplinary nature of the geographical approach depends on an
  7  attentiveness to the relationship between physical and human phenomena and its spatial patterns.[8]
  8  Names of places...are not geography...know by heart a whole gazetteer full of them would not, in itself, constitute anyone a geographer. Geography has higher
  9  aims than this: it seeks to classify phenomena (alike of the natural and of the political world, in so far as it treats of the latter), to compare, to
generalize, to ascend from effects to causes, and, in doing so, to trace out the laws of nature and to mark their influences upon man. This is a description of
 10   11  the world—that is Geography. In a word Geography is a Science—a thing not of mere names but of argument and reason, of cause and effect.[9]
 12  —William Hughes, 1863
 13  Just as all phenomena exist in time and thus have a history, they also exist in space and have a geography.[10]
—United States National Research Council, 1997
 14   15  Geography as a discipline can be split broadly into two main subsidiary fields: human geography and physical geography. The former largely focuses on the built
 16  environment and how humans create, view, manage, and influence space. The latter examines the natural environment, and how organisms, climate, soil, water, and
landforms produce and interact.[11] The difference between these approaches led to a third field, environmental geography, which combines the physical and the
 17   18  human geography, and looks at the interactions between the environment and humans.[7]
 19  The oldest known world maps date back to ancient Babylon from the 9th century BC.[12] The best known Babylonian world map, however, is the Imago Mundi of 600
 20  BC.[13] The map as reconstructed by Eckhard Unger shows Babylon on the Euphrates, surrounded by a circular landmass showing Assyria, Urartu[14] and several
 21  cities, in turn surrounded by a "bitter river" (Oceanus), with seven islands arranged around it so as to form a seven-pointed star. The accompanying text
 22  mentions seven outer regions beyond the encircling ocean. The descriptions of five of them have survived.[15] In contrast to the Imago Mundi, an earlier
Babylonian world map dating back to the 9th century BC depicted Babylon as being further north from the center of the world, though it is not certain what
 23   24  that center was supposed to represent.[12]
 25  The ideas of Anaximander (c. 610 BC-c. 545 BC): considered by later Greek writers to be the true founder of geography, come to us through fragments quoted by
 26  his successors. Anaximander is credited with the invention of the gnomon, the simple, yet efficient Greek instrument that allowed the early measurement of
latitude. Thales is also credited with the prediction of eclipses. The foundations of geography can be traced to the ancient cultures, such as the ancient,
 27   28  medieval, and early modern Chinese. The Greeks, who were the first to explore geography as both art and science, achieved this through Cartography,
Philosophy, and Literature, or through Mathematics. There is some debate about who was the first person to assert that the Earth is spherical in shape, with
 29   30  the credit going either to Parmenides or Pythagoras. Anaxagoras was able to demonstrate that the profile of the Earth was circular by explaining eclipses.
However, he still believed that the Earth was a flat disk, as did many of his contemporaries. One of the first estimates of the radius of the Earth was made
 31   32  by Eratosthenes.[16]
 33  The first rigorous system of latitude and longitude lines is credited to Hipparchus. He employed a sexagesimal system that was derived from Babylonian
 34  mathematics. The meridians were sub-divided into 360°, with each degree further subdivided 60 (minutes). To measure the longitude at different location on
Earth, he suggested using eclipses to determine the relative difference in time.[17] The extensive mapping by the Romans as they explored new lands would
 35   36  later provide a high level of information for Ptolemy to construct detailed atlases. He extended the work of Hipparchus, using a grid system on his maps and
adopting a length of 56.5 miles for a degree.[18]
 37   38  From the 3rd century onwards, Chinese methods of geographical study and writing of geographical literature became much more complex than what was found in
 39  Europe at the time (until the 13th century).[19] Chinese geographers such as Liu An, Pei Xiu, Jia Dan, Shen Kuo, Fan Chengda, Zhou Daguan, and Xu Xiake wrote
important treatises, yet by the 17th century advanced ideas and methods of Western-style geography were adopted in China.';
 40   41  begin
   execute immediate 'insert into clob_test values (''BBB'', :1)' using clobdata;
 42   43  end;
/  44

PL/SQL procedure successfully completed.

SQL> select * from clob_test;

NAME
--------------------
DETAILS
--------------------------------------------------------------------------------
BBB
Geography is a systematic study of earth and its features. Traditionally, geogra


SQL>

Now you can see that the data we are trying to insert worked without issues.

There are also other ways using DBMS_SQL package and also using SQL*Loader. You can check about them using Oracle support notes Doc ID 160885.1 and Doc ID 2158771.1

Happy data loading...!!!

Monday, October 3, 2016

IGNOREDELETES with Oracle GoldenGate SQLEXEC

This is one of the several issues I underwent during my current project involving Oracle GoldenGate.
I'm using the below mapping statement with a lookup query on another table using SQLEXEC.
SQLEXEC (ID LOOKUP_MOI, QUERY "select instanceid from DATASTORE.MANAGEDOBJECTINSTANCE &
where TROUBLEREPORTID=:TR_IN and INSTANCENAME=:I_IN", PARAMS (TR_IN = C911001001, I_IN = C911001102)),& 
COLMAP ( 
ATTRIBUTEID=@COLSTAT (MISSING), 
TROUBLEREPORTID=@IF (@STRFIND (C911001001,'ID',1)=1,@COLSTAT (NULL),C911001001), 
NAME=C911003014, 
VALUE=C911005010, 
TYPE=C911003018, 
PARENTNAME=C911005001, 
INSTANCEID=@IF (@COLTEST (@GETVAL(LOOKUP_MOI.instanceid),NULL,INVALID),@COLSTAT (NULL),@GETVAL(LOOKUP_MOI.instanceid)), 
ENTRYID=C1);
Here when the lookup query executes, for the data it can find, we don't have any issues, but when a record doesn't have any value, for eg. let's say there is no matching where clause values to find, then the query results in ORA-01403 no data found.

Few data were never replaced by the new instanceid value or NULL during update operations. This is not an expected behaviour and hence in order to find which record's data the query is not able to find or to confirm that the query is actually looked up to result in ORA-01403, I had to turn on reporting of the error records as instanceid is a NOT NULL column. Reporting of the records erring can be turned on by using ERROR REPORT in the SQLEXEC statement as below.
SQLEXEC (ID LOOKUP_MOI, QUERY "select instanceid from DATASTORE.MANAGEDOBJECTINSTANCE &
where TROUBLEREPORTID=:TR_IN and INSTANCENAME=:I_IN", PARAMS (TR_IN = C911001001, I_IN = C911001102), ERROR REPORT) & 
This makes the GoldenGate process to register the details on the discard file with the records erred. I'm getting the following "VALUE NOT MAPPED" in the discard file for few records.
Stored procedure LOOKUP_MOI failed at seqno 1 rba 144430729 
Missing required param: TR_IN 

Input parameter values... 

TR_IN: * VALUE NOT MAPPED * 
I_IN: stbordel 
TR_IN: * VALUE NOT MAPPED * 
I_IN: * VALUE NOT MAPPED * 
* 
* 
This shouldn't be the case as I'm forcing the source table to log both C911001001 and C911001102 columns. C911001102 is not mapped to any of target table column but used in the lookup query.
GGSCI (xxx00450 as ggs_dba@XXXDB) 5> info trandata aradmin.t539 

Logging of supplemental redo log data is enabled for table ARADMIN.T539. 

Columns supplementally logged for table ARADMIN.T539: C1, C911001001, C911001102. 

Prepared CSN for table ARADMIN.T539: 13678637504852 
Now to check whether the columns are actually logged by the extract process, I use logdump utility to dig the trail file at that particular rba which reveals the values are actually logged.
Logdump 68 >pos 144430729 
Reading forward from RBA 144430729 
Logdump 69 >n 
___________________________________________________________________ 
Hdr-Ind : E (x45) Partition : . (x0c) 
UndoFlag : . (x00) BeforeAfter: A (x41) 
RecLength : 171 (x00ab) IO Time : 2016/09/12 03:44:44.000.000 
IOType : 134 (x86) OrigNode : 255 (xff) 
TransInd : . (x00) FormatType : R (x52) 
SyskeyLen : 0 (x00) Incomplete : . (x00) 
AuditRBA : 50785 AuditPos : 51572240 
Continued : N (x00) RecCount : 1 (x01) 

2016/09/12 03:44:44.000.000 GGSUnifiedUpdate Len 171 RBA 144430729 
Name: ARADMIN.T539 (TDR Index: 2) 
After Image: Partition 12 G b 
0000 0068 0000 0013 0000 000f 3030 3030 3030 3034 | ...h........00000004 
3236 3731 3136 3200 0400 0a00 0000 0674 6d75 7365 | 2671162........tmuse 
7200 0500 0a00 0000 0000 0057 d65c 4a00 0600 0a00 | r..........W.\J..... 
0000 0000 0000 0000 0200 0a00 1300 0000 0f54 5230 | .................TR0 
3030 3030 3137 3137 3838 3537 0017 000c 0000 0008 | 000017178857........ 
7374 626f 7264 656c 0000 0013 0000 000f 3030 3030 | stbordel........0000 
3030 3034 3236 3731 3136 3200 0400 0800 0000 0474 | 00042671162........t 
Before Image Len 108 (x0000006c) 
BeforeColumnLen 104 (x00000068) 
Column 0 (x0000), Len 19 (x0013) 
Column 4 (x0004), Len 10 (x000a) 
Column 5 (x0005), Len 10 (x000a) 
Column 6 (x0006), Len 10 (x000a) 
Column 10 (x000a), Len 19 (x0013) 
Column 23 (x0017), Len 12 (x000c) 

After Image Len 63 (x0000003f) 
Column 0 (x0000), Len 19 (x0013) 
Column 4 (x0004), Len 8 (x0008) 
Column 5 (x0005), Len 10 (x000a) 
Column 6 (x0006), Len 10 (x000a) 
Now, I got confused on why is that the discard file shows or GoldenGate throws that the values are not mapped. Raised an SR to get it clarified but unfortunately Oracle support was dead slow in responding and with no proper updates after uploading all the required param files, report files, discard files, trail log files, etc.

After careful consideration and investigation and comparing the data which were discarded and which were correctly getting the instanceid values, I can get a common pattern on the data as only the records deleted on the source database are having this VALUE NOT MAPPED error. I now got the clue on why this is happening. As per the application requirement, we are not propagating deletes from the source DB to the target DB, target DB being a form of warehousing for the closed tickets. I am using IGNOREDELETES in my replicat parameter file. The records are deleted and hence the fields in the trail has no value on it. :)

Coming to the actual issue on how to resolve the SQLEXEC not to reject the record, according to Oracle GoldenGate documents, when a SQLEXEC encounters an issue/ error, this column should be a "column missing" condition

IGNORE
Causes Oracle GoldenGate to ignore all errors associated with the stored procedure or query and continue processing. Any resulting parameter extraction results in "column missing" conditions. This is the default.


Find details here
So in my above mapping statement, by making INSTANCEID column to check for MISSING instead of NULL or INVALID should fix the issue. Isn't it? 
INSTANCEID=@IF (@COLTEST (@GETVAL(LOOKUP_MOI.instanceid),MISSING),@COLSTAT (NULL),@GETVAL(LOOKUP_MOI.instanceid)),
If you have answered Yes, then we have another twist here..
As there is actually no value mapped for the query to execute, this record is rejected and is not returning NULL as desired.
From Oracle documentation,

PARAMS [OPTIONAL | REQUIRED] parameter_name = {source_column | OGG_function} |NOPARAMS


For Oracle, whether or not a parameter is optional is automatically determined when retrieving the stored procedure definition.
REQUIRED indicates that a parameter value must be present. If the parameter value is not present, the SQL will not be executed.

We can also create a procedure to return NULL if there is an ORA-01403 returned only in case of providing both the required parameters. So this effort will again result in rejection of the record (doing nothing on the record)

The solution is to take the value from the lookup query itself by using the case statement of the select query which I have used in my parameter file as below.
select case when exists (select instanceid from DATASTORE.MANAGEDOBJECTINSTANCE where TROUBLEREPORTID=:TR_IN and INSTANCENAME=:I_IN)   
then (select instanceid from DATASTORE.MANAGEDOBJECTINSTANCE where TROUBLEREPORTID=:TR_IN and INSTANCENAME=:I_IN)   
else NULL end instanceid from dual;
This actually returns either a valid result or a NULL so we don't have a missing column here.. The suggestion is not to leave the record unhandled.
Thanks K.Gan for letting me know this via OTN community discussion here.

The solution seems simple but the effort it took to debug the issue and to derive the actual result is really a painful task. The result is a bliss..

Few things..
The entire investigation including SR update and deriving at the solution/desired result took around a week's time..
When ignoring part of transaction (IGNOREDELETES in our case), be very careful on what might happen to the data and how to deal with it..
Tech forums such as OTN community is your best friend when seeking technical help. Make friends/ Network as much as you can..

Happy Ignoring transactions !! :)