Início / dba / Perguntas / 292276
Accepted
AxD
Asked: 2021-05-27 01:19:04 +0800 CST

Como posso fazer este PROCEDIMENTO DE DECLARAÇÃO funcionar?

  • 772

Sou novo no Oracle DB, vindo do MS SQL Server.

Eu criei este script:

DEFINE USER_NAME = &1
DEFINE PASSWORD = &2
DEFINE TABLESPACE = &3

CONNECT &DB_ADMIN/&DB_PWD@//localhost:1521/&DB_NAME

SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF

DECLARE PROCEDURE Create_User
  ( userName IN VARCHAR2
  , password IN VARCHAR2
  , tSpace IN VARCHAR2
  ) AS
  dbCount INTEGER := -1;
  createStmt VARCHAR2(500);
BEGIN
  SELECT COUNT(*)
    INTO dbCount
    FROM CDB_USERS u
      INNER JOIN v$pdbs d ON u.CON_ID = d.CON_ID
    WHERE d.NAME = '&DB_NAME'
      AND u.USERNAME = userName;

  IF dbCount > 0
    THEN
      EXECUTE IMMEDIATE 'DROP USER ' || userName || ' CASCADE';
      DBMS_OUTPUT.PUT_LINE('User ' || userName || ' dropped.');
    END IF;

  createStmt := 'CREATE USER ' || userName || ' IDENTIFIED BY ' || password || ' DEFAULT TABLESPACE ' || tSpace || ' QUOTA UNLIMITED ON ' || tSpace;
  DBMS_OUTPUT.PUT_LINE('User about to be created:');
  DBMS_OUTPUT.PUT_LINE(createStmt);
  DBMS_OUTPUT.PUT_LINE('');


  DBMS_OUTPUT.PUT_LINE('Creating user ' || userName || ' ...');
  EXECUTE IMMEDIATE createStmt;
  DBMS_OUTPUT.PUT_LINE('Database ' || userName || ' successfully created.');

  END Create_User;

BEGIN
  Create_User('&USER_NAME', '&PASSWORD', '&TABLESPACE');
  END;
  /

Quando eu executo no SqlPlus, assim:

SQL> DEFINE DB_NAME = 'MYDB'
SQL> DEFINE DB_ADMIN = 'SA'
SQL> DEFINE DB_PWD = 'pwd'
SQL> @/db-install/sql/add-user.sql 'USERNAME' 'pwd' 'TSPACE'

Recebo esta mensagem de erro:

Connected.
DECLARE PROCEDURE Create_User
*
ERROR at line 1:
DECLARE
*
ERROR at line 1:
ORA-01918: user 'USERNAME' does not exist
ORA-06512: at line 19
ORA-06512: at line 36

Alguém por favor pode me indicar a direção certa? O que estou fazendo errado?

Sua resposta é apreciada.

EDITAR

Eu tenho exatamente esse mesmo código em outro script e está funcionando bem lá:

DEFINE DB_NAME = &1
DEFINE DB_ADMIN = &2
DEFINE DB_PWD = &3

CONNECT &DB_ADMIN/&DB_PWD@//localhost:1521/&DB_NAME

SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF

BEGIN
  DBMS_OUTPUT.PUT_LINE('Adding tablespaces to database ...');
  END;
  /


--- Procedure: dropping and creating a given tablespace ---
DECLARE
  dbCount INTEGER;
  createStmt VARCHAR2(500);
  filePath dba_data_files.FILE_NAME%TYPE;
PROCEDURE Create_TS
...

EDIÇÃO 2

Não faz diferença se eu colocar as variáveis ​​entre DECLAREe PROCEDURE:

DECLARE
  dbCount INTEGER := -1;
  createStmt VARCHAR2(500);
PROCEDURE Create_User
  ( userName IN VARCHAR2
  , password IN VARCHAR2
  , tSpace IN VARCHAR2
  ) AS
BEGIN

Connected.
DECLARE
*
ERROR at line 1:
ORA-01918: user 'USERNAME' does not exist
ORA-06512: at line 19
ORA-06512: at line 36
oracle plsql

1 respostas

  1. Best Answer

    E esse é um ótimo exemplo de por que você nunca deve nomear suas variáveis ​​PL/SQL da mesma forma que os nomes das colunas.

    set serveroutput on
DECLARE
  PROCEDURE Create_User
  ( userName IN VARCHAR2
  , password IN VARCHAR2
  , tSpace IN VARCHAR2
  ) AS
  dbCount INTEGER := -1;
  createStmt VARCHAR2(500);
BEGIN
  SELECT COUNT(*)
    INTO dbCount
    FROM DBA_USERS u
    WHERE u.USERNAME = userName;
  dbms_output.put_line('User count: ' || dbcount);
end;
begin
  create_user('abc', 'abc', 'abc');
end;
/

User count: 26

    O u.USERNAME = userNamefiltro é username = username, que retorna todas as linhas em que o nome de usuário não é nulo.

    • 1

relate perguntas