1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to display data without knowing columns

Discussion in 'SQL PL/SQL' started by jagadekara, Mar 10, 2015.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    One of my friend asked me below question.

    I want TO display data LIKE this FROM EMP TABLE USING plsql.

    7499 'ALLEN SALESMAN' 7698 '2/20/1981' 1600 300 30

    Without knowing COLUMNs IN emp TABLE i want TO display data.i have tried but NOT working..

    DECLARE
    CURSOR c1 IS SELECT * FROM all_tab_columns WHERE table_naMe='EMP';
    CURSOR c2 IS SELECT * FROM emp;
    v_column_name varchar(200);
    BEGIN
    FOR i IN c1 LOOP
    FOR j IN c2 LOOP
    v_column_name :='j.'||i.column_name;
    dbms_output.put_line(v_column_name);
    END LOOP;
    END LOOP;
    END;

    So do you have an Idea?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    to use NDS or DBMS_SQL...

    DBMS_SQL

    DBMS_SQL.DESC_TAB3

    dynamic sql enhancements in 11g


    for example :
    Code (SQL):

    DECLARE
      curid      NUMBER;
      desctab    DBMS_SQL.DESC_TAB3;
      colcnt     NUMBER;
      sql_stmt   VARCHAR2(200) := 'select * from all_tab_columns';
    BEGIN
     
        curid := DBMS_SQL.OPEN_CURSOR;
     
        DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
     
        DBMS_SQL.DESCRIBE_COLUMNS3(curid, colcnt, desctab);
     
        FOR i IN 1 .. colcnt LOOP
            DBMS_OUTPUT.PUT_LINE(desctab(i).col_name);
        END LOOP;
     
        DBMS_SQL.CLOSE_CURSOR(curid);
    END;
     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which isn't what he wants; the goal is to dynamically generate a column list to output the desired values from EMP, not just the column names. For the example of EMP the goal is to generate, dynamically, dbms_output statements to display the contents of the record returned. Of course the easiest way to do that is with a ref cursor:


    Code (SQL):

    SQL> CREATE OR REPLACE FUNCTION get_tab_data(tabname IN varchar2)
      2  RETURN sys_refcursor AS
      3
      4          mycur sys_refcursor;
      5
      6          sqlstr varchar2(4000);
      7
      8  BEGIN
      9          sqlstr := 'select * from '||tabname;
     10
     11          OPEN mycur FOR sqlstr;
     12
     13          RETURN mycur;
     14
     15  END;
     16  /


    FUNCTION created.


    SQL>
    SQL> SHOW errors FUNCTION get_tab_data
    No errors.
    SQL>
    SQL> SELECT get_tab_data('EMP') FROM dual;


    GET_TAB_DATA('EMP')
    --------------------
    CURSOR STATEMENT : 1
    CURSOR STATEMENT : 1


         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20


         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
          7939 DUKE       CEO                  17-NOV-81       5000                    10
          7949 PRINCE     CFO                  17-NOV-81       5000                    10
          7959 QUEEN      CIO                  17-NOV-81       5000                    10
          7869 JACK       PRESIDENT            17-NOV-81       5000                    10


    18 ROWS selected.

    SQL>
     
     
    Maheswariforever likes this.
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks David,

    Your solution is working fine in SQL *Plus.

    But when I use it in SQL Developer, then I got like this.

    {<EMPNO=7369,ENAME=SMITH,JOB=CLERK,MGR=7902,HIREDATE=17-DEC-80,SAL=800,COMM=null,DEPTNO=20>,<EMPNO=7499,ENAME=ALLEN,JOB=SALESMAN,MGR=7698,HIREDATE=20-FEB-81,SAL=1600,COMM=300,DEPTNO=30>,<EMPNO=7521,ENAME=WARD,JOB=SALESMAN,MGR=7698,HIREDATE=22-FEB-81,SAL=1250,COMM=500,DEPTNO=30>,<EMPNO=7566,ENAME=JONES,JOB=MANAGER,MGR=7839,HIREDATE=02-APR-81,SAL=2975,COMM=null,DEPTNO=20>,<EMPNO=7654,ENAME=MARTIN,JOB=SALESMAN,MGR=7698,HIREDATE=28-SEP-81,SAL=1250,COMM=1400,DEPTNO=30>,<EMPNO=7698,ENAME=BLAKE,JOB=MANAGER,MGR=7839,HIREDATE=01-MAY-81,SAL=2850,COMM=null,DEPTNO=30>,<EMPNO=7782,ENAME=CLARK,JOB=MANAGER,MGR=7839,HIREDATE=09-JUN-81,SAL=2450,COMM=null,DEPTNO=10>,<EMPNO=7788,ENAME=SCOTT,JOB=ANALYST,MGR=7566,HIREDATE=09-DEC-82,SAL=3000,COMM=null,DEPTNO=20>,<EMPNO=7839,ENAME=KING,JOB=PRESIDENT,MGR=null,HIREDATE=17-NOV-81,SAL=5000,COMM=null,DEPTNO=10>,<EMPNO=7844,ENAME=TURNER,JOB=SALESMAN,MGR=7698,HIREDATE=08-SEP-81,SAL=1500,COMM=0,DEPTNO=30>,<EMPNO=7876,ENAME=ADAMS,JOB=CLERK,MGR=7788,HIREDATE=12-JAN-83,SAL=1100,COMM=null,DEPTNO=20>,<EMPNO=7900,ENAME=JAMES,JOB=CLERK,MGR=7698,HIREDATE=03-DEC-81,SAL=950,COMM=null,DEPTNO=30>,<EMPNO=7902,ENAME=FORD,JOB=ANALYST,MGR=7566,HIREDATE=03-DEC-81,SAL=3000,COMM=null,DEPTNO=20>,<EMPNO=7934,ENAME=MILLER,JOB=CLERK,MGR=7782,HIREDATE=23-JAN-82,SAL=1300,COMM=null,DEPTNO=10>,}

    Only one row coming.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which version of SQL Developer?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're getting all of the records, it's the formatting that is causing issues. When I know what version of SQL Developer you're using I'll have better answers.
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    I am using Version 1.5.4
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to upgrade; 4 is the latest major release and it handles refcursor results correctly.
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks David,

    I will inform to my IT Team to upgrade it. I will inform to my friend also who asked me this question.

    Thanks a ton.
     
  10. vprog

    vprog Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Ghaziabad
    select * from emp;
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hello Vprog,

    Please review my first post, I need this through PL/SQL.
     
  12. vprog

    vprog Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Ghaziabad
    You can use only SQL to execute this query. If you want to use only PL/SQL, then the above solutions, with declare etc., will work...

    this is SQL....
    SELECT * FROM emp
    WHERE empID = 8;