Itgendid103 with stored procedure : provider InMemory Storage 'incapable' when trying to fetch data

Since I have upgraded to 22.0.444 PROD+3699 on november 23th, I have a script automation with a stored procedure that fails with a itgendid103 error.

The script ran fine for almost 2 years and is a pretty long (578 lines).

Any idea what is going wrong given this error ? Should i share my full script here ?

This is the extract of the error:

2022-12-05 06:34:07.602 Information itgengpr065: Ouverture du conteneur de données 'DataDictionaryInMemory'.
2022-12-05 06:34:07.604 Information itgengpr152: Ouverture du conteneur de données 'DataDictionaryInMemory'.
2022-12-05 06:34:09.922 Warning itgendhb173: Contexte: --23/08/21 -- added paging for the salesdocumentlines -- to do...InMemoryStorage;     drop table Newshifts@InMemoryStorage; end;
2022-12-05 06:34:09.953 Error itgencun016: Erreur itgendid103: Le fournisseur 'InMemoryStorage' a renvoyé le statut 'Incapable' lorsqu'il a essayé de récupérer des données sur 'NEWSHIFTS'.
2022-12-05 06:34:11.955 Error itgendid103: ValidationException
   à Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId, String url)
   à Invantive.Sql.V1.DataSourceOrFunctionTree.<GetData>d__51.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Sql.V1.ChainedFirehose.PM.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   à Invantive.Sql.V1.GroupByIterator.TS.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Sql.V1.SelectListIterator.ZS.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.MaterializeSparseArrayIterator.<Iterator>d__1.MoveNext()
   à Invantive.Sql.V1.QueryPlan.JS.MoveNext()
   à Invantive.Data.ExtensionMethods.BOQ`1.COQ.MoveNext()
   à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   à System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   à Invantive.Sql.V1.TransformToExpression.EvaluateInnerSelectStatementScalar(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, QueryPlan plan, DatabaseColumnDefinitionCollection fields, ISparseArray pSqlVariableRow)
   à LambaExecutePSqlTree186(Closure , GlobalState , ExecutionOptions , IQueryStatePerExecution , ISparseArray , ISparseArray )
   à Invantive.Sql.V1.PSqlStatementTree.<Fetch>d__29.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.QueryPlan.JS.MoveNext()
   à Invantive.Data.ExtensionMethods.BOQ`1.COQ.MoveNext()
   à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   à System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   à Invantive.Data.ConnectionManager.XG.T(QueryPlan )
   à System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   à System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   à System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   à Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   à Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   à IDH.R.ProcessStatement(GlobalState owner, ExecutionOptions executionOptions, String statement, ResultSet& resultSet, Action`2 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)
   à IDH.R.ProcessStatements(GlobalState owner, ExecutionOptions executionOptions, Action`2 fetchMoreRows, Action`9 executeStatement, IEnumerable`1 statements, String sourceFileName, ScriptingLanguageNextStepSpecification& exitSpecification)
   à IDH.R.B(String[] )
   à Invantive.Producer.QueryEngine.Program.Main(String[] arguments)
   à Invantive.Sql.V1.DataSourceOrFunctionTree.<GetData>d__51.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Sql.V1.ChainedFirehose.PM.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   à Invantive.Sql.V1.GroupByIterator.TS.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Sql.V1.SelectListIterator.ZS.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.BF.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.MaterializeSparseArrayIterator.<Iterator>d__1.MoveNext()
   à Invantive.Sql.V1.QueryPlan.JS.MoveNext()
   à Invantive.Data.ExtensionMethods.BOQ`1.COQ.MoveNext()
   à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   à System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   à Invantive.Sql.V1.TransformToExpression.EvaluateInnerSelectStatementScalar(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, QueryPlan plan, DatabaseColumnDefinitionCollection fields, ISparseArray pSqlVariableRow)
   à LambaExecutePSqlTree186(Closure , GlobalState , ExecutionOptions , IQueryStatePerExecution , ISparseArray , ISparseArray )
   à Invantive.Sql.V1.PSqlStatementTree.<Fetch>d__29.MoveNext()
   à Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__10.MoveNext()
   à Invantive.Sql.V1.FirehoseResultSet.KF.MoveNext()
   à Invantive.Sql.V1.QueryPlan.JS.MoveNext()
   à Invantive.Data.ExtensionMethods.BOQ`1.COQ.MoveNext()
   à System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   à System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   à Invantive.Data.ConnectionManager.XG.T(QueryPlan )
   à System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   à System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   à System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   à Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- Fin de la trace de la pile à partir de l'emplacement précédent au niveau duquel l'exception a été levée ---
   à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
