Home » SQL & PL/SQL » SQL & PL/SQL » Errors running Proc that compiles fine (SQL Developer Version 17.3.2.341 \ Build 341.0937)
Errors running Proc that compiles fine [message #681600] Fri, 07 August 2020 10:36 Go to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
Good morning. Below is the code from my stored procedure, a copy\paste of the error message, and a copy \ paste of some of the values in the Review_Count_SQL column from the table I pull in. Any help is greatly appreciated.

create or replace PROCEDURE Tracker_Status_Counts
   (RptPeriod  IN CHAR  ) AS 
   
 InsertCode  VARCHAR2(2000) ; 

 BEGIN 

-- exec Tracker_Status_Counts ('2020-10' ) ;

---------------------------------------------
InsertCode := 

'INSERT INTO                                     ' ||
'       VACLT.XCLT_Tracker_Results               ' ||
'             (Rpt_Period  , Wave   ,            ' || 
'              ValueStream , Task   ,            ' ||
'              CNT                  ,            ' ||
'              Txn_Variance_Count                ' ||
'             )                                  ' ||
' SELECT '''||RptPeriod || ''' AS RptPeriod ,    ' ||
'    Wave   ,                  ' || 
'        ValueStream , Task   ,                  ' || 
'      ('||    '''||Review_Count_SQL  ||'') AS Cnt'      ||
'     ,   NULL AS TXN_VARIANCE_COUNT             ' ||
'  FROM VACLT.XCLT_TRACKER_SQL                   ' ||
' WHERE REVIEW_COUNT_SQL IS NOT NULL             '      
; 
---------------------------------------------
---------------------------------------------

EXECUTE IMMEDIATE InsertCode ; 

---------------------------------------------
---------------------------------------------

COMMIT;

COMMIT;
END;  
HERE IS THE ERROR MESSAGE

Error starting at line : 1 in command -
BEGIN Tracker_Status_Counts ('2020-10' ); END;
Error report -
ORA-01722: invalid number
ORA-06512: at "VACLT.TRACKER_STATUS_COUNTS", line 32
ORA-06512: at line 1
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

HERE ARE THREE VALUES IN THE Review_Count_SQL COLUMN

SELECT COUNT(*) FROM VCLT_FIXED_ASSETS WHERE AO = '40'
SELECT COUNT(*) FROM VCLT_P2P WHERE Wave = 'ABC'
SELECT COUNT(TRANS_NUMBER) FROM VCLT_OBL WHERE WAVE = 'ABC' AND FLAG_ISSUE <> 'Yes'

