Skip to main content
ExLibris
  • Subscribe by RSS
  • ExLibris Dev

    Voyager Stored Functions in Oracle are not accesible to Read-Only user

    • Article Type: General
    • Product: Voyager
    • Product Version: 8.1.2

    Description:
    Module(s): Oracle Read-Only User Script (Packaging)
    Server platform(s) affected: All

    Release(s) replicated in: Voyager 7.2.1
    Last version without bug (if applicable): n/a

    Expected results: Read-only user should have access to Voyager stored functions in Oracle (as documented in Chapter 31 of the Technical Manual).

    Example of what you should see as the read-only user, using the read-write user here:

    [VGER] voyager@supzv721 : ini/ => sqlplus
    SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 17 09:51:19 2010
    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    Enter user-name: voydb
    Enter password:

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> desc getBibBlob
    FUNCTION getBibBlob RETURNS CLOB
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    INTBIBID BINARY_INTEGER IN

    SQL> desc getMFHDBlob
    FUNCTION getMFHDBlob RETURNS CLOB
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    INTMFHDID BINARY_INTEGER IN

    SQL> desc getAUTHBlob
    FUNCTION getAUTHBlob RETURNS CLOB
    Argument Name Type In/Out Default?
    ------------------------------ ----------------------- ------ --------
    INTAUTHID BINARY_INTEGER IN



    <BUG:ACTUAL>
    Actual results: Read-only user script does not generate synonyms for stored procedures, so the read-only user does not have access to them.
    </BUG:ACTUAL>

    Example of the inability of the read-only user to access the stored procedures:

    [VGER] voyager@supzv721 : ini/ => sqlplus
    SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 17 09:46:11 2010
    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    Enter user-name: ro_voydb
    Enter password:

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> desc getBibBlob
    ERROR:
    ORA-04043: object getBibBlob does not exist

    SQL> desc getMFHDBlob
    ERROR:
    ORA-04043: object getMFHDBlob does not exist

    SQL> desc getAUTHBlob
    ERROR:
    ORA-04043: object getAUTHBlob does not exist

    Workflow implications: Read-Only user cannot access Oracle stored functions; customer-built scripts that use these functions will not work.

    Replication steps:
    1) Run the read-only user script from the Voyager package to create/refresh the ro_xxxdb user
    2) Log into sqlplus as ro_xxxdb/ro_xxxdb
    3) Run sql to display user's synonym for the procedures:
    a. desc getBibBlob
    b. desc getMFHDBlob
    c. desc getAUTHBlob
    d. … (see below for the rest)
    4) You will see errors that the procedures don't exist
    5) Log in as the application's read-write user, and they will exist

    Workaround: Manually create synonyms / grant permission for the ro user on these procedures. Or use the fully qualified tablespace name: "voydb.getAuthBlob", etc.

    Resolution:
    Fixed in Voyager 8.1.2

    Additional Information

    Other information: Oracle Stored Functions requested: MARC Functions: ? GetAllAuthTag This function returns all tags for an Auth_Id ? GetAllBibTag This function returns all tags for a Bib_Id ? GetAllMFHDTag This function returns all tags for a MFHD_Id ? GetAuthTag This function returns the MARC field for an Auth_Id and tag ? GetBibTag This function returns the MARC field for a Bib_Id and tag ? GetMFHDSubfield This function returns the MARC subfield inormation for a specified MFHD_ID, tag, and subfield ? GetMFHDTag This function returns the MARC field for a MFHD_Id and tag Advanced MARC Functions: ? GetAllTags This function returns all matching tags ? getAuthBlob This function extracts raw authority blob information for a specific MARC record ? getBibBlob This function extracts raw bibliographic blob information for a specific MARC record ? GetMarcField This function populates the inputs to GetMarcField ( ) and returns the MARC field ? getMFHDBlob This function extracts raw holdings blob information for a specific MARC record ? GetTag This function populates the inputs to MARCField ( ) and returns the MARC field Miscellaneous Functions: ? getBaseCurrencyCode This function returns the base currency code ? getCallNoClass This function returns the class of the call number ? getConversionRate This function returns a currency conversion rate ? getFirstPatronBarcodeID This function returns the first active patron group for a patron on the basis of status and date ? getItemBarcode This function returns the active item Barcode ? getLatestItemStatus This function returns the ID of the current status of an item ? getPatronActiveAdress This function returns the active address ID for a patron ? getPatronEmailAddress This function returns the active email address for a patron ? setCurrencyDecimals This function uses the Currency_Code to convert the database representation of an amount to a more familiar currency amount ? setConvDecimals This function converts an integer Rate to be a floating-point number ? toBaseCurrency This function converts the database representation of an amount to a base currency amount ? truncField This function limits strings to 4000 characters (the maximum number of characters that can be Output by SQLPLUS)


    • Article last edited: 3/16/2015
    //doorbell.io feedback widged