Nov 30, 2010

General Select Statments..

How to get Column Name:


select * from all_tab_columns where column_name like 'CURRENCY%' and data_type='VARCHAR2'

How to get Table Name:
select * from all_objects where object_type='TABLE' and object_name like 'CUSTOMER%'

select * from v$parameter where UPPER(name) like 'UTL_FILE_DIR'

      
How to Know column comments from Database?
----For knowing the column comments-----      
select * from ALL_COL_COMMENTS  
where table_name =     'CASHRECEIPTS_ALL'
and column_name ='CAREASONCODE'
      
How to Know the Proc and Packages from Database?
----for knowing the Proc and Packages------------
select * from all_objects
 where object_type like 'PROCEDURE'
 and object_name like '%BSCS%'

 select * from all_objects
 where object_type like 'PACKAGE'
 and object_name like '%BSCS%'

 copy the Proc or Pack and Press F4.



How to Know the columns and rows select stmt from Database?

----columns and rows select stmt---------------

SELECT --deptno,
       SUBSTR(SYS_CONNECT_BY_PATH(ename,','),2) name
  FROM
      (SELECT ename
             ,deptno
             ,COUNT(*) over (PARTITION BY deptno) cnt
             ,row_number () over (PARTITION BY deptno ORDER BY ename) seq
        FROM emp
       WHERE deptno IS NOT NULL
      )
WHERE cnt=seq
START WITH seq=1
CONNECT BY PRIOR seq+1=seq
AND PRIOR deptno=deptno

No comments:

OraApps Search

Custom Search

Search This Blog