problem max size of oracle's listagg is 4000 characters. I've copied some code from tom kyte, changed some varchars in clobs and now we're happyly concatenatin large strings again.
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
g_string VARCHAR2(32767),
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
--returnValue OUT VARCHAR2,
returnValue OUT CLOB,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER
);
/
SHOW ERROR
CREATE OR REPLACE TYPE BODY t_string_agg IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER IS
BEGIN
sctx := t_string_agg(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER IS
BEGIN
self.g_string := self.g_string || value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
--returnValue OUT VARCHAR2,
returnValue OUT CLOB,
flags IN NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := SELF.g_string;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER IS
BEGIN
SELF.g_string := SELF.g_string || ctx2.g_string;
RETURN ODCIConst.Success;
END;
END;
/
SHOW ERROR
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERROR
VERPLICHT:
ODCIAggregateInitialize
This function typically initializes the aggregation context (an instance of the implementation object type) and returns it (as an OUT parameter) to Oracle.
ODCIAggregateIterate
The ODCIAggregateIterate function is invoked by Oracle to process the next input row.
The routine is invoked by passing in the aggregation context and the value of the next input to be aggregated. This routine processes the input value,
updates the aggregation context accordingly, and returns the context back to Oracle. This routine is invoked by Oracle for every value in the underlying
group, including NULL values.
ODCIAggregateTerminate
The ODCIAggregateTerminate function is invoked by Oracle as the final step of aggregation.
This routine takes the aggregation context as input and returns the resultant aggregate value to Oracle. This routine also typically performs any necessary
cleanup operations such as freeing memory, and so on.
OPTIONEEL:
ODCIAggregateMerge
The ODCIAggregateMerge function is invoked by Oracle to merge two aggregation contexts into a single object instance.
Two aggregation contexts may need to be merged during either serial or parallel evaluation of the user-defined aggregate. This function takes the two
aggregation contexts as input, merges them, and returns the single, merged instance of the aggregation context.
ODCIAggregateInitialize: This function is called to create a new context for a set of values. The purpose of this static method is to return a new, initialized instance for the type.
ODCIAggregateIterate: Each value is passed to this method which is responsible for making the necessary calculations, etc.
ODCIAggregateMerge: This method is used when two contexts are merged into a single one. Merging happens if the original set of values are iterated using several different contexts.
ODCIAggregateTerminate: When all values have been handled and contexts are merged, this function returns the result.
PROBLEEM......
Aggragatie sorteert numeriek.
Search for oracle objects in unix scripts with specific name.
Search for oracle objects in unix scripts with specific name.
On Unix:
1. Search script with "prm_" in the text:
find . -name '*' -print | xargs grep -i 'prm_' > /tmp/all_files
On Client
2. Copy file (with winscp) to local dir.
3. Remove all 'binary shit' lines
4. Create on Oracle some table with a clob field
(table prm_clob, with 2 fields t0(varchar2(10)) and t1(clob)).
5. Copy all content of the all_files (with toad) into this Oracle table (t1 as clob and t0 as 'some descriptive value').
6. Create a table type for storing varchar2 values (in this example varchar2(500) is used. Use a size as you like.
CREATE OR REPLACE TYPE PRM.prm_jka_nt_array as table of varchar2(500)
7. Create a function in which a clob can be read line by line (I've created a package function)
CREATE OR REPLACE PACKAGE PRM.prm_jka_clob AS
-- Public Package Procedures en Functions
function clob_as_lines(p_cursor in sys_refcursor)
return prm_jka_nt_array
pipelined;
END prm_jka_clob;
CREATE OR REPLACE PACKAGE BODY PRM.prm_jka_clob AS
function clob_as_lines(p_cursor in sys_refcursor)
return prm_jka_nt_array
pipelined
is
l_clob clob;
l_pos number := 1; -- Current position in the file (file begins at position 1)
l_sum binary_integer := 0; -- Amount of characters have been read
l_buf varchar2(500); -- Read Buffer
l_end number; -- End of the current word which will be read
begin
fetch p_cursor into l_clob;
if (p_cursor%notfound)
then
close p_cursor;
raise no_data_found;
end if;
close p_cursor;
if (dbms_lob.isopen(l_clob) != 1)
then
dbms_lob.open(l_clob, 0);
end if;
loop
-- Calculate the end of the current word
l_end :=
dbms_lob.instr(l_clob
,chr(10)
,l_pos
,1);
-- Process end-of-file
if (l_end = 0)
then
l_end := length(l_clob);
l_sum := l_end - l_pos + 1;
if l_sum > 0
then
dbms_lob.read(l_clob
,l_sum
,l_pos
,l_buf);
l_buf := rtrim(l_buf, chr(13));
pipe row (l_buf);
end if;
exit;
end if;
-- Read until end-of-file
l_sum := l_end - l_pos;
dbms_lob.read(l_clob
,l_sum
,l_pos
,l_buf);
l_buf := rtrim(rtrim(l_buf, chr(10)), chr(13));
pipe row (l_buf);
l_pos := l_pos + l_sum + 1;
end loop;
if (dbms_lob.isopen(l_clob) = 1)
then
dbms_lob.close(l_clob);
end if;
end clob_as_lines;
END prm_jka_clob;
8. Now let Oracle do some regexp thingies to match any object in the unix-grep output.
declare
cursor c_objects
is
select object_name
from all_objects where owner = 'PRM';
type t_objects is table of c_objects%rowtype index by binary_integer;
a_objects t_objects;
cursor c_clob
is
SELECT column_value
FROM TABLE(prm_jka_clob.clob_as_lines(CURSOR(SELECT t1
FROM prm_clob
where t0=1)));
type t_clob is table of c_clob%rowtype index by binary_integer;
a_clob t_clob;
l_found boolean;
l_object varchar2(100);
begin
--collect all objects for comparing
open c_objects;
fetch c_objects bulk collect into a_objects;
close c_objects;
--collect the values from the clob. every line is a single array record now
open c_clob;
fetch c_clob bulk collect into a_clob;
close c_clob;
-- loop through the clob lines
-- layout of the line is output from the unix grep command: [file name]:[matching string]
for i in 1..a_clob.count
loop
l_found := false;
l_object := null;
-- for every line loop through comparing object names
for j in 1..a_objects.count
loop
-- if an (regeular expression wise) object name (from c_objects) is is found in the clob-string-line then 'bingo'
l_found := regexp_like(regexp_replace(a_clob(i).column_value,'(^.*):(.*)$','\2'),a_objects(j).object_name,'i');
if l_found then
--found, so store the object
l_object := a_objects(j).object_name;
-- exit the j-loop
exit;
end if;
end loop;
-- outside the loop, something has been found, so report it as csv line: [filename];[object_name];[line context]
-- use a simple regexp_replace for selecting the grep output part before (\1) the double-point and after (\2).
if l_found then
dbms_output.put_line(regexp_replace(a_clob(i).column_value,'(^.*):(.*)$','\1')||';'||l_object||';"'''||regexp_replace(a_clob(i).column_value,'(^.*):(.*)$','\2')||'"');
end if;
end loop;
end;
What’s that script doing
Found this script on some media hard drive….
#!/bin/sh SAVEIFS=$IFS IFS=$(echo "\n\b") for f in * do if [ ! -d "/media/usbdisk/kidz/$f" ] then echo $(du -sh "$f") fi done IFS=$SAVEIFS
Very curious why I left it there and what is was looking for.
IFS is the field splitting \n\b is….
\b = character
\n = newline
ok that’s just to avoid splitting on a space, file names with a space…..
learnt something from myself from the past.
minimalize raspian
sudo apt-get remove –purge wolfram-engine scratch omxplayer penguinspuzzle
sudo apt-get remove –purge debian-reference-en dillo idle3 python3-tk idle python-pygame python-tk
sudo apt-get remove –purge lightdm gnome-themes-standard gnome-icon-theme raspberrypi-artwork
sudo apt-get remove –purge gvfs-backends gvfs-fuse desktop-base lxpolkit netsurf-gtk zenity xdg-utils
sudo apt-get remove –purge mupdf gtk2-engines alsa-utils lxde lxtask menu-xdg gksu
sudo apt-get remove –purge midori xserver-xorg xinit xserver-xorg-video-fbdev
sudo apt-get remove –purge libraspberrypi-dev libraspberrypi-doc
sudo apt-get remove –purge dbus-x11 libx11-6 libx11-data libx11-xcb1 x11-common x11-utils
sudo apt-get remove –purge lxde-icon-theme gconf-service gconf2-common
sudo apt-get remove –purge python python3 python2.7 python3.2
sudo apt-get remove –purge alsa* gstreamer*
dpkg –list |grep “^rc” | cut -d ” ” -f 3 | xargs sudo dpkg –purge
sudo apt-get autoremove
sudo rm -rf /home/pi/python_games
sudo apt-get install vim
sudo rm -rv /usr/share/icons/*
sudo rm -rv /opt/vc/src/*
Just ….
select column_name
, case data_type
when 'NUMBER' then data_type||' ('||data_precision || case when data_scale != '0' then ','||data_scale
else ''
end ||')'
when 'VARCHAR2' then data_type||' ('||data_length||' '||case char_used
when 'B' then 'Byte'
when 'C' then 'Char'
end ||')'
when 'DATE' then data_type
end datatype
, case nullable when 'N' then 'Not Null' else '' end nullable
from user_tab_columns
where table_name = 'MMK_MIXMATCHES'
order by column_id;
ufw
setting ufw rules…..
1. Read the rules:
~$ sudo ufw status numbered
Status: active
To Action From
-- ------ ----
[ 1] Anywhere DENY IN xxx.xxx.xxx.xxx
[ 2] Anywhere DENY IN xxx.xxx.xxx.xxx
[ 3] 22 ALLOW IN Anywhere
[ 4] 80 ALLOW IN Anywhere
[ 5] 25 ALLOW IN Anywhere
[ 6] 22 (v6) ALLOW IN Anywhere (v6)
[ 7] 80 (v6) ALLOW IN Anywhere (v6)
[ 8] 25 (v6) ALLOW IN Anywhere (v6)
2. Delete some numbered rule (delete rule number 1):
sudo ufw delete 1
Now importantly: Rule number 2 is now known as rule number 1,
So to delete that rule, do sudo ufw delete 1 again 😉
or refresh the list with sud ufw status numbered
3. Create a new rule, to ban all trafic from some ip-number:
sudo ufw insert 1 deny from xxx.xxx.xxx.xxx
-> this sets a new rule at first place in the rule list.
How to exit some bulk collect loop
declare cursor c_cur is select id from sometable; -- type t_cur is table of c_cur%rowtype index by binary_integer; a_cur t_cur; l_limit binary_integer := 1000; begin -- open c_cur; -- loop fetch c_cur bulk collect into a_cur limit l_limit; exit when c_cur%notfound; /*[some code]*/ end loop; -- close c_cur; -- end; declare cursor c_cur is select id from sometable; -- type t_cur is table of c_cur%rowtype index by binary_integer; a_cur t_cur; l_limit binary_integer := 1000; begin -- open c_cur; -- loop fetch c_cur bulk collect into a_cur limit l_limit; exit when a_cur.count = 0; /*[some code]*/ end loop; -- close c_cur; -- end; declare cursor c_cur is select id from sometable; -- type t_cur is table of c_cur%rowtype index by binary_integer; a_cur t_cur; l_limit binary_integer := 1000; begin -- open c_cur; -- loop fetch c_cur bulk collect into a_cur limit l_limit; /*[some code]*/ exit when a_cur.count < l_limit; end loop; -- close c_cur; -- end;
change default text editor in Ubuntu
The default editor for crontab on Ubunut is Nano. I don’t like it. I just like vi (or the vim version for linux).
According to the official notes about changing the default editor, I should use this command:
$ sudo update-alternatives –config editor
This gives a screen like this:
$ sudo update-alternatives –config editor
[sudo] password for johannes:
There are 4 choices for the alternative editor (providing /usr/bin/editor).
Selection Path Priority Status
————————————————————
0 /bin/nano 40 auto mode
1 /bin/ed -100 manual mode
2 /bin/nano 40 manual mode
* 3 /usr/bin/vim.basic 30 manual mode
4 /usr/bin/vim.tiny 10 manual mode
Press enter to keep the current choice[*], or type selection number:
Well as you can see I choose number 3. That didn’t help.
What did help was this command:
$sudo select-editor
Select an editor. To change later, run ‘select-editor’.
1. /bin/ed
2. /bin/nano <—- easiest
3. /usr/bin/vim.basic
4. /usr/bin/vim.tiny
Choose 1-4 [2]:
I choose 3 and voila
the command sudo crontab -e opens in vi.
actually this command created a file ‘.selected_editor’ containing:
# Generated by /usr/bin/select-editor
SELECTED_EDITOR=”/usr/bin/vim.basic”
There are many ways leading to Rome, and this is just another one.
cleaning up, weeknumber in php…
<?php
setlocale(LC_ALL, ‘nl_NL’);
$weeknrkeep=-1;
$today = time();
echo $today.”<br>”;
for ($i=0;$i<200;$i++) :
$weeknr= strftime(‘%V’, strtotime(‘+’.$i.’ day’,$today));
if ($weeknr != $weeknrkeep):
echo “<br><strong>week “.$weeknr.”</strong><br>”;
endif;
echo strftime(‘%a %d-%m-%Y’, strtotime(‘+’.$i.’ day’,$today)).”<br>\n\r”;
$weeknrkeep=$weeknr;
endfor;
?>
this outputs a list of weeknumbers and the days’s and dates in that week. must have been some test for some website…..
output looks like:
week 14
vr 03-04-2015
za 04-04-2015
zo 05-04-2015
week 15
ma 06-04-2015
di 07-04-2015
wo 08-04-2015
do 09-04-2015
vr 10-04-2015
za 11-04-2015
zo 12-04-2015
week 16
ma 13-04-2015
di 14-04-2015
wo 15-04-2015
do 16-04-2015
vr 17-04-2015
za 18-04-2015
zo 19-04-2015
…..etc etc
Cleaning up, found some more ‘exiting’ code
#include <stdio.h>
#include <math.h>
int main()
{
float x, y, *fp, *fp2;
x = 6.5;
printf(“Value of x is %f, address of x %ld\n”, x, &x);
fp = &x;
printf(“Value in memory location fp is %f\n”, *fp);
*fp = 9.2;
printf(“New value of x is %f = %f \n”, *fp, x);
*fp = *fp + 1.5;
printf(“Final value of x is %f = %f \n”, *fp, x);
y = *fp;
fp2 = fp;
printf(“Transfered value into y = %f and fp2 = %f \n”, y, *fp2);
return 0;
}
compiled and executed it:
$ ./testfloat
Value of x is 6.500000, address of x 140734612936552
Value in memory location fp is 6.500000
New value of x is 9.200000 = 9.200000
Final value of x is 10.700000 = 10.700000
Transfered value into y = 10.700000 and fp2 = 10.700000
o–kay ?? why did I have that on my server.