{"id":9392,"date":"2019-11-14T13:21:09","date_gmt":"2019-11-14T16:21:09","guid":{"rendered":"http:\/\/kb.elipse.com.br\/en\/?p=9392"},"modified":"2019-11-14T13:21:09","modified_gmt":"2019-11-14T16:21:09","slug":"kb-97376-error-when-executing-stored-procedure-in-oracle-db","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/","title":{"rendered":"KB-97376: Error when executing Stored Procedure in Oracle DB."},"content":{"rendered":"<h2><b>Question:<\/b><\/h2>\n<div>\n<p>How can I work around a syntax error in a stored procedure in an Oracle database?<\/p>\n<p>I developed a <a href=\"https:\/\/www.w3schools.com\/sql\/sql_stored_procedures.asp\" target=\"_blank\" rel=\"noopener noreferrer\">stored procedure<\/a> in an <a href=\"https:\/\/www.oracle.com\/technetwork\/pt\/database\/express-edition\/overview\/index.html\" target=\"_blank\" rel=\"noopener noreferrer\">Oracle 11g<\/a> databse which returns an OUTPUT parameter. Therefore, <b>in SQL Developer<\/b>, this is how the stored procedure is executed:<\/p>\n<pre>VARIABLE P_TOTAL VARCHAR2(200);\r\nexecute calcula_valores(10,:P_TOTAL);\r\nprint :P_TOTAL;<\/pre>\n<p>Then, the following result is displayed:<\/p>\n<\/div>\n<div><\/div>\n<div>\n<div align=\"center\"><img loading=\"lazy\" title=\"\" src=\"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID5499\/1.png\" alt=\"\" width=\"540\" height=\"401\" align=\"Baseline\" border=\"0\" \/><\/div>\n<p>Soon after, the <a href=\"https:\/\/kb.elipse.com.br\/en\/category\/english\/elipse-e3-en\/e3query-2\/\" target=\"_blank\" rel=\"noopener noreferrer\">E3Query<\/a> executes the stored procedure like in the example below:<\/p>\n<pre>VARIABLE P_TOTAL VARCHAR(200)\r\nCall CALCULA_VALORES(10, :P_TOTAL)\r\nSELECT P_TOTAL as Retorno<\/pre>\n<p>However, there is an SQL syntax error, which records the following message in the log:<\/p>\n<pre><i>('ora11preto') ErrorLocal = OpenRecordset() Error #0x80040E14 Description:ORA-00900: instru\u00e7\u00e3o SQL inv\u00e1lida\r\n(Source: Microsoft OLE DB Provider for Oracle) (SQL State: (null)) (NativeError: 900)<\/i><\/pre>\n<p>So, how can I return the value of this stored procedure properly via <strong>Elipse E3<\/strong>?<\/p>\n<\/div>\n<div><\/div>\n<h2><b>Solution:<\/b><\/h2>\n<div>\n<div align=\"justify\">First of all, when in the <a href=\"https:\/\/kb.elipse.com.br\/en\/structure-query-language-sql-introduction\/\" target=\"_blank\" rel=\"noopener noreferrer\">SQL Server<\/a> environment, we know it&#8217;s possible to create a <a href=\"https:\/\/kb.elipse.com.br\/structure-query-language-sql-chapter-9-stored-procedures\/\" target=\"_blank\" rel=\"noopener noreferrer\">stored procedure<\/a> that passes input and return parameters; this stored procedure will then be executed via E3Query, and you will need to add an OUTPUT variable to it.<\/div>\n<pre>DECLARE @VarSaida\r\nEXEC NOME_sp VarEntrada, @VarSaida OUTPUT\r\nSELECT @VarSaida as Retorno<\/pre>\n<div align=\"justify\">\n<p>However, when editing the query&#8217;s SQL for execution, we depende on ADO (ActiveX Data Object) or OCI (Oracle Call Interface), technologies that Elipse E3 uses in order to access the Oracle database and, most importantly, the very DBServer on the other end to obtain a return. This issue is more prone to happening where INPUT and OUTPUT parameters are involved; that is why you may sometimes get an error in a stored procedure in an Oracle database.<\/p>\n<p>To work around this, you can execute a synchronous VB that returns the stored procedure&#8217;s value. You can execute the script below either via Viewer or via Server:<\/p>\n<\/div>\n<\/div>\n<pre><b>Sub <\/b><b><span id=\"anchor_div\">CommandButton1_Click()<\/span> <\/b> <b>'As defini\u00e7\u00f5es de constantes podem ser vistas em:<\/b> <b>'C:\\Program Files\\Common Files\\System\\ado\\adovbs.inc Const adCmdStoredProc = &H0004<\/b>'<\/pre>\n<p><a href=\"https:\/\/www.w3schools.com\/asp\/met_comm_createparameter.asp#parameterdirenum\">ParameterDirectionEnum Values <\/a><\/p>\n<pre>Const adVarChar = 200\r\nConst adParamInput = 1\r\nConst adParamOutput = 2\r\n\r\nSet con = CreateObject(\"ADODB.Connection\")\r\ncon.open \"provider=oraoledb.oracle;user id=system;password=Abcd1234;data source=ora11preto\"\r\n'con.open \"dsn=ora11preto;uid=system;pwd=Abcd1234\"\r\n\r\nstrvalfcode = \"10\"\r\n\r\nSet cmd = CreateObject(\"ADODB.Command\")\r\ncmd.CommandText = \"CALCULA_VALORES\"\r\ncmd.commandtype=adCmdStoredProc\r\ncmd.ActiveConnection = con\r\n\r\n'CreateParameter(name,type,direction,size,value)\r\nSet prminput = cmd.CreateParameter(\"\", adVarChar, adParamInput, Len(strvalfcode), strvalfcode)\r\ncmd.Parameters.Append prminput\r\n\r\nSet prmoutput = cmd.CreateParameter(\"\", adVarChar, adParamOutput, 500)\r\ncmd.Parameters.Append prmoutput\r\n\r\ncmd.Execute\r\nMsgBox prmoutput.value 'execu\u00e7\u00e3o no Viewer \r\n\r\n<b>'Application.Trace prmoutput.value 'execu\u00e7\u00e3o no server\r\n<\/b>\r\n<b>con.Close\r\n<\/b>\r\nEnd Sub<\/pre>\n<div align=\"justify\"><\/div>\n<div align=\"justify\">For a <strong>better performance in the application<\/strong>, you can add the script with the stored procedure call to an <a href=\"https:\/\/kb.elipse.com.br\/en\/category\/english\/drivers-en\/elipse-vbscript-2\/\" target=\"_blank\" rel=\"noopener noreferrer\">Elipse VBScript Driver<\/a>, and execute it in a different IOServer process.<\/div>\n<div><\/div>\n<div align=\"center\"><img loading=\"lazy\" title=\"\" src=\"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID5499\/2.png\" alt=\"\" width=\"464\" height=\"718\" align=\"Baseline\" border=\"0\" \/><\/div>\n<div align=\"center\"><\/div>\n<div><\/div>\n<div align=\"justify\">To create the <b>CALCULA_VALORES <\/b><b>stored procedure<\/b>, which returns data from the HISTORICO_CORRENTES table, we used the following syntax in the Oracle database.<\/div>\n<pre>Create or replace PROCEDURE CALCULA_VALORES\r\n(P_FUN_COD IN VARCHAR2,\r\nP_TOTAL OUT VARCHAR2)\r\nIS\r\nBEGIN\r\nSELECT SUM(CORR_BP14) INTO P_TOTAL\r\nFROM HISTORICO_CORRENTES\r\nWHERE lit_10 = P_FUN_COD;\r\nEND;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Question: How can I work around a syntax error in a stored procedure in an Oracle database? I developed a stored procedure in an Oracle 11g databse which returns an&hellip;<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0},"categories":[735,730],"tags":[975,957,970,969],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>KB-97376: Error when executing Stored Procedure in Oracle DB.<\/title>\n<meta name=\"description\" content=\"This article shows how to work around error #0x80040E14 message when a Stored Procedure is executed in E3Query with a Oracle 11g database.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"KB-97376: Error when executing Stored Procedure in Oracle DB.\" \/>\n<meta property=\"og:description\" content=\"This article shows how to work around error #0x80040E14 message when a Stored Procedure is executed in E3Query with a Oracle 11g database.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/\" \/>\n<meta property=\"og:site_name\" content=\"Elipse Knowledgebase\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/elipsesoftware\" \/>\n<meta property=\"article:published_time\" content=\"2019-11-14T16:21:09+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID5499\/1.png\" \/>\n<meta name=\"author\" content=\"D\u00e9lio Damin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"D\u00e9lio Damin\" \/>\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:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/\"},\"author\":{\"name\":\"D\u00e9lio Damin\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/be597eff34b5f24af940a55332870778\"},\"headline\":\"KB-97376: Error when executing Stored Procedure in Oracle DB.\",\"datePublished\":\"2019-11-14T16:21:09+00:00\",\"dateModified\":\"2019-11-14T16:21:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/\"},\"wordCount\":315,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"keywords\":[\"error\",\"oracle\",\"oracle11g\",\"stored procedure\"],\"articleSection\":[\"DataBases\",\"Elipse E3\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/\",\"url\":\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/\",\"name\":\"KB-97376: Error when executing Stored Procedure in Oracle DB.\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#website\"},\"datePublished\":\"2019-11-14T16:21:09+00:00\",\"dateModified\":\"2019-11-14T16:21:09+00:00\",\"description\":\"This article shows how to work around error #0x80040E14 message when a Stored Procedure is executed in E3Query with a Oracle 11g database.\",\"breadcrumb\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"In\u00edcio\",\"item\":\"https:\/\/kb.elipse.com.br\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"KB-97376: Error when executing Stored Procedure in Oracle DB.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/kb.elipse.com.br\/#website\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"name\":\"Elipse Knowledgebase\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/kb.elipse.com.br\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\",\"name\":\"Elipse Software\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"sameAs\":[\"http:\/\/www.facebook.com\/elipsesoftware\"],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"contentUrl\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"width\":161,\"height\":58,\"caption\":\"Elipse Software\"},\"image\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/be597eff34b5f24af940a55332870778\",\"name\":\"D\u00e9lio Damin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/fdf251d36430f8dd22144c3f1bc53376?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/fdf251d36430f8dd22144c3f1bc53376?s=96&d=mm&r=g\",\"caption\":\"D\u00e9lio Damin\"},\"url\":\"https:\/\/kb.elipse.com.br\/en\/author\/delio\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"KB-97376: Error when executing Stored Procedure in Oracle DB.","description":"This article shows how to work around error #0x80040E14 message when a Stored Procedure is executed in E3Query with a Oracle 11g database.","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:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/","og_locale":"en_US","og_type":"article","og_title":"KB-97376: Error when executing Stored Procedure in Oracle DB.","og_description":"This article shows how to work around error #0x80040E14 message when a Stored Procedure is executed in E3Query with a Oracle 11g database.","og_url":"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/","og_site_name":"Elipse Knowledgebase","article_publisher":"http:\/\/www.facebook.com\/elipsesoftware","article_published_time":"2019-11-14T16:21:09+00:00","og_image":[{"url":"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID5499\/1.png"}],"author":"D\u00e9lio Damin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"D\u00e9lio Damin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/#article","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/"},"author":{"name":"D\u00e9lio Damin","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/be597eff34b5f24af940a55332870778"},"headline":"KB-97376: Error when executing Stored Procedure in Oracle DB.","datePublished":"2019-11-14T16:21:09+00:00","dateModified":"2019-11-14T16:21:09+00:00","mainEntityOfPage":{"@id":"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/"},"wordCount":315,"commentCount":0,"publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"keywords":["error","oracle","oracle11g","stored procedure"],"articleSection":["DataBases","Elipse E3"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/","url":"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/","name":"KB-97376: Error when executing Stored Procedure in Oracle DB.","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/#website"},"datePublished":"2019-11-14T16:21:09+00:00","dateModified":"2019-11-14T16:21:09+00:00","description":"This article shows how to work around error #0x80040E14 message when a Stored Procedure is executed in E3Query with a Oracle 11g database.","breadcrumb":{"@id":"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/kb.elipse.com.br\/en\/kb-97376-error-when-executing-stored-procedure-in-oracle-db\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"In\u00edcio","item":"https:\/\/kb.elipse.com.br\/en\/"},{"@type":"ListItem","position":2,"name":"KB-97376: Error when executing Stored Procedure in Oracle DB."}]},{"@type":"WebSite","@id":"https:\/\/kb.elipse.com.br\/#website","url":"https:\/\/kb.elipse.com.br\/","name":"Elipse Knowledgebase","description":"","publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/kb.elipse.com.br\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/kb.elipse.com.br\/#organization","name":"Elipse Software","url":"https:\/\/kb.elipse.com.br\/","sameAs":["http:\/\/www.facebook.com\/elipsesoftware"],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/","url":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","contentUrl":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","width":161,"height":58,"caption":"Elipse Software"},"image":{"@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/be597eff34b5f24af940a55332870778","name":"D\u00e9lio Damin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/fdf251d36430f8dd22144c3f1bc53376?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/fdf251d36430f8dd22144c3f1bc53376?s=96&d=mm&r=g","caption":"D\u00e9lio Damin"},"url":"https:\/\/kb.elipse.com.br\/en\/author\/delio\/"}]}},"_links":{"self":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/9392"}],"collection":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/comments?post=9392"}],"version-history":[{"count":4,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/9392\/revisions"}],"predecessor-version":[{"id":9396,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/9392\/revisions\/9396"}],"wp:attachment":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/media?parent=9392"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/categories?post=9392"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/tags?post=9392"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}