oracle - Extracting output from PLSQL procedure to local drive of my laptop -
i have database connection server "server_dev" in sqldeveloper .
now want create procedure output can directly saved in csv file data comparison later in local drive of laptop.
so tried using utl_file oracle package when ran procedure utl_file trying write in file of server "server_dev" whereas dont have access server hence command isnt working. example: code is:-
create or replace procedure export_to_csv_test v_file utl_file.file_type; v_string varchar2 (4000); cursor c_contexts select workspace_id,context_id contexts rownum<5; begin v_file := utl_file.fopen ('z:\my_project_knowledge\csvdir', 'empdata.csv','w',1000); cur in c_contexts `enter code here`loop v_string := cur.workspace_id || ',' || cur.context_id; utl_file.put_line (v_file, v_string); end loop; utl_file.fclose (v_file); end; calling :- begin export_to_csv_test; end; error report: ora-29280: invalid directory path ora-06512: @ "sys.utl_file", line 41 ora-06512: @ "sys.utl_file", line 478 ora-06512: @ "ray_dev07_owner.export_to_csv_test", line 20 ora-06512: @ line 3 29280. 00000 - "invalid directory path" *cause: corresponding directory object not exist. *action: correct directory object parameter, or create corresponding directory object create directory command.
so,i analysed , found sql developer connected server local machin , since office laptop cant alter it.
can have other way in i can save output of stored procedure local drive in text or csv file?
to write file local machine may use dbms_output; example in sqlplus:
sql> set feedback off sql> set echo off sql> set serveroutput on sql> spool d:\spool.txt sql> begin 2 in (select level dual connect level <= 5) loop 3 dbms_output.put_line('level ' || i.level); 4 end loop; 5 end; 6 /
will produce file d:\spool.txt:
level 1 level 2 level 3 level 4 level 5
Comments
Post a Comment