Quine in PL/SQL

A quine is a computer program which produces its own source code as its output. The name is referring to famous American philosopher Willard van Orman Quine who has a powerful influence on analytical philosophy. I recommend you to have a look at his brief biography and the paradox he introduced.

The anonymous PL/SQL block below stores its output in a table named t2 so that we can see the output by selecting the row inserted from the table after execution of the block.

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option

SQL> drop table t2;

Table dropped.

SQL> create table t2 (v varchar2(4000));

Table created.

SQL> declare
  2   s varchar2(4000) := 'declare
  3   s varchar2(4000):="$";
  4  begin
  5   insert into t2
  6   values
  7     (replace(replace(s, chr(34), chr(39)),
  8      chr(36),
  9      "$"));
 10  end;';
 11  begin
 12    insert into t2
 13    values
 14      (replace(replace(s, chr(34), chr(39)),
 15               chr(36),
 16               'declare
 17   s varchar2(4000):="$";
 18  begin
 19   insert into t2 values(replace(replace(s,chr(34),chr(39)),chr(36),"$"));
 20  end;'));
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL> select * from t2;

V
--------------------------------------- 
declare
 s varchar2(4000):='declare
 s varchar2(4000):="$";
begin
 insert into t2
 values
   (replace(replace(s,chr(34),chr(39)),
    chr(36),
    "$"));
end;';
begin
 insert into t2
 values
   (replace(replace(s, chr(34), chr(39)),
chr(36),
'declare
 s varchar2(4000):="$";
begin
 insert into t2 values(replace(replace(s,chr(34),chr(39)),chr(36),"$"));
end;'));
end;

Universal Data Printer in PL/SQL

The listing below demonstrates two procedures which are to execute any sort of SQL and print the data set in ";" seperated format. Both procedures use DBMS_SQL package for describing and fetching the data. The difference between those procedures is that the second one opens the data set by using a weak ref cursor then the ref cursor is casted to a DBMS_SQL cursor. This feature is announced with Oracle 11g Release 1.

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option

SQL> create or replace procedure pr_universal_data_printer(p_sql in varchar2) is
  2    fetch_out_of_seq exception;
  3    pragma exception_init(fetch_out_of_seq, -1002);
  4 
  5    unsupported_type exception;
  6    pragma exception_init(unsupported_type, -20001);
  7 
  8    desc_tab dbms_sql.desc_tab2;
  9    cols     binary_integer;
 10    cursor_  binary_integer;
 11 
 12    date_col date;
 13    char_col varchar2(4000);
 14    num_col  number;
 15    exe      number;
 16  begin
 17    if dbms_sql.is_open(cursor_) then
 18      dbms_sql.close_cursor(cursor_);
 19    end if;
 20 
 21    cursor_ := dbms_sql.open_cursor;
 22 
 23    dbms_sql.parse(cursor_, p_sql, dbms_sql.native);
 24 
 25    dbms_sql.describe_columns2(cursor_, cols, desc_tab);
 26 
 27    exe := dbms_sql.execute(cursor_);
 28 
 29    for i in 1 .. cols loop
 30      --print header
 31      dbms_output.put(desc_tab(i).col_name || ';');
 32 
 33      --define columns
 34      case desc_tab(i).col_type
 35        when dbms_types.typecode_varchar then
 36          dbms_sql.define_column(cursor_, i, char_col, 4000);
 37        when dbms_types.typecode_varchar2 then
 38          dbms_sql.define_column(cursor_, i, char_col, 4000);
 39        when dbms_types.typecode_char then
 40          dbms_sql.define_column(cursor_, i, char_col, 4000);
 41        when dbms_types.typecode_number then
 42          dbms_sql.define_column(cursor_, i, num_col);
 43        when dbms_types.typecode_date then
 44          dbms_sql.define_column(cursor_, i, date_col);
 45        else
 46          raise unsupported_type;
 47      end case;
 48    end loop;
 49 
 50    dbms_output.new_line;
 51 
 52    --fetch row
 53    while dbms_sql.fetch_rows(cursor_) > 0 loop
 54      --assign values according to the column types
 55      for i in 1 .. cols loop
 56        case desc_tab(i).col_type
 57          when dbms_types.typecode_varchar then
 58            dbms_sql.column_value(cursor_, i, char_col);
 59            dbms_output.put(replace(char_col,chr(10),'') || ';');
 60          when dbms_types.typecode_varchar2 then
 61            dbms_sql.column_value(cursor_, i, char_col);
 62            dbms_output.put(replace(char_col,chr(10),'') || ';');
 63          when dbms_types.typecode_char then
 64            dbms_sql.column_value(cursor_, i, char_col);
 65            dbms_output.put(replace(char_col,chr(10),'') || ';');
 66          when dbms_types.typecode_number then
 67            dbms_sql.column_value(cursor_, i, num_col);
 68            dbms_output.put(num_col || ';');
 69          when dbms_types.typecode_date then
 70            dbms_sql.column_value(cursor_, i, date_col);
 71            dbms_output.put(to_char(date_col, 'dd/mm/yyyy hh24:mi:ss') || ';');
 72          else
 73            raise unsupported_type;
 74        end case;
 75      end loop;
 76      dbms_output.new_line;
 77    end loop;
 78 
 79    dbms_sql.close_cursor(cursor_);
 80  exception
 81    when fetch_out_of_seq then
 82      dbms_output.new_line;
 83      dbms_output.put_line('End of data.');
 84    when unsupported_type then
 85      dbms_output.new_line;
 86      dbms_output.put_line('Unsupported type in data set.');
 87    when others then
 88      raise;
 89  end pr_universal_data_printer;
 90  /

Procedure created.

SQL> set serveroutput on;
SQL> set autoprint on;
SQL> exec pr_universal_data_printer('select object_name, object_type, status from all_objects where owner =''SYS'' and rownum < 11');
OBJECT_NAME;OBJECT_TYPE;STATUS;                                                
/1000323d_DelegateInvocationHa;JAVA CLASS;VALID;                               
/1000e8d1_LinkedHashMapValueIt;JAVA CLASS;VALID;                               
/1005bd30_LnkdConstant;JAVA CLASS;VALID;                                       
/10076b23_OraCustomDatumClosur;JAVA CLASS;VALID;                               
/100c1606_StandardMidiFileRead;JAVA CLASS;VALID;                               
/10128284_OpenMBeanAttributeIn;JAVA CLASS;VALID;                               
/101419a4_NormalDataCollector;JAVA CLASS;VALID;                                
/1020ed5e_Param;JAVA CLASS;VALID;                                              
/1023e902_OraCharsetUTFE;JAVA CLASS;VALID;                                     
/10297c91_SAXAttrList;JAVA CLASS;VALID;                                        

PL/SQL procedure successfully completed.

SQL> create or replace procedure pr_universal_data_printer2(p_sql in varchar2) is
  2    fetch_out_of_seq exception;
  3    pragma exception_init(fetch_out_of_seq, -1002);
  4 
  5    unsupported_type exception;
  6    pragma exception_init(unsupported_type, -20001);
  7 
  8    weak_ref_cur sys_refcursor;
  9 
 10    desc_tab dbms_sql.desc_tab2;
 11    cols     binary_integer;
 12    cursor_  binary_integer;
 13 
 14    date_col date;
 15    char_col varchar2(4000);
 16    num_col  number;
 17    exe      number;
 18  begin
 19    if weak_ref_cur%isopen then
 20      close weak_ref_cur;
 21    end if;
 22 
 23    open weak_ref_cur for p_sql;
 24 
 25    cursor_ := dbms_sql.to_cursor_number(weak_ref_cur);
 26 
 27    dbms_sql.describe_columns2(cursor_, cols, desc_tab);
 28 
 29    for i in 1 .. cols loop
 30      --print header
 31      dbms_output.put(desc_tab(i).col_name || ';');
 32 
 33      --define columns
 34      case desc_tab(i).col_type
 35        when dbms_types.typecode_varchar then
 36          dbms_sql.define_column(cursor_, i, char_col, 4000);
 37        when dbms_types.typecode_varchar2 then
 38          dbms_sql.define_column(cursor_, i, char_col, 4000);
 39        when dbms_types.typecode_char then
 40          dbms_sql.define_column(cursor_, i, char_col, 4000);
 41        when dbms_types.typecode_number then
 42          dbms_sql.define_column(cursor_, i, num_col);
 43        when dbms_types.typecode_date then
 44          dbms_sql.define_column(cursor_, i, date_col);
 45        else
 46          raise unsupported_type;
 47      end case;
 48    end loop;
 49 
 50    dbms_output.new_line;
 51 
 52    --fetch row
 53    while dbms_sql.fetch_rows(cursor_) > 0 loop
 54      --assign values according to the column types
 55      for i in 1 .. cols loop
 56        case desc_tab(i).col_type
 57          when dbms_types.typecode_varchar then
 58            dbms_sql.column_value(cursor_, i, char_col);
 59            dbms_output.put(replace(char_col,chr(10),'') || ';');
 60          when dbms_types.typecode_varchar2 then
 61            dbms_sql.column_value(cursor_, i, char_col);
 62            dbms_output.put(replace(char_col,chr(10),'') || ';');
 63          when dbms_types.typecode_char then
 64            dbms_sql.column_value(cursor_, i, char_col);
 65            dbms_output.put(replace(char_col,chr(10),'') || ';');
 66          when dbms_types.typecode_number then
 67            dbms_sql.column_value(cursor_, i, num_col);
 68            dbms_output.put(num_col || ';');
 69          when dbms_types.typecode_date then
 70            dbms_sql.column_value(cursor_, i, date_col);
 71            dbms_output.put(to_char(date_col, 'dd/mm/yyyy hh24:mi:ss') || ';');
 72          else
 73            raise unsupported_type;
 74        end case;
 75      end loop;
 76      dbms_output.new_line;
 77    end loop;
 78 
 79    dbms_sql.close_cursor(cursor_);
 80  exception
 81    when fetch_out_of_seq then
 82      dbms_output.new_line;
 83      dbms_output.put_line('End of data.');
 84    when unsupported_type then
 85      dbms_output.new_line;
 86      dbms_output.put_line('Unsupported type in data set.');
 87    when others then
 88      raise;
 89  end pr_universal_data_printer2;
 90  /

Procedure created.

SQL>  exec pr_universal_data_printer2('select object_name, object_type, status from all_objects where owner =''SYS'' and rownum < 11');
OBJECT_NAME;OBJECT_TYPE;STATUS;                                                
/1000323d_DelegateInvocationHa;JAVA CLASS;VALID;                               
/1000e8d1_LinkedHashMapValueIt;JAVA CLASS;VALID;                               
/1005bd30_LnkdConstant;JAVA CLASS;VALID;                                       
/10076b23_OraCustomDatumClosur;JAVA CLASS;VALID;                               
/100c1606_StandardMidiFileRead;JAVA CLASS;VALID;                               
/10128284_OpenMBeanAttributeIn;JAVA CLASS;VALID;                               
/101419a4_NormalDataCollector;JAVA CLASS;VALID;                                
/1020ed5e_Param;JAVA CLASS;VALID;                                              
/1023e902_OraCharsetUTFE;JAVA CLASS;VALID;
/10297c91_SAXAttrList;JAVA CLASS;VALID;                                        

PL/SQL procedure successfully completed.

The Day

Photo by Katinka Matson

What can a man like me do on a day like this? 

A day that's cold and snowy. 

A day which bears the energies of differerent sources of the great great universe... 

What can a man like me do on a Thursday, in the year of 2012? 

A man like me can wake up at 6:40 AM and leave the bed half an hour later. 

That man also can lead his daughter up to her school bus at 7:30 AM. 

That man can kiss her wife and send her off to work at 7:31 AM and look to the white and thick sky spraying showflakes down to earth.

What can a man like me expect from a day like this in a month like February?

That man can shave, put on a jean and a t-shirt and  head to a professional event like this at the hotel like this.

On his way to hotel, just in front of the next building to his one, he may see a sweet dog like this:

After saying "hi" to this guy, he can take a taxi to the hotel.

He may meet some friends at the hotel, chat with them, listen to the great presentation delivered by these gentlemen and come back home on foot at 6:00 PM.

A man like me can have a bottle of this beer and some slices of toasted spicy bread prepared by his wife.

After his daughter fell asleep, a man like me and his wife can watch a great movie like this.

And, my friends, the man like me can be hypnotized by the movie.
He may describe that movie as a full visual feast.
The perfection of the pictures, the power and the simplicity of the words and the whole harmony can make a man like me cry at the end of the movie.

On a day like this, while it is still snowing at night, what is a man like me supposed to do?

Although he is not a fan of religions or those sort of things, he may want to share what Father Haynes preached in the movie:

Job imagined he might build his nest on high – that the integrity of his behavior would protect him against misfortune. And his friends thought, mistakenly, that the Lord could only have punished him because secretly he’d done something wrong.

But, no, misfortune befalls the good as well. We can’t protect ourselves against it. We can’t protect our children. We can’t say to ourselves, even if I’m not happy, I’m going to make sure they are.

We vanish as a cloud. We wither as the autumn grass, and like a tree are rooted up.
Is there some fraud in the scheme of the universe? Is there nothing which is deathless? Nothing which does not pass away?

We cannot stay where we are. We must journey forth.

We must find that which is greater than fortune or fate. Nothing can bring us peace but that.
Is the body of the wise man, or the just, exempt from any pain? From any disquietude, from the deformity that might blight its beauty, from the weakness that might destroy its health?

Do you trust in God?

Job, too, was close to the Lord. Are your friends and children your security? There is no hiding place in all the world where trouble may not find you. No one knows when sorrow might visit his house, any more than Job did.

The very moment everything was taken away from Job, he knew it was the Lord who’d taken it away. He turned from the passing shows of time. He sought that which is eternal.
Does he alone see God’s hand who sees that He gives, or does not also the one see God’s hand who sees that He takes away? Does he alone see God who sees God turn His face towards him? Does not also he see God who sees God turn his back?