! sqrsh (sqr shell)
! Copyright (C) 1997 Ray Ontko & Company
!
! This program is free software; you can redistribute it and/or modify
! it under the terms of the GNU General Public License as published by
! the Free Software Foundation; either version 2 of the License, or
! (at your option) any later version.
!
! This program is distributed in the hope that it will be useful,
! but WITHOUT ANY WARRANTY; without even the implied warranty of
! MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
! GNU General Public License for more details.
!
! You should have received a copy of the GNU General Public License
! along with this program; if not, write to the Free Software
! Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
!
!++
!
! columns
!
! columns atable
! columns sometables%
! columns owner.atable
! columns owner.sometables%
! columns someowners%.atable
! columns someowners%.sometables%
!
! columns lists columns in all tables which match the
! [owner.]table mask provided.  Listing includes column name
! data type, lenght, precision, scale, and nullability.
!
! if owner is not specified, it is assumed to be the current user.
!
!--
!
!
! Author
!
! Ray Ontko
! Ray Ontko & Company
! Richmond, In 47375
!
!
! Modification History
!
! Name             Date       Comment
! ---------------- ---------- ----------------------------------------
! Ray Ontko        1997/06/01 Added this header
! Ray Ontko        1997/06/11 Modified to work under SQR 2.5.x
!

! note
! the following procedure is oracle specific
!
!---------------------------------------------------------------- columns
begin-procedure columns local
#if {SQR-DATABASE}=ORACLE
uppercase $_p1
if instr($_p1,'.',1) > 0
   unstring $_p1 by '.' into $like_owner_name $like_table_name
else
   move $_username to $like_owner_name
   move $_p1 to $like_table_name
end-if
do columns_main( $like_owner_name , $like_table_name )
#else
   show 'sqrsh: "columns" is only available for ORACLE at the present time'
#endif
end-procedure ! columns

!----------------------------------------------------------- columns_main
begin-procedure columns_main( $like_owner_name , $like_table_name )
move 0 to #_columns_main__count
begin-select
owner||'.'||table_name &_columns_main__table_name () on-break print=never
   before=columns_before_table
   after=columns_after_table
column_name &column_name
data_type &data_type
data_length &data_length
data_precision &data_precision
data_scale &data_scale
nullable &nullable
   if #count >= 23
      show '[More...]' noline
      input $more noprompt
      uppercase $more
      extract $more from $more 0 1
      if $more = 'Q'
         exit-select
      end-if
      move 0 to #count
   end-if
   show &column_name edit 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ' noline
   move &data_type to $data_type
   evaluate &data_type
   when = 'VARCHAR2'
   when = 'CHAR'
   when = 'RAW'
      concat '(' with $data_type
      concat &data_length with $data_type
      concat ')' with $data_type
      break
   when = 'NUMBER'
      if not isnull(&data_precision)
         concat '(' with $data_type
         concat &data_precision with $data_type
         if &data_scale <> 0
            concat ',' with $data_type
            concat &data_scale with $data_type
         end-if
         concat ')' with $data_type
      end-if
      break
   end-evaluate
   show $data_type edit 'xxxxxxxxxxxxxxxx' noline
   if &nullable = 'Y'
      show ' NULL'
   else
      show ' NOT NULL'
   end-if
   add 1 to #_columns_main__count
from all_tab_columns
where
    owner like upper( $like_owner_name )
and table_name like upper( $like_table_name )
order by owner , table_name , column_id
end-select
end-procedure ! columns_main

!--------------------------------------------------- columns_before_table
begin-procedure columns_before_table
   show &columns_main__table_name
   show ''
   show 'Name                           Data Type        Null?'
   show '------------------------------ ---------------- --------'
   add 4 to #columns_main__count
end-procedure ! columns_before_table

!---------------------------------------------------- columns_after_table
begin-procedure columns_after_table
   show ''
   add 1 to #columns_main__count
end-procedure ! columns_after_table