Quote:
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.
dBforums
>
Oracle
11-27-03, 04:27
Linear Mode
