Home » SQL & PL/SQL » SQL & PL/SQL » sample data printing (Oracle)
sample data printing [message #348963] Thu, 18 September 2008 07:39 Go to next message
paparaou
Messages: 1
Registered: September 2008
Junior Member
for sample purpose i want to print one record from every table in user_tables in the following order. i gave employee table details..

Table_name Column_name Sample_Data
------------------------------------
EMP EMPNO 7934
EMP ENAME MILLER
EMP JOB CLERK
EMP MGR 7782
EMP HIREDATE 1/10/2008
EMP SAL 2000
EMP COMM 100
EMP DEPTNO 20

how to achieve the above logic.

Paparao
Re: sample data printing [message #348967 is a reply to message #348963] Thu, 18 September 2008 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use PL/SQL and DBMS_SQL package to do this or SQL that generates SQL.

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Thu, 18 September 2008 07:42]

Report message to a moderator

Re: sample data printing [message #348977 is a reply to message #348967] Thu, 18 September 2008 08:02 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Micehl

I am printing all the tables using follwoing snippet , how can i go for each table and print it's records.

declare
cursor c1 is select distinct table_name from user_tables;
begin
for var in c1 loop
dbms_output.put_line ('Table_name ' ||var.table_name);
end loop;
end ;
/


Table_name REPCAT$_REPGROUP_PRIVS
Table_name REPCAT$_GROUPED_COLUMN
Table_name REPCAT$_TEMPLATE_STATUS
Table_name REPCAT$_USER_AUTHORIZATIONS
Table_name REPCAT$_TEMPLATE_OBJECTS
Table_name REPCAT$_TEMPLATE_SITES
Table_name REPCAT$_TEMPLATE_TARGETS
Table_name RAVI_TEST2
Table_name JAVA$JVM$STATUS
Table_name MVIEW$_ADV_OWB
Table_name LOGMNR_INDPART$
Table_name LOGMNR_TS$
Table_name LOGMNRC_GTCS
Table_name LOGMNR_DICTIONARY$
Re: sample data printing [message #348989 is a reply to message #348977] Thu, 18 September 2008 08:57 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Set heading   off
Set feedback  off
Set pagesize  0
Set termout   off
Set trimout   on
Set trimspool on
Set recsep    off
Set linesize  100
Spool tmp
Select 'Prompt
Prompt '||table_name||':
Prompt
Select * from '||table_name||' where rownum=1;'||'
Prompt'
from user_tables
order by table_name
/
Spool off
Set heading on
Set pagesize 100
Set lines 200
Set termout on
@tmp.LST

Put this in a script and execute it.

Regards
Michel
Previous Topic: updated/new records fetch
Next Topic: displaying create trigger details
Goto Forum:
  


Current Time: Tue Dec 06 10:40:32 CST 2016

Total time taken to generate the page: 0.12228 seconds