Re: function, alter user, dblink ? Problem? [oracle]

On Jan 12, 3:33 am, "szaki" <ljag..._at_gmail.com> wrote:
> Hi,
> I have a problem when I traied execute function to dblink:
>
> CREATE OR REPLACE FUNCTION password_change(LOGIN VARCHAR2, pass
> VARCHAR2)
> RETURN VARCHAR2 IS
> V_SQL varchar2(200);
> Kom varchar2(200);
> BEGIN
> V_SQL := 'ALTER USER ' || UPPER(LOGIN) || ' IDENTIFIED BY
> "'||pass||'"';
> EXECUTE IMMEDIATE V_SQL;
>
> Kom := 'OK ' ||LOGIN||' - '||pass;
> RETURN Kom;
>
> END;
>
> I execute:
>
> declare
> i varchar2(200);
> begin
> i := aaa.password_change_at_DGH('test','qwe123');
> dbms_output.put_line(i);
> end;
>
> when I execuce I get error:
> "ORA-02064: distributed operation not supported"
>
> My question is:
> How use alter user with dblink.????
>
> Sorry for my english.
>
> Luk

What version of Oracle?

I do not think this has changed with 10g but definitely through version 9.2 you cannot issue DDL over a database link.

What you do is define a stored procedure that uses execute immediate to issue DDL on the remote database under a privileged username. You then execute this procedure via the link.

execute procedure_name_at_database_link

HTH -- Mark D Powell -- Received on Fri Jan 12 2007 - 10:00:13 CST