Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Your Error Code is: 1406 Database Unavailable
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Your Error Code is: 1406 Database Unavailable
 Login/Join
 
Gold member
posted
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
 
Posts: 55 | Registered: September 08, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 55 | Registered: September 08, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 55 | Registered: September 08, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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.


WebFOCUS 8.0.08
Solaris
HTML, PDF, XLS, CSV
 
Posts: 55 | Registered: September 08, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Your Error Code is: 1406 Database Unavailable

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.