2

I have created user on another database using database link and stored procedure but facing problem while grant permission to the new created users.

Check below code:

CREATE OR replace PROCEDURE Hostname10 (user_name   IN VARCHAR2,
                                        pass_word   IN VARCHAR2,
                                        table_space IN VARCHAR2,
                                        pro_file    IN VARCHAR2)
AS
BEGIN
    dbms_utility.Exec_ddl_statement@rahul2('CREATE USER '
                                           ||user_name
                                           ||' IDENTIFIED BY '
                                           ||pass_word
                                           ||' DEFAULT TABLESPACE '
                                           ||table_space
                                           || ' PROFILE '
                                           || pro_file
                                           || ' ACCOUNT UNLOCK');

dbms_utility.Exec_ddl_statement@rahul2('grant create table,create session,create view,create sequence,create procedure,create job,create synonym  to'
                                       ||user_name
                                       ||'');
END;

/

I am getting error while executing it:

Error:
Error report -
ORA-06550: line 1, column 7:
PLS-00201: identifier 'HOSTANAME10' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Rahul Joshi
  • 31
  • 1
  • 9

1 Answers1

1

Your error message says:

PLS-00201: identifier 'HOSTANAME10' must be declared
                           ^

But your procedure is created as Hostname10. So this is just a typo, you have an extra a in the name when you try to call the procedure.

You also seem to have a mistake in the grant call, though you're not currently getting that far; that ends with:

... create synonym  to'
                                   ||user_name
                                   ||'');

so in the generated command there will be no space between to and the username; that needs to be:

... create synonym  to '
                                   ||user_name);

Concatenating the null/empty string after the username isn't doing anything so I've taken the liberty of removing that too.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • @RahulJoshi - what do you mean? You tried executing it as `Hostname10` instead of `Hostaname10` and it still didn't work? Or you only added the space inside the procedure but still called it with the wrong name? – Alex Poole Jan 16 '15 at 10:22
  • i have added space and tryed to executing it as Hostname10 instead of Hostaname10 in both scenario result is same..Error report - ORA-06550: line 1, column 7: PLS-00201: identifier 'HOSTANAME10' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: – Rahul Joshi Jan 16 '15 at 10:32
  • @RahulJoshi - the error still says "HOSTANAME10" so you have not changed how you're calling it. It might help if you add your call to the question; maybe you have more than one call in an anonymous block and you've only fixed one. Otherwise you don't seem to be running what you think you are. – Alex Poole Jan 16 '15 at 10:35
  • :thanx for you response..yes it was mine mistakes in coding..i forgot some spaces to add – Rahul Joshi Jan 16 '15 at 11:37