{"id":156,"date":"2012-08-15T15:20:28","date_gmt":"2012-08-15T14:20:28","guid":{"rendered":"http:\/\/www.psinke.nl\/wordpress\/?p=156"},"modified":"2015-01-16T10:08:53","modified_gmt":"2015-01-16T09:08:53","slug":"how-to-copy-users-to-new-workspace-in-application-express","status":"publish","type":"post","link":"https:\/\/www.psinke.nl\/blog\/how-to-copy-users-to-new-workspace-in-application-express\/","title":{"rendered":"How to copy users to new workspace in Application Express"},"content":{"rendered":"<div class=\"afa2b3b851443650502c43e85c746c1f\" data-index=\"1\" style=\"float: none; margin:0px;\">\n<script type=\"text\/javascript\"><!--\r\ngoogle_ad_client = \"ca-pub-4223201538046844\";\r\n\/* standaardblok *\/\r\ngoogle_ad_slot = \"9522527129\";\r\ngoogle_ad_width = 320;\r\ngoogle_ad_height = 50;\r\n\/\/-->\r\n<\/script>\r\n<script type=\"text\/javascript\"\r\nsrc=\"http:\/\/pagead2.googlesyndication.com\/pagead\/show_ads.js\">\r\n<\/script>\n<\/div>\n<p>In Application Express, there is no standard way of copying users in an existing workspace to a new workspace. Everytime you create a new workspace, all developers and users must be added manually.<\/p>\n<p>Here&#8217;s a little script that does the work for you. Just replace the WORKSPACE with the name of your own workspace where you want to copy the users from, and execute in a SQL Command window in a new workspace. Might save you some time!<\/p>\n<pre>declare\r\ncursor c_usr is\r\n\u00a0 select t.user_name, t.first_name, t.last_name, t.email_address\r\n\u00a0\u00a0 , t.default_schema\r\n\u00a0  , fd.developer_role\r\n\u00a0 from APEX_040100.WWV_FLOW_FND_USER t\r\n\u00a0 join apex_040100.wwv_flow_developers fd on fd.user_id = t.user_id\r\n\u00a0 where t.security_group_id = ( select cp.PROVISIONING_COMPANY_ID \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from apex_040100.wwv_flow_companies cp \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where cp.short_name = 'WORKSPACE')\r\n\u00a0\u00a0;\r\nbegin\r\nfor r_usr in c_usr loop\r\n\u00a0 APEX_UTIL.CREATE_USER(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_user_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =&gt; r_usr.user_name ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_first_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =&gt; r_usr.first_name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_last_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =&gt; r_usr.last_name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_description\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =&gt; null,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_email_address\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =&gt; r_usr.email_address,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_web_password\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =&gt; 'Welcome01',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_developer_privs\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =&gt; r_usr.developer_role,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_default_schema\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =&gt; r_usr.default_schema,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_allow_access_to_schemas\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 =&gt; null,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 p_change_password_on_first_use\u00a0 =&gt; 'Y'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 );\r\n\u00a0\u00a0\u00a0 end loop;\r\n\u00a0 end;<\/pre>\n<p>I never understood why this is not a standard function in Application Express, but the above snippet is easily converted into a function for reuse.<\/p>\n<!--CusAds0-->\n<div style=\"font-size: 0px; height: 0px; line-height: 0px; margin: 0; padding: 0; clear: both;\"><\/div>","protected":false},"excerpt":{"rendered":"<p>In Application Express, there is no standard way of copying users in an existing workspace to a new workspace. Everytime you create a new workspace, all developers and users must be added manually. Here&#8217;s a little script that does the work for you. Just replace the WORKSPACE with the name of your own workspace where&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[141,3,121],"tags":[130,131,55,132,111,6,148,7,147,146],"class_list":["post-156","post","type-post","status-publish","format-standard","hentry","category-apexapplication-express","category-oracle-blog","category-tips-and-tricks","tag-apex","tag-application-express","tag-copy","tag-development","tag-how-to","tag-oracle","tag-productivity","tag-sql","tag-users","tag-workspace"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/comments?post=156"}],"version-history":[{"count":2,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/156\/revisions"}],"predecessor-version":[{"id":433,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/156\/revisions\/433"}],"wp:attachment":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/media?parent=156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/categories?post=156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/tags?post=156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}