CODEWITHSUNDEEP
databaseoracle-databasestored-proceduresdatabase-link
Rahul Joshi
Rahul Joshi

Reputation: 31

Create user through procedure on another database via database link

I'd like to create users through procedure on another database via database link.I am getting error while executing procedure. Here are the code which i used.

create or REPLACE PROCEDURE hostname   
(host_name in varchar2,user_name in VARCHAR2, pass_word in VARCHAR2,    
table_space in varchar2,pro_file in varchar2)    
as    
db_link_name varchar2(30);    
begin    
select db_link into db_link_name from all_db_links where host=host_name;    
EXECUTE IMMEDIATE 'dbms_utility.exec_ddl_statement@'||db_link_name||('CREATE     USER '||user_name||' IDENTIFIED BY '||pass_word||'     
DEFAULT TABLESPACE '||table_space||' PROFILE '|| pro_file||' ACCOUNT UNLOCK');    
EXECUTE IMMEDIATE 'dbms_utility.exec_ddl_statement@'||db_link_name||    
('GRANT CONNECT,RESOURCE,EXECUTE_CATALOG_ROLE,Create table,create session,create     view,create sequence,create procedure,create job,create synonym TO '||user_name);    
end;    
/    
execute hostname('orcl1','rahul1','rahul','users','default');

Error: ORA-00900: invalid SQL statement ORA-06512: at "SYS.HOSTNAME", line 6 ORA-06512: at line 1
00900. 00000 - "invalid SQL statement"
*Cause:
*Action:

Upvotes: 0

Views: 1064

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64959

I think you may have got a little confused by some of the advice provided by the commenter mustaccio. What he meant to say is that the SQL strings in your EXECUTE IMMEDIATE statements need to use PL/SQL blocks in order to call stored procedures.

In other words, instead of writing

EXECUTE IMMEDIATE 'dbms_utility.exec_ddl_statement ... ';

you should write

EXECUTE IMMEDIATE 'BEGIN dbms_utility.exec_ddl_statement ... ; END;';

For your procedure, I would recommend doing introducing a local variable for the strings you are passing to EXECUTE IMMEDIATE. These can be notoriously tricky to get right, and if you've assigned them to a local variable you can easily output them using dbms_output.put_line. In fact, I did exactly this while fixing up the problems in your procedure. I would recommend continuing to do this if you wish to modify or extend your procedure.

Anyway, here's what I ended up with, which appeared to work:

create or REPLACE PROCEDURE hostname   
  (host_name in varchar2,user_name in VARCHAR2, pass_word in VARCHAR2,    
  table_space in varchar2,pro_file in varchar2)    
as    
  db_link_name      varchar2(30);
  l_ddl_sql         varchar2(4000);
begin    
  select db_link into db_link_name from all_db_links where host=host_name; 
  l_ddl_sql := 'begin dbms_utility.exec_ddl_statement@'||db_link_name||'(''CREATE     USER '||user_name||' IDENTIFIED BY '||pass_word||'     
    DEFAULT TABLESPACE '||table_space||' PROFILE '|| pro_file||' ACCOUNT UNLOCK''); END;';

  EXECUTE IMMEDIATE l_ddl_sql;

  l_ddl_sql := 'begin dbms_utility.exec_ddl_statement@'||db_link_name||    
    '(''GRANT CONNECT,RESOURCE,EXECUTE_CATALOG_ROLE,Create table,create session,create     view,create sequence,create procedure,create job,create synonym TO '||user_name || '''); END;';

  EXECUTE IMMEDIATE l_ddl_sql;
end;
/

Upvotes: 3

Related Questions