Home » SQL & PL/SQL » SQL & PL/SQL » Writing from Ref cursor to UTIL file (Oracle 11g)
Writing from Ref cursor to UTIL file [message #678998] Mon, 27 January 2020 03:33 Go to next message
Samhita_Varma
Messages: 1
Registered: January 2020
Junior Member
create or replace procedure p_get_company_events_temp
(
i_country_code in varchar2
, i_company_type in varchar2
, i_company_id_list in varchar2
, o_cursor out SYS_REFCURSOR
)
AS
v_query varchar2(32000);
v_company_id_list varchar2(32000);
v_company_id_count number := 0;
v_common_event_code_exclusion varchar2(100) := 'XX, DBT, TN, CS';
v_file UTL_FILE.FILE_TYPE; --- added by samhita
v_string varchar2(4000);
begin
-- quote values on the list so they are passed in as strings
v_company_id_list := replace(i_company_id_list, ',', ,);

v_company_id_count := length(i_company_id_list) - length(replace(i_company_id_list, ','));

if v_company_id_count > 999 then
raise_application_error(-20000, 'More than 1000 company_ids provided on input.');
end if;

-- this version is meant only for company_type x and 0. different company details link required for company_type=1
if i_country_code = 'DE' and i_company_type = '1' then
v_query :='select NC.cs_company_id as org_number' || chr(10)
||', UPPER(||i_company_type||) as company_type' || chr(10)
||', nc.company_name as name' || CHR(10)
||', nc.street_housenumber as address1' || chr(10)
||', null as address2' || chr(10)
||', null as address3' || chr(10)
||', nc.city as town' || chr(10)
||', null as address5' || chr(10)
||', nc.zip as zip' || chr(10)
||', nc.org_number as reg_number' || chr(10)
||', null as type' || chr(10)
||', to_char(nc.status) as status' || chr(10)
||', ||i_country_code|| as country_code' || chr(10)
||', DECODE(ge.event_type, DX, DN, ge.event_type) as event_type' || chr(10)
||', ge.event_date' || CHR(10)
||', ge.old_value' || chr(10)
||', ge.new_value' || CHR(10)
||', ge.event_id' || chr(10)
||'from (' || chr(10)
||' select to_char(nc.org_number) as org_number' || chr(10)
||' , nc.cs_company_id' || chr(10)
||' , nc.company_name' || chr(10)
||' , nc.status' || CHR(10)
||' , nc.street as street_housenumber' || chr(10)
||' , nc.postalcode as zip' || chr(10)
||' , nc.city' || chr(10)
||' from cs.v_ggs_nreg_monitoring@csde1 nc' || CHR(10)
||' where nc.cs_company_id in (||v_company_id_list||)' || CHR(10) -- list of varchar2 is used to use correct PK
||' ) nc ' || CHR(10)
||' left join (' || CHR(10)
||' select * ' || chr(10)
||' from global_events ' || chr(10)
||' where country_code = ||i_country_code||' || chr(10)
||' and company_type = ||i_company_type||' || chr(10)
||' and event_status = 1' || chr(10)
||' and event_type not in ('||v_common_event_code_exclusion||')' || chr(10)
||' ) ge on nc.cs_company_id = ge.org_number' || chr(10);
--DBMS_OUTPUT.PUT_LINE(v_query);
open o_cursor for v_query;
END IF;
v_file := UTL_FILE.FOPEN('EVENTS_IMPORT', 'test.txt', 'W');
FOR i IN o_cursor
LOOP
v_string := o_cursor;
UTL_FILE.PUTF(v_file, v_string);
END LOOP;
UTL_FILE.FCLOSE(v_file);
END p_get_company_events_temp;



Error :

PLS-00221 : o_cursor is not a procedure or undefined
Re: Writing from Ref cursor to UTIL file [message #678999 is a reply to message #678998] Mon, 27 January 2020 05:01 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
The problem will be this line:
v_string := o_cursor;

You can't assign cursors to strings.
Cursors are pointers to queries.
To get the results of the query you need to fetch the cursor into a variable or set of variables and then interrogate those.

You're using a for loop, so that does the fetch for you.
You need to use i.
Re: Writing from Ref cursor to UTIL file [message #679001 is a reply to message #678998] Mon, 27 January 2020 09:56 Go to previous message
Michel Cadot
Messages: 67288
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 80 characters.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Previous Topic: Design Question
Next Topic: How to download a BLOB field data to local storage (merged)
Goto Forum:
  


Current Time: Thu Aug 06 16:18:35 CDT 2020