Morning Scott,
far I am unable to do so, I have been given the following
select and insert code to create the user: SELECT * FROm
ALL_USERS call dbms_utility.exec_ddl_statement('CREATE
USER' || :USERNAME || 'IDENTIFIED BY P@s! sword')SELECT *
FROM ALL_USER WHERE USERNAME = :USERNAME
The above gives me the impression that you have managed to cram three
separate statements together.
Your concatenation of SQL text and bind variable is suspect.
You cannot use binds variables in a DDL statement.
"All_user" should be "all_users".
You need to create a user as follows:
create user
identified by
default tablespace
temporary tablespace
quota 0 on system
quota on ;
And to see if it worked:
select
from all_users -- PLURAL
where username = :username;
You need to bind in the username to the second statement of course, in
UPPER CASE.
If you create a user in some versions of Oracle, you don't need the
temporary tablespace clause if the database has been set up with a
default temporary tablespace. This applies from 9i onwards.
I'm not sure, we don't have many 10g databases, but the same may apply
to users created from 10g onwards where a default tablespace need not be
specified if there is one already defaulted for the database. Don't
quote me on that one though! 
Also, when building a statement, similar to your create user one above,
but for DML, you don't concatenate the binds, you would simply do:
'select lots_of_columns
from table_name
where some_column = :check_this
order by some_order_clause'
Then you would parse it, and, in a loop, bind a value, execute, fetch
results, repeat.
If the bind values you have used above are really just literals, the
concatenation in this manner will leave you open to an SQL Injection
attack - if the code can be executed from a webby type interface.
HTH
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk