[Virtua driver] ORA-00942: table or view does not exist (hlrcdetl)

[targui2601]

i faced an error "ORA-00942: table or view does not exist" while access to Virtua Database. What purpose of "hlrcdetl" table and i could replace by another table equaly ? pls. help. Tks

VuFind\Exception\ILS: ORACLE ERROR Oracle 'executing' Error ============= (code) => 942 (message) => ORA-00942: table or view does not exist (offset) => 446 (sqltext) => SELECT d.itemid as item_id, d.copyno, d.barcode, c.due_date, s.name as status, s.status_code, l.name as location, l.location_id, b.call_number as bib_call_num, i.call_number as item_call_num, iss.latest_issue, r.item_class as reserve_item_class, ic.item_class, d.units, br.bib_req, ir.item_req FROM dbadmin.itemdetl2 d, dbadmin.location l, dbadmin.statdetl sd, dbadmin.item_status s, dbadmin.circdetl c, dbadmin.bibliographic_fields b, dbadmin.item_call_number i, item_class_v ic, (SELECT d1.itemid, MAX(h1.request_control_number) AS req_num FROM dbadmin.itemdetl2 d1, dbadmin.hlrcdetl h1 WHERE d1.itemid = h1.itemid AND d1.bibid = :bib_id GROUP BY d1.itemid) h, (SELECT h.bibid, count() as bib_req FROM hlrcdetl h WHERE h.itemid = 0 GROUP BY h.bibid) br, (SELECT h.itemid, count() as item_req FROM hlrcdetl h WHERE h.itemid <> 0 GROUP BY h.itemid) ir, (SELECT MAX(s.issue_id) AS latest_issue, h.bibid FROM serials_issue s, holdlink h WHERE h.bibid = :bib_id AND h.holdingsid = s.holdingsid GROUP BY h.bibid) iss, (SELECT DISTINCT item_id, item_class FROM reserve_item_v) r WHERE d.location = l.location_id AND d.itemclass = ic.item_class_id AND d.itemid = sd.itemid (+) AND sd.stat = s.status_code (+) AND d.itemid = c.itemid (+) AND d.itemid = h.itemid (+) AND d.bibid = br.bibid (+) AND d.itemid = ir.itemid (+) AND d.itemid = i.itemid (+) AND d.itemid = r.item_id (+) AND d.bibid = iss.bibid (+) AND d.bibid = b.bib_id AND d.bibid = :bib_id ORDER BY l.location_id, d.units_sort_form desc, d.copyno ============= Offset into SQL: item_call_number i, item_class_v ic, (SELECT d1.itemid, MAX(h1.request_control_number) AS req_num FROM dbadmin.itemdetl2 d1, dbadmin.hlrcdetl h1 WHERE d1.itemid = h1.itemid AND d1.bibid = :bib_id GROUP BY d1.itemid) h, (SELECT h.bibid, count() as bib_req FROM hlrcdetl h WHERE h.itemid = 0 GROUP BY h.bibid) br, (SELECT h.itemid, count() as item_req FROM hlrcdetl h WHERE h.itemid <> 0 GROUP BY h.itemid) ir, (SELECT MAX(s.issue_id) AS latest_issue, h.bibid FROM serials_issue s, holdlink h WHERE h.bibid = :bib_id AND h.holdingsid = s.holdingsid GROUP BY h.bibid) iss, (SELECT DISTINCT item_id, item_class FROM reserve_item_v) r WHERE d.location = l.location_id AND d.itemclass = ic.item_class_id AND d.itemid = sd.itemid (+) AND sd.stat = s.status_code (+) AND d.itemid = c.itemid (+) AND d.itemid = h.itemid (+) AND d.bibid = br.bibid (+) AND d.itemid = ir.itemid (+) AND d.itemid = i.itemid (+) AND d.itemid = r.item_id (+) AND d.bibid = iss.bibid (+) AND d.bibid = b.bib_id AND d.bibid = :bib_id ORDER BY l.location_id, d.units_sort_form desc, d.copyno ============= Bind Variables: bib_id:string => 000135456
in /usr/local/vufind/module/VuFind/src/VuFind/ILS/Driver/Virtua.php:408 Stack trace: #0 [internal function]: VuFind\ILS\Driver\Virtua->getHolding() #1 /usr/local/vufind/module/VuFind/src/VuFind/ILS/Connection.php(1109): call_user_func_array() #2 /usr/local/vufind/module/VuFind/src/VuFind/ILS/Connection.php(1071): VuFind\ILS\Connection->__call() #3 /usr/local/vufind/module/VuFind/src/VuFind/ILS/Logic/Holds.php(215): VuFind\ILS\Connection->getHolding() #4 /usr/local/vufind/module/VuFind/src/VuFind/RecordDriver/Feature/IlsAwareTrait.php(114): VuFind\ILS\Logic\Holds->getHoldings() #5 /usr/local/vufind/themes/bootstrap3/templates/RecordTab/holdingsils.phtml(13): VuFind\RecordDriver\SolrMarc->getRealTimeHoldings() #6 /usr/local/vufind/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(505): include('/usr/local/vufi...') #7 /usr/local/vufind/module/VuFind/src/VuFind/View/Helper/Root/Record.php(361): Laminas\View\Renderer\PhpRenderer->render() #8 /usr/local/vufind/themes/bootstrap3/templates/record/view.phtml(74): VuFind\View\Helper\Root\Record->getTab() #9 /usr/local/vufind/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(505): include('/usr/local/vufi...') #10 /usr/local/vufind/vendor/laminas/laminas-view/src/View.php(206): Laminas\View\Renderer\PhpRenderer->render() #11 /usr/local/vufind/vendor/laminas/laminas-view/src/View.php(235): Laminas\View\View->render() #12 /usr/local/vufind/vendor/laminas/laminas-view/src/View.php(199): Laminas\View\View->renderChildren() #13 /usr/local/vufind/vendor/laminas/laminas-mvc/src/View/Http/DefaultRenderingStrategy.php(104): Laminas\View\View->render() #14 /usr/local/vufind/vendor/laminas/laminas-eventmanager/src/EventManager.php(331): Laminas\Mvc\View\Http\DefaultRenderingStrategy->render() #15 /usr/local/vufind/vendor/laminas/laminas-eventmanager/src/EventManager.php(180): Laminas\EventManager\EventManager->triggerListeners() #16 /usr/local/vufind/vendor/laminas/laminas-mvc/src/Application.php(366): Laminas\EventManager\EventManager->triggerEvent() #17 /usr/local/vufind/vendor/laminas/laminas-mvc/src/Application.php(347): Laminas\Mvc\Application->completeRequest() #18 /usr/local/vufind/public/index.php(71): Laminas\Mvc\Application->run() #19 {main}
(message) => ORA-00942: table or view does not exist

