423,680 Members | 2,461 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,680 IT Pros & Developers. It's quick & easy.

COMMAND LINE for generating script for oracle table

P: 25
Hi all,

I am using Oracle_OraClient11g and PL/SQL developer. I am finding a COMMAND LINE of DOS to generate a script.sql. That means the COMMAND LINE will be sth as following:

_ Input: name of table

_ Output: file sql.script

I think I can find such a COMMAND LINE like that in "..\product\11.1.0\client_1\BIN" or "\product\11.1.0\client_1\LIB". However, I have not found anything like that.

Have you got any suggestion for me?

Thank you so much!
Mar 22 '10 #1

✓ answered by magicwand

Expand|Select|Wrap|Line Numbers
  1. set heading  off
  2. set showmode off
  3. set feedback off
  4. set define   on
  5. set verify   off
  6.  
  7. set long 1000000
  8. set pagesize 9999
  9. set linesize 150
  10.  
  11. undefine TNAME
  12. undefine FNAME
  13.  
  14. accept TNAME prompt "Enter table name : "
  15. accept FNAME prompt "Enter file name  : "
  16.  
  17. spool &FNAME
  18. select dbms_metadata.get_ddl('TABLE',upper('&TNAME')) "CREATE Statement" from dual;
  19. spool off
  20. exit
  21.  
Save the script as i.e. cr_tbl.sql an run as:

sqlplus -s <username>/<pwd>@<dbconn> @cr_tbl

Share this Question
Share on Google+
2 Replies


P: 41
Expand|Select|Wrap|Line Numbers
  1. set heading  off
  2. set showmode off
  3. set feedback off
  4. set define   on
  5. set verify   off
  6.  
  7. set long 1000000
  8. set pagesize 9999
  9. set linesize 150
  10.  
  11. undefine TNAME
  12. undefine FNAME
  13.  
  14. accept TNAME prompt "Enter table name : "
  15. accept FNAME prompt "Enter file name  : "
  16.  
  17. spool &FNAME
  18. select dbms_metadata.get_ddl('TABLE',upper('&TNAME')) "CREATE Statement" from dual;
  19. spool off
  20. exit
  21.  
Save the script as i.e. cr_tbl.sql an run as:

sqlplus -s <username>/<pwd>@<dbconn> @cr_tbl
Mar 25 '10 #2

P: 25
thank you. I did it.
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.