11 Replies Latest reply: Mar 12, 2013 4:33 PM by clcarter RSS

    Reg : Connecting an XE database -

    ranit B
      Hi Experts,

      Suppose I install Oracle XE database in my machine. Then, will my other team members (from their machines) be able to connect it and use.

      Sorry, for not being able to phrase the question properly (much ignorance in this domain).

      Can anybody please give me some pointers to this?
      Please let me know if you need any further clarifications.

      note - We are on MS Windows 7 (X86) machines.

      Thanks,
      Ranit B.
        • 1. Re: Reg : Connecting an XE database -
          Paul M.
          will my other team members (from their machines) be able to connect it and use.
          Why not ? What's exactly the problem ?
          • 2. Re: Reg : Connecting an XE database -
            ranit B
            What's exactly the problem ?
            No problem as such.
            But had this doubt coz afaik we can't have multiple XEs installed in a single machine, I thought might be connecting to an XE database remotely is also not possible.

            Actually, I want to set up a local replica of our production database (for testing purposes). So, this should support many developers to simultaneously connect it.
            Another thing - the machine where I want to install is a normal MS Win7 and no server.

            Now do you see any issues?
            • 3. Re: Reg : Connecting an XE database -
              Paul M.
              Now do you see any issues?
              Not at all. Just remember that no Windows Home editions are certified for Oracle.
              • 4. Re: Reg : Connecting an XE database -
                ranit B
                no Windows Home editions are certified for Oracle.
                I didn't get that properly.

                What do you exactly mean by 'certified'?
                Some legal aspects or just as an OS with some robustness constraints?
                • 5. Re: Reg : Connecting an XE database -
                  Paul M.
                  Some legal aspects
                  No, technical ones. A system is certified when Oracle has tested it. Not certified systems may work or may not : if your OS is not certified and you have problems, you are on your own.
                  • 6. Re: Reg : Connecting an XE database -
                    ranit B
                    Thanks a lot, Paul.

                    Also, does mail sending mechanism need some special setting in XE?

                    I'm getting error on executing this -
                    DECLARE
                       v_conn        UTL_SMTP.connection;
                       v_smtp_host   VARCHAR2 (50)       := '127.0.0.1';
                       v_smtp_port   VARCHAR2 (10)       := 25;
                    BEGIN
                    
                        UTL_TCP.Close_All_Connections();
                    
                       v_conn :=
                             UTL_SMTP.open_connection ('127.0.0.1',                  -- smtp host
                                                       '25'                          -- smtp port
                                                           );
                       UTL_SMTP.helo (v_conn, '127.0.0.1');
                       UTL_SMTP.mail (v_conn, 'xyz@gmail.com');
                       UTL_SMTP.rcpt (v_conn, 'abc@gmail.com');
                       UTL_SMTP.open_data (v_conn);
                       UTL_SMTP.write_data (v_conn, 'testing : Hello Universe');
                       UTL_SMTP.close_data (v_conn);
                       UTL_SMTP.quit (v_conn);
                    EXCEPTION
                       WHEN OTHERS
                       THEN
                          DBMS_OUTPUT.put_line ('loc... ' || DBMS_UTILITY.format_error_backtrace);
                          DBMS_OUTPUT.put_line (SQLERRM);
                    END;
                    Error:
                    loc... ORA-06512: at "SYS.UTL_SMTP", line 54
                    ORA-06512: at "SYS.UTL_SMTP", line 138
                    ORA-06512: at "SYS.UTL_SMTP", line 197
                    ORA-06512: at line 9
                    ORA-29278: SMTP transient error: 421 Service not available
                    I just installed UTL_SMTP.sql but still getting this.
                    Any pointers?
                    • 7. Re: Reg : Connecting an XE database -
                      ranit B
                      re-opening this...
                      • 8. Re: Reg : Connecting an XE database -
                        Paul M.
                        ORA-29278: SMTP transient error: 421 Service not available
                        Did you try searching ?
                        • 9. Re: Reg : Connecting an XE database -
                          clcarter
                          error on executing this - ... v_smtp_host VARCHAR2 (50) := '127.0.0.1'; ... UTL_SMTP.helo (v_conn, '127.0.0.1');
                          So you're running a mailserver on localhost? Or not? That could explain the 'service not available' error.

                          Try pointing smtp_host to ... a host that has a running smtp service.

                          Back to the original problem ...
                          I thought might be connecting to an XE database remotely is also not possible.
                          Yes indeed it is possible.

                          If your database is up, and listener running, and your firewall program is not blocking the listener port, remote database connections work just fine, if the client is correctly configured.

                          Configuring a client means identifying the remote database host or IP address, listener port number, and database service name.

                          From your XE host, you can test remote connections to your database, look at how the client is setup, usually in tnsnames.ora. Test it using a command box with:
                          tnsping xe
                          ... TNS Ping Utility ... parameter files:... <client %ORACLE_HOME%>\network\admin\sqlnet.ora
                          Attempting to contact (DESCRIPTION=... host ... port ... service_name ...
                          OK (<n> msec)
                          # Or result can also be not OK, like:
                          TNS-12535: TNS:operation timed out # maybe invalid hostname, or host is offline/down
                          TNS-12541: TNS:no listener  # doh ... the listener is not running on that port at that host
                          # to verify the correct service_name:
                          sqlplus /nolog
                          connect <username>@xe
                          ... password ... Connected.
                          # or not. maybe:
                          ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect # doh client specified service_name is not correct
                          Know the remote database host, port, and service_name. Or the ORACLE_SID for the database instance could be used in place of service_name.

                          And on the database host, remote connection attempts will not be blocked by the firewall, to check if the firewall is blocking connections, a remote connect test is needed.
                          • 10. Re: Reg : Connecting an XE database -
                            ranit B
                            clcarter wrote:
                            error on executing this - ... v_smtp_host VARCHAR2 (50) := '127.0.0.1'; ... UTL_SMTP.helo (v_conn, '127.0.0.1');
                            So you're running a mailserver on localhost? Or not? That could explain the 'service not available' error.
                            I didn't get the question you are asking? How can i check if i'm running mailserver or not?
                            Try pointing smtp_host to ... a host that has a running smtp service.
                            How to do this?
                            My set up is just my MS Win7 laptop with XE installed.

                            Please help guys.
                            • 11. Re: Reg : Connecting an XE database -
                              clcarter
                              How to do this?
                              Install and configure a mailserver.

                              Or set your code to use the IP address or hostname of a working mail server. How to do that on Windows? I have no idea. Others might be able to offer better insight on that task, but this is the XE forum, not the sendmail forum.

                              Anyways, the code you're trying wants to chat with an SMTP server at IP 127.0.0.1. That is the localhost a.k.a. the loopback address, which is good for verifying whether or not TCP/IP is installed on the host. And not much else.
                              does mail sending mechanism need some special setting in XE?
                              Yes there is some setup required for sending email from the database.

                              In oracle 11g, ACL (Access Control List) must be configured before SMTP can work from the database, it gives "permission" for the database instance to reach the outside world for non-database services. If you wanted the database to chat with an Active Directory (MSAD) or LDAP directory service, same thing, ACL must be configured for that to work as well.

                              http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#sthref141

                              Note the XMLDB requirement, but that is OK since an out-of-the-box XE installation has xmldb, all ready to roll.