Re: Errors running Proc that compiles fine [message #681601 is a reply to message #681600] Fri, 07 August 2020 10:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You are probably trying to insert a string into a date. That doesn't work (or if it does, only by luck). SQL is a strongly typed language: you MUST use type casting functions such as to_date if you want your code to run reliably. Note that this is not just a matter of not getting errors, it also about getting results that are correct.
Re: Errors running Proc that compiles fine [message #681602 is a reply to message #681601] Fri, 07 August 2020 10:54 Go to previous messageGo to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
Correct results are ALWAYS good. Keeps me employed.

The CNT column that equates to the string with Review_Count_SQL is a NUMBER. I just double checked, in fact

Other ideas?
Re: Errors running Proc that compiles fine [message #681603 is a reply to message #681602] Fri, 07 August 2020 10:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to show your table definitions. It is (obviously!) a problem with data types.
Re: Errors running Proc that compiles fine [message #681604 is a reply to message #681603] Fri, 07 August 2020 11:04 Go to previous messageGo to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
Here is tracker status counts
RPT_PERIOD	VARCHAR2(7 CHAR)	Yes	
WAVE	VARCHAR2(25 CHAR)	Yes	
VALUESTREAM	VARCHAR2(25 CHAR)	Yes	
TASK	VARCHAR2(25 CHAR)	Yes	
CNT	NUMBER(15,0)	Yes	
TXN_VARIANCE_COUNT	NUMBER(15,0)	Yes	
Thanks
Re: Errors running Proc that compiles fine [message #681605 is a reply to message #681604] Fri, 07 August 2020 11:21 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
what do you get when you execute just the SELECT portion of your built INSERT statement? That should be pretty revealing.
Re: Errors running Proc that compiles fine [message #681606 is a reply to message #681604] Fri, 07 August 2020 11:28 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
cfairtp wrote on Fri, 07 August 2020 17:04
Here is tracker status counts
RPT_PERIOD	VARCHAR2(7 CHAR)	Yes	
WAVE	VARCHAR2(25 CHAR)	Yes	
VALUESTREAM	VARCHAR2(25 CHAR)	Yes	
TASK	VARCHAR2(25 CHAR)	Yes	
CNT	NUMBER(15,0)	Yes	
TXN_VARIANCE_COUNT	NUMBER(15,0)	Yes	
Thanks
Er... Is the above meant to be useful information? Smile Just describe both tables. Use SQL*Plus, and copy/paste.
Re: Errors running Proc that compiles fine [message #681607 is a reply to message #681605] Fri, 07 August 2020 11:29 Go to previous messageGo to next message
cfairtp
Messages: 15
Registered: July 2020
Junior Member
Good for you! And even better for me. I need an open paren and a closed paren to start\end that line. I have a lunch date but will hop on that when I get back. Thank you so, so much. all of you
Re: Errors running Proc that compiles fine [message #681608 is a reply to message #681607] Fri, 07 August 2020 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

With any SQL or PL/SQL question, please, Post a working Test case: create statements and insert statements for all objects so that we will be able work to reproduce what you have.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

[Updated on: Fri, 07 August 2020 12:46]

Report message to a moderator

Re: Errors running Proc that compiles fine [message #681610 is a reply to message #681600] Fri, 07 August 2020 15:44 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
When you use dynamic SQL it is always a good idea to check generated SQL statements:

SQL> create or replace PROCEDURE Tracker_Status_Counts
  2     (RptPeriod  IN CHAR  ) AS
  3
  4   InsertCode  VARCHAR2(2000) ;
  5
  6   BEGIN
  7
  8  -- exec Tracker_Status_Counts ('2020-10' ) ;
  9
 10  ---------------------------------------------
 11  InsertCode :=
 12
 13  'INSERT INTO                                     ' ||
 14  '       VACLT.XCLT_Tracker_Results               ' ||
 15  '             (Rpt_Period  , Wave   ,            ' ||
 16  '              ValueStream , Task   ,            ' ||
 17  '              CNT                  ,            ' ||
 18  '              Txn_Variance_Count                ' ||
 19  '             )                                  ' ||
 20  ' SELECT '''||RptPeriod || ''' AS RptPeriod ,    ' ||
 21  '    Wave   ,                  ' ||
 22  '        ValueStream , Task   ,                  ' ||
 23  '      ('||    '''||Review_Count_SQL  ||'') AS Cnt'      ||
 24  '     ,   NULL AS TXN_VARIANCE_COUNT             ' ||
 25  '  FROM VACLT.XCLT_TRACKER_SQL                   ' ||
 26  ' WHERE REVIEW_COUNT_SQL IS NOT NULL             '
 27  ;
 28  DBMS_OUTPUT.PUT_LINE(InsertCode);
 29  END;
 30  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC Tracker_Status_Counts ('2020-10' )
INSERT INTO
VACLT.XCLT_Tracker_Results                            (Rpt_Period  , Wave   ,
ValueStream , Task   ,                          CNT                  ,
Txn_Variance_Count                             )
SELECT '2020-10' AS RptPeriod ,        Wave   ,
ValueStream , Task   ,                        ('||Review_Count_SQL  ||') AS Cnt
,   NULL AS TXN_VARIANCE_COUNT               FROM VACLT.XCLT_TRACKER_SQL
WHERE REVIEW_COUNT_SQL IS NOT NULL

PL/SQL procedure successfully completed.

SQL>
So obviously inserting '||Review_Count_SQL ||' into numeric column will fail. And, Review_Count_SQL isn't even a column in VACLT.XCLT_TRACKER_SQL to begin with.

SY.
Previous Topic: INSERT WHEN NOT EXIST
Next Topic: update foreign key of all the referenced table of table
Goto Forum:
  


Current Time: Thu Mar 28 04:50:06 CDT 2024