Skip to Main Content
  • Questions

  • Oracle forms to create a database user

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mitch.

Asked: August 25, 2016 - 9:00 pm UTC

Last updated: September 01, 2016 - 2:25 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hello,

Is there a way to create a form with username and password and create the user in the database?

I have tried using a host command to log into sqlplus and execute the create user .... but it doesn't want to execute from the form host command. Although when I execute the .bat file on the server, it creates the user. Have given the DBA priv to the user who logs into sqlplus and executes the create user.

Is there a way to execute the create user from a proc in the form?

thank you in advance.
Mitch

and Connor said...

You dont need to have a batch file, or sql plus etc.

The form can simply call a database procedure, which is owned by someone with appropriate privileges, eg

SQL> create or replace
  2  procedure create_user(p_user varchar2, p_pass out varchar2) is
  3    l_pass varchar2(30);
  4  begin
  5    l_pass := 'P'||trunc(dbms_random.value(100000000,900000000));
  6
  7    execute immediate
  8      'create user '||p_user||' identified by '||l_pass||' password expire';
  9
 10    execute immediate
 11      'grant create session to '||p_user;
 12
 13    p_pass := l_pass;
 14  end;
 15  /

Procedure created.


Then the Form can do

SQL> set serverout on
SQL> declare
  2    new_password varchar2(30);
  3  begin
  4    create_user('DEMO1',new_password);
  5
  6    dbms_output.put_line('Your password is '||new_password||' and must be changed');
  7  end;
  8  /
Your password is P751921205 and must be changed

PL/SQL procedure successfully completed.


using a "message" call or an item to show the new password.

Naturally you would extend this to check for pre-existing user accounts, etc etc

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Oracle Forms 11.2 Create User

Mitch, August 31, 2016 - 3:54 am UTC

Connor McDonald,
Thank you for your help!
Connor McDonald
September 01, 2016 - 2:25 am UTC

glad we could help

Follow up to response

Mitch, September 01, 2016 - 2:08 am UTC

Hello Connor,
Thank you again for answering my question. I had to fight the syntax and found that I had to put double quotes around the password, but its up and running.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library