How to pass CLOB argument in EXECUTE IMMEDIATE
Oracle 11g allows the usage of CLOB datatypes as an argument which eradicates the constraint we faced on the length of strings when passed as an argument to Execute immediate.
Lets take an example to show how execute immediate failed for strings of size > 32K
Example 1:
DECLARE
var VARCHAR2 (32767);
BEGIN
var := 'create table temp_a(a number(10))';
-- to make a string of length > 32767
WHILE (LENGTH (var) < 33000)
LOOP
var := var || CHR (10) || '--comment';
END LOOP;
DBMS_OUTPUT.put_line (LENGTH (var));
EXECUTE IMMEDIATE var;
END;
It will throw an obvious error : ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Lets start with how these scenarios were handled prior to introduction of CLOB argument in Execute Immediate
DBMS_SQL was used with its inbuilt functions to take care of Dynamic SQL. Its inbuilt function PARSE was used to take care of dynamic SQL of 64k size.
But it had certain drawbacks:
- DBMS_SQL.PARSE() could not handle CLOB argument
- A REF CURSOR can not be converted to a DBMS_SQL cursor and vice versa to support interoperability
- DBMS_SQL did not supports the full range of data types (including collections and object types)
- DBMS_SQL did not allows bulk binds using user-define (sic) collection types
A Simple example to show how DBMS_SQL was used to take care of long strings :
Example 2:
DECLARE
vara VARCHAR2 (32767);
varb VARCHAR2 (32767);
ln_cursor NUMBER;
ln_result NUMBER;
ln_sql_id NUMBER := 1;
BEGIN
ln_cursor := DBMS_SQL.open_cursor;
vara := 'create table testa( a number(10),';
-- to make length 32 k
while length(vara) <32000
loop
vara := vara || chr(10) || '--comment';
end loop;
varb := ' b number(10))';
-- to make length 32 k
while length(varb) <32000 loop
varb := varb || chr(10) || '--comment';
end loop;
dbms_output.put_line (length(vara)||'and'||length(varb));
DBMS_SQL.parse (ln_cursor, vara ||chr(10)|| varb, DBMS_SQL.native);
ln_result := DBMS_SQL.EXECUTE (ln_cursor);
DBMS_SQL.close_cursor (ln_cursor);
END;
CLOB argument in Oracle 11g
Oracle Database 11g removes DBMS_SQL limitations restrictions to make the support of dynamic SQL from PL/SQL functionally complete.
Lets see it through an Example.
Example 3: The only difference in Example 3 as compared to Example 2 is Use of CLOB for the declaration of vara variable.
DECLARE
vara CLOB;
ln_cursor NUMBER;
ln_result NUMBER;
ln_sql_id NUMBER := 1;
BEGIN
ln_cursor := DBMS_SQL.open_cursor;
vara := 'create table testa( a number(10))';
-- to make length 32 k
while length(vara) <70000
loop
vara := vara || chr(10) || '--comment';
end loop;
dbms_output.put_line (length(vara));
DBMS_SQL.parse (ln_cursor, vara, DBMS_SQL.native);
ln_result := DBMS_SQL.EXECUTE (ln_cursor);
DBMS_SQL.close_cursor (ln_cursor);
END;
Now Both Native Dynamic SQL and DBMS_SQL support SQL strings stored in CLOBs. But using DBMS_SQL has an overload of PARSE that accepts a collection of SQL string fragments.
This is not ideal and the CLOB implementation in EXECUTE IMMEDIATE solves any issues we might have had with the previous alternatives.
Example 4:
DECLARE
vara CLOB;
BEGIN
vara := 'create table testa( a number(10))';
-- to make length 64k k
while length(vara) <70000
loop
vara := vara || chr(10) || '--comment';
end loop;
dbms_output.put_line (length(vara));
EXECUTE IMMEDIATE vara;
END;
So now don’t worry about the size of strings, Oracle 11g has solved the limitations we had for Dynamic SQL executions.
The post How to pass CLOB argument in EXECUTE IMMEDIATE appeared first on ViralPatel.net.
via ViralPatel.net http://ift.tt/1monbfE
Researchers Fit Trackers To Bees Backs
It is a mystery why we are seeing a massive decline in bee populations. The major concern comes from the fact that bees pollinate one-third of the world’s crops and so researchers are keen to discover why they are dying out.
Australia’s National Science Agency has come up with a solution in trying to solve the mystery. They are using radio-frequency identification tags in order to track the bees movement.
The trackers measure only 0.1-inch square and are being attached to the backs of 5,000 bees in Tasmania’s capital Hobart. The researchers collect the bees and then with the help of a fridge they are anaesthetized. Once docile, the bees can be fitted with their trackers, which will feed information to the recorders strategically placed near the hives and food sources.
By finding out the bees daily movements, it is hoped that the researchers can monitor any unusual behaviour and give them a starting reference to investigate what is causing the colony to act in such a way.
“Honey bees play an extremely important role in our daily lives. Around one third of the food we eat relies on pollination and this is a free service these insects provide. A recent CSIRO study showed that honey bees helped increase faba bean yield by up to 17 per cent. Knowing how bees interact with their environment will allow farmers, fruit growers and seed producers to manage their properties using honey bees to increase productivity,” says Dr Paolo de Souza of the Commonwealth Scientific and Industrial Research Organisation.
At the moment it is not clear whether it is pollution, parasites or pesticides that are causing the bees to die out but once the cause is known, then hopefully preventative steps can be taken and stop this essential stripey insect from being wiped out completely.
[Image via CSIRO]
SOURCE: http://ift.tt/1mcHdwA
The post Researchers Fit Trackers To Bees Backs appeared first on TechBeat.
via TechBeat http://ift.tt/1f2QW5f