Results 1 to 2 of 2

Thread: Oracle

  1. #1
    Join Date
    Nov 2003
    Location
    hyderabad
    Posts
    12

    Unanswered: Oracle

    hai friends,
    i need a proceudre which will display all the data in the entire database
    from all tables.

    waiting for reply....
    bye..

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle

    Originally posted by sreepadasharma
    hai friends,
    i need a proceudre which will display all the data in the entire database
    from all tables.

    waiting for reply....
    bye..
    What an extraordinary concept!

    By "all the data in the entire database" do you just mean for the current user's tables, or for all tables in all schemas in the entire database?

    Will the user be browsing this on a screen, or will the output be printed on a ton of paper and so someone can take it home and read it at their leisure? ;-)

    To do this in a procedure you would need to use the DBMS_SQL package to read the data, and UTL_FILE to write it to a file (DBMS_OUTPUT is probably no good, as you are very likely to exceed the million byte output limit). Something like this:

    Code:
    PROCEDURE dump_my_database
      c INTEGER := DBMS_SQL.OPEN_CURSOR;
      v_sql VARCHAR2(200);
    BEGIN
      FOR r in (SELECT table_name FROM user_tables -- or dba_tables?)
      LOOP
        v_sql := 'SELECT * FROM ' || r.table_name;
        DBMS_SQL.PARSE( c, v_sql, DBMS_SQL.NATIVE );
        ... etc.
      END LOOP;
    END;
    /
    I haven't gone into full detail because it's quite a bit of work.

    It would actually be far easier to do this in SQL Plus rather than a procedure, using a "SQL from SQL" script like this:

    Code:
    SET TERM OFF PAGESIZE 0
    COLUMN sql NEWLINE
    SPOOL entire_database_temp.sql
    PROMPT SPOOL entire_database.txt
    SELECT 'PROMPT ' || table_name table_name,
           'SELECT * FROM '||table_name||';' sql
      FROM user_tables;
    PROMPT SPOOL OFF
    SPOOL OFF
    SET PAGESIZE 100 LINESIZE 2000
    @entire_database_temp.sql
    SET TERM ON PAGESIZE 20
    Cut and paste that into a file called entire_database.sql, then run it like this:

    SQL> @entire_database

    Now take a look at the output file entire_database.txt and it will be something like this (only MUCH bigger!):

    Code:
    DEPT
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    4 rows selected.
    
    EMP
    
         EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO        AGE
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-1980        800                    20         42
          7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30         42
          7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30         42
          7566 JONES      MANAGER         7839 02-APR-1981       2975                    20         42
          7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30         42
          7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30         42
          7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10         42
          7788 SCOTT      ANALYST         7566 19-APR-2087       3000                    20         42
          7839 KING       PRESIDENT            17-NOV-1981       5000                    10         42
    
    9 rows selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •