Errors running Proc that compiles fine [message #681600] |
Fri, 07 August 2020 10:36 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/e175e027caf6b080f5ee8f1ac8a1bfb4?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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 #681610 is a reply to message #681600] |
Fri, 07 August 2020 15:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Solomon Yakobson
Messages: 3280 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.
|
|
|