5 Replies Latest reply: Sep 28, 2007 7:02 AM by joelkallman-Oracle RSS

    DBMS_XDB.SETLISTENERLOCALACCESS (missing on Linux)

    502306
      I've installed Oracle Express on Linux with a GUI and need to enable remote clients

      oracle-xe-univ-10.2.0.1-1.0.i386.rpm

      This version does not have the stored procedure

      dbms_xdb.setListenerLocalAccess

      It does exist on the windows version.

      Is there a work around for this problem using the command line?
        • 1. Re: DBMS_XDB.SETLISTENERLOCALACCESS (missing on Linux)
          502306
          Sorry I meant to say without a gui.
          • 2. Re: DBMS_XDB.SETLISTENERLOCALACCESS (missing on Linux)
            joelkallman-Oracle
            "user499303",

            >> This version does not have the stored procedure

            Not a true statement. The same version of Oracle Application Express is used on the Windows and Linux versions of XE - same thing, byte for byte.

            The PL/SQL process in Application Express to enable/disable local HTTP access is dbms_xdb.setListenerLocalAccess. So it is there.

            If you search for this on the XE forum, you'll find postings that mention this procedure and successful execution of it on Linux systems.

            http://forums.oracle.com/forums/search.jspa?objID=f251&q=setListenerLocalAccess

            Joel
            • 3. Re: DBMS_XDB.SETLISTENERLOCALACCESS (missing on Linux)
              502306
              Windows:

              SQL> desc dbms_xdb
              FUNCTION ACLCHECKPRIVILEGES RETURNS BINARY_INTEGER
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ACL_PATH VARCHAR2 IN
              OWNER VARCHAR2 IN
              PRIVS XMLTYPE IN
              PROCEDURE APPENDRESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA XMLTYPE IN
              PROCEDURE APPENDRESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA REF OF XMLTYPE IN
              FUNCTION CFG_GET RETURNS XMLTYPE
              PROCEDURE CFG_REFRESH
              PROCEDURE CFG_UPDATE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              XDBCONFIG XMLTYPE IN
              FUNCTION CHANGEPRIVILEGES RETURNS BINARY_INTEGER
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              RES_PATH VARCHAR2 IN
              ACE XMLTYPE IN
              FUNCTION CHECKPRIVILEGES RETURNS BINARY_INTEGER
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              RES_PATH VARCHAR2 IN
              PRIVS XMLTYPE IN
              PROCEDURE CLEANSGAFORUPGRADE
              FUNCTION CREATEFOLDER RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              FUNCTION CREATEOIDPATH RETURNS VARCHAR2
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              OID RAW IN
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA VARCHAR2 IN
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA XMLTYPE IN
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA REF OF XMLTYPE IN
              STICKY BOOLEAN IN DEFAULT
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA CLOB IN
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA BINARY FILE LOB IN
              CSID NUMBER IN DEFAULT
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA BLOB IN
              CSID NUMBER IN DEFAULT
              PROCEDURE DELETERESOURCE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DELETE_OPTION BINARY_INTEGER IN DEFAULT
              PROCEDURE DELETERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              METADATA REF OF XMLTYPE IN
              DELETE_OPTION BINARY_INTEGER IN DEFAULT
              PROCEDURE DELETERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              METADATANS VARCHAR2 IN
              METADATANAME VARCHAR2 IN
              DELETE_OPTION BINARY_INTEGER IN DEFAULT
              FUNCTION EXISTSRESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              FUNCTION GETACLDOCUMENT RETURNS XMLTYPE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              FUNCTION GETFTPPORT RETURNS NUMBER
              FUNCTION GETHTTPPORT RETURNS NUMBER
              PROCEDURE GETLISTENERENDPOINT
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ENDPOINT NUMBER IN
              HOST VARCHAR2 OUT
              PORT NUMBER OUT
              PROTOCOL NUMBER OUT
              PROCEDURE GETLOCKTOKEN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              LOCKTOKEN VARCHAR2 OUT
              FUNCTION GETPRIVILEGES RETURNS XMLTYPE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              RES_PATH VARCHAR2 IN
              FUNCTION GETRESOID RETURNS RAW
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              FUNCTION GETXDB_TABLESPACE RETURNS VARCHAR2
              PROCEDURE LINK
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              SRCPATH VARCHAR2 IN
              LINKFOLDER VARCHAR2 IN
              LINKNAME VARCHAR2 IN
              FUNCTION LOCKRESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DEPTHZERO BOOLEAN IN
              SHARED BOOLEAN IN
              PROCEDURE MIGRATEALLXMLFROM9201
              PROCEDURE MIGRATECOLUMNFROM9201
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              OWNER VARCHAR2 IN
              TABLE_NAME VARCHAR2 IN
              COLUMN_NAME VARCHAR2 IN
              PROCEDURE MIGRATETABLEFROM9201
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              OWNER VARCHAR2 IN
              TABLE_NAME VARCHAR2 IN
              PROCEDURE MOVEXDB_TABLESPACE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              NEW_TABLESPACE VARCHAR2 IN
              PROCEDURE PURGERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              PROCEDURE REBUILDHIERARCHICALINDEX
              PROCEDURE RENAMERESOURCE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              SRCPATH VARCHAR2 IN
              DESTFOLDER VARCHAR2 IN
              NEWNAME VARCHAR2 IN
              PROCEDURE SETACL
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              RES_PATH VARCHAR2 IN
              ACL_PATH VARCHAR2 IN
              PROCEDURE SETFTPPORT
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              NEW_PORT NUMBER IN
              PROCEDURE SETHTTPPORT
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              NEW_PORT NUMBER IN
              PROCEDURE SETLISTENERENDPOINT
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ENDPOINT NUMBER IN
              HOST VARCHAR2 IN
              PORT NUMBER IN
              PROTOCOL NUMBER IN
              PROCEDURE SETLISTENERLOCALACCESS
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              L_ACCESS BOOLEAN IN
              FUNCTION UNLOCKRESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DELTOKEN VARCHAR2 IN
              PROCEDURE UPDATERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              OLDMETADATA REF OF XMLTYPE IN
              NEWMETADATA REF OF XMLTYPE IN
              PROCEDURE UPDATERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              OLDMETADATA REF OF XMLTYPE IN
              NEWMETADATA XMLTYPE IN
              PROCEDURE UPDATERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              OLDNS VARCHAR2 IN
              OLDNAME VARCHAR2 IN
              NEWMETADATA XMLTYPE IN
              PROCEDURE UPDATERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              OLDNS VARCHAR2 IN
              OLDNAME VARCHAR2 IN
              NEWMETADATA REF OF XMLTYPE IN


              Linux

              SQL> desc dbms_xdb
              FUNCTION ACLCHECKPRIVILEGES RETURNS BINARY_INTEGER
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ACL_PATH VARCHAR2 IN
              OWNER VARCHAR2 IN
              PRIVS XMLTYPE IN
              PROCEDURE APPENDRESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA XMLTYPE IN
              PROCEDURE APPENDRESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA REF OF XMLTYPE IN
              FUNCTION CFG_GET RETURNS XMLTYPE
              PROCEDURE CFG_REFRESH
              PROCEDURE CFG_UPDATE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              XDBCONFIG XMLTYPE IN
              FUNCTION CHANGEPRIVILEGES RETURNS BINARY_INTEGER
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              RES_PATH VARCHAR2 IN
              ACE XMLTYPE IN
              FUNCTION CHECKPRIVILEGES RETURNS BINARY_INTEGER
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              RES_PATH VARCHAR2 IN
              PRIVS XMLTYPE IN
              PROCEDURE CLEANSGAFORUPGRADE
              FUNCTION CREATEFOLDER RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              FUNCTION CREATEOIDPATH RETURNS VARCHAR2
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              OID RAW IN
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA VARCHAR2 IN
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA XMLTYPE IN
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA REF OF XMLTYPE IN
              STICKY BOOLEAN IN DEFAULT
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA CLOB IN
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA BINARY FILE LOB IN
              CSID NUMBER IN DEFAULT
              FUNCTION CREATERESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DATA BLOB IN
              CSID NUMBER IN DEFAULT
              PROCEDURE DELETERESOURCE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DELETE_OPTION BINARY_INTEGER IN DEFAULT
              PROCEDURE DELETERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              METADATA REF OF XMLTYPE IN
              DELETE_OPTION BINARY_INTEGER IN DEFAULT
              PROCEDURE DELETERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              METADATANS VARCHAR2 IN
              METADATANAME VARCHAR2 IN
              DELETE_OPTION BINARY_INTEGER IN DEFAULT
              FUNCTION EXISTSRESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              FUNCTION GETACLDOCUMENT RETURNS XMLTYPE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              FUNCTION GETFTPPORT RETURNS NUMBER
              FUNCTION GETHTTPPORT RETURNS NUMBER
              PROCEDURE GETLOCKTOKEN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              LOCKTOKEN VARCHAR2 OUT
              FUNCTION GETPRIVILEGES RETURNS XMLTYPE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              RES_PATH VARCHAR2 IN
              FUNCTION GETRESOID RETURNS RAW
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              FUNCTION GETXDB_TABLESPACE RETURNS VARCHAR2
              PROCEDURE LINK
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              SRCPATH VARCHAR2 IN
              LINKFOLDER VARCHAR2 IN
              LINKNAME VARCHAR2 IN
              FUNCTION LOCKRESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DEPTHZERO BOOLEAN IN
              SHARED BOOLEAN IN
              PROCEDURE MIGRATEALLXMLFROM9201
              PROCEDURE MIGRATECOLUMNFROM9201
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              OWNER VARCHAR2 IN
              TABLE_NAME VARCHAR2 IN
              COLUMN_NAME VARCHAR2 IN
              PROCEDURE MIGRATETABLEFROM9201
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              OWNER VARCHAR2 IN
              TABLE_NAME VARCHAR2 IN
              PROCEDURE MOVEXDB_TABLESPACE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              NEW_TABLESPACE VARCHAR2 IN
              PROCEDURE PURGERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              PROCEDURE REBUILDHIERARCHICALINDEX
              PROCEDURE RENAMERESOURCE
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              SRCPATH VARCHAR2 IN
              DESTFOLDER VARCHAR2 IN
              NEWNAME VARCHAR2 IN
              PROCEDURE SETACL
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              RES_PATH VARCHAR2 IN
              ACL_PATH VARCHAR2 IN
              PROCEDURE SETFTPPORT
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              NEW_PORT NUMBER IN
              PROCEDURE SETHTTPPORT
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              NEW_PORT NUMBER IN
              FUNCTION UNLOCKRESOURCE RETURNS BOOLEAN
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              DELTOKEN VARCHAR2 IN
              PROCEDURE UPDATERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              OLDMETADATA REF OF XMLTYPE IN
              NEWMETADATA REF OF XMLTYPE IN
              PROCEDURE UPDATERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              OLDMETADATA REF OF XMLTYPE IN
              NEWMETADATA XMLTYPE IN
              PROCEDURE UPDATERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              OLDNS VARCHAR2 IN
              OLDNAME VARCHAR2 IN
              NEWMETADATA XMLTYPE IN
              PROCEDURE UPDATERESOURCEMETADATA
              Argument Name Type In/Out Default?
              ------------------------------ ----------------------- ------ --------
              ABSPATH VARCHAR2 IN
              OLDNS VARCHAR2 IN
              OLDNAME VARCHAR2 IN
              NEWMETADATA REF OF XMLTYPE IN

              From these descriptions you can see that
              PROCEDURE SETLISTENERENDPOINT
              and
              PROCEDURE SETLISTENERLOCALACCESS
              are missing on LINUX
              (oracle-xe-univ-10.2.0.1-1.0.i386.rpm)

              Error when attempting
              exec dbms_xdb.setlistenerlocalaccess(false);

              SQL> exec dbms_xdb.setlistenerlocalaccess(false);
              BEGIN dbms_xdb.setlistenerlocalaccess(false); END;

              *
              ERROR at line 1:
              ORA-06550: line 1, column 16:
              PLS-00302: component 'SETLISTENERLOCALACCESS' must be declared
              ORA-06550: line 1, column 7:
              PL/SQL: Statement ignored

              A description of the package in Linux and Windows does not agree with your comment that this is identical on both systems. I cant comment about whether the UI works on Linux or not since have not access to a local browser.

              Thank you for your assistance.
              • 4. Re: DBMS_XDB.SETLISTENERLOCALACCESS (missing on Linux)
                502306
                It turns out on Linux the step SETLISTENERLOCALACCESS is not necessary
                however the url used to access the service was not 'apex' but

                http://<assigned-ip-address>/htmldb

                I had made the following change although I dont know if it is necessary or not

                alter system
                set local_listener='(address = (protocol=TCP)
                (host=<assigned-ip-address>)(port=1521))';

                alter system register;

                where
                <assigned-ip-address> is something like 192.168.1.2
                • 5. Re: DBMS_XDB.SETLISTENERLOCALACCESS (missing on Linux)
                  joelkallman-Oracle
                  I downloaded and installed Universal XE for Linux.
                  -bash-3.1$ uname -a
                  Linux dhcp-columbus-east-10-143-140-184.us.oracle.com 2.6.18-8.1.10.0.1.el5 #1 SMP Thu Sep 13 17:11:00 EDT 2007 i686 i686 i386 GNU/Linux
                  -bash-3.1$ sqlplus /nolog

                  SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 27 16:05:36 2007

                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.

                  SQL> connect / as sysdba;
                  Connected.
                  SQL> select * from v$version;

                  BANNER
                  ----------------------------------------------------------------
                  Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
                  PL/SQL Release 10.2.0.1.0 - Production
                  CORE    10.2.0.1.0      Production
                  TNS for Linux: Version 10.2.0.1.0 - Production
                  NLSRTL Version 10.2.0.1.0 - Production

                  SQL> exec dbms_xdb.setlistenerlocalaccess( FALSE );

                  PL/SQL procedure successfully completed.

                  SQL>
                  I have no issues with it.

                  Joel