{"id":17099,"date":"2022-02-02T11:09:39","date_gmt":"2022-02-02T10:09:39","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/"},"modified":"2022-02-02T11:09:39","modified_gmt":"2022-02-02T10:09:39","slug":"ords-with-tns","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/","title":{"rendered":"ORDS with TNS"},"content":{"rendered":"

The aim of that blog is to look into Oracle REST Data Services (ORDS)<\/a> DB connection with TNS. This is following some experience at a customer who wanted to move to latest version of ORDS with APEX 21.2, in late 2021, and run into an issue.
\n
\nFor general presentation about ORDS installation please see the blog<\/a> of my colleague Lazhar Felahi<\/a> on that topic.
\n 
\nDuring installation or upgrade of ORDS you have to select the DB connection mode between following values:<\/p>\n

\nSpecify the database connection type to use.\nEnter number for [1] Basic [2] TNS [3] Custom URL [1]:\n<\/pre>\n
If you are using DataGuard or RAC, you will have to choose the TNS option [2] in order to benefit from the DB features in terms of availability. I would even recommend to use TNS as general rule, as this is mostly defined and provided by your DBA for connecting your client to the DB.
\nTo make use of TNS you have to provide the service name (TNS Network Alias) and the location of your tnsnames.ora file (TNS folder location) during the setup process.
\nUntil version 21.1.3<\/a> of ORDS that feature was working nicely.
\nUnfortunately, starting with version 21.2<\/a> some error is raised when trying to update or newly install ORDS with DB connection over TNS, at the time the connection is used.<\/p>\n
\noracle.net.resolver.EZConnectResolver.parseExtendedProperties Extended settings parsing failed.\n java.lang.RuntimeException: Invalid character at 24 : \n...\noracle.dbtools.common.logging.JDKLogSink.record IO Error: Invalid connection string format, \na valid format is: \"host:port:sid\" (CONNECTION_ID=X8ADepYuRpK1p8FSAKp41Q==),|apex||...,\njdbc:oracle:thin:@myDB?TNS_ADMIN=D:AppOracletns\n...\noracle.dbtools.common.logging.JDKLogSink.record The pool named: |apex|| is invalid and will be ignored: \nORDS was unable to make a connection to the database. \nThis can occur if the database is unavailable, the maximum number of sessions has been reached\nor the pool is not correctly configured. The connection pool named: |apex|| had the following error(s): \nIO Error: Invalid connection string format, a valid format is: \"host:port:sid\"\n...\n<\/pre>\n
That issue is still present in versions 21.2.4<\/a>, 21.3<\/a>, 21.3.1<\/a> and 21.4<\/a>
\nIt was reported to Oracle by opening and SR with the support, by my customer, and a bug was registered: Bug 33583853 – FAILED TO CONNECT TO USER: ORDS_PUBLIC_USER URL: JDBC:ORACLE:THIN:@MYDB?TNS_ADMIN
\nFortunately, there is a workaround in using the option [3] Custom URL, until the bug fix is implemented.
\nWhen you make use of that option, you have to build your own Java connection string which looks as following:<\/p>\n
\njdbc:oracle:thin:@MY_TNS_ALIAS?TNS_ADMIN=D:\\Oracle\\tns\\admin\n<\/pre>\n
Where MY_TNS_ALIAS is the alias defined in your tnsnames.ora file and D:\\Oracle\\tns\\admin is the path to your tnsnames.ora file (in this case on a Windows server so the ” needs to be doubled). This is working fine on all previously listed ORDS versions.
\nYou can have a look into Oracle Community Discussion<\/a> for details on the testing and the solution.
\nAccording to Jeff Smith the bug resolution is provided “…before the Groundhog gets his day in PA.” which is today, February 2nd.
\nVersion 21.4.1<\/a> was released.
\nAfter testing, I can confirm that this release is fixing the issue and behavior is back to normal. Thanks Oracle for the fix.
\nSpecial thanks to Jeff Smith<\/a>, the Oracle product manager of ORDS, for the clarifications given and the follow-up on the bug resolution.
\n 
\nSo if you want to use TNS without the Custom URL, stay on ORDS version prior to 21.2 or move to version 21.4.1<\/strong>.
\nI hope that blog will be helpful, if you run into similar issue. Enjoy ORDS.<\/p>\n
Note:<\/strong> If you are looking for details about licencing please have look into that blog<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
The aim of that blog is to look into Oracle REST Data Services (ORDS) DB connection with TNS. This is following some experience at a customer who wanted to move to latest version of ORDS with APEX 21.2, in late 2021, and run into an issue.<\/p>\n","protected":false},"author":17,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197,59],"tags":[1545,1352,2470],"type_dbi":[],"class_list":["post-17099","post","type-post","status-publish","format-standard","hentry","category-application-integration-middleware","category-oracle","tag-error","tag-ords","tag-tns"],"acf":[],"yoast_head":"\nORDS with TNS - dbi Blog<\/title>\n<meta name=\"description\" content=\"ORDS with TNS connection to DB. What if Invalid character and Invalid connection string error? Workaround and solution. Versions 21.2 to 21.4\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ORDS with TNS\" \/>\n<meta property=\"og:description\" content=\"ORDS with TNS connection to DB. What if Invalid character and Invalid connection string error? Workaround and solution. Versions 21.2 to 21.4\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-02-02T10:09:39+00:00\" \/>\n<meta name=\"author\" content=\"Alain Lacour\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Alain Lacour\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/\"},\"author\":{\"name\":\"Alain Lacour\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/acabba2b6ca473b44b098c2de61294b0\"},\"headline\":\"ORDS with TNS\",\"datePublished\":\"2022-02-02T10:09:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/\"},\"wordCount\":474,\"commentCount\":0,\"keywords\":[\"Error\",\"ORDS\",\"TNS\"],\"articleSection\":[\"Application integration & Middleware\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/\",\"name\":\"ORDS with TNS - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-02-02T10:09:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/acabba2b6ca473b44b098c2de61294b0\"},\"description\":\"ORDS with TNS connection to DB. What if Invalid character and Invalid connection string error? Workaround and solution. Versions 21.2 to 21.4\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ORDS with TNS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/acabba2b6ca473b44b098c2de61294b0\",\"name\":\"Alain Lacour\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/455c4c7743e192ae868a6f9e6ed875415c5887567577eccbbbf2d65012cd976a?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/455c4c7743e192ae868a6f9e6ed875415c5887567577eccbbbf2d65012cd976a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/455c4c7743e192ae868a6f9e6ed875415c5887567577eccbbbf2d65012cd976a?s=96&d=mm&r=g\",\"caption\":\"Alain Lacour\"},\"description\":\"Alain Lacour has more than 15 years of experience in application development with Oracle technologies. He is specialized in APEX, Java, and PL\/SQL development. His expertise also includes middleware technologies such as Oracle Weblogic and EMC Documentum. He also has a broad experience in business analysis and architecture solutions as well as in service and project management. Prior to dbi services, Alain Lacour was Senior Consultant at IMS Health in Basel. He also worked as a Software Engineer for M&H Informatics in Basel. Alain Lacour holds a Master of Science in Industrial Computing from the University of Mulhouse (F). His branch-related experience mainly covers the pharma industry, with an in-depth knowledge of the drug development process as well as drug distribution.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/alain-lacour\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"ORDS with TNS - dbi Blog","description":"ORDS with TNS connection to DB. What if Invalid character and Invalid connection string error? Workaround and solution. Versions 21.2 to 21.4","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/","og_locale":"en_US","og_type":"article","og_title":"ORDS with TNS","og_description":"ORDS with TNS connection to DB. What if Invalid character and Invalid connection string error? Workaround and solution. Versions 21.2 to 21.4","og_url":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/","og_site_name":"dbi Blog","article_published_time":"2022-02-02T10:09:39+00:00","author":"Alain Lacour","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Alain Lacour","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/"},"author":{"name":"Alain Lacour","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/acabba2b6ca473b44b098c2de61294b0"},"headline":"ORDS with TNS","datePublished":"2022-02-02T10:09:39+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/"},"wordCount":474,"commentCount":0,"keywords":["Error","ORDS","TNS"],"articleSection":["Application integration & Middleware","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/","url":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/","name":"ORDS with TNS - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-02-02T10:09:39+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/acabba2b6ca473b44b098c2de61294b0"},"description":"ORDS with TNS connection to DB. What if Invalid character and Invalid connection string error? Workaround and solution. Versions 21.2 to 21.4","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/ords-with-tns\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"ORDS with TNS"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/acabba2b6ca473b44b098c2de61294b0","name":"Alain Lacour","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/455c4c7743e192ae868a6f9e6ed875415c5887567577eccbbbf2d65012cd976a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/455c4c7743e192ae868a6f9e6ed875415c5887567577eccbbbf2d65012cd976a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/455c4c7743e192ae868a6f9e6ed875415c5887567577eccbbbf2d65012cd976a?s=96&d=mm&r=g","caption":"Alain Lacour"},"description":"Alain Lacour has more than 15 years of experience in application development with Oracle technologies. He is specialized in APEX, Java, and PL\/SQL development. His expertise also includes middleware technologies such as Oracle Weblogic and EMC Documentum. He also has a broad experience in business analysis and architecture solutions as well as in service and project management. Prior to dbi services, Alain Lacour was Senior Consultant at IMS Health in Basel. He also worked as a Software Engineer for M&H Informatics in Basel. Alain Lacour holds a Master of Science in Industrial Computing from the University of Mulhouse (F). His branch-related experience mainly covers the pharma industry, with an in-depth knowledge of the drug development process as well as drug distribution.","url":"https:\/\/www.dbi-services.com\/blog\/author\/alain-lacour\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17099","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17099"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17099\/revisions"}],"predecessor-version":[{"id":29024,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17099\/revisions\/29024"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17099"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17099"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17099"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17099"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}