How to run a sql script in Postgres which contain many blank lines due to a huge decode statement

Question

Let me try to explain the situation. We are migrating a black box application from a provider, and one of the steps is to change the database from Oracle to Postgres. The application developer has provided a set of Oracle SQL scripts to generate output sql files that contains the insert instructions. Well, I actually had to modify all the scripts he gave me because they were creating broken lines, as he works in graphical interfaces ( SQL Developer and PGAdmin ).

Anyway, at the end I was able to fix all of them, except one, and I would like to know whether this script will work in psql or not. If I do run it using any GUI it works. I have no psql client in my laptop, and I can't access the db server either, so I can't test it that way. My only option here is ask our DBA to do it.

However, my DBA says that the script fails. He does not provide any useful feedback, unfortunately. Anyway, I am quite sure that is because of the blank lines ( see below )

So, these are the steps and elements involved:

Original Table in Oracle

SQL> desc is_core.t_xmlh
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(100)
 ID_HIST                                   NOT NULL VARCHAR2(100)
 XML                                                BLOB

Query to get the output of the BLOB column using dbms_lob and rawtohex

SET FEEDBACK OFF
SET TERMOUT OFF
SET HEADING OFF
SET VERIFY OFF
SET ARRAY 5000 
spool export_t_xmlh.sql 
select
'Insert into IS_CORE.T_XMLH 
( ID,
  ID_HIST,
  XML
) 
values 
(''' ||REPLACE(T_XMLH.ID, '''', '''''') || ''',''' ||
       REPLACE(T_XMLH.ID_HIST, '''', '''''') || ''',',
       'decode(' || NVL2(dbms_lob.substr(T_XMLH.XML,1000,1), '''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,1)) || '''', ''''''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,1001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,1001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,2001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,2001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,3001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,3001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,4001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,4001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,5001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,5001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,6001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,6001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,7001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,7001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,8001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,8001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,9001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,9001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,10001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,10001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,11001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,11001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,12001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,12001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,13001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,13001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,14001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,14001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,15001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,15001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,16001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,16001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,17001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,17001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,18001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,18001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,19001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,19001)) || '''', ''),
        NVL2(dbms_lob.substr(T_XMLH.XML,1000,20001), ' || ''' || rawtohex(dbms_lob.substr(T_XMLH.XML,1000,20001)) || '''', ''),
        ', ''hex''));'
    from
      IS_CORE.T_XMLH
    where rownum < 2; -- just to get one row 

That query creates an output like this ( only one row as example )

Insert into IS_CORE.T_XMLH (ID,ID_HIST,XML) values ('2c9772e183867087018389d53b796ad5','2c9772e183867087018389d53b776ad4',                                                                                                                                                                                                                                                                                                                                                 
decode('789CED5A5993ABC8727EBF11F73F74F43CCA3AEC484C68DAC10E1220B14BBC38D80488552C42E2D7BBA45EA64F9F993B33BEBE0E87C30FDD5099599959595955F99558FDFBB52C9E2E71DB6575F5CB33F20D7E7E8AABB08EB22AF9E5D9B684F9F2F9DF5FFEFEB75557FB4D5C5D7EE6AB4B5CD44DFC04FA55DDCF6FE45F9ED3BE6F7E86A0711CBF8DD8B7BA4D201486310826A0BBC83C7EEBF6FCD62F3B46DFF501ED6F510CEDDABAAFC3BA78171BBB2EFE908BEAB0FB56FB5DD6CD81A6EA610408DC0DE1108C40AFAC7B0B46E68071FF8BC3A1CDFADB1CBCC4D77E0EC6F7EDDA45CF2F1FE391623F8ADB97D55DF667F34DFCE99D5B0E5D6F5740A0EBFD0A788C3CBF493259E5B7B77779ABCEE3EAC3E5E1BFC5E3A1CF8AFBF3DDE527FE6D5AAC5BF34FC4E4311B65DC757E12CF3F8C01233F317E1793F8EBC09E9F1CBF18E27FCED49580A9797F0FCDBC69EB6356C40F3B7B40BE60CF4F20503FCB20A6DF0571BE2431142590608E462832C7611C9F2FB1233AC7C8238691647C5C62E1F38B2ACBC27062599ADC24F42833742273F4B651551A1659F32C9A7280713ACFB0A34DABB2B850F56E64F503E7E8BAC88F6BDEB078205B8B3462F32CA37226EF58AA218F3CFD9091F8B1D8FBAE331D4A21F75CFC2A9EE80393680E43D716EF689C2938A26E3272805E73DF252A9917A44F36146063ED703CA272EAC3067D553B1BD664C7F618994F2F816834411926BA1D268AC9F8DE7E0DFBAED7C4655184953378983645A2D305A29D18937CDD9CE89A49F2739A672235C20CABF3AE883540D668BCB2680F7B830E51AD8E5C2F555C6A08C462F04C22F72C3A1646F8A65A34A19EE42B7822AA25F80FDA891F55CB063C9BD0ACC207719A7E234E9A4A77EF71026D8DD972FC56A5C7078DB9AA422C229D87855779A2D3F71889859606959106A5518419C3052831452E727F670F2ED13EE2658CA398BCC5EBAAC2DA49BFA816F089A37B8D3BC0CA04A81C3DAA46378AA
F3E71FC752DC46E540492560798570419A3F9FB757370E1EBDAA24F5F632454DA2992D4DEDF1B97106626601B067FB9B797074FB4C724E1B3AF3943EB364DE332C38DF49DBFA16B905F3A4B515C1943CB40B3336F2450680B859051DA1C3CC3CFE76B792DA7B52BA4C812DF9CA062297928BB744DB6C08D1D5973E5426EEDD22E352E9B0426923658265D9C76311CC30D0DB4ED6947908976E489E35618CC7545AE0BC425F0998BEA7B16EF6574CA1DED68B5875D9023BB33D1151B7999EC6DC9BBA5BB703DC3539649CDED5271CA8C111222E4C5CE5B1C0FC464C35292077D1F4214645D6F56949FDCD96927701BF3029B83674FB753EC7BA2CF5EF7F
|| '130A325BDAE4542747B7AEF5845DD41875E69EADF3611830D452690776316394BC0DC1D360E0B19FB017ED946E63719F9288E1B82CA15E50C2A31395A169F19484E39884F96BEE19B0C5D0F24873B4C1244592E609935E5296D67930392ACB28F4C88E0FB91D98188E4E2C9033B9C8BAAC68D2A238E3C1DCE97A2764CDC7BAF2510196450201F95746AE739385F7358CEC82F20AD6A29D789803F20AACA71B3E4AAF7E6C19E6C00B3BBB44EB628DC6E670A9CE079B4E258E87B630485F9559DED741248FFA41657C5AE023ECB2AB5AF37060156657D004874CA9CDD43246A82CFF90E575DD5719304B7F909B9C7E586F6A4F4E2FA106C6AE303ACD2509C845ED984B6DC6715936145DBE11551AA715A294D64BEDD6585C132C5B4582D6D97E90DBB675DDF5922B9723A6DBE56256372A2B983A972B1D2A9F7858DD89526AE74D73909A4EF72C2DBF8C4A2D6B0D8CC5EA829A19C20EBD51C4BE571BB5290DE13CAC198BBF6152C4F4EB5D592906ECF09CA7C1711A95E77808C3519CD570339D77BE911D95524FCD4BC6EC617116B4330E69AF27214621D386D0D64F085271F32E019B7CE9C0B6CC4C985832B3656885E90047FA594040246705232737BF08F66197E75792E7B8E1B4DE84B391298EFBD01AE9062AAE5DD242978594F4E2757B38F07A82AC4967EABC0D570EFB6C61309769A11D892D2A1D528C707C35430B5E6E1A4A2B5C05A535E502073771E46DD494AC71560D46790A754F691A1D2C42C2D515D8E02C01DEC7C2ADD791499E4412539BBC5FF69C78BD6D8D691AC905CAF5274DAF17F96864ED6C5CC3D906B60B91DB5AB78540C6DB91BC10996A3493EA46B9523AE8768913465F35D7425FEA08469C096D47E1393420D8B510EA034122F408F6096A495F10CEDC9F0B15190DE89894DBAA931514B32F4774EDC0C36DDD558BB291086D3C811C3DED9A664F5FB10D7E4CF4D245CA6A7F4E55076BB585E9CDF2643AAD8D91353178065333C8BA1D7C 
C8890B1E262564BC9D890CCEDA9D423BDB4B3C20D92142E3A23988C4C49D6315BE99D58DF6C12EC63B1E47ACB1E3B096458B36BC7469D363ED348AEEF18DA66D1A6305FD5EB5F4B2BA823A139493617B6BFA38DAC4B7B702EA4EFF9D7A128140A901A480C44FCFDF6B00E5AB1AF7691D3DD14552033369F92794B49D3FAFFD185444C9116980CAA8CB929FB92C89BBFE4DDDAB5377FAEFE8832198BAEBBB8BFCF4FC87E6BF13EF521F797E825E56D06F8EE5CD1F101BB93AD67FD5952F45ED23EC467C8C5B602A7E637EB49F6C43FEE5F9A7FF4231F63F502ADE03F40FC7027D8ED35B62B05993C62DE7F7FE778487B72FB4A443142ADE362DA1979
|| 'AF57753D9A1B97134965B811FA89EA6F797A70DC36AF4DAEB5A35C76717668C4528146049AB6A8C7F34B913C494920C513745A1250555C16575476AC88F62E1B472DC705D1B366AEB44A17140375528A7E21C8811ED9EE76B1A0A0F432AC15C7662EF4A2CDAB59850885B61926E2EC103275187136BD1D05E7B2B4AA31A8644A13C6B4B80414C2282D7D9290F54024C98DC573A1E1AE1379B3B062B41621EF26279A8DAE350237CC3863B6C242484FB2B537CFDB6D5E90B63DA383C4D875332B38171A7A10765227ACD9CD741E82095D5C7A5056FBF21E97876D1C6455E980229AA57FF9E52D913F87FA3BD2A7E9F8983E25EBFA37DA9DFD352545F8BA38062185FB0B7F715CCE03EE91107F248FC1E8310AA8E59C08B94F4BEC8B55E8875DE8FF01DEBF16E0213886FA31B598C34414CD71FC48803D65197FEC29FED2275E011E7A0778CBCF008FA6FF1FE0FD26C0BB6A138DAB1C00781C8F6A9677077813007B937A02006F7A077892F847004FB752EEF700DE9F8AD1F7A0EEAA9E5400EAE449E3ECFEFE04A00EF8A323AA598F1BFA0DD4DD04D6CD183D2CA93C727BD8469D5B541627CF5E23E18DEE6581B905A881F81888298A039DF4F83556F2F7B12A94BD468498018A46A338A0E9E0BBFDAFF103A014E417239F68EDB31E50640B34BD05406349DFF96CB2B9830E7A2A1A2BCC1BCA19B914AE04538C0EDB704D97414112AD3D93271B11B6B53DCB9AA0AFB0CA2F2F2EC5CB51895C8C72826B2BCDFB6A9D3872C6260735F2A6C63E9666D477DC851F18AAB1AD6197ECC34524B8AEBF71389247F25D295FD64A1320B942AF2FC586A93BA8B69AC3DED2A3D6F376887B15751A763469672FD637E856ADC3FC608EB39DE379A60341501E37BC88E1366FAFC9E3750B314A7C5EFBD0DE17115A11F694178D349CC5ECF6D69FE865B6A996CB720D21188F1D0DE448C5A76372D9A3F4D5CA0F4B5D2866B90C1DC25AF07D714F85A6DC5484C46F91E30655FD1A5740 
8D4C9787C1E38B92DF3A317A25066C0B5D8886E297DDB20D776C3F9C79E30A93261E178E2A783A58CF3ACDD4B8E481BC74553A7F0547A3CEAA343DF200FC1C644FA60F01AE273CF30AF4CCF12D57236E041333CAFC92A389EF41220C90D2E1EC962087C4A204F39E462C33447B39F12AE77E0161E970EA1839DF5B28D181BC2D6431423CF70AD66738A80CFCAA3FD15D86D16562DF52E6A1F6F649AA42482A742756F2A94D0A5BF4F1BE162453E5458E7613C690A2C544907029D2D96811FA56A742DCA17D569B59F4F09035787E3D81FAD0D4C8CFC0D4339129DE6BB0E722E04FAB0390C3BAA49D7CC9290FAF6BFC4B9E76AF799AF07411E858423431
|| '1296524A95B30512E8D31401A035BAF416DEA955554BF931611BBE83855AB92C006A2CCF2A7A8910D1D9EBAA762BF38578643165969E97EA612262DD2C9946DD35F6D67108D571AD7399D22C43E822BB714B73333124E65BA46290CBDD007B6034D5912653EF826F479F231BADEFE87D6EC86931F52A7968A9BD5F2EDDE406CDAA7356631ABBC4B261E02A112D446726DD76FEC90AB8C1C96F75D22A99917B9D6CA75C78DDEB3E12F5F4A15617D69097E7F5C46D368175F6B6C8D29C9DF3CDD85A3B9C594B3726EC4D5D8B7655C6AA501BC1FEE1741E162AA9D519452F6045BC0634BE0B46EBB8D69566832D90903D9A4EBCC9055A03705B50E2CEC6C3C398E81426C0E22910AEBB6118F6F4B9CF488E3C9F6854CFFB53A597CB6237D3495A9107175D76A2EFE2B0A4B5E1BAE78D03C56D8D33D7AB93B7F559A123B03DD1D142CC5C4C4D2094252148D065E077E522A4BD53CDF8FAB2DCA00903576B6970315ADAB3B242F90B36AE6B48B3ADCBC01C43B6B84C01B6CE4586DA04ED45C73BEC34CB20AFD37851F6EB4E3C5C0F95AAAF03C8E2A3824F52AA982DD84DBC653563236F30EAEA3160BF5E10711C2E84931E94EB8B9626F8410DE9B497BC5925C0F1946FE1EBB9A0088A6AA65038F965CD86FAB2D671FE8829C7642267E70BC3EEE0DBA2C8057FDD9C38B0F4C6DE5FAF8753B2CF7FF94760D0CC92CAEF87F6ED77853F876FFEFEB747BF387A54FD7FFFDBD3D38AF5ABBACA42BFC826FF2F614204BE2B9EC7D7701E2278F5D3BD647C68FCF0EC2FC2CBBB8FF3B26EE307C804400F25C80FA55F6B56399A93A0F02197C7C5DC47087F8E930836F79748F451172D43187D7EF406FDADD6AFBA63DD96DD1BE533ED9F182D5003FDA07BF51D1EFE2BF0FAFB517F687AC5058293480E653259215B476D7009A5B1D2DD7ACB1C9C2E212EA89B36F266E19AB84983D4F9DCF31142E857F8F77B213DC268102FFD684E447838C729149E071845FC0A5F310AFB3F1552 
1A4BD79E25D934538CB308BF0DE48D72182C1ECEF46E74111A25AFA37056D55BC2FF17430A2F5180F9490AC0003F06D53B41CC0302453E428AFAF7EAFDFF5048FB5B1634E78BA36C765DDA58D46EAD13071366468354D9641B87EA5AA0A301A202F80F43BA82BE6C579FB797D74E0AA94FC17854BC516448386E511AC77A46048508EA315A743C2DDB137C9E9ACDFAB25B300689A0C782140FC6B535D9BC59A7E8053FC26087E73209A767462D87D4619F1CAA03DA7AC3F1BCEF9DE1C8D4A8E22DD53CDF5FB03358820C5B21647FDAC39C5EDC5418768472B188A580E627E7D0127D2DB8E742D82E850D7298A192740E9CED62815F07ABCFA6C4242A
|| 'AF65D0D19A06CDA0BDCA8E54B887DD4E46AE17A76516812D0308C15F1C9331D341B1713C31AED6FEAA1C0FCE7E661260C89A0FCF64702EE195AE6C288BB36DD971ECC43F1F38189B480C5E6CAFF9255675D769633C3FDB81C3BB0D069384F1B897F812E8D5C765D13FB8587A3F8C9EFFD46DD99F42B6FF2B6ECB3EC6FE6B54BEF6795965C7E8E7F7DFE3FFD5571FBFFF7BFBC72DC29F3E221F8EBF7DD1F0220BDC7F20DFF015F499F8DA7A1FDB6B07CB6F93B8FFDF31CE3F776E3DDCCEAAAECFFAA18FDF9AD18B58C77D9F55495CBD0E13901EACB0C8E2AA7FC1500AC3106CF9CA7C23BE497EAF0BA4D625BB7F1B210B3B8AA240F545C2AF821F9CD7E66BE840E3EB3715EF6DA68E6EFFEAC87E0ADF9F3BA356415D01CFAAACF7E3BEF3872E1FAAE37B0204F78F61DEDC04439C77611A97FEFD0B951F7A98AC640B34D06786E9707CBF8D9C5E4094117CB1801104ACB2EF592B2BCE8A2A4ECBB86DDF69080CE47E830E0E613FEF1F59FB2B172CE76AA892179CC41104C6A9CF3DDF792B276E83D61F80E76DE4F78F22FBE1EA5CE65E58D55EEC15527644E0DC0775E5D46DE597A08ED02C69AB1D56D03B61A5F961FA78130DDA362D5ED656D0076D25C62040451CA6FD8BBA823EB5002718DABE030E0CE50B0C660BF98650D4E22EF489F12E56B7FD8BC6DBAACD6BC086F12175A7AFE8BC1FE2A288E9A88D412280E1F4AD7F7F717943E30D53A2451E3C9F5C5E7C5A92605C6FECD54EF15E509CC080D5FBEB6AFBD5CA9DB052C0427CE1787B053DDE56D00FF64030FCAFB6199E07A172EE9A8C6F4F38F61B66F1BF66F6B315E8C73954C10C977ED1DDAFAFE35F9B7C11978F95FC46F87809EB084C9ABC823EB757CC7D8ADA380BEE9922666D9DD7555FAFA0EFE81FCAFDBE6F01E96E0F1C5C2FA93F347DF9CA039907281FDA3F8B421FAE403F78097D19861902A09455F77AEE01DB1ED9FA85F6C5EB07FBABC7DC5BA2DF796F8F3106614671FCBE0ADF9B2B23EBB2 
BC3E0F35308EE0FFB604A9F199F4DA33002B300BD317E9ADEB7BFB95DBC7D7FE7ED47EB87927BC3030F24451C8F28981D1A719300B5EB02709FCC79F28FC41279E8437851FBDBE28B1B42714465198422964815004896128B65C2CC825FC434FE86DACD0DBD0A11FC3F603297EC4B26F87A46B5A3FBE80297908F2601F0F2A3023604A3760893F59062F08F754FDCCB9C7FCC7BED0AFFB14F


















, 'hex'));

My questions are the following

1. Will psql be able to run a script like this ? I mean, my DBA says it does not work, but I would like a better opinion. Is there any set parameter to allow blank lines , like set sqlblanklines on in sqlplus.
2. If that is not possible, then should I treat the file to remove all blank lines between the last hexadecimal expression and the final part of the decode ?
3. Is there any buffer line limit in psql as in sqlplus ?

BTW, the DBA is supposed to run the script using -f or in interactive mode \i

Thank you in advance