Home » Other » Client Tools » SQLPLUS stops spool (Oracle 10.2.0.3)
SQLPLUS stops spool [message #557996] Mon, 18 June 2012 10:35 Go to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,
I am using the below setting to pull the script of views

set feedback off
set long 1000000
set trimspool on
SET LINESIZE 32767
SET HEADING  on 
set PAGESIZE 40000
set verify off 
set termout off
set echo off
column a format a200


I use the below kind of statements to pull the ddl and spool

spool K2K_STAGE_VIEW 
select dbms_metadata.get_ddl('VIEW', 'B_APPLIKATIONSARTEN',  'BB2BB') a from dual;

select dbms_metadata.get_ddl('VIEW', 'B_VERWENDUNGEN',  'BB2BB') a from dual;

select dbms_metadata.get_ddl('VIEW', 'B_USECASEARTEN_SPEZ',  'BB2BB') a from dual;

select dbms_metadata.get_ddl('VIEW', 'B_PRODUKTARTEN',  'BB2BB') a from dual;

select dbms_metadata.get_ddl('VIEW', 'B_ENTSCHEIDARTEN',  'BB2BB') a from dual;

select dbms_metadata.get_ddl('VIEW', 'B_AUFTRAGGEBER',  'BB2BB') a from dual;

select dbms_metadata.get_ddl('VIEW', 'B_SABE_OE',  'BB2BB') a from dual;

select dbms_metadata.get_ddl('VIEW', 'B_SABE_OE_SICHT',  'BB2BB') a from dual;

spool off


I have nearly 26 above spool commands like above in a script.

When i try to run the the script from sqlplus it runs for most of the spool commands but stops and waiting for the user . There are no control character.
Do I need to set any sqlplus setting to contrine further and not to stop.

Thank you very much in advance

Regards,
Pointers

Report message to a moderator

Re: SQLPLUS stops spool [message #557997 is a reply to message #557996] Mon, 18 June 2012 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
set pause off

Regards
Michel

Report message to a moderator

Re: SQLPLUS stops spool [message #557999 is a reply to message #557997] Mon, 18 June 2012 10:45 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Micheal,

I just got to try that, but it dint work, still the same.

Regards,
Pointers

Report message to a moderator

Re: SQLPLUS stops spool [message #558000 is a reply to message #557999] Mon, 18 June 2012 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So insufficient data to compute.

Regards
Michel

Report message to a moderator

Re: SQLPLUS stops spool [message #558014 is a reply to message #558000] Mon, 18 June 2012 15:49 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member

No, Micheal, I got to run each spool individually, there is no syntax or other errors and there was some result if I run individually.

Regards,
Pointers

Report message to a moderator

Re: SQLPLUS stops spool [message #558016 is a reply to message #558014] Mon, 18 June 2012 17:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


how can we reproduce what you report?

Report message to a moderator

Re: SQLPLUS stops spool [message #558072 is a reply to message #558016] Tue, 19 June 2012 04:23 Go to previous message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi Micheal, Blackswan,

This is exactly what I have.

set feedback off
set long 1000000
set trimspool on
SET LINESIZE 32767
SET HEADING  on 
set PAGESIZE 40000
set verify off 
set termout off
set echo off
column a format a200
set pause off

spool midbbV_OE_HIERARCHY.vw
select dbms_metadata.get_ddl('VIEW', 'midbbV_OE_HIERARCHY',             'bb2bb') a from dual;
spool off

spool perv_abgebrochene_gf_lis.vw
select dbms_metadata.get_ddl('VIEW', 'perV_ABGEBROCHENE_GF_LIS',      'bb2bb') a from dual;
spool off

spool perv_abgebrochene_gf_lis_crn.vw
select dbms_metadata.get_ddl('VIEW', 'perV_ABGEBROCHENE_GF_LIS_CRN',  'bb2bb') a from dual;
spool off

spool perv_freigegebene_at_lis.vw 
select dbms_metadata.get_ddl('VIEW', 'perV_FREIGEGEBENE_AT_LIS'   ,   'bb2bb') a from dual;
spool off

spool perv_freigegebene_at_lis_crn.vw
select dbms_metadata.get_ddl('VIEW', 'perV_FREIGEGEBENE_AT_LIS_CRN',  'bb2bb') a from dual;
spool off

spool perv_instruktfelder_lis_crn.vw
select dbms_metadata.get_ddl('VIEW', 'perV_INSTRUKTFELDER_LIS_CRN' ,  'bb2bb') a from dual;
spool off

spool  perv_instruktionsfelder_lis.vw
select dbms_metadata.get_ddl('VIEW', 'perV_INSTRUKTIONSFELDER_LIS' ,  'bb2bb') a from dual;
spool off

spool  midbbV_ANTRAEGE.vw
select dbms_metadata.get_ddl('VIEW', 'midbbV_ANTRAEGE'  ,               'bb2bb') a from dual;
spool off

spool  midbbV_ANTRAEGE_DT.vw
select dbms_metadata.get_ddl('VIEW', 'midbbV_ANTRAEGE_DT'   ,           'bb2bb') a from dual;
spool off

spool  midbbV_KUBE_GESCHFALL_OE.vw
select dbms_metadata.get_ddl('VIEW', 'midbbV_KUBE_GESCHFALL_OE',        'bb2bb') a from dual;
spool off

spool  midbbV_KUBE_GFVERW_OE.vw
select dbms_metadata.get_ddl('VIEW', 'midbbV_KUBE_GFVERW_OE'   ,        'bb2bb') a from dual;
spool off

spool midbbV_KUBE_PRODUKT_OE --synonym
select dbms_metadata.get_ddl('SYNONYM', 'midbbV_KUBE_PRODUKT_OE', 'bb2bb') a from dual;
spool off

spool  midbbv_produkttypen_sort.vw
select dbms_metadata.get_ddl('VIEW', 'midbbV_PRODUKTTYPEN_SORT'   ,     'bb2bb') a from dual;
spool off

spool  midbbV_SABE_GESCHFALL_OE.vw
select dbms_metadata.get_ddl('VIEW', 'midbbV_SABE_GESCHFALL_OE'   ,     'bb2bb') a from dual;
spool off

spool  midbbV_SABE_GFVERW_OE.vw
select dbms_metadata.get_ddl('VIEW', 'midbbV_SABE_GFVERW_OE'      ,     'bb2bb') a from dual;
spool off

spool midbbV_SABE_PRODUKT_OE --synonym
select dbms_metadata.get_ddl('SYNONYM', 'midbbV_SABE_PRODUKT_OE', 'bb2bb') a from dual;
spool off

spool K2K_CONTROL_VIEW 
select dbms_metadata.get_ddl('VIEW', 'CTLV_OBJECT_LOG',       'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'CTLV_ERRORS', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'CTLV_OBJECT_LOG_LAST_VERSION', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'CTLV_BATCHMELD_HEUTE', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'CTLV_BATCHMELD_LETZTE_STUNDE', 'bb2bb') a from dual;
spool off

spool K2K_REF_VIEW 
select dbms_metadata.get_ddl('VIEW', 'K2KV_REF_BEWERTUNGWGS','bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'K2KV_REF_VERWENDUNGEN', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'K2KV_REF_AUFTRAGGEBER', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'K2KV_REF_AUFTRAGSINIT', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'K2KV_REF_AUFTRAGSSTAT', 'bb2bb') a from dual;
spool off

spool K2K_SOURCE_VIEW 
select dbms_metadata.get_ddl('VIEW', 'SRCV_CREDITCENTER','bb2bb') a from dual; 
select dbms_metadata.get_ddl('VIEW', 'SRCV_EMPTEAMCONNECTION', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'SRCV_SERVICECENTER','bb2bb') a from dual; 

-------Here it is stopping

select dbms_metadata.get_ddl('VIEW', 'SRCV_TEAM', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'SRCV_GKV_RD_WMRATES','bb2bb') a from dual; 
spool off

spool K2K_STAGE_VIEW 
select dbms_metadata.get_ddl('VIEW', 'STGV_APPLIKATIONSARTEN',  'bb2bb') a from dual;

select dbms_metadata.get_ddl('VIEW', 'STGV_VERWENDUNGEN',  'bb2bb') a from dual;

select dbms_metadata.get_ddl('VIEW', 'STGV_USECASEARTEN_SPEZ',  'bb2bb') a from dual;

select dbms_metadata.get_ddl('VIEW', 'STGV_PRODUKTARTEN',  'bb2bb') a from dual;

select dbms_metadata.get_ddl('VIEW', 'STGV_ENTSCHEIDARTEN',  'bb2bb') a from dual;

select dbms_metadata.get_ddl('VIEW', 'STGV_AUFTRAGGEBER',  'bb2bb') a from dual;

select dbms_metadata.get_ddl('VIEW', 'STGV_SABE_OE',  'bb2bb') a from dual;

select dbms_metadata.get_ddl('VIEW', 'STGV_SABE_OE_SICHT',  'bb2bb') a from dual;

spool off

spool K2K_STAGE_VIEW_ZUS_midbb   
select dbms_metadata.get_ddl('VIEW', 'STGV_AUFTRAGSSTATI', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'STGV_GFBEARBARTEN', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'STGV_GESCHFALLSTATI', 'bb2bb') a from dual;
spool off

spool K2K_STAGE_VIEW_ZUS_midbb_2 
select dbms_metadata.get_ddl('VIEW', 'STGV_PRODUKTE', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'STGV_PRODUKTVARIANTEN', 'bb2bb') a from dual;
spool off

spool K2K_STARING_VIEW  
select dbms_metadata.get_ddl('VIEW', 'midbbV_CCO_MA_SICHT', 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'midbbV_KUBE_MA_SICHT,' 'bb2bb') a from dual;
select dbms_metadata.get_ddl('VIEW', 'midbbV_SABE_MA_SICHT', 'bb2bb') a from dual;
spool off

spool  perV_per13_SABE_CRN.vw
select dbms_metadata.get_ddl('VIEW', 'perV_per13_SABE_CRN'    ,       'bb2bb') a from dual;
spool off

spool  perV_per13_SABE_DE.vw
select dbms_metadata.get_ddl('VIEW', 'perV_per13_SABE_DE'      ,      'bb2bb') a from dual;
spool off

spool  perV_per13_SABE_FR.vw
select dbms_metadata.get_ddl('VIEW', 'perV_per13_SABE_FR'      ,      'bb2bb') a from dual;
spool off

spool  perV_per13_SABE_IT.vw
select dbms_metadata.get_ddl('VIEW', 'perV_per13_SABE_IT'      ,      'bb2bb') a from dual;
spool off


As marked above, it get executed till the marked line above (-------Here it is stopping) after that execution, in my sqlplus it shows a line like
select dbms_metadata.get_ddl('VIEW', 'SRCV_TEAM',
and stops there.

Could you please let me know how to make the sqlplus window to execute entire script .

Thank you very much in advance.

Regards,
Pointers

Report message to a moderator

Previous Topic: set echo on is not working in shell scripts
Next Topic: insert into miultiple table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 02 16:41:40 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.