[CLOSED] Your Error Code is: 1406 Database Unavailable
Does anyone know what could give this error. The DBA says he can run this without an error, although I'm still getting an Oracle error. (WF 5.2.3)
SELECT TO_NUMBER(auth_amt) auth_amt,
card_nbr account_no,
reply_cd auth_resp_cd,
auth_cd approval_cd,
TRUNC(auth_timestamp) auth_date,
TO_CHAR(auth_timestamp,'HH24:MI:SS') auth_time,
cvv2_result,
visa_product_id,
mc_bank_net_ref_nbr
FROM TABLE(da_imc_tran.srch_auth(TO_DATE(:01), TO_DATE(:02), :03, NULL, NULL, NULL, :04, NULL, TO_DATE(:05), :06, :07, NULL, :08, :09, :10, NULL, :11, :12, :13, :14));
END
SQL SQLORA BIND DEF USING VARCHAR(20), VARCHAR(20), VARCHAR(50), VARCHAR(20), VARCHAR(20), DECIMAL(20), VARCHAR(1), VARCHAR(30), VARCHAR(10), DECIMAL(13,2), VARCHAR(20), DECIMAL(10), VARCHAR(10), VARCHAR(10)
END
SQL SQLORA EXECUTE DEF USING '08-NOV-2010', '08-NOV-2010', 'SSAVILLE', '0004707527', '08-NOV-2010', 1112868609470340011, 'E', '0007340000000004707527', '00552B', 1200.00, 'CRAUDCRD_Q1', 50000, 'Y', 'C' ;
END
DEFINE FILE DEF
END
TABLE FILE DEF
PRINT
*
ON TABLE HOLD AS AUTHDTL FORMAT ALPHA
END
-RUN
(FOC1400) SQLCODE IS 1722 (HEX: 000006BA)
: ORA-01722: invalid number
: ORA-06512: at "IMC.DA_IMC_TRAN", line 3293
: ORA-06512: at line 1
This message has been edited. Last edited by: Kerry,
WebFOCUS 8.0.08 Solaris HTML, PDF, XLS, CSV
November 23, 2010, 01:42 PM
GamP
quote:
1112868609470340011
Could be this number. I think it is a bit more than what WebFOCUS can handle. It might result in an overflow situation, thereby causing this error. Check if that's the case by activating traces and investigating them.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
November 23, 2010, 03:35 PM
Suzy
I've run it as a string and and integer with the same error. At one point this was working fine. And I don't have the ability to activate traces.
WebFOCUS 8.0.08 Solaris HTML, PDF, XLS, CSV
November 23, 2010, 04:19 PM
GamP
quote:
And I don't have the ability to activate traces
If you can code a fex, then you can activate traces. There are plenty of threads in this forum that can tell you how to do that. It can be (or is) very helpful when you need to analyze sql translation things.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
November 23, 2010, 05:50 PM
Suzy
I added this to the report.
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
And view source is this.
SELECT TO_NUMBER(auth_amt) auth_amt,
card_nbr account_no,
reply_cd auth_resp_cd,
auth_cd approval_cd,
TRUNC(auth_timestamp) auth_date,
TO_CHAR(auth_timestamp,'HH24:MI:SS') auth_time,
cvv2_result,
visa_product_id,
mc_bank_net_ref_nbr
FROM TABLE(da_imc_tran.srch_auth(TO_DATE(:01), TO_DATE(:02), :03, NULL, NULL, NULL, :04, NULL, TO_DATE(:05), :06, :07, NULL, :08, :09, :10, NULL, :11, :12, :13, :14));
END
SQL SQLORA BIND DEF USING VARCHAR(20), VARCHAR(20), VARCHAR(50), VARCHAR(20), VARCHAR(20), DECIMAL(20), VARCHAR(1), VARCHAR(30), VARCHAR(10), DECIMAL(13,2), VARCHAR(20), DECIMAL(10), VARCHAR(10), VARCHAR(10)
END
SQL SQLORA EXECUTE DEF USING '08-NOV-2010', '08-NOV-2010', 'SSAVILLE', '0004707527', '08-NOV-2010', 1112868609470340048, 'E', '0007340000000004707527', '003877', 1092.00, 'CRAUDCRD_Q1', 50000, 'Y', 'C' ;
END
DEFINE FILE DEF
END
TABLE FILE DEF
PRINT
*
ON TABLE HOLD AS AUTHDTL FORMAT ALPHA
END
-RUN
17.46.14 AE SELECT TO_NUMBER(auth_amt) auth_amt, card_nbr account_no,
17.46.14 AE reply_cd auth_resp_cd, auth_cd approval_cd,
17.46.14 AE TRUNC(auth_timestamp) auth_date, TO_CHAR(auth_timestamp,
17.46.14 AE 'HH24:MI:SS') auth_time, cvv2_result, visa_product_id,
17.46.14 AE mc_bank_net_ref_nbr FROM
17.46.14 AE TABLE(da_imc_tran.srch_auth(TO_DATE(:01), TO_DATE(:02), :03,
17.46.14 AE NULL, NULL, NULL, :04, NULL, TO_DATE(:05), :06, :07, NULL, :08,
17.46.14 AE :09, :10, NULL, :11, :12, :13, :14));
(FOC1400) SQLCODE IS 1722 (HEX: 000006BA)
: ORA-01722: invalid number
: ORA-06512: at "IMC.DA_IMC_TRAN", line 3293
: ORA-06512: at line 1
L (FOC1406) SQL OPEN CURSOR ERROR. : DEF
WebFOCUS 8.0.08 Solaris HTML, PDF, XLS, CSV
November 24, 2010, 10:48 AM
GamP
Only now I notice that it may very well be the END that screws things up. The very first END after the FROM line. END usually is a very meaningfull token in the webfocus / sql language, I'm suspecting it won't be any different in this case. It may be best to open a case with IB tech support to ask if the construction you have is supported at all. I suspect it isn't. If that is the case, then maybe they can also tell you if, and if so how, what you are trying to do this can be done using webfocus.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
November 24, 2010, 12:02 PM
Suzy
Thank you I will open a case, I don't think it could be the construction because we have about 30 other reports with the same construction that work.