Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) (Oracle 12.1.0.1.0 on a Windows 10)
Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) [message #676959] Thu, 01 August 2019 19:09 Go to next message
ALONG
Messages: 3
Registered: August 2019
Location: Anchorage, AK
Junior Member
I've got several stored procedures that have been in use for some time and compile in SQL Developer on a Windows 7, Oracle 11g release 11.2.0.3.0 system but do not compile under our new 12c release 12.1.0.1.0 system, Windows 10. Most did compile but I'm having a tough time resolving the few that don't. Below is a procedure that errors and the errors. I was able to resolve a different procedure by stripping out the line definitions which isn't working on this procedure. Ideally I wouldn't need to strip that out. Any help or direction with this is appreciated. Unfortunately my coding ability is pretty basic.
create or replace
PROCEDURE          "TOKEN_PROJECT_COMMENTS"(p_limsdata_seq_in in limsdata_pull_list.limsdata_seq%type)
/*
||   PURPOSE:    To retrieve the comments (footnotes) attached to workorder(s)
||   PARAMETERS:
||   INPUT: LIMSDATA_SEQ
||   OUTPUT: ROWS IN TBL_PROJECT_COMMENTS
||   RETURNED VALUE: NONE
/*

is
   /* retrieve comments/footnotes */
   cursor comments_cur (p_footnote_type_in in footnotes.footnote_type%type := 'P') is
      select *
	    from
		     (
             select lpl.limsdata_seq,
	                'TRUE' as return_record,
			        lpl.project_hbn,
			        nvl(custom_comment,'') as project_comment,
			        substr(footnotes.flags,1,1) as reportable,
			        substr(footnotes.flags,2,1) as print_text,
			        substr(footnotes.flags,3,1) as placement,
			        substr(footnotes.flags,4,1) as epa_flag,
			        footnotes.sort_item as srt1,
			        footnotes.sort_item as srt2,
			        '' as line_def
               from
		            limsdata_pull_list lpl,
			        footnotes
              where length(nvl(predefined,' ')) < 2
                and footnote_type = p_footnote_type_in
                and footnote_id = lpl.project_hbn
                and lpl.limsdata_seq = p_limsdata_seq_in
              union
             select lpl.limsdata_seq,
	                'TRUE' as return_record,
			        lpl.project_hbn,
			        nvl(app_text,'') as project_comment,
			        substr(footnotes.flags,1,1) as reportable,
			        substr(footnotes.flags,2,1) as print_text,
			        substr(footnotes.flags,3,1) as placement,
			        substr(footnotes.flags,4,1) as epa_flag,
			        footnotes.sort_item as srt1,
			        application_text.app_sort as srt2,
			        substr(application_text.flags,1,1) as line_def
               from
		            limsdata_pull_list lpl,
			        footnotes,
			        application_text
              where application_text.app_use = footnotes.predefined
                and footnote_type = p_footnote_type_in
                and footnote_id = lpl.project_hbn
                and lpl.limsdata_seq = p_limsdata_seq_in
			 ) wo_comments
--       WHERE project_comment IS NOT NULL
	order by project_hbn, reportable, srt1, srt2;

   /* records to hold current and previous info */
   comments_rec comments_cur%rowtype;
   old_comments_rec comments_cur%rowtype;

   /* var to hold aggregate comment */
   entire_comment tbl_project_comments.project_comment%type;

   /* var as trigger for new entry */
   old_wo tbl_project_comments.project_hbn%type;
   old_status tbl_project_comments.reportable%type;

begin

   old_wo := 0;
   old_status := '';

   open comments_cur('P');

   fetch comments_cur into comments_rec;
   while comments_cur%found
   loop
      if comments_rec.project_hbn <> old_wo or comments_rec.reportable <> old_status then
         begin
	        if old_wo <> 0 then
               insert into tbl_project_comments
	                         (
                              limsdata_seq,
 			                  return_record,
       			              project_hbn,
			                  project_comment,
			                  reportable,
			                  print_text,
			                  placement,
			                  epa_flag,
                              srt1
					         )
                    values
		                     (
                              old_comments_rec.limsdata_seq,
 			                  old_comments_rec.return_record,
			                  old_wo,
			                  entire_comment,
			                  old_comments_rec.reportable,
			                  old_comments_rec.print_text,
			                  old_comments_rec.placement,
			                  old_comments_rec.epa_flag,
                              old_comments_rec.srt1
					         );
		    end if;
		    old_wo := comments_rec.project_hbn;
			old_status := comments_rec.reportable;
		    entire_comment := comments_rec.project_comment;
		    old_comments_rec := comments_rec;
		 end;
	  else
         if comments_rec.line_def is null or comments_rec.line_def = '' or comments_rec.line_def = '.' then
            entire_comment := entire_comment || chr(13) || chr(10) || comments_rec.project_comment;
         else
		    if comments_rec.line_def = 'P' then
               entire_comment := entire_comment || chr(13) || chr(10) || chr(13) || chr(10) || chr(9) || comments_rec.project_comment;
            else
			   if comments_rec.line_def = '*' then
                  entire_comment := entire_comment || chr(160) ||ltrim(comments_rec.project_comment); -- fix for 1.1.1.1 added CHR(160) = &nbsp;
			   end if;
            end if;
         end if;
	  end if;
      fetch comments_cur into comments_rec;
   end loop;

   if old_wo <> 0 then
      insert into tbl_project_comments
                    (
                     limsdata_seq,
                     return_record,
                     project_hbn,
                     project_comment,
                     reportable,
                     print_text,
                     placement,
                     epa_flag,
                     srt1
                    )
             values
                    (
                     comments_rec.limsdata_seq,
                     comments_rec.return_record,
                     comments_rec.project_hbn,
                     entire_comment,
                     comments_rec.reportable,
                     comments_rec.print_text,
                     comments_rec.placement,
                     comments_rec.epa_flag,
                     comments_rec.srt1
                    );
   end if;

   commit;

   close comments_cur;

exception
  when no_data_found then
    null;
  when others then
    null;
end token_project_comments;
Returned Errors below,
Project: sqldev.temp:/IdeConnections%23Test_12C_Sample.jpr
Procedure SAMPLE.TOKEN_PROJECT_COMMENTS@Test_12C_Sample
Error(21,11): PLS-00341: declaration of cursor 'COMMENTS_CUR' is incomplete or malformed
Error(22,7): PL/SQL: SQL Statement ignored
Error(54,19): PL/SQL: ORA-00904: "APPLICATION_TEXT"."FLAGS": invalid identifier
Error(54,36): PLS-00302: component 'FLAGS' must be declared
Error(68,17): PL/SQL: Item ignored
Error(69,21): PL/SQL: Item ignored
Error(85,4): PL/SQL: SQL Statement ignored
Error(85,28): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(88,7): PL/SQL: Statement ignored
Error(88,10): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(134,7): PL/SQL: SQL Statement ignored
Error(134,31): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(138,7): PL/SQL: SQL Statement ignored
Error(160,22): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(160,35): PL/SQL: ORA-00984: column not allowed here
*BlackSwan added {code tags}

[Updated on: Thu, 01 August 2019 21:54] by Moderator

Report message to a moderator

Re: Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) [message #676961 is a reply to message #676959] Thu, 01 August 2019 21:55 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) [message #676962 is a reply to message #676959] Fri, 02 August 2019 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67239
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Which one is line 160?
Which one is line 138?
Which one is line 134?
Which one is line 88?
Which one is line 85?
...

Quote:
  when others then
    null;

This is a bug, read WHEN OTHERS and remove it.

Re: Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) [message #676963 is a reply to message #676959] Fri, 02 August 2019 01:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
ALONG wrote on Fri, 02 August 2019 05:39

/*
|| PURPOSE: To retrieve the comments (footnotes) attached to workorder(s)
|| PARAMETERS:
|| INPUT: LIMSDATA_SEQ
|| OUTPUT: ROWS IN TBL_PROJECT_COMMENTS
|| RETURNED VALUE: NONE
/*

is
/* retrieve comments/footnotes */
You have not closed the comment properly, hence the "IS" keyword is commented along with other lines till the comment is closed with "*/".
Re: Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) [message #676965 is a reply to message #676963] Fri, 02 August 2019 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
And given what Lalit spotted I don't see anyway that code compiled on any version of oracle.
Are you sure that's the original code?
Re: Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) [message #676968 is a reply to message #676961] Fri, 02 August 2019 11:13 Go to previous messageGo to next message
ALONG
Messages: 3
Registered: August 2019
Location: Anchorage, AK
Junior Member
Thank You, I will refer to in the future
Re: Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) [message #676969 is a reply to message #676965] Fri, 02 August 2019 11:23 Go to previous messageGo to next message
ALONG
Messages: 3
Registered: August 2019
Location: Anchorage, AK
Junior Member
You both are correct, I stripped out some commenting and closed it incorrectly. In the original code it is closed */ vs /* and does compile in the 11G version. I've listed out the lines that error below, with new error log showing new line references,

Error lines 22/23
cursor comments_cur (p_footnote_type_in in footnotes.footnote_type%type := 'P') is
select *

Error line 55
substr(application_text.flags,1,1) as line_def

Error lines 69/70
comments_rec comments_cur%rowtype;
old_comments_rec comments_cur%rowtype;

Error Line 86
fetch comments_cur into comments_rec;

Error Line 89
if comments_rec.project_hbn <> old_wo or comments_rec.reportable <> old_status then

Error Line 135
fetch comments_cur into comments_rec;

Error Line 139
insert into tbl_project_comments

and lastly Error line 161
comments_rec.srt1


Procedure SAMPLE.TOKEN_PROJECT_COMMENTS@Test_12C_Sample
Error(22,11): PLS-00341: declaration of cursor 'COMMENTS_CUR' is incomplete or malformed
Error(23,7): PL/SQL: SQL Statement ignored
Error(55,19): PL/SQL: ORA-00904: "APPLICATION_TEXT"."FLAGS": invalid identifier
Error(55,36): PLS-00302: component 'FLAGS' must be declared
Error(69,17): PL/SQL: Item ignored
Error(70,21): PL/SQL: Item ignored
Error(86,4): PL/SQL: SQL Statement ignored
Error(86,28): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(89,7): PL/SQL: Statement ignored
Error(89,10): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(135,7): PL/SQL: SQL Statement ignored
Error(135,31): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(139,7): PL/SQL: SQL Statement ignored
Error(161,22): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(161,35): PL/SQL: ORA-00984: column not allowed here

Re: Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) [message #676971 is a reply to message #676969] Fri, 02 August 2019 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
consider to read & follow Posting Guidelines

It is a real challenge to debug code we can not see.

Consider to work from the top down & debug first error while commenting out the code below it.
Otherwise You're On Your Own (YOYO)!
Re: Stored Procedures compile in 11G but do not in 12C (12.1.0.1.0) [message #676988 is a reply to message #676971] Mon, 05 August 2019 03:00 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Fix this one:
Error(55,19): PL/SQL: ORA-00904: "APPLICATION_TEXT"."FLAGS": invalid identifier

and I suspect most of the others will go away.

It seems the table application_text doesn't have a column called flags.
Previous Topic: Convert Columns to rows
Next Topic: Oracle insert statement
Goto Forum:
  


Current Time: Fri Jul 10 15:18:50 CDT 2020