0

I wish to redeploy some packages. Here the simplified code:

vCode CLOB;
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY', 'MY_PACKAGE', USER) INTO vCode FROM DUAL t;
  
EXECUTE IMMEDIATE CONVERT(vCode, 'WE8MSWIN1252', 'US8PC437');

Would it be possible to check if the code would be compiled without errors? And if so, run the EXECUTE IMMEDIATE command? Many thanks!

hajduk
  • 103
  • 8
  • 2
    What are you trying to accomplish here (i.e. why would you want to redeploy code from the same database)? Perhaps you just want to `alter package my_package compile body` to recompile the package body? – Justin Cave Feb 09 '22 at 07:57
  • I wish to redeploy with converted encoding. Here the line convert CONVERT(vCode, 'WE8MSWIN1252', 'US8PC437'); – hajduk Feb 09 '22 at 08:03
  • 1
    Related to https://stackoverflow.com/questions/71016315/special-characters-german-umlauts-are-displayed-as-ascii – Wernfried Domscheit Feb 09 '22 at 08:17
  • 3
    Try it out. In case of an error it will raise exception `ORA-24344: success with compilation error`. Bear in mind, using this is a high risk. You should really use spooled SQL files, because the PL/SQL package will be overwritten even with errors, i.e. your original source code will be lost! Obviously you don't saved the source code somewhere else. – Wernfried Domscheit Feb 09 '22 at 08:22
  • 2
    If this is a production system, shouldn't you be doing this conversion on the source code stored in Git or whatever source control system you use? – William Robertson Feb 09 '22 at 08:57
  • There is nothing like control system now. We are on the way to get it. – hajduk Feb 09 '22 at 09:14
  • 2
    If you don't have the source code saved off in a version control system already, I would be **extremely** hesitant to try to do this sort of in-place conversion. I would strongly suggest that you extract whatever code you have to the file system first and then re-run the SQL with the proper `SQL*Plus` settings. That way if something doesn't go as expected, you at least have the source sitting on the file system to try again. – Justin Cave Feb 09 '22 at 09:47
  • 1
    *"There is nothing like control system now. We are on the way to get it."* Putting your code under source control ought to be your **number one** priority. – APC Feb 09 '22 at 15:07
  • Ok. Thanks all. Will put the code under version control. – hajduk Feb 10 '22 at 06:40

0 Answers0