Home » SQL & PL/SQL » SQL & PL/SQL » print the columns of a table
print the columns of a table [message #589396] Fri, 05 July 2013 04:36 Go to next message
maddula
Messages: 8
Registered: June 2013
Location: India
Junior Member
DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
BEGIN
JOBSFILE := UTL_FILE.FOPEN('FILES','JOBS.TXT','W');
UTL_FILE.PUT_LINE(JOBSFILE,'Action = insert');
FOR REC IN JOBSCUR

LOOP
UTL_FILE.PUT_LINE(JOBSFILE,'DDOCNAME='|| REC.DDOCNAME
||CHR(13)||'DDOCTITLE='|| REC.DDOCTITLE
||CHR(13)||'DSECURITYGROUP='|| REC.DSECURITYGROUP
||CHR(13)||'DDOCAUTHOR='|| REC.DDOCAUTHOR
||CHR(13)||'DDOCTYPE='|| REC.DDOCTYPE
||CHR(13)||'DINDATE='|| REC.DINDATE
||CHR(13)||'PRIMARYFILE='|| REC.PRIMARYFILE
||CHR(13)||'EXTRACTIONDATE='|| REC.EXTRACTIONDATE
||CHR(13)||'BATCH_ID='|| REC.BATCH_ID
||CHR(13)||'<<EOD>>'
);
END LOOP;
UTL_FILE.FCLOSE(JOBSFILE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( 'ERROR -->' || SQLERRM);
END;
this is my program can you please suggest me is there anyway to print the values of full table .
i have used all column names to print .
Re: print the columns of a table [message #589397 is a reply to message #589396] Fri, 05 July 2013 04:38 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, for example
select * from target_ucm;
(if that's what you are asking)
Re: print the columns of a table [message #589398 is a reply to message #589397] Fri, 05 July 2013 04:40 Go to previous messageGo to next message
maddula
Messages: 8
Registered: June 2013
Location: India
Junior Member
i want this loop to repeat
UTL_FILE.PUT_LINE(JOBSFILE,'DDOCNAME='|| REC.DDOCNAME
||CHR(13)||'DDOCTITLE='|| REC.DDOCTITLE
||CHR(13)||'DSECURITYGROUP='|| REC.DSECURITYGROUP
||CHR(13)||'DDOCAUTHOR='|| REC.DDOCAUTHOR
||CHR(13)||'DDOCTYPE='|| REC.DDOCTYPE
||CHR(13)||'DINDATE='|| REC.DINDATE
||CHR(13)||'PRIMARYFILE='|| REC.PRIMARYFILE
||CHR(13)||'EXTRACTIONDATE='|| REC.EXTRACTIONDATE
||CHR(13)||'BATCH_ID='|| REC.BATCH_ID
||CHR(13)||'<<EOD>>'
);
i am selecting all the columns . is there any other way to print all columns
Re: print the columns of a table [message #589401 is a reply to message #589398] Fri, 05 July 2013 04:44 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, that! Another way would be to PUT_LINE the whole cursor record. Did you try it? Any success? No? Then I guess that what you already did is the right way to do that.
Re: print the columns of a table [message #589403 is a reply to message #589401] Fri, 05 July 2013 05:01 Go to previous messageGo to next message
maddula
Messages: 8
Registered: June 2013
Location: India
Junior Member
As I hard coded the column names here,I would like to get them on the fly along with the data with respect to the column.
Re: print the columns of a table [message #589408 is a reply to message #589403] Fri, 05 July 2013 05:33 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, Maddula
It depends on what you give or pass the strings you want to select list.
You can use the function instr to return the first string with delimeter, then deal with this sub-string, again the seond one, over and over again you can generate a statement like what you expected.

Re: print the columns of a table [message #589432 is a reply to message #589408] Fri, 05 July 2013 09:32 Go to previous message
mikomi
Messages: 33
Registered: July 2013
Member
maddula wrote on Fri, 05 July 2013 11:01
As I hard coded the column names here,I would like to get them on the fly along with the data with respect to the column.


If you mean, "can I do something like SELECT * FROM T but with dbms_output.put_line", then no, I don't think you can.
If you mean, "can I dynamically discover the column list in a SELECT list" then yes, I think you can, using method 4 dynamic sql with DBMS_SQL.
Below is a minor modification of Tom Kyte's print_table procedure; the original is at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1035431863958 but it prints table row as a number of lines, one line for each column+column value in the row. So I've modified it slightly to print a header and then to print each row on one line. You can of course modify it easily to suit your purposes:

create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    l_header        varchar2(4000);
    l_detailline    varchar2(4000);
    l_chrs_to_pad   number:=20;
begin
    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns ( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column (l_theCursor, i, l_columnValue, 4000);
        l_header:=l_header|| rpad( l_descTbl(i).col_name, l_chrs_to_pad );
    end loop;

    dbms_output.put_line(l_header);

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        l_detailline:='';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            l_detailline:=l_detailline || rpad( l_columnValue, l_chrs_to_pad );
        end loop;
        dbms_output.put_line(l_detailline);
    end loop;
exception
    when others then
      raise;
end;
/

SCOTT@ORCL> exec print_table('select * from emp where rownum < 5');
EMPNO               ENAME               JOB                 MGR                 HIREDATE            SAL
COMM                DEPTNO
9000                Nik O'Teen
7369                SMITH               CLERK               7902                17-Dec-1980         800
20
7499                ALLEN               SALESMAN            7698                20-Feb-1981         1600
300                 30
7521                WARD                SALESMAN            7698                22-Feb-1981         1250
500                 30

PL/SQL procedure successfully completed.



Note that as for select * from tab if you have many columns the output will wrap on your monitor. Should be fine for printing to file though.

[Updated on: Fri, 05 July 2013 09:33]

Report message to a moderator

Previous Topic: ORA-31603 when using DBMS_JOB.SUBMIT
Next Topic: Help with Query
Goto Forum:
  


Current Time: Fri Apr 18 15:12:14 CDT 2014

Total time taken to generate the page: 0.16226 seconds