Sometimes you learn something new and ask yourself : how is it possible I’ve never seen this before. Zenity is a tool that allows you to display Gtk+ dialog boxes from the command line and through shell scripts. Here is a rather crude example:

#!/bin/sh

ORATAB=/etc/oratab; export ORATAB
ORAENV=/usr/local/bin/oraenv;export ORAENV



# Set this in accordance with the platform
db=`if [ ! $ORATAB ] ; then
      echo "$ORATAB not found"
      exit 1;
    fi

    (cat $ORATAB |grep -v "^$" | while read LINE
    do
    case $LINE in
      \#*)                ;;
      *)                  echo FALSE
                          echo $LINE | awk -F: '{print $1}'
                          ;;
    esac
    done) |
    zenity  --list  --text "Choose a database" --radiolist  --column "Pick" --column "Database" --width=300 --height=300`

un=`zenity --entry \
        --title="Username for $db " \
        --text="Enter your _username:" \
        --entry-text "system"`

pw=`zenity --entry \
        --title="Add an Entry" \
        --text="Enter your _password for $db:" \
        --entry-text "password" \
        --hide-text`

zenity --question \
          --text="Are you sure you wish to logon on $db as $un?"
if [ $? ]
then
   ORACLE_SID=$db;export ORACLE_SID
   ORAENV_ASK=NO;export ORAENV_ASK
   $ORAENV $db
   sqlplus $un/$pw@$db
fi

Run this script:

Oracle VM VirtualBox is an x86 virtualization software package. It is installed on an existing host operating system; within this application, additional guest operating systems, each known as a Guest OS, can be loaded and run, each with its own virtual environment.

Get it here

Hidden parameters are not included in the v$parameter view. Use this statement to view all optimizer parameters in a 11g (and 10g) database:

SELECT
  x.ksppinm name,
  y.ksppstvl VALUE,
  decode(ksppity,
    1,   'BOOLEAN',
    2,   'STRING',
    3,   'INTEGER',
    4,   'PARAMETER FILE',
    5,   'RESERVED',
    6,   'BIG INTEGER',
    'UNKNOWN') typ,
  decode(ksppstdf,
    'TRUE',   'DEFAULT VALUE',
    'FALSE',   'INIT.ORA') isdefault,
  decode(bitand(ksppiflg / 256,   1),
    1,   'IS_SESS_MOD(TRUE)',
    'FALSE') isses_modifiable,
  decode(bitand(ksppiflg / 65536,   3),
    1,   'MODSYS(NONDEFERED)',
    2,   'MODSYS(DEFERED)',
    3,   'MODSYS(*NONDEFERED*)',
    'FALSE') issys_modifiable,
  decode(bitand(ksppstvf,   7),
    1,   'MODIFIED_BY(SESSION)',
    4,   'MODIFIED_BY(SYSTEM)',
    'FALSE') is_modified,
  decode(bitand(ksppstvf,   2),
    2,   'ORA_STARTUP_MOD(TRUE)',
    'FALSE') is_adjusted,
  ksppdesc description,
  ksppstcmnt update_comment
FROM x$ksppi x,
  x$ksppcv y
WHERE x.inst_id = userenv('Instance')
 AND y.inst_id = userenv('Instance')
 AND x.indx = y.indx
 AND x.ksppinm LIKE '%optim%';

Using this method whe can use a function that returns a collection as a table in the FROM clause of a SELECT

First we create a FUNCTION that returns a Collection.

CREATE OR REPLACE TYPE rob_type IS OBJECT (n1 NUMBER,n2 NUMBER,v1 VARCHAR2(100));
/

CREATE OR REPLACE TYPE rob_table_type IS TABLE OF rob_type;
/

CREATE OR REPLACE FUNCTION rob_function (startn INTEGER , endn INTEGER)
RETURN rob_table_type
IS
  retval rob_table_type := rob_table_type();
  PROCEDURE expand_collection (rob IN rob_type)
  IS
  BEGIN
    retval.extend;
    retval(retval.LAST) := rob;
  END;
BEGIN
  FOR i in startn .. endn
  LOOP
    expand_collection(rob_type(i,i*i,to_char(sysdate,'SSSSS')));
  END LOOP;
  return retval;
END;
/

Now we can use it in a SELECT

SQL> select * from TABLE(CAST(rob_function(1,10) AS rob_table_type));

    N1     N2 V1
------ ------ ----------------------------------------------------------------
     1      1 30823
     2      4 30823
     3      9 30823
     4     16 30823
     5     25 30823
     6     36 30823
     7     49 30823
     8     64 30823
     9     81 30823
    10    100 30823

10 rows selected.