OraFAQ Forum: SQL & PL/SQL » Need to retrieve records by getting columns from all_tab

Home » SQL & PL/SQL » SQL & PL/SQL » Need to retrieve records by getting columns from all_tab_columns

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to retrieve records by getting columns from all_tab_columns [message #609397]

Thu, 06 March 2014 05:04

HemaV
Messages: 6
Registered: July 2012
Location: Bangalore

Junior Member

Hi All,

Need an generalized SQL query to retrieve records by getting column names from all_tab_columns.

SELECT 'SELECT '|| WM_CONCAT(COLUMN_NAME)||' FROM '|| aschema.BACS_DEBIT_STD18_D_LND' ||';' FROM all_tab_columns where table_name=EMP_LND';

This above query gives me select statement sql query but unable to get records.

Thanks in Advance,
H

Report message to a moderator

Re: Need to retrieve records by getting columns from all_tab_columns [message #609398 is a reply to message #609397]

Thu, 06 March 2014 05:15

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Put the result in a file and execute the file.

Oracle version?

Report message to a moderator

Re: Need to retrieve records by getting columns from all_tab_columns [message #609402 is a reply to message #609398]

Thu, 06 March 2014 06:16

HemaV
Messages: 6
Registered: July 2012
Location: Bangalore

Junior Member

Hi,

I wanted in SQL query format because in my tool i cant put into file and execute the file.

Oracle 11i we are using.

Report message to a moderator

Re: Need to retrieve records by getting columns from all_tab_columns [message #609403 is a reply to message #609402]

Thu, 06 March 2014 06:17

HemaV
Messages: 6
Registered: July 2012
Location: Bangalore

Junior Member

Sorry to type in wrong query. Corrected SQL query:

SELECT 'SELECT '|| WM_CONCAT(COLUMN_NAME)||' FROM '|| aschema.EMP_LND' ||';' FROM all_tab_columns where table_name=EMP_LND';

Report message to a moderator

Re: Need to retrieve records by getting columns from all_tab_columns [message #609406 is a reply to message #609402]

Thu, 06 March 2014 06:50

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So store it in a variable and execute this one.
Or open a ref cursor for this SQL string.
Something like:

SQL> var rc refcursor
SQL> declare
  2    sql_text varchar2(4000);
  3  begin
  4    select 'select '||listagg(column_name,',') within group (order by column_id)||
  5           ' from '||owner||'.'||table_name
  6    into sql_text
  7    from all_tab_columns
  8    where owner = 'SCOTT' and table_name = 'EMP'
  9    group by owner, table_name;
 10    open :rc for sql_text;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> print rc
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 23/05/1987 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10

Report message to a moderator

Previous Topic:	Sending UTL_SMTP mail with jpg picture as header inside an e-mail
Next Topic:	Delete thousends of tables per script (without undo)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 23:34:57 CDT 2024