stop using the 'gui'


Start -> run -> sqlplus.exe

do it in character mode.


ops$tkyte@ORA920> select 'A' || chr(13) || chr(10) || 'B' from dual;

'A'|
----
A
B


ops$tkyte@ORA920>



Unix difficult?  Hah, windows - now I find that hard cause nothing but nothing can be done without a mouse, without a gui display.  Ease of use?  I doubt it.  I never get questions from unix people like "i set the utl_file_dir to an NFS mounted disk, now when I try to access it, it doesn't work".  I get tons of questions from windows people like "i set the utl_file_dir to a windows share, now when I try to access it from the explorer -- it works but the database doesn't see it, why not?"


The list goes on.  Unix is not "hard".  Windows to me is painfully hard.  Give me grep, sed, awk and a good shell and I'm set to do whatever I need.  Give me windows and I need to buy the MKS toolkit just to make it bearable.... 

  1* select ename, length(ename)-length( replace(ename,'A','') ) "A's" from emp
ops$tkyte@ORA920LAP> /

ENAME             A's
---------- ----------
SMITH               0
ALLEN               1
WARD                1
JONES               0
MARTIN              1
BLAKE               1
CLARK               1
SCOTT               0
KING                0
TURNER              0
ADAMS               2
JAMES               1
FORD                0
MILLER              0

14 rows selected.


replace 'A' with chr(10) for newlines. 

is "longets" really "longest" the "longest" (or largest, or line with the most characters)?

we'd probably be writing a plsql function for that.


ops$tkyte@ORA920PC> create or replace type str2tblType as table of varchar2(4000);
  2  /
 
Type created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return str2tblType
  2  PIPELINED
  3  as
  4      l_str      long default p_str || p_delim;
  5      l_n        number;
  6  begin
  7      loop
  8          l_n := instr( l_str, p_delim );
  9          exit when (nvl(l_n,0) = 0);
 10          pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
 11          l_str := substr( l_str, l_n+1 );
 12      end loop;
 13      return;
 14  end;
 15  /
 
Function created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> drop table t;
 
Table dropped.
 
ops$tkyte@ORA920PC> create table t
  2  as
  3  select ename, 'Hows this going to ' || chr(10) || ' work?'  || chr(10) || ' i have no idea' cmt
  4   from scott.emp;
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select ename, length(cmt)-length(replace(cmt,'a','')) "A's",
  2         (select max(length(column_value)) from TABLE(str2tbl(cmt))) max_length
  3    from t;
 
ENAME             A's MAX_LENGTH
---------- ---------- ----------
SMITH               2         42
ALLEN               2         42
WARD                2         42
JONES               2         42
MARTIN              2         42
BLAKE               2         42
CLARK               2         42
SCOTT               2         42
KING                2         42
TURNER              2         42
ADAMS               2         42
JAMES               2         42
FORD                2         42
MILLER              2         42
 
14 rows selected.
 

is one approach 

SQL> set recsep off
 

& is only an issue in SQLPLUS.

sqlplus is seeing the & and saying "ah, must prompt user for input"

SQL> set define off

disables that. 

<b>don't play around as sys!</b>


ops$tkyte@ORA9IR2> set recsep off
ops$tkyte@ORA9IR2> select
  2    object_name || chr(10) ||
  3    object_id || chr(10) ||
  4    object_type || chr(10) ||
  5    status || chr(10) ||
  6    created as usr_obj
  7  from user_objects
  8  where rownum < 5;

USR_OBJ
-------------------------------------------------------------------------------
A
34139
TABLE
VALID
03-MAY-06
B
34140
TABLE
VALID
03-MAY-06
BLOBDATA
34126
TABLE
VALID
02-MAY-06
BLOBDATA_TEMP
34127
TABLE
VALID
02-MAY-06
 

ops$tkyte%ORA9IR2> create table t ( x number );

Table created.

ops$tkyte%ORA9IR2> insert into t values ( 9.0000000000000000000000000001 );

1 row created.

ops$tkyte%ORA9IR2> select * from t;

         X
----------
         9

ops$tkyte%ORA9IR2> select * from t where x = 9;

no rows selected

ops$tkyte%ORA9IR2> select x,dump(x) from t where x > 8 and x < 10;

         X
----------
DUMP(X)
-------------------------------------------------------------------------------
         9
Typ=2 Len=16: 193,10,1,1,1,1,1,1,1,1,1,1,1,1,1,2


ops$tkyte%ORA9IR2> select x,dump(x) from t where trim(x) = 9;

no rows selected