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.
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...!!!
No comments:
Post a Comment