Hi Chris,
I was asked to develop an app for password change of selected database and below select query works when called from sqlworkshop of oracle apex .

select * from dual@SYS_DBALINK

DUMMY
X

The full error when called when submitting button is below

PLS-00352: Unable to access another database 'SYS_DBALINK' ORA-06550: line 1, column 8: PLS-00201: identifier 'ACCOUT_CHANGE@SYS_DBALINK' must be declared ORA-06550: line 1, column 8: PL/SQL: Statement ignored
ora_sqlcode: -6550

I changed mycode to bind variable as below but showing error as

ORA-06550: line 1, column 22:
PLS-00103: Encountered the symbol "" when expecting one of the following:
@ <an identifier> <a double-quoted delimited-identifier>
ORA-06550: line 1, column 35:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

. ( , * % & - + / at mod remainder rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || multiset bulk
1. begin
2. PASSWORD_CHANGE_1('SYS_DBA','TestDB','TestDB1');
3. END

My code is

create or replace PROCEDURE PASSWORD_CHANGE_1(P2_DATABASE VARCHAR2,P2_USERAME VARCHAR2 ,P2_PASSWORD VARCHAR2) IS
SQL_STRING VARCHAR(500);
 
BEGIN
 SQL_STRING:=' begin accout_change@:1LINK(:2,:3); end; ';
EXECUTE IMMEDIATE SQL_STRING using in P2_DATABASE ,P2_USERAME,P2_PASSWORD;

dbms_output.put_line(SQL_STRING);
END;
create or replace PROCEDURE accout_change_1 (
    P2_USERAME VARCHAR2,
    P2_PASSWORD VARCHAR2
) IS
    sql_string   VARCHAR2(500);
BEGIN
    sql_string := 'alter user :1 identified by :2 ';
   EXECUTE IMMEDIATE sql_string USING P2_USERAME, P2_PASSWORD ;

    -- dbms_output.put_line('string ' || sql_string);
    -- dbms_output.put_line('Success');
EXCEPTION 
WHEN OTHERS then
dbms_output.put_line('Failed');
END;

Also getting the below error when executing below error .Please help me out solving in this db password change

ORA-01935: missing user or role name

create or replace PROCEDURE accout_change_1 (
    P2_USERAME VARCHAR2,
    P2_PASSWORD VARCHAR2
) IS
    sql_string   VARCHAR2(500);
BEGIN
    sql_string := 'alter user :1 identified by :2 ';
   EXECUTE IMMEDIATE sql_string USING P2_USERAME, P2_PASSWORD ;

    -- dbms_output.put_line('string ' || sql_string);
    -- dbms_output.put_line('Success');
EXCEPTION 
WHEN OTHERS then
dbms_output.put_line('Failed');
END;

Hi Chris ,Can you please help me

Hi - PMFJI, but afaik please look at it is NOT possible to use BINDs for identifiers in DDLs, and ALTER USER is a DDL command, so imho your cod will NOT fly : please look at https://asktom.oracle.com/pls/apex/asktom.search?tag=bind-variables-in-ddl-with-execute-immediate , http://www.orafaq.com/forum/t/150992/ , https://stackoverflow.com/questions/10315170/execute-immediate-alter-user-bind-variable and https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:588045400346317188 for examples/references...
So, in your procedure you MUST concat the strings for username and password, and NOT to bind them ... OF COURSE, to minimize SQL injection risks you WILL sanitize your inputs , maybe with DBMS_ASSERT and other techniques....

Regards,

Chiappa

P.S. :

1. of course, many languages/programming tools have the capacity for changing passwords, https://www.php.net/manual/en/function.oci-password-change.php show how to do it in php : check if the programming language/tool in use for your application have it , if yes you could write something OUTSIDE the database, inside yor app...

2. you always can call the OCI function OCIPasswordChange, maybe via EXTERNAL PROCEDURES : check My Oracle Support note "Using OCIPasswordChange() to Change Password" (Doc ID 99457.1) for an example...

I used the same way as you told but the getting error
ORA-00922: missing or invalid option

create or replace PROCEDURE accout_change_3 (
    P2_USERAME VARCHAR2,
    P2_PASSWORD VARCHAR2
) IS
    sql_string   VARCHAR2(500);
    usr VARCHAR2(50);
    pwd VARCHAR2(50);
BEGIN
    sql_string := 'alter user dbms_assert.SCHEMA_NAME(:username)
 identified by dbms_assert.ENQUOTE_NAME(:password);';
   EXECUTE IMMEDIATE sql_string using P2_USERAME,P2_PASSWORD;
   

END

    sql_string := 'alter user ' || dbms_assert.SCHEMA_NAME(username) || 
' identified by ' || dbms_assert.ENQUOTE_LITERAL(password) || ';';

Hi chris thanks,
When i used dbms_assert.ENQUOTE_literal it shows error as "missing or invalid passowrds"
so I used dbms_assert.ENQUOTE_name,now it shows error as invalid character

please find my code and execution

create or replace PROCEDURE accout_change_test (
    P2_USERAME VARCHAR2,
    P2_PASSWORD VARCHAR2
) IS

sql_string VARCHAR2(500) ;
BEGIN
   sql_string := 'alter user ' || dbms_assert.SCHEMA_NAME(P2_USERAME) || 
      ' identified by ' || dbms_assert.ENQUOTE_name(P2_PASSWORD)||';' ;
dbms_output.put_line(sql_string);
EXECUTE IMMEDIATE sql_string using  P2_USERAME,P2_PASSWORD ;

END;

begin 
accout_change_test('TESTDB','TestDB1');
end;

You do not need the extra ";" when executing commands using Execute Immediate. 

Hence the SQL string should be:

 sql_string := 'alter user ' || dbms_assert.SCHEMA_NAME(P2_USERAME) || 
      ' identified by ' || dbms_assert.ENQUOTE_name(P2_PASSWORD);

Cheers!

Hi Chris,
Thank you so much .its working

Well, the first thing from Tom for this question - would be highlighting his "i hate your code" url like this.

EXCEPTION 
WHEN OTHERS then
dbms_output.put_line('Failed');
END; 

I hate your code:

https://www.google.com/search?q=site%3Aasktom.oracle.com+"i+hate+your+code"

https://asktom.oracle.com/pls/apex/asktom.search?tag=is-there-an-easy-way-to-make-a-ctas#1535781400346575552