[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Fetch syntax issue

I am testing an sp and I simply wish to loop throughthe cursor and print the record return to screen - incorrectly as it is not working.  can anyone take a look and tell me what I have done wrong:
OPEN EXPORTERREGULATEEINFO;

FETCH EXPORTERREGULATEEINFO INTO recExporterRegInfo;

WHILE EXPORTERREGULATEEINFO%found Loop

  DBMS_OUTPUT.PUT_LINE('EXPORTERREGULATEEINFO:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || recExporterRegInfo.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('RegistrationNumber = ' || recExporterRegInfo.RegistrationNumber);
  DBMS_OUTPUT.PUT_LINE('CompanyName = ' || recExporterRegInfo.CompanyName);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || recExporterRegInfo.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || recExporterRegInfo.LastName);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
FETCH EXPORTERREGULATEEINFO INTO recExporterRegInfo;

EXIT WHEN EXPORTERREGULATEEINFO%notfound;
END LOOP;
0
Mosquitoe
Asked:
Mosquitoe
  • 5
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
Need more of the code.

How is recExporterRegInfo declared?

The following works for me.
drop table tab1 purge;
create table tab1(col1 char(1));
insert into tab1 values('a');
commit;

declare
	myrow tab1%rowtype;
	myCur sys_refcursor;
begin
	open myCur for 'select * from tab1';
	fetch myCur into myrow;
	dbms_output.put_line('Got: ' || myrow.col1);
	close mycur;
end;
/

Open in new window

0
 
MosquitoeAuthor Commented:
I had it working when I just got the first record:

FETCH EXPORTERREGULATEEINFO INTO recExporterRegInfo;

DBMS_OUTPUT.PUT_LINE('EXPORTERREGULATEEINFO:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || recExporterRegInfo.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('RegistrationNumber = ' || recExporterRegInfo.RegistrationNumber);
  DBMS_OUTPUT.PUT_LINE('CompanyName = ' || recExporterRegInfo.CompanyName);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || recExporterRegInfo.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || recExporterRegInfo.LastName);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');

But I want to loop through the entire cursor and print to screen everything - when i added that code - it threw errors.
0
 
slightwv (䄆 Netminder) Commented:
Error messages would help.

The logic works for me using the test below.
drop table tab1 purge;
create table tab1(col1 char(1));
insert into tab1 values('a');
insert into tab1 values('b');
commit;

declare
	myrow tab1%rowtype;
	myCur sys_refcursor;
begin
	open myCur for 'select * from tab1';

	fetch myCur into myrow;
	while mycur%found loop
	dbms_output.put_line('Got: ' || myrow.col1);

	fetch myCur into myrow;
	exit when mycur%notfound;
	end loop;
	close mycur;
end;
/

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MosquitoeAuthor Commented:
An error was encountered performing requested operation:
ORA-06550: line 119, column 1:
PLS-00382:expression is of wrong type
OA-06550: line 119, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: at line 58

Vendor code 6550
0
 
MosquitoeAuthor Commented:
This is everything:

DECLARE
  P_DOSSIERID NUMBER;
  P_DOSSIERTYPE VARCHAR2(200);
  P_DOSSIERYEAR NUMBER;
  P_NOTICEOFFICERUSERID NUMBER;

  EXPORTERREGULATEEINFO EIHW.PK_NOTICEDETERMINATION.cur_RegulateeInfo;
  EXPORTERREGULATEEADDRESS EIHW.PK_NOTICEDETERMINATION.cur_RegulateeAddress;
  EXPORTERREGULATEECONTACT EIHW.PK_NOTICEDETERMINATION.cur_RegulateeContact;
 
  IMPORTERREGULATEEINFO EIHW.PK_NOTICEDETERMINATION.cur_RegulateeInfo;
  IMPORTERREGULATEEADDRESS EIHW.PK_NOTICEDETERMINATION.cur_RegulateeAddress;
  IMPORTERREGULATEECONTACT EIHW.PK_NOTICEDETERMINATION.cur_RegulateeOptionalContact;
 
  TRANSPORTERREGULATEEINFO EIHW.PK_NOTICEDETERMINATION.cur_RegulateeInfo;
  TRANSPORTERREGULATEEADDRESS EIHW.PK_NOTICEDETERMINATION.cur_RegulateeOptionalAddress;
  TRANSPORTERREGULATEECONTACT EIHW.PK_NOTICEDETERMINATION.cur_RegulateeOptionalContact;
 
  WASTEGENERATORREGULATEEINFO EIHW.PK_NOTICEDETERMINATION.cur_RegulateeInfo;
  WASTEGENERATORREGULATEEADDRESS EIHW.PK_NOTICEDETERMINATION.cur_RegulateeAddress;
  WASTEGENERATORREGULATEECONTACT EIHW.PK_NOTICEDETERMINATION.cur_RegulateeContact;
 
  FACILITYREGULATEEINFO EIHW.PK_NOTICEDETERMINATION.cur_RegulateeInfo;
  FACILITYREGULATEEADDRESS EIHW.PK_NOTICEDETERMINATION.cur_SpecifiedRegulateeAddress;
  FACILITYREGULATEECONTACT EIHW.PK_NOTICEDETERMINATION.cur_RegulateeOptionalContact;
 
  FACILITYDETAILS EIHW.PK_NOTICEDETERMINATION.cur_FacilityDetails;
 
  NOTICEDETAILS EIHW.PK_NOTICEDETERMINATION.cur_NoticeDetails;
 
  WASTESTREAMDETAILS EIHW.PK_NOTICEDETERMINATION.cur_WasteStreamDetails;
 
  TRANSITCOUNTRY EIHW.PK_NOTICEDETERMINATION.cur_TransitCountry;
 
  NOTICESUBMITTER EIHW.PK_NOTICEDETERMINATION.cur_NoticeSubmitter;
 
  NOTICEOFFICERDETAILS EIHW.PK_NOTICEDETERMINATION.cur_NoticeOfficerDetails;

  --Exporter
  recExporterRegInfo EIHW.PK_NOTICEDETERMINATION.rec_RegulateeInfo;
  rec_EXPRegulateeAddress EIHW.PK_NOTICEDETERMINATION.rec_RegulateeAddress;
  rec_EXPRegulateeContact EIHW.PK_NOTICEDETERMINATION.rec_RegulateeContact;
 
  --Importer
  rec_ImporterRegInfo EIHW.PK_NOTICEDETERMINATION.rec_RegulateeInfo;
  rec_IMPRegulateeAddress EIHW.PK_NOTICEDETERMINATION.rec_RegulateeAddress;
  rec_ImporterRegulateeContact EIHW.PK_NOTICEDETERMINATION.rec_RegulateeOptionalContact;
 
  --Transporter
  rec_TransporterRegInfo EIHW.PK_NOTICEDETERMINATION.rec_RegulateeInfo;
  rec_TransRegulateeAddress EIHW.PK_NOTICEDETERMINATION.rec_RegulateeOptionalAddress;
  rec_TransRegulateeContact EIHW.PK_NOTICEDETERMINATION.rec_RegulateeOptionalContact;
 
  --Waste Generator
  rec_WasteRegInfo EIHW.PK_NOTICEDETERMINATION.rec_RegulateeInfo;
  rec_WasteRegAddress EIHW.PK_NOTICEDETERMINATION.rec_RegulateeAddress;
  rec_WasteRegContact EIHW.PK_NOTICEDETERMINATION.rec_RegulateeContact;
 
  --Facilities
  rec_FacilityRegInfo EIHW.PK_NOTICEDETERMINATION.rec_RegulateeInfo;
  rec_FacilityRegAddress EIHW.PK_NOTICEDETERMINATION.rec_SpecifiedRegulateeAddress;
  rec_FacilityRegContact EIHW.PK_NOTICEDETERMINATION.rec_RegulateeOptionalContact;
 
  --Facility Details
  rec_facilityDetails EIHW.PK_NOTICEDETERMINATION.rec_FacilityDetails;
 
  --NoticeDetails
  rec_NotiDetails EIHW.PK_NOTICEDETERMINATION.rec_NoticeDetails;
 
  --Waste Stream Details
  rec_Waste EIHW.PK_NOTICEDETERMINATION.rec_WasteStreamDetails;
 
  --TransitCountry
  rec_TransitCountryDetails EIHW.PK_NOTICEDETERMINATION.rec_TransitCountry;
 
  --NoticeSubmitter
  rec_Notice_Submitter EIHW.PK_NOTICEDETERMINATION.rec_NoticeSubmitter;
 
  --Notice Officer
  rec_Officer EIHW.PK_NOTICEDETERMINATION.rec_NoticeOfficerDetails;
 
 
BEGIN
  P_DOSSIERID := 325;
  P_DOSSIERTYPE := 'EXP';
  P_DOSSIERYEAR := 10;
  P_NOTICEOFFICERUSERID := 300;
      

  PK_NOTICEDETERMINATION.SP_NOTICE(
    P_DOSSIERID => P_DOSSIERID,
    P_DOSSIERTYPE => P_DOSSIERTYPE,
    P_DOSSIERYEAR => P_DOSSIERYEAR,
    P_NOTICEOFFICERUSERID => P_NOTICEOFFICERUSERID,
    EXPORTERREGULATEEINFO => EXPORTERREGULATEEINFO,
    EXPORTERREGULATEEADDRESS => EXPORTERREGULATEEADDRESS,
    EXPORTERREGULATEECONTACT => EXPORTERREGULATEECONTACT,
    IMPORTERREGULATEEINFO => IMPORTERREGULATEEINFO,
    IMPORTERREGULATEEADDRESS => IMPORTERREGULATEEADDRESS,
    IMPORTERREGULATEECONTACT => IMPORTERREGULATEECONTACT,
    TRANSPORTERREGULATEEINFO => TRANSPORTERREGULATEEINFO,
    TRANSPORTERREGULATEEADDRESS => TRANSPORTERREGULATEEADDRESS,
    TRANSPORTERREGULATEECONTACT => TRANSPORTERREGULATEECONTACT,
    WASTEGENERATORREGULATEEINFO => WASTEGENERATORREGULATEEINFO,
    WASTEGENERATORREGULATEEADDRESS => WASTEGENERATORREGULATEEADDRESS,
    WASTEGENERATORREGULATEECONTACT => WASTEGENERATORREGULATEECONTACT,
    FACILITYREGULATEEINFO => FACILITYREGULATEEINFO,
    FACILITYREGULATEEADDRESS => FACILITYREGULATEEADDRESS,
    FACILITYREGULATEECONTACT => FACILITYREGULATEECONTACT,
    FACILITYDETAILS => FACILITYDETAILS,
    NOTICEDETAILS => NOTICEDETAILS,
    WASTESTREAMDETAILS => WASTESTREAMDETAILS,
    TRANSITCOUNTRY => TRANSITCOUNTRY,
    NOTICESUBMITTER => NOTICESUBMITTER,
    NOTICEOFFICERDETAILS => NOTICEOFFICERDETAILS
  );

  -- Modify the code to output the variable -- line 80 below
OPEN EXPORTERREGULATEEINFO;

FETCH EXPORTERREGULATEEINFO INTO recExporterRegInfo;

WHILE EXPORTERREGULATEEINFO%found Loop

  DBMS_OUTPUT.PUT_LINE('EXPORTERREGULATEEINFO:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || recExporterRegInfo.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('RegistrationNumber = ' || recExporterRegInfo.RegistrationNumber);
  DBMS_OUTPUT.PUT_LINE('CompanyName = ' || recExporterRegInfo.CompanyName);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || recExporterRegInfo.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || recExporterRegInfo.LastName);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
FETCH EXPORTERREGULATEEINFO INTO recExporterRegInfo;

EXIT WHEN EXPORTERREGULATEEINFO%notfound;

END LOOP;
CLOSE EXPORTERREGULATEEINFO;
-- Modify the code to output the variable -- line 92 below
FETCH EXPORTERREGULATEEADDRESS INTO rec_EXPRegulateeAddress;

  DBMS_OUTPUT.PUT_LINE('EXPORTERREGULATEEADDRESS:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_EXPRegulateeAddress.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('AddressText = ' || rec_EXPRegulateeAddress.AddressText);
  DBMS_OUTPUT.PUT_LINE('CityText = ' || rec_EXPRegulateeAddress.CityText);
  DBMS_OUTPUT.PUT_LINE('PostalCode = ' || rec_EXPRegulateeAddress.PostalCode);
  DBMS_OUTPUT.PUT_LINE('StateProvinceCode = ' || rec_EXPRegulateeAddress.StateProvinceCode);
  DBMS_OUTPUT.PUT_LINE('CountryCode = ' || rec_EXPRegulateeAddress.CountryCode);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
  -- Modify the code to output the variable -- line 105 below
FETCH EXPORTERREGULATEECONTACT INTO rec_EXPRegulateeContact;

  DBMS_OUTPUT.PUT_LINE('EXPORTERREGULATEECONTACT:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_EXPRegulateeContact.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || rec_EXPRegulateeContact.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || rec_EXPRegulateeContact.LastName);
  DBMS_OUTPUT.PUT_LINE('PhoneNumberText = ' || rec_EXPRegulateeContact.PhoneNumberText);
  DBMS_OUTPUT.PUT_LINE('FaxText = ' || rec_EXPRegulateeContact.FaxText);
  DBMS_OUTPUT.PUT_LINE('EmailText = ' || rec_EXPRegulateeContact.EmailText);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
  -- Modify the code to output the variable -- line 105 below
FETCH IMPORTERREGULATEEINFO INTO rec_ImporterRegInfo;

  DBMS_OUTPUT.PUT_LINE('IMPORTERREGULATEEINFO:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_ImporterRegInfo.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('RegistrationNumber = ' || rec_ImporterRegInfo.RegistrationNumber);
  DBMS_OUTPUT.PUT_LINE('CompanyName = ' || rec_ImporterRegInfo.CompanyName);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || rec_ImporterRegInfo.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || rec_ImporterRegInfo.LastName);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
  -- Modify the code to output the variable -- line 92 below
FETCH IMPORTERREGULATEEADDRESS INTO rec_IMPRegulateeAddress;

  DBMS_OUTPUT.PUT_LINE('IMPORTERREGULATEEADDRESS:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_IMPRegulateeAddress.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('AddressText = ' || rec_IMPRegulateeAddress.AddressText);
  DBMS_OUTPUT.PUT_LINE('CityText = ' || rec_IMPRegulateeAddress.CityText);
  DBMS_OUTPUT.PUT_LINE('PostalCode = ' || rec_IMPRegulateeAddress.PostalCode);
  DBMS_OUTPUT.PUT_LINE('StateProvinceCode = ' || rec_IMPRegulateeAddress.StateProvinceCode);
  DBMS_OUTPUT.PUT_LINE('CountryCode = ' || rec_IMPRegulateeAddress.CountryCode);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
  -- Modify the code to output the variable -- line 155
FETCH IMPORTERREGULATEECONTACT INTO rec_ImporterRegulateeContact;

  DBMS_OUTPUT.PUT_LINE('IMPORTERREGULATEECONTACT:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_ImporterRegulateeContact.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || rec_ImporterRegulateeContact.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || rec_ImporterRegulateeContact.LastName);
  DBMS_OUTPUT.PUT_LINE('PhoneNumberText = ' || rec_ImporterRegulateeContact.PhoneNumberText);
  DBMS_OUTPUT.PUT_LINE('FaxText = ' || rec_ImporterRegulateeContact.FaxText);
  DBMS_OUTPUT.PUT_LINE('EmailText = ' || rec_ImporterRegulateeContact.EmailText);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
  -- Modify the code to output the variable -- line 80 below
FETCH TRANSPORTERREGULATEEINFO INTO rec_TransporterRegInfo;

  DBMS_OUTPUT.PUT_LINE('TRANSPORTERREGULATEEINFO:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_TransporterRegInfo.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('RegistrationNumber = ' || rec_TransporterRegInfo.RegistrationNumber);
  DBMS_OUTPUT.PUT_LINE('CompanyName = ' || rec_TransporterRegInfo.CompanyName);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || rec_TransporterRegInfo.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || rec_TransporterRegInfo.LastName);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');

-- Modify the code to output the variable -- line 92 below
FETCH TRANSPORTERREGULATEEADDRESS INTO rec_TransRegulateeAddress;

  DBMS_OUTPUT.PUT_LINE('TRANSPORTERREGULATEEADDRESS:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_TransRegulateeAddress.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('AddressText = ' || rec_TransRegulateeAddress.AddressText);
  DBMS_OUTPUT.PUT_LINE('CityText = ' || rec_TransRegulateeAddress.CityText);
  DBMS_OUTPUT.PUT_LINE('PostalCode = ' || rec_TransRegulateeAddress.PostalCode);
  DBMS_OUTPUT.PUT_LINE('StateProvinceCode = ' || rec_TransRegulateeAddress.StateProvinceCode);
  DBMS_OUTPUT.PUT_LINE('CountryCode = ' || rec_TransRegulateeAddress.CountryCode);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
   -- Modify the code to output the variable -- line 192 below
FETCH TRANSPORTERREGULATEECONTACT INTO rec_TransRegulateeContact;

  DBMS_OUTPUT.PUT_LINE('TRANSPORTERREGULATEECONTACT:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_TransRegulateeContact.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || rec_TransRegulateeContact.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || rec_TransRegulateeContact.LastName);
  DBMS_OUTPUT.PUT_LINE('PhoneNumberText = ' || rec_TransRegulateeContact.PhoneNumberText);
  DBMS_OUTPUT.PUT_LINE('FaxText = ' || rec_TransRegulateeContact.FaxText);
  DBMS_OUTPUT.PUT_LINE('EmailText = ' || rec_TransRegulateeContact.EmailText);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
  -- Modify the code to output the variable -- line 80 below
FETCH WASTEGENERATORREGULATEEINFO INTO rec_WasteRegInfo;

  DBMS_OUTPUT.PUT_LINE('WASTEREGULATEEINFO:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_WasteRegInfo.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('RegistrationNumber = ' || rec_WasteRegInfo.RegistrationNumber);
  DBMS_OUTPUT.PUT_LINE('CompanyName = ' || rec_WasteRegInfo.CompanyName);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || rec_WasteRegInfo.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || rec_WasteRegInfo.LastName);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
  -- Modify the code to output the variable -- line 92 below
FETCH WASTEGENERATORREGULATEEADDRESS INTO rec_WasteRegAddress;

  DBMS_OUTPUT.PUT_LINE('WASTEREGULATEEADDRESS:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_WasteRegAddress.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('AddressText = ' || rec_WasteRegAddress.AddressText);
  DBMS_OUTPUT.PUT_LINE('CityText = ' || rec_WasteRegAddress.CityText);
  DBMS_OUTPUT.PUT_LINE('PostalCode = ' || rec_WasteRegAddress.PostalCode);
  DBMS_OUTPUT.PUT_LINE('StateProvinceCode = ' || rec_WasteRegAddress.StateProvinceCode);
  DBMS_OUTPUT.PUT_LINE('CountryCode = ' || rec_WasteRegAddress.CountryCode);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
   -- Modify the code to output the variable -- line 105 below
FETCH WASTEGENERATORREGULATEECONTACT INTO rec_WasteRegContact;

  DBMS_OUTPUT.PUT_LINE('WASTEREGULATEECONTACT:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_WasteRegContact.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || rec_WasteRegContact.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || rec_WasteRegContact.LastName);
  DBMS_OUTPUT.PUT_LINE('PhoneNumberText = ' || rec_WasteRegContact.PhoneNumberText);
  DBMS_OUTPUT.PUT_LINE('FaxText = ' || rec_WasteRegContact.FaxText);
  DBMS_OUTPUT.PUT_LINE('EmailText = ' || rec_WasteRegContact.EmailText);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
   -- Modify the code to output the variable -- line 80 below
FETCH FACILITYREGULATEEINFO INTO rec_FacilityRegInfo;

  DBMS_OUTPUT.PUT_LINE('FACILITYREGULATEEINFO:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_FacilityRegInfo.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('RegistrationNumber = ' || rec_FacilityRegInfo.RegistrationNumber);
  DBMS_OUTPUT.PUT_LINE('CompanyName = ' || rec_FacilityRegInfo.CompanyName);
  DBMS_OUTPUT.PUT_LINE('FirstName = ' || rec_FacilityRegInfo.FirstName);
  DBMS_OUTPUT.PUT_LINE('LastName = ' || rec_FacilityRegInfo.LastName);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
   -- Modify the code to output the variable -- line 92 below
FETCH FACILITYREGULATEEADDRESS INTO rec_FacilityRegAddress;

  DBMS_OUTPUT.PUT_LINE('FACILITYREGULATEEADDRESS:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_FacilityRegAddress.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('AddressText = ' || rec_FacilityRegAddress.AddressText);
  DBMS_OUTPUT.PUT_LINE('CityText = ' || rec_FacilityRegAddress.CityText);
  DBMS_OUTPUT.PUT_LINE('PostalCode = ' || rec_FacilityRegAddress.PostalCode);
  DBMS_OUTPUT.PUT_LINE('StateProvinceCode = ' || rec_FacilityRegAddress.StateProvinceCode);
  DBMS_OUTPUT.PUT_LINE('CountryCode = ' || rec_FacilityRegAddress.CountryCode);
  DBMS_OUTPUT.PUT_LINE('AddressCode = ' || rec_FacilityRegAddress.AddressCode);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
  -- Modify the code to output the variable
  FETCH FACILITYDETAILS INTO rec_facilityDetails;

  DBMS_OUTPUT.PUT_LINE('FacilityDetails:');
  DBMS_OUTPUT.PUT_LINE('RegulateeID = ' || rec_facilityDetails.RegulateeID);
  DBMS_OUTPUT.PUT_LINE('PreconsentedBoolean = ' || rec_facilityDetails.PreconsentedBoolean);
  DBMS_OUTPUT.PUT_LINE('DRCodeList = ' || rec_facilityDetails.DRCodeList);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
 -- Modify the code to output the variable
  FETCH NOTICEDETAILS INTO rec_NotiDetails;

  DBMS_OUTPUT.PUT_LINE('NOTICEDETAILS:');
  DBMS_OUTPUT.PUT_LINE('ExportCountryCode = ' || rec_NotiDetails.ExportCountryCode);
  DBMS_OUTPUT.PUT_LINE('ExportCountryNoticeID = ' || rec_NotiDetails.ExportCountryNoticeID);
  DBMS_OUTPUT.PUT_LINE('ImportCountryCode = ' || rec_NotiDetails.ImportCountryCode);
  DBMS_OUTPUT.PUT_LINE('ImportCountryNoticeID = ' || rec_NotiDetails.ImportCountryNoticeID);
  DBMS_OUTPUT.PUT_LINE('DisposalBoolean = ' || rec_NotiDetails.DisposalBoolean);
  DBMS_OUTPUT.PUT_LINE('RecoveryBoolean = ' || rec_NotiDetails.RecoveryBoolean);
  DBMS_OUTPUT.PUT_LINE('FirstDepartureDate = ' || rec_NotiDetails.FirstDepartureDate);
  DBMS_OUTPUT.PUT_LINE('LastDepartureDate = ' || rec_NotiDetails.LastDepartureDate);
  DBMS_OUTPUT.PUT_LINE('PackagingList = ' || rec_NotiDetails.PackagingList);
  DBMS_OUTPUT.PUT_LINE('PackagingTypeOtherText = ' || rec_NotiDetails.PackagingTypeOtherText);
  DBMS_OUTPUT.PUT_LINE('SplHandlingRequirementsText = ' || rec_NotiDetails.SplHandlingRequirementsText);
  DBMS_OUTPUT.PUT_LINE('MeansOfTransportList = ' || rec_NotiDetails.MeansOfTransportList);
  DBMS_OUTPUT.PUT_LINE('TechnologyEmployedText = ' || rec_NotiDetails.TechnologyEmployedText);
  DBMS_OUTPUT.PUT_LINE('ReasonForExportText = ' || rec_NotiDetails.ReasonForExportText);
  DBMS_OUTPUT.PUT_LINE('ImportPortOfEntryList = ' || rec_NotiDetails.ImportPortOfEntryList);
  DBMS_OUTPUT.PUT_LINE('ExportPortOfExitList = ' || rec_NotiDetails.ExportPortOfExitList);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
 -- Modify the code to output the variable
  FETCH WASTESTREAMDETAILS INTO rec_Waste;

  DBMS_OUTPUT.PUT_LINE('WasteStreamDETAILS:');
  DBMS_OUTPUT.PUT_LINE('WasteStreamNumber = ' || rec_Waste.WasteStreamNumber);
  DBMS_OUTPUT.PUT_LINE('NumberOfShipments = ' || rec_Waste.NumberOfShipments);
  DBMS_OUTPUT.PUT_LINE('QuantityNumber = ' || rec_Waste.QuantityNumber);
  DBMS_OUTPUT.PUT_LINE('UnitOfMeasureCode = ' || rec_Waste.UnitOfMeasureCode);
  DBMS_OUTPUT.PUT_LINE('DRCodeList = ' || rec_Waste.DRCodeList);
  DBMS_OUTPUT.PUT_LINE('WasteDesigCompositionText = ' || rec_Waste.WasteDesigCompositionText);
  DBMS_OUTPUT.PUT_LINE('PhysicalStateCode = ' || rec_Waste.PhysicalStateCode);
  DBMS_OUTPUT.PUT_LINE('BaselAnnexCodeList = ' || rec_Waste.BaselAnnexCodeList);
  DBMS_OUTPUT.PUT_LINE('OECDCodeList = ' || rec_Waste.OECDCodeList);
  DBMS_OUTPUT.PUT_LINE('ExporterNationalCodeList = ' || rec_Waste.ExporterNationalCodeList);
  DBMS_OUTPUT.PUT_LINE('ImporterNationalCodeList = ' || rec_Waste.ImporterNationalCodeList);
  DBMS_OUTPUT.PUT_LINE('WasteCodeOtherText = ' || rec_Waste.WasteCodeOtherText);
  DBMS_OUTPUT.PUT_LINE('YCodeList = ' || rec_Waste.YCodeList);
  DBMS_OUTPUT.PUT_LINE('HCodeList = ' || rec_Waste.HCodeList);
  DBMS_OUTPUT.PUT_LINE('IWICCode = ' || rec_Waste.IWICCode);
  DBMS_OUTPUT.PUT_LINE('UNClassCodeList = ' || rec_Waste.UNClassCodeList);
  DBMS_OUTPUT.PUT_LINE('UNNumberShippingCodeList = ' || rec_Waste.UNNumberShippingCodeList);
  DBMS_OUTPUT.PUT_LINE('CustomCode = ' || rec_Waste.CustomCode);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
   -- Modify the code to output the variable
  FETCH TRANSITCOUNTRY INTO rec_TransitCountryDetails;

  DBMS_OUTPUT.PUT_LINE('TRANSITCOUNTRY:');
  DBMS_OUTPUT.PUT_LINE('CountryCode = ' || rec_TransitCountryDetails.CountryCode);
  DBMS_OUTPUT.PUT_LINE('PortOfExportList = ' || rec_TransitCountryDetails.PortOfExportList);
  DBMS_OUTPUT.PUT_LINE('PortOfImportList = ' || rec_TransitCountryDetails.PortOfImportList);
  DBMS_OUTPUT.PUT_LINE('LengthOfStayText = ' || rec_TransitCountryDetails.LengthOfStayText);

  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
 
   -- Modify the code to output the variable
  FETCH NOTICESUBMITTER INTO rec_Notice_Submitter;
  DBMS_OUTPUT.PUT_LINE('NOTICESUBMITTERDetails:');
  DBMS_OUTPUT.PUT_LINE('ExporterFirstName = ' || rec_Notice_Submitter.ExporterFirstName);
  DBMS_OUTPUT.PUT_LINE('ExporterLastName = ' || rec_Notice_Submitter.ExporterLastName);
  DBMS_OUTPUT.PUT_LINE('ExporterDeclarationDate = ' || rec_Notice_Submitter.ExporterDeclarationDate);
  DBMS_OUTPUT.PUT_LINE('GeneratorFirstName = ' || rec_Notice_Submitter.GeneratorFirstName);
  DBMS_OUTPUT.PUT_LINE('GeneratorLastName = ' || rec_Notice_Submitter.GeneratorLastName);
  DBMS_OUTPUT.PUT_LINE('GeneratorDeclarationDate = ' || rec_Notice_Submitter.GeneratorDeclarationDate);
  DBMS_OUTPUT.PUT_LINE('SubmitterFirstLastName = ' || rec_Notice_Submitter.SubmitterFirstLastName);
  DBMS_OUTPUT.PUT_LINE('SubmitterDeclarationDate = ' || rec_Notice_Submitter.SubmitterDeclarationDate);
 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
 
  -- Modify the code to output the variable
  FETCH NOTICEOFFICERDETAILS INTO rec_Officer;

  DBMS_OUTPUT.PUT_LINE('NoticeOfficerDetails:');
  DBMS_OUTPUT.PUT_LINE('PhoneNumberText = ' || rec_Officer.PhoneNumberText);
  DBMS_OUTPUT.PUT_LINE('EmailText = ' || rec_Officer.EmailText);

END;
0
 
slightwv (䄆 Netminder) Commented:
How is EIHW.PK_NOTICEDETERMINATION.cur_RegulateeInfo defined?
0
 
MosquitoeAuthor Commented:
-- RegulateeInfo
TYPE rec_RegulateeInfo IS RECORD ( RegulateeId VARCHAR2(200),
RegistrationNumber VARCHAR2(255),
CompanyName VARCHAR2(255) NOT NULL := 'N/A',
FirstName VARCHAR2(35) NOT NULL := 'N/A',
LastName VARCHAR2(35) NOT NULL := 'N/A');

--Cursor for regulatee info
TYPE cur_RegulateeInfo IS REF CURSOR RETURN rec_RegulateeInfo;
0
 
slightwv (䄆 Netminder) Commented:
Again.

Works for me.

Verify how PK_NOTICEDETERMINATION.SP_NOTICE is assigning the cursor to EXPORTERREGULATEEINFO.

My sample code below.
drop table tab1 purge;
create table tab1 (
 RegulateeId VARCHAR2(200),
 RegistrationNumber VARCHAR2(255),
 CompanyName VARCHAR2(255),
 FirstName VARCHAR2(35),
 LastName VARCHAR2(35)
);

insert into tab1 values('a','b','c','d','e');
insert into tab1 values('1','2','3','4','5');
commit;

declare

TYPE rec_RegulateeInfo IS RECORD ( RegulateeId VARCHAR2(200),
RegistrationNumber VARCHAR2(255),
CompanyName VARCHAR2(255) NOT NULL := 'N/A',
FirstName VARCHAR2(35) NOT NULL := 'N/A',
LastName VARCHAR2(35) NOT NULL := 'N/A');

TYPE cur_RegulateeInfo IS REF CURSOR RETURN rec_RegulateeInfo; 

EXPORTERREGULATEEINFO cur_RegulateeInfo;

myrec rec_RegulateeInfo;

begin

open EXPORTERREGULATEEINFO for select * from tab1;
fetch EXPORTERREGULATEEINFO into myrec;

while EXPORTERREGULATEEINFO%found loop
   dbms_output.put_line('RegulateeId: ' || myrec.regulateeid);

   fetch EXPORTERREGULATEEINFO into myrec;

   exit when EXPORTERREGULATEEINFO%notfound;

end loop;

close EXPORTERREGULATEEINFO;

end;
/

Open in new window

0
 
MosquitoeAuthor Commented:
Thank You.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now