I want to create schema dynamically i.e. it should prompt for the input. Or how to use collections, nested table to create users from PL/SQL code blocks i.e. procedures, functions etc?
-
1There is no prompting in pl/sql. You use EXECUTE IMMEDIATE to execute dynamic sql.OldProgrammer– OldProgrammer2017-08-29 18:35:26 +00:00Commented Aug 29, 2017 at 18:35
-
Okay, Then how the 'create users' statement should be written in pl/sql to EXECUTE IMMEDIATE? can you tell with example. Consider taking username from array of nested tables.user8353718– user83537182017-08-29 18:41:10 +00:00Commented Aug 29, 2017 at 18:41
-
Have you investigated programmatic build libraries such as Liquibase?APC– APC2017-08-30 05:02:39 +00:00Commented Aug 30, 2017 at 5:02
Add a comment
|
1 Answer
I give you a small example for that:
create or replace procedure p(p_user varchar2) is
begin
execute immediate 'create user '||p_user||' identified by x';
end;
/
begin
p('&user');
end;
/
Here the procedure gets a parameter which can be used to put together your DDL commands. The procedure must be called somehow. You can call it from sqlplus, or execute it via web server, or from a desktop app, etc. I wrote the last 4 lines as example how to call it from sqlplus. Sqlplus can prompt and read input from user.
