{"id":1958,"date":"2011-02-21T11:03:23","date_gmt":"2011-02-21T16:03:23","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=1958"},"modified":"2011-02-21T11:09:53","modified_gmt":"2011-02-21T16:09:53","slug":"goldengate-custom-processing-sqlexec","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=1958","title":{"rendered":"GoldenGate Custom Processing: SQLEXEC"},"content":{"rendered":"<span id=\"SQLEXEC\"><h3>SQLEXEC<\/h3><\/span>\n<p>* Can be used in Extract or Replicat processes to communicate with database via<br \/>\n&#8211; DB commands (DBLOGIN, ENCRYPT PASSWORD, LIST TABLES)<br \/>\n&#8211; SQL queries<br \/>\n&#8211; Stored procedures<br \/>\n&#8211; DML<br \/>\n* As a standalone parameter at the root level of the parameter file<br \/>\n&#8211; Extract<br \/>\n&#8211; Replicat<br \/>\n* As a clause in the statement (procedures and queries only, no db commands)<br \/>\n&#8211; TABLE<br \/>\n&#8211; MAP<\/p>\n<span id=\"SQLEXEC_in_TABLE_or_MAP_Statement\"><h3>SQLEXEC in TABLE or MAP Statement<\/h3><\/span>\n<p>* Can pass and accept parameters.<br \/>\n* Can use queries and procedures, but no db commands.<br \/>\n* SQL syntax<\/p>\n<pre lang=\"bash\">\r\nSQLEXEC (\r\nID <logical name>,  # Used to get col values (ID.Col)\r\nQUERY \"<sql query>\",  # SQL query\r\n{PARAMS <param spec> | NOPARAMS}  # Input parameters\r\n[, <option>] [, ...]  # Options\r\n)\r\n\r\n-- Example\r\nSQLEXEC (ID appphone, &\r\nQUERY \" select per_type from ps_personal_data \" &\r\n\" where emplid = :vemplid \" &\r\n\" and per_status = 'N' and per_type = 'A' \", &\r\nPARAMS (vemplid = emplid)), &\r\nTOKENS (applid = @GETVAL(appphone.per_type));\r\n<\/pre>\n<p>* Procedure syntax<\/p>\n<pre lang=\"bash\">\r\nSQLEXEC (\r\nSPNAME <sp name>,  # Procedure name\r\n[ID <logical name>,]  # Used if need to execute multiple times only\r\n{PARAMS <param spec> | NOPARAMS}  # Input parameters\r\n[, <option>] [, ...]  # Options\r\n)\r\n\r\n-- Example\r\nSQLEXEC (ID appphone, &\r\nQUERY \" select per_type from ps_personal_data \" &\r\n\" where emplid = ? \" &\r\n\" and per_status = 'N' and per_type = 'A' \", &\r\nPARAMS (p1 = emplid)), &\r\nTOKENS (applid = @GETVAL(appphone.per_type)); &\r\n<\/pre>\n<span id=\"Use_InputOutput_Parameters\"><h3>Use Input\/Output Parameters<\/h3><\/span>\n<p>* Input<\/p>\n<pre lang=\"bash\">\r\nPARAMS (\r\n[OPTIONAL | REQUIRED] \r\n<param name> \r\n= {<source column> | <GG function>}\r\n[, ...] )\r\n<\/pre>\n<p>* Output<br \/>\n&#8211; Use dot notation (logical name followed by dot parameter).<\/p>\n<pre lang=\"bash\">\r\n<procedure name> | <logical name>.<parameter>\r\n<\/pre>\n<span id=\"Options\"><h3>Options<\/h3><\/span>\n<p>* AFTERFILTER<br \/>\nExecute query ONLY after filter returns true. This is the DEFAULT.<br \/>\n* BEFOREFILTER<br \/>\nExecute query BEFORE filter returns. If filter clause uses query return values, make SURE this is used.<br \/>\n* ALLPARAMS {OPTIONAL | REQUIRED}<br \/>\nIf all parameters need to be present for query to execute. Default is OPTIONAL.<br \/>\n* DBOP<br \/>\nDoes a commit.<br \/>\n* ERROR <action><br \/>\n&#8211; IGNORE: ignores errors<br \/>\n&#8211; REPORT: reports error to discard file. Need to specify DISCARDFILE parameter<br \/>\n&#8211; RAISE: handles errors according to rules set by a REPERROR parameter<br \/>\n&#8211; FINAL: invoke error processing immediately<br \/>\n&#8211; FATAL: abend immediately<br \/>\n* EXEC <frequency><br \/>\n&#8211; MAP: execute ONCE for each source-target table map. This is DEFAULT.<br \/>\n&#8211; ONCE: execute ONCE during the course of GoldenGate run. The results remain valid during the course.<br \/>\n&#8211; TRANSACTION: execute ONCE per source transaction. Results remain valid within transaction.<br \/>\n&#8211; SOURCEROW: execute once per source row operation.<br \/>\n* MAXVARCHARLEN<br \/>\n&#8211; Specifies max length for any output parameter.<br \/>\n&#8211; Defaults to 255<br \/>\n&#8211; Example:MAXVARCHARLEN 100<br \/>\n* PARAMBUFSIZE<br \/>\n&#8211; Max number of bytes allowed for parameter memory buffer.<br \/>\n&#8211; Defaults to 10,000 bytes<br \/>\n* TRACE<br \/>\n&#8211; Logs input and output parameters to the report file.<br \/>\n&#8211; Syntax: TRACE {ALL | ERROR}<br \/>\n&#8211; Example<\/p>\n<pre lang=\"bash\">\r\nSQLEXEC (SPNAME lookup, PARAMS (long_name = birth_state, short_name = state),\r\nTRACE ERROR)\r\n<\/pre>\n<span id=\"Examples\"><h3>Examples<\/h3><\/span>\n<pre lang=\"bash\">\r\n-- Lookup CUSTOMER table for DESC column whose value start with M\r\nTABLE CUSTOMERS, &\r\nSQLEXEC (ID lookup, &\r\nQUERY \"SELECT DESC FROM NAME WHERE ID = :CUSTID\", PARAMS(CUSTID = ID), &\r\nBEFOREFILTER, EXEC SOURCEROW), &\r\nFILTER (@STRFIND (lookup.DESC, \"M\")=1);\r\n<\/pre>\n<span id=\"SQLEXEC_as_Standalone_Statement\"><h3>SQLEXEC as Standalone Statement<\/h3><\/span>\n<p>* Can use queries, procedures, and db command.<br \/>\n* Can NOT accept input parameters<br \/>\n* Can NOT return output parameters<br \/>\n* Procedure Syntax<\/p>\n<pre lang=\"bash\">\r\nSQLEXEC \"call <procedure name>()\"\r\n[EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}]  # Execute every so often\r\n[ONEXIT]  # Execute on exit\r\n<\/pre>\n<p>* Query Syntax<\/p>\n<pre lang=\"bash\">\r\nSQLEXEC \"<sql query>\"\r\n[EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}]\r\n[ONEXIT]\r\n<\/pre>\n<p>* DB commands<\/p>\n<pre lang=\"bash\">\r\nSQLEXEC \"<database command>\"\r\n[EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}]\r\n[ONEXIT]\r\n<\/pre>\n<p>* Examples<\/p>\n<pre lang=\"bash\">\r\nSQLEXEC \"call prc_job_count ()\"\r\nSQLEXEC \" select x from dual \"\r\nSQLEXEC \"call prc_job_count ()\" EVERY 30 SECONDS\r\nSQLEXEC \"call prc_job_count ()\" ONEXIT\r\nSQLEXEC \"SET TRIGGERS OFF\"\r\n<\/pre>\n<span id=\"References\"><h3>References<\/h3><\/span>\n<p>* gg_wux_admin_v104.pdf, page 264.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQLEXEC * Can be used in Extract or Replicat processes to communicate with database via &#8211; DB commands (DBLOGIN, ENCRYPT PASSWORD, LIST TABLES) &#8211; SQL queries &#8211; Stored procedures &#8211; DML * As a standalone parameter at the root level &hellip; <a href=\"https:\/\/jianmingli.com\/wp\/?p=1958\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[88],"tags":[],"class_list":["post-1958","post","type-post","status-publish","format-standard","hentry","category-goldengate"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-vA","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1958","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1958"}],"version-history":[{"count":3,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1958\/revisions"}],"predecessor-version":[{"id":1962,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1958\/revisions\/1962"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1958"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1958"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1958"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}