(sqltext) => SELECT d.itemid as item_id, d.copyno, d.barcode, c.due_date, s.name as status, s.status_code, l.name as location, l.location_id, b.call_number as bib_call_num, i.call_number as item_call_num, iss.latest_issue, r.item_class as reserve_item_class, ic.item_class, d.units, br.bib_req, ir.item_req FROM dbadmin.itemdetl2 d, dbadmin.location l, dbadmin.statdetl sd, dbadmin.item_status s, dbadmin.circdetl c, dbadmin.bibliographic_fields b, dbadmin.item_call_number i, item_class_v ic, (SELECT d1.itemid, MAX(h1.request_control_number) AS req_num FROM dbadmin.itemdetl2 d1, dbadmin.hlrcdetl h1 WHERE d1.itemid = h1.itemid AND d1.bibid = :bib_id GROUP BY d1.itemid) h, (SELECT h.bibid, count() as bib_req FROM hlrcdetl h WHERE h.itemid = 0 GROUP BY h.bibid) br, (SELECT h.itemid, count() as item_req FROM hlrcdetl h WHERE h.itemid <> 0 GROUP BY h.itemid) ir, (SELECT MAX(s.issue_id) AS latest_issue, h.bibid FROM serials_issue s, holdlink h WHERE h.bibid = :bib_id AND h.holdingsid = s.holdingsid GROUP BY h.bibid) iss, (SELECT DISTINCT item_id, item_class FROM reserve_item_v) r WHERE d.location = l.location_id AND d.itemclass = ic.item_class_id AND d.itemid = sd.itemid (+) AND sd.stat = s.status_code (+) AND d.itemid = c.itemid (+) AND d.itemid = h.itemid (+) AND d.bibid = br.bibid (+) AND d.itemid = ir.itemid (+) AND d.itemid = i.itemid (+) AND d.itemid = r.item_id (+) AND d.bibid = iss.bibid (+) AND d.bibid = b.bib_id AND d.bibid = :bib_id ORDER BY l.location_id, d.units_sort_form desc, d.copyno

