yesf
yesf

Reputation: 13

PL/SQL exporting csv file

I've installed oracle server through colima on mac m1.There was no problem about developing procedure,function etc.Now i want to export some data as a csv file but it gives some error like:

ORA-29283: invalid file operation[29434] 'SYS.UTIL_FILE' line 536.

I tried some solutions like;

create or replace directory MYDIR as 'export_files/';

CREATE OR REPLACE PROCEDURE export_to_csv(refcur out sys_refcursor) IS
      v_file   UTL_FILE.file_type;
      v_string VARCHAR2(4000);
      CURSOR c_emp IS
        SELECT p.PRODUCT_TITLE,p.PRODUCT_PRICE FROM e_commerce_products p;
    BEGIN
      open refcur for
        SELECT p.PRODUCT_TITLE,p.PRODUCT_PRICE FROM e_commerce_products p;
      v_file := UTL_FILE.fopen('MYDIR', 'empdata.csv', 'w', 1000);
      -- if you do not want heading then remove below two lines
      v_string := 'product title, product price';
      UTL_FILE.put_line(v_file, v_string);
      FOR cur IN c_emp LOOP
        v_string := cur.PRODUCT_TITLE || ',' || cur.PRODUCT_PRICE;
        UTL_FILE.put_line(v_file, v_string);
      END LOOP;
      UTL_FILE.fclose(v_file);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(sqlerrm);
        IF UTL_FILE.is_open(v_file) THEN
          UTL_FILE.fclose(v_file);
        END IF;
    END;

create or replace directory MYDIR as 'export_files/'; here i've tried some others path but i guess it wont work because I'm working on colima/docker.

What should i do? Can anyone help?

My oracle version 23c also I'm using DataGrip as an IDE.

Upvotes: 1

Views: 122

Answers (0)

Related Questions