migrate oracle to Postgresql UTL_FILE EXCEPTIONS

Question

I have the same situation as described in this post: Porting Oracle procedure to PostgreSQL (exception codes from orafce for utl_file module)

I'm migrating from oracle to postgres with ora2pg and I`m trying to correct the syntax of the exception handling that I have for 'utl_file'. Someone commented a solution in the other post but I didn't understand how to properly apply it.

I have the next code :

   V_Step := 1;

    FOR Rec_Report IN C_Report LOOP

      V_Counter := V_Counter + 1;

      -- Header
      IF V_Counter = 1 THEN

         -- Open File
        V_File_Type := UTL_FILE.FOPEN(V_Dir_Name, V_File_Name,'w');

        V_Step := 2;

        bl_create_flat_file_pg.open_html(V_File_Type);
         .......
         .......
         .......

          V_Step :=10

EXCEPTION
when UTL_FILE.INVALID_PATH then
PERFORM control_reports_pg.send_error_mail('invalid_path 
V_Step'||V_Step,C_Function_Name);
PERFORM UTL_FILE.FCLOSE(V_File_Type);

when UTL_FILE.INVALID_MODE then
PERFORM control_reports_pg.send_error_mail('INVALID_MODE  
 V_Step'||V_Step,C_Function_Name);
     PERFORM  UTL_FILE.FCLOSE(V_File_Type);

Therefore for every step I have a piece of code like this that handles the exception. I get a syntax error : ERROR: syntax error at or near "UTL_FILE".

Please help...

Do you have orafce plugin ---> github.com/orafce/orafce, this emulates Oracle-s internal pacakges such as UTL_FILE without that you cannot use UTL_FILE in POSTGRES as it doesn't exist on its own. — ivanzg
– ivanzg, Commented Jul 5, 2017 at 20:38
Yes I have orafce plugin. I`m trying to solve the exception handles and not the utl_files. Any idea ? — JeyJ
– JeyJ, Commented Jul 6, 2017 at 6:00
If you replace the oracle tag with the orafce tag, then maybe one of the orafce maintainers might jump in if they're watching. — Jeff Holt
– Jeff Holt, Commented Jul 6, 2017 at 6:05

Jeff Holt · Accepted Answer · 2017-07-06 06:08:12Z

0

I've been writing PL/SQL for quite some time and I've never come across the PERFORM imperative. If this code is to run on an Oracle instance, then I would simply the remove the two "PERFORM" strings.

If this is supposed to run on a POSTGRES instance, then I'd not expect UTL_FILE to exist. If it does exist, then maybe the user running the code has not been granted permissions to execute UTL_FILE.

edited Jul 6, 2017 at 6:08

answered Jul 5, 2017 at 18:18

Jeff Holt

3,2623 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JeyJ Over a year ago

I have orafce plugin. I`m trying to solve the exception handles and not the utl_files. Any idea ?

Collectives™ on Stack Overflow

migrate oracle to Postgresql UTL_FILE EXCEPTIONS

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related