[EreMaijala]

@targui2601 I'm afraid the Virtua driver hasn't been updated in a while, and it's possible that there have been significant changes in Virtua meanwhile. However, here's a super-wild shot in the dark: it seems like hlrcdetl could be an acronym for "holdings records details" or something like that. Can you see a table that would have anything in resemblance? If not, I'd look for a table that has same or similar columns (bibid, holdingsid, itemid).

[targui2601]

Thank you so much for reply, that is helpful for us to work-around.

[demiankatz]

@targui2601, if you are successful in getting things working, please feel free to open a pull request with your improvements. I don't think the current Virtua driver has been updated in more than a decade, and having a version that is known to work somewhere would be an improvement. It might also be worth reaching out to the team at National Library of Ireland (@eocarragain and @ekilfeather) in case they have any advice. I believe they are using a locally-customized Virtua driver which probably differs significantly from the default one bundled in the project, but perhaps they'd be willing to help us get the default version into a better state.

[ekilfeather]

Hi @targui2601 - sorry I was only just pointed to this comment now. We have done some modifications to the Virtua driver here;

https://gist.github.com/ekilfeather/4deac245d78d09be536598b4fb3d6e75

But as you can see we also reference the hlrcdetl table. Most of the customisation is to do with parsing the holdings dates for display. We last updated our Virtua instance to 2016 version but looking at the commits around that time we didn't make any changes to the table references in the driver so I'm not sure our code will be of great benefit to you. I see this discussion is marked as answered but if you are still having issues get back to us and we can see if we can help you troubleshoot.

[targui2601]

Hi Eoin Kilfeather, Thank you for your share. we still stuck with ILS offline status due to could not found "htlcdetl" table. We were success about connect to Virtua on Oct 2020. Until now we have not any updated Virtual anymore, some change are oracle version and vufind. My solution for testing is changing oracle version to 12 and then revert to old vufind version if not oracle version. Regards,

…

On Wed, Nov 9, 2022 at 4:22 PM Eoin Kilfeather ***@***.***> wrote: Hi @targui2601 <https://github.com/targui2601> - sorry I was only just pointed to this comment now. We have done some modifications to the Virtua driver here; https://gist.github.com/ekilfeather/4deac245d78d09be536598b4fb3d6e75 But as you can see we also reference the hlrcdetl table. Most of the customisation is to do with parsing the holdings dates for display. We last updated our Virtua instance to 2016 version but looking at the commits around that time we didn't make any changes to the table references in the driver so I'm not sure our code will be of great benefit to you. I see this discussion is marked as answered but if you are still having issues get back to us and we can see if we can help you troubleshoot. — Reply to this email directly, view it on GitHub <#2557 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABXMEESJG4BWQOS4T6VO4W3WHNULZANCNFSM6AAAAAAQWPCMW4> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[demiankatz]

@targui2601, the VuFind Virtua driver has changed very little over time (you can see from https://github.com/vufind-org/vufind/blame/dev/module/VuFind/src/VuFind/ILS/Driver/Virtua.php that most of the code hasn't changed at all in 5-10 years, and more recent changes are largely about style rather than content). Thus, it seems fairly unlikely to me that an earlier version of VuFind would work better than a more recent version, unless one of these formatting changes inadvertently introduced a significant bug. I think it's more likely that some external factor is the cause, if it used to work in the past but no longer works today. Of course, I could be wrong, and I'll be interested to hear what you find!

[ekilfeather]

Hi @targui2601, would agree with @demiankatz on this one - you may want to check with Virtua / Innovative as to which changes have been made to the tablespace in each version upgrade. I have to admit that the Virtua upgrade process isn't very transparent in any case but they may be able to help you troubleshoot?