Forum Stats

  • 3,750,348 Users
  • 2,250,159 Discussions
  • 7,866,944 Comments

Discussions

Categories

Get Oracle server version and SQL session id with OCIAttrGet()

user11763611
user11763611 Member Posts: 38 Red Ribbon
edited Nov 17, 2017 8:01AM in Oracle Call Interface (OCI)

Hi all!

After connecting to Oracle using OCI, we need to know the current server version, and the user session id.

I searched then OCI doc, but could not find anything about that.

Is there a way to get that information, without executing SQL?

Obviously for now we do some SQL like:

  SELECT banner FROM v$version WHERE banner LIKE 'CORE%'

  SELECT userenv('SESSIONID') FROM dual

But if we could avoid that it would speed the connection steps.

Thanks!

Seb

· Share on TwitterShare on Facebook

Best Answer

Answers

  • Sdhamoth-Oracle
    Sdhamoth-Oracle Member Posts: 184 Employee
    edited Nov 15, 2017 8:55AM Accepted Answer

    OCIServerVersion() will help you to get the server version and banner.

    · Share on TwitterShare on Facebook
  • user11763611
    user11763611 Member Posts: 38 Red Ribbon
    edited Nov 15, 2017 11:26AM

    Thanks for your quick answer!

    I see there is also an OCIServerRelease() function...

    Can someone explain what is done by these functions?

    If internally OCI executes SQL to get the information, it will not help much.

    Seb

    · Share on TwitterShare on Facebook
  • Sdhamoth-Oracle
    Sdhamoth-Oracle Member Posts: 184 Employee
    edited Nov 15, 2017 11:33AM

    It won't execute that SQL.

    · Share on TwitterShare on Facebook
  • user11763611
    user11763611 Member Posts: 38 Red Ribbon
    edited Nov 15, 2017 11:35AM

    Understand: I am looking for the fastest solution to get the server version and session id...

    If it takes a client-server round-trip it's a pity... we are already connected...

    Linux strace shows some activity on the socket to the server so I suspect OCI asks the server:

    write(2, ">> MARK-1\n", 10>> MARK-1

    )             = 10

    write(6, "\0\0\0<\6\0\0\0\0\0\21k\4\"\0\0\0\201\275\0\0\1\0\0\0\3;\5\376\377\377\377"..., 60) = 60

    read(6, "\0\0\0\272\6\0\0\0\0\0\10\241\0\241Oracle Database 12"..., 8208) = 186

    write(2, ">> MARK-2\n", 10>> MARK-2

    :-(

    · Share on TwitterShare on Facebook
  • Sdhamoth-Oracle
    Sdhamoth-Oracle Member Posts: 184 Employee
    edited Nov 15, 2017 11:55AM

    OCIServerRelease() may not make a server round trip. Please try that.

    · Share on TwitterShare on Facebook
  • user11763611
    user11763611 Member Posts: 38 Red Ribbon
    edited Nov 16, 2017 4:39AM

    I have already checked: The trace output I provided was produced from OCIServerRelease(), which makes a server round trip.

    It is still an option for us, if it takes less time than a SELECT to execute.

    Seb

    · Share on TwitterShare on Facebook
  • user11763611
    user11763611 Member Posts: 38 Red Ribbon
    edited Nov 16, 2017 9:02AM

    After some testing (executing 100 processes in sequence, using client/server config over TCP):

    1) Regular OCIStmtPrepare + OCIStmtExecute + OCIDefineByPos +  OCIStmtFetch of SELECT to get Oracle version: 3.7 secs

    2) Using OCIServerRelease(): 3.3 secs.

    A little better but if all information would be available on client side after connection that would certainly be faster.

    Using OCIServerRelease() simplifies the code, thought.

    Seb

    · Share on TwitterShare on Facebook
  • Sdhamoth-Oracle
    Sdhamoth-Oracle Member Posts: 184 Employee
    edited Nov 16, 2017 9:12AM

    Have you tried fetching the details about database using OCIDescribeAny() with type OCI_PTYPE_DATABASE?

    DB Version will can be read using OCIAttrGet() API from attribute OCI_ATTR_VERSION.

    · Share on TwitterShare on Facebook
  • user11763611
    user11763611 Member Posts: 38 Red Ribbon
    edited Nov 17, 2017 8:01AM

    Nope, will try OCIDescribeAny(), thanks for the tip!

    Seb

    · Share on TwitterShare on Facebook
This discussion has been closed.