This discussion is archived
11 Replies Latest reply: Mar 12, 2013 2:33 PM by clcarter RSS

Reg : Connecting an XE database -

ranit B Expert
Currently Being Moderated
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. Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    re-opening this...
  • 8. Re: Reg : Connecting an XE database -
    Paul M. Oracle ACE
    Currently Being Moderated
    ORA-29278: SMTP transient error: 421 Service not available
    Did you try searching ?
  • 9. Re: Reg : Connecting an XE database -
    clcarter Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points