--- Fin de la trace de la pile à partir de l'emplacement précédent au niveau duquel l'exception a été levée ---
   à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   à Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   à IDH.R.ProcessStatement(GlobalState owner, ExecutionOptions executionOptions, String statement, ResultSet& resultSet, Action`2 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)
2022-12-05 06:34:11.959 Information itgendhb223: Terminer l'exécution du programme avec le code de sortie 3.

The cause is probably a change in the dynamic nature of parsing temporary tables. Probably somewhere in a PSQL-block a temporary (in-memory) table NEWSHIFTS is used before it can be interpreted due to creation.

Please include the full script.

After some digging I found the code block in cause and it relates to httpdownload@DataDictionary with the method detailed here : How to send multiple key values in Authorization header with httpdownload.

The API enpoint now returns a 404 error.

The error is not caught in the error catch logic :

begin
  select * from httpdownload@DataDictionary (...)
  join jsontable (...)
exception
    when others
    then
    -- do exception work here 
end

My findings so far :

  • The 404 error returned by the httpdownload@DataDictionary is not caught by the exception handler
  • It seems there is a change in the httpdownload@DataDictionary method. I tested the API with a cURL query and it works.

I will share my query in a private message, because it contains some confidential access key.

It seems it is linked to Itgenclr083 of itgenclr094 (404) niet gevonden Chromium.

I’m trying to upgrade now to 22.0.455

my issue was created by the Itgenclr083 of itgenclr094 (404) niet gevonden Chromium.
I confirm that upgrading to 22.0.455 solves the issue.

1 like

Thanks. Also retested on 22.0.456 and latest BETA. Both display "Caught HTTP error correctly: " using this test code with secret removed.

declare
  l_cnt pls_integer;
begin
  select count(*) 
  into   l_cnt 
  from   httpdownload@DataDictionary 
  ( url => 'https://api.laddition.com/ShiftDocumentsWRONGFOR404?opening_date=2022-11-01 00:00:00&closing_date=2022-11-30 00:00:00'
  , contentType => 'application/json'
  , headers => 'Authorization=Bearer secret' ||  '&'  || 'customerid=15932'
  , diskCache => false
  , method => 'GET'
  ) htp
  join jsontable
  ( 'data.[*]'
    passing htp.contents_char
    columns shift_id                           varchar2 path 'id'
    ,       opening_date                       varchar2 path 'opening_date'
    ,       closing_date                       varchar2 path 'closing_date'
    ,       begin_cash_float                   decimal  path 'begin_cash_float' -- fond de caisse ouverture
    ,       amount_offered_ivat                decimal  path 'amount_offered_ivat' -- total des offerts
    ,       amount_discount_ivat               decimal  path 'amount_discount_ivat' -- total des remises
    ,       amount_total_evat                  decimal  path 'amount_total_evat' -- Total HT
    ,       amount_tax                         decimal  path 'amount_tax' -- Total TVA
    ,       amount_total_wo_discount_ivat      decimal  path 'amount_total_wo_discount_ivat' -- Total CA TTC Brut (hors offerts et remises)
    ,       nb_guest                           int      path 'nb_guest'
    ,       nb_sales_document                  int      path 'nb_sales_document' -- nombre de commandes
    ,       amount_avg_basket                  decimal  path 'amount_avg_basket' -- panier moyen
    ,       amount_avg_sales_document          decimal  path 'amount_avg_sales_document' -- Ticket moyen
    ,       amount_payment_ivariation          decimal  path 'amount_payment_ivariation' -- Total règlements (prenant en compte les écarts renseignées manuellement)
    ,       amount_payment_shift               decimal  path 'amount_payment_shift' -- Total règlements (ventilation CA, normalement égal au CA TTC)
    ,       amount_liquid                      decimal  path 'amount_liquid' -- Montant liquide
    ,       amount_solid                       decimal  path 'amount_solid' -- Montant solide (normalement liquide + solide = CA TTC)
    ,       cancelled_manufacturing            decimal  path 'cancelled_manufacturing' --  Annulation de produits envoyés en cuisine
    ,       cancelled_manufacturing_after_note decimal  path 'cancelled_manufacturing_after_note' --  Annulation de produits après ticket
    ,       cancelled_non_manufacturing        decimal  path 'cancelled_non_manufacturing' -- Annulation de produits hors fabrication cuisine
  ) res
  ;
exception 
  when others 
  then 
    raise_application_error(-20163, 'Caught HTTP error correctly: ' || sqlerrm);
end;