Sunday, January 8, 2012

how to show a string as a ROW in oracle database query?

very simply you can do it. see the example......

 SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 9 10:10:57 2012  
 Copyright (c) 1982, 2005, Oracle. All rights reserved.  
 SQL> conn scott@orcl105  
 Enter password:  
 Connected.  
 SQL>  
 SQL>  
 SQL> variable a varchar2(4000) ;  
 SQL>  
 SQL> exec :a:='MUHAMMADABDULHALIM' ;  
 PL/SQL procedure successfully completed.  
 SQL> SELECT   SUBSTR (:a, LEVEL, 1)  
  2    FROM DUAL  
  3 CONNECT BY LEVEL <= LENGTH (:a);  
 S  
 -  
 M  
 U  
 H  
 A  
 M  
 M  
 A  
 D  
 A  
 B  
 D  
 S  
 -  
 U  
 L  
 H  
 A  
 L  
 I  
 M  
 18 rows selected.  
 SQL>  
 SQL>  
 SQL> exec :a:='MUHAMMAD ABDUL HALIM' ;  
 PL/SQL procedure successfully completed.  
 SQL>  
 SQL>  
 SQL> SELECT SUBSTR (:a, LEVEL, 1) FROM DUAL CONNECT BY LEVEL <= LENGTH (:a);  
 S  
 -  
 M  
 U  
 H  
 A  
 M  
 M  
 A  
 D  
 A  
 B  
 S  
 -  
 D  
 U  
 L  
 H  
 A  
 L  
 I  
 M  
 20 rows selected.  
 SQL>  


in a contrary of this, is here...
http://halimdba.blogspot.com/2009/11/use-of-vmconcat.html

No comments: