Понадобилось заменить одну букву другой в артикуле.
При создании префикса не продумали сначала этот вопрос.
У нас одиночный магазин.
SQL код:
SET echo on feedback on verify on
spool analyse_.log
ALTER TABLE SMCARD DISABLE CONSTRAINT SMCCARD_GLOBAL ;
ALTER TABLE SMCARDPROPERTIES DISABLE CONSTRAINT SMCCARDPROPERTIES_CARD ;
ALTER TABLE SMCARDTAX DISABLE CONSTRAINT SMCCARDTAXART ;
ALTER TABLE SMCASHQUEUE DISABLE CONSTRAINT SMCCASHQUEUE_ARTICLE ;
ALTER TABLE SMCOMPLEXARTICLES DISABLE CONSTRAINT SMCCOMPLEXARTICLES_PART ;
ALTER TABLE SMCOMPLEXARTICLES DISABLE CONSTRAINT SMCCOMPLEXARTICLES_CPLX ;
ALTER TABLE SMDISCLIMITS DISABLE CONSTRAINT SMCDISCLIMITS_ART ;
ALTER TABLE SMPRICEHISTORY DISABLE CONSTRAINT SMCPRICEHISTARTICLE ;
ALTER TABLE SMPRICERARTICLEHISTORY DISABLE CONSTRAINT SMCPRICERARTHISTORY_ARTICLE ;
ALTER TABLE SMPRICERPRINTED DISABLE CONSTRAINT SMCPRICERPRINTED_ART ;
ALTER TABLE SMPRICES DISABLE CONSTRAINT SMCPRICESARTICLE ;
ALTER TABLE SMSPEC DISABLE CONSTRAINT SMCSPECARTICLE ;
ALTER TABLE SMSPECCO DISABLE CONSTRAINT SMCSPECCO_ARTICLE ;
ALTER TABLE SMSPECCOMPINF DISABLE CONSTRAINT SMCSPECCOMPINF_COMPLEXARTICLE ;
ALTER TABLE SMSPECCQ DISABLE CONSTRAINT SMCSPECCQ_ARTICLE ;
ALTER TABLE SMSTOREUNITHIST DISABLE CONSTRAINT SMCSTOREUNITHIST_CARD ;
ALTER TABLE SMSTOREUNITS DISABLE CONSTRAINT SMCBARCODEARTICLE ;
COMMIT;
spool off
SET echo off feedback off verify off pagesize 0 linesize 250
PROMPT Create command file ...
spool analyse.txt
select 'ALTER TRIGGER ' || TRiGGER_NAME || ' DISABLE;'
FROM user_triggers
WHERE status = 'ENABLED' AND TRiGGER_NAME LIKE 'SM%';
select 'update ' || TABLE_NAME ||
' set ' || TABLE_NAME || '.' || COLUMN_NAME || ' = replace ('
|| TABLE_NAME || '.' || COLUMN_NAME || ', ''H00'', ''╓═0'') where '
|| COLUMN_NAME || ' LIKE ''%H00%'';'
from ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%ARTICLE%' and TABLE_NAME LIKE 'SM%' and owner = 'SUPERMAG'
order by table_name;
select 'update ' || TABLE_NAME ||
' set ' || TABLE_NAME || '.' || COLUMN_NAME || ' = replace ('
|| TABLE_NAME || '.' || COLUMN_NAME || ', ''╓H0'', ''╓═0'') where '
|| COLUMN_NAME || ' LIKE ''%╓H0%'';'
from ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%ARTICLE%' and TABLE_NAME LIKE 'SM%' and owner = 'SUPERMAG'
order by table_name;
select 'ALTER TRIGGER ' || TRiGGER_NAME || ' ENABLE;'
FROM user_triggers
WHERE TRiGGER_NAME LIKE 'SM%';
spool off
SET echo on feedback on verify on
spool analyse.log
start analyse.txt
ALTER TABLE SMCARD ENABLE VALIDATE CONSTRAINT SMCCARD_GLOBAL ;
ALTER TABLE SMCARDPROPERTIES ENABLE VALIDATE CONSTRAINT SMCCARDPROPERTIES_CARD ;
ALTER TABLE SMCARDTAX ENABLE VALIDATE CONSTRAINT SMCCARDTAXART ;
ALTER TABLE SMCASHQUEUE ENABLE VALIDATE CONSTRAINT SMCCASHQUEUE_ARTICLE ;
ALTER TABLE SMCOMPLEXARTICLES ENABLE VALIDATE CONSTRAINT SMCCOMPLEXARTICLES_PART ;
ALTER TABLE SMCOMPLEXARTICLES ENABLE VALIDATE CONSTRAINT SMCCOMPLEXARTICLES_CPLX ;
ALTER TABLE SMDISCLIMITS ENABLE VALIDATE CONSTRAINT SMCDISCLIMITS_ART ;
ALTER TABLE SMPRICEHISTORY ENABLE VALIDATE CONSTRAINT SMCPRICEHISTARTICLE ;
ALTER TABLE SMPRICERARTICLEHISTORY ENABLE VALIDATE CONSTRAINT SMCPRICERARTHISTORY_ARTICLE ;
ALTER TABLE SMPRICERPRINTED ENABLE VALIDATE CONSTRAINT SMCPRICERPRINTED_ART ;
ALTER TABLE SMPRICES ENABLE VALIDATE CONSTRAINT SMCPRICESARTICLE ;
ALTER TABLE SMSPEC ENABLE VALIDATE CONSTRAINT SMCSPECARTICLE ;
ALTER TABLE SMSPECCO ENABLE VALIDATE CONSTRAINT SMCSPECCO_ARTICLE ;
ALTER TABLE SMSPECCOMPINF ENABLE VALIDATE CONSTRAINT SMCSPECCOMPINF_COMPLEXARTICLE ;
ALTER TABLE SMSPECCQ ENABLE VALIDATE CONSTRAINT SMCSPECCQ_ARTICLE ;
ALTER TABLE SMSTOREUNITHIST ENABLE VALIDATE CONSTRAINT SMCSTOREUNITHIST_CARD ;
ALTER TABLE SMSTOREUNITS ENABLE VALIDATE CONSTRAINT SMCBARCODEARTICLE ;
COMMIT;
spool off
set echo off
exit;