BLOB
-------------------------
CREATE TABLE DOKUMENTY
(
ID NUMBER(12) PRIMARY KEY,
DOKUMENT CLOB
);

TRUNCATE TABLE DOKUMENTY;
SELECT * FROM DOKUMENTY;

DECLARE
  lobd clob;
  text VARCHAR2(10) := 'Oto tekst.';
  position_tmp INTEGER := 1;
  text_size BINARY_INTEGER := LENGTHB(text);
BEGIN
  INSERT INTO DOKUMENTY VALUES (1, EMPTY_CLOB());
  COMMIT;

  SELECT DOKUMENT INTO lobd FROM DOKUMENTY WHERE ID = 1 FOR UPDATE;
  SYS.DBMS_LOB.OPEN(lobd, SYS.DBMS_LOB.LOB_READWRITE);

  FOR i IN 1..10000 LOOP
    SYS.DBMS_LOB.WRITE(lobd, text_size, position_tmp, text);
    position_tmp := position_tmp + text_size;
  END LOOP;

  SYS.DBMS_LOB.CLOSE(lobd);

  COMMIT;
END;
/

SELECT LENGTHB('Oto tekst.') FROM DUAL;

SELECT * FROM DOKUMENTY;
SELECT ID, UPPER(DOKUMENT) FROM DOKUMENTY;
SELECT ID, LENGTH(DOKUMENT) AS clob_length FROM DOKUMENTY;
SELECT ID, SYS.DBMS_LOB.GETLENGTH(DOKUMENT) AS clob_length FROM DOKUMENTY;
SELECT ID, SUBSTR(DOKUMENT, 5, 1000) AS clob_length FROM DOKUMENTY;
SELECT ID, SYS.DBMS_LOB.SUBSTR(DOKUMENT, 1000, 5) AS clob_length FROM DOKUMENTY;

INSERT INTO DOKUMENTY VALUES (2, EMPTY_CLOB());

INSERT INTO DOKUMENTY VALUES (3, NULL);

SELECT * FROM DOKUMENTY;

SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM SYS.ALL_DIRECTORIES;

DECLARE
  lobd clob;
  fils BFILE := BFILENAME('MBD_DIR','dokument.txt');
  dest_offset INTEGER := 1;
  src_offset INTEGER := 1;
  bfile_csid NUMBER := 0;
  lang_context INTEGER := 0;
  my_warning INTEGER;
BEGIN
  SELECT DOKUMENT INTO lobd FROM DOKUMENTY WHERE ID = 2 FOR UPDATE;

  SYS.DBMS_LOB.FILEOPEN(fils, DBMS_LOB.file_readonly);
  SYS.DBMS_LOB.LOADCLOBFROMFILE(lobd, fils, DBMS_LOB.GETLENGTH(fils), dest_offset, src_offset, bfile_csid, lang_context, my_warning);
  SYS.DBMS_LOB.FILECLOSE(fils);

  COMMIT;
END;
/

SELECT DBMS_LOB.GETLENGTH(BFILENAME('MBD_DIR','dokument.txt')) FROM DUAL;

DECLARE
  lobd clob;
  fils BFILE := BFILENAME('MBD_DIR','dokument.txt');
  dest_offset INTEGER := 1;
  src_offset INTEGER := 1;
  bfile_csid NUMBER := 0;
  lang_context INTEGER := 0;
  my_warning INTEGER;
BEGIN

  SYS.DBMS_LOB.FILEOPEN(fils, DBMS_LOB.file_readonly);
  SYS.DBMS_LOB.CREATETEMPORARY(lobd, true);
  SYS.DBMS_LOB.LOADCLOBFROMFILE(lobd, fils, DBMS_LOB.GETLENGTH(fils), dest_offset, src_offset, bfile_csid, lang_context, my_warning);
  SYS.DBMS_LOB.FILECLOSE(fils);

  UPDATE DOKUMENTY SET DOKUMENT = lobd WHERE ID = 3;

  SYS.DBMS_LOB.FREETEMPORARY(lobd);
  COMMIT;
END;
/

SELECT ID, SYS.DBMS_LOB.GETLENGTH(DOKUMENT) AS clob_length FROM DOKUMENTY;

DROP TABLE DOKUMENTY;

------------------------------------------
STILLIMG

SELECT * FROM FLAGS;

SELECT UNIQUE f.FLAG.SI_Format() FORMAT FROM FLAGS f;

