| Errors running Proc that compiles fine [message #681600] |
Fri, 07 August 2020 10:36  |
 |
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;
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  |
John Watson
Messages: 8804
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 |
Solomon Yakobson
Messages: 3212
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>
SY.
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
Just describe both tables. Use SQL*Plus, and copy/paste. 