SELECT COUNTRY FROM FLAGS f WHERE f.FLAG.SI_Width() = (SELECT g.FLAG.SI_Width() FROM FLAGS g WHERE COUNTRY = 'Croatia') AND f.FLAG.SI_Height() = (SELECT g.FLAG.SI_Height() FROM FLAGS g WHERE COUNTRY = 'Croatia') AND COUNTRY != 'Croatia';

SELECT g.COUNTRY FROM FLAGS g, FLAGS f WHERE f.COUNTRY = 'Croatia' AND g.COUNTRY != 'Croatia' AND f.FLAG.SI_Width() = g.FLAG.SI_Width() AND f.FLAG.SI_Height() = g.FLAG.SI_Height();

SELECT f.COUNTRY, f.FLAG.SI_Thumbnail().SI_Width() WIDTH, f.FLAG.SI_Thumbnail().SI_Height() HEIGHT FROM FLAGS f WHERE f.COUNTRY = 'Poland' OR f.COUNTRY = 'Croatia';

SELECT f.COUNTRY, f.FLAG.SI_Width() / f.FLAG.SI_Height() ORG_RATIO, f.FLAG.SI_Thumbnail().SI_Width() / f.FLAG.SI_Thumbnail().SI_Height() THUMB_RATIO FROM FLAGS f WHERE f.COUNTRY LIKE 'C%';

SELECT f.COUNTRY FROM FLAGS f WHERE SI_ColorHistogram((SELECT FLAG FROM FLAGS WHERE COUNTRY = 'Poland')).SI_Score(f.FLAG) < 10;

SELECT f.COUNTRY FROM FLAGS f, FLAGS g WHERE g.COUNTRY = 'Poland' AND SI_ColorHistogram(g.FLAG).SI_Score(f.FLAG) < 10 AND
SI_AverageColor(g.FLAG).SI_Score(f.FLAG) < 10 AND SI_Texture(g.FLAG).SI_Score(f.FLAG) < 10 AND SI_PositionalColor(g.FLAG).SI_Score(f.FLAG) < 10;

SELECT f.COUNTRY FROM FLAGS f, FLAGS g WHERE g.COUNTRY = 'Guinea' AND SI_ColorHistogram(g.FLAG).SI_Score(f.FLAG) < 10 AND
SI_AverageColor(g.FLAG).SI_Score(f.FLAG) < 10 AND SI_Texture(g.FLAG).SI_Score(f.FLAG) < 10 AND SI_PositionalColor(g.FLAG).SI_Score(f.FLAG) < 10;

SELECT ROWNUM as RANK_HIST, COUNTRY, SCORE FROM
	(SELECT COUNTRY, ROUND(SI_ColorHistogram((SELECT FLAG FROM FLAGS WHERE COUNTRY = 'Poland')).SI_Score(FLAG), 2) AS SCORE FROM FLAGS ORDER BY SCORE)
WHERE ROWNUM <= 10;

SELECT ROWNUM as RANK_AVGC, COUNTRY, SCORE FROM
	(SELECT COUNTRY, ROUND(SI_AverageColor((SELECT FLAG FROM FLAGS WHERE COUNTRY = 'Poland')).SI_Score(FLAG), 2) AS SCORE FROM FLAGS ORDER BY SCORE)
WHERE ROWNUM <= 10;

SELECT ROWNUM as RANK_POSC, COUNTRY, SCORE FROM
	(SELECT COUNTRY, ROUND(SI_PositionalColor((SELECT FLAG FROM FLAGS WHERE COUNTRY = 'Poland')).SI_Score(FLAG), 2) AS SCORE FROM FLAGS ORDER BY SCORE)
WHERE ROWNUM <= 10;

SELECT ROWNUM as RANK_TXTR, COUNTRY, SCORE FROM
	(SELECT COUNTRY, ROUND(SI_Texture((SELECT FLAG FROM FLAGS WHERE COUNTRY = 'Poland')).SI_Score(FLAG), 2) AS SCORE FROM FLAGS ORDER BY SCORE)
WHERE ROWNUM <= 10;

SELECT ROWNUM as RANK_ALL, COUNTRY, SCORE FROM
	(SELECT f.COUNTRY,
  (ROUND((SI_Texture(g.FLAG).SI_Score(f.FLAG) * 0.25) + ((SI_PositionalColor(g.FLAG).SI_Score(f.FLAG)) * 0.25) + ((SI_AverageColor(g.FLAG).SI_Score(f.FLAG)) * 0.25) + ((SI_ColorHistogram(g.FLAG).SI_Score(f.FLAG)) * 0.25), 2)) AS SCORE
  FROM FLAGS f, FLAGS g WHERE g.COUNTRY = 'Poland' ORDER BY SCORE)
WHERE ROWNUM <= 10;