问题[plsql](dba)

我想在用户建立数据库连接 8 小时后终止用户会话（也可以选择锁定用户）。我尝试创建一个配置文件，但它只是不会在 8 小时后终止用户的会话。我使用 Oracle Enterprise DB 12c。

CREATE PROFILE "PROFILE_8H_EXPIRY" LIMIT
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL UNLIMITED
  CONNECT_TIME 480 -- Specify the total elapsed time limit for a session, expressed in minutes.
  IDLE_TIME UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  LOGICAL_READS_PER_CALL UNLIMITED
  COMPOSITE_LIMIT UNLIMITED
  PRIVATE_SGA UNLIMITED
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  INACTIVE_ACCOUNT_TIME UNLIMITED
  PASSWORD_LIFE_TIME 0.33 -- Specify the number of days the same password can be used for authentication
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_LOCK_TIME UNLIMITED -- Specify the number of days an account will be locked after too many failed login attemps.
  PASSWORD_GRACE_TIME 0.33 --  If the password is not changed during the grace period marked in days, the password expires.
  PASSWORD_VERIFY_FUNCTION FUNCTION_8H_EXPIRY;

ALTER USER JOHN PROFILE PROFILE_8H_EXPIRY;

我正在尝试查找包含名为 的列的所有表，在这些表的该列中arg_column_name查找值arg_column_value，并将表的名称和找到的行数返回给用户。

我使用 dbeaver 作为 IDE。我知道 T-SQL 和 pl/pgsql - 但我没有 Oracle PL/SQL 的先验知识。

这就是问题很重的原因。

我的程序是：

CREATE OR REPLACE PROCEDURE FIND_TABLE_WITH_COLUMN_VALUE (arg_column_name IN VARCHAR2, arg_column_value IN VARCHAR2) 
IS 
    v_rowcount NUMBER;
    v_sql_statement VARCHAR2(4000);
BEGIN
    FOR L IN (
        SELECT 
            OWNER || '.' || TABLE_NAME AS OWNER_TABLE,
                'BEGIN 
                    SELECT 1 FROM ' || OWNER || '.' || TABLE_NAME || ' 
                    WHERE ' || COLUMN_NAME || '=''' || arg_column_value || ''';
                    :0:=SQL%ROWCOUNT;
                END;
                ' AS SQL_STATEMENT
        FROM ALL_TAB_COLUMNS 
        WHERE 1=1
            AND COLUMN_NAME = arg_column_name
    )
    LOOP 
        v_sql_statement := L.SQL_STATEMENT;
        EXECUTE IMMEDIATE v_sql_statement USING OUT v_rowcount;
        DBMS_OUTPUT.put_line(L.OWNER_TABLE);
        DBMS_OUTPUT.put_line(v_rowcount);
    END LOOP;
END;
/

和错误：

SQL Error [6550] [65000]: ORA-06550: line 14, column 22:
PL/SQL: ORA-00904: "ARG_COLUMN_NAME": invalid identifier
ORA-06550: line 3, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 17, column 3:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 17, column 3:
PL/SQL: Statement ignored
ORA-06550: line 18, column 21:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 18, column 3:
PL/SQL: Statement ignored
ORA-06550: line 19, column 24:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 19, column 3:
PL/SQL: Statement ignored
ORA-06550: line 20, column 24:
PLS-00201: identifier 'V_ROWCOUNT' must be declared
ORA-06550: line 20, column 3:
PL/SQL: Statement ignored

这怎么可能？

1. “ARG_COLUMN_NAME”：无效标识符- 这不是我指定的函数参数吗？
2. 必须声明标识符“V_SQL_STATEMENT” - 它是否尚未在存储过程的 IS 部分中指定？

更新：@Littlefoot 的答案已经奏效。我认为我的错误的原因可能是双重的：

1. dbeaver 存在 oracle 语法问题，或者
2. 我对 oracle 语法有疑问（尤其是语句终止符\ ）

我还下载了 Oracle SQL Developer。它比 dbeaver 更有效地完成 Oracle SQL 开发工作。

这是我根据@Littlefoot 的输入调整的最终代码：

create or replace procedure find_table_with_column_value
  (arg_column_name in varchar2, arg_column_value in varchar2, row_count_limit in number default 100000)
is
    v_rowcount      number;
    v_sql_statement varchar2(4000);
    v_owner_table varchar2(400);
    v_prefix varchar2(10) := '';
    err_msg varchar2(4000);
BEGIN
    FOR l IN (
        SELECT
            c.owner || '.' || c.table_name AS owner_table,
            c.column_name,
            t.num_rows
        FROM
            all_tab_columns c
        INNER JOIN all_tables t ON
            t.table_name = c.table_name
            AND t.owner = c.owner
        WHERE
            1 = 1
            AND upper(c.column_name) = upper(dbms_assert.qualified_sql_name(arg_column_name))
            AND t.NUM_ROWS <= row_count_limit
            AND c.DATA_TYPE = 'VARCHAR2' -- safeguard against NUMERIC columns
           )
    LOOP
        v_sql_statement :=
          'select count(*) from ' || l.owner_table ||
          ' where ' || l.column_name || ' = ' ||
            chr(39) || arg_column_value || chr(39);

        v_owner_table := l.owner_table;
        
        EXECUTE IMMEDIATE v_sql_statement INTO v_rowcount;
        
        IF v_rowcount > 0 
            THEN v_prefix := '> ';
            ELSE v_prefix := '';
        END IF;
        
        dbms_output.put_line(v_prefix || l.owner_table || ': ' || v_rowcount);
    END LOOP;

EXCEPTION
WHEN OTHERS THEN
    err_msg := SQLERRM;
    dbms_output.put_line('Error with ' || v_owner_table);
    dbms_output.put_line('Error message = ' || err_msg);
END;

有人可以帮我确认一下，如果我正在运行一个包含多个代码块的 PLSQL 脚本并且我捕获了一个错误 - 是否使用它自己的“RAISE”命令跳过到脚本末尾的异常处理或将它继续运行剩余的代码块吗？或者，如果我使用 'RAISE handle_tns_error' 引发异常，其中 handle_tns_error 被定义为异常，并在最后的异常部分中列出（如下例所示），是否会跳过代码中的所有其他块并立即退出一个错误？谢谢你。

.
.
.

  ELSIF SQLCODE = -12154 THEN
     DBMS_OUTPUT.PUT_LINE('Detected a TNS error');
     RAISE handle_tns_error;
  ELSE
     RAISE;
  END IF;
END;

<all code here will be ignored>

EXCEPTION
    WHEN handle_tns_error THEN
        DBMS_OUTPUT.PUT_LINE('tns error detected');
        :ret_code := 1;
    WHEN OTHERS THEN RAISE;
END;
/

我是 PLSQL 的新手，所以我有一个非常基本的问题，希望有人能帮助我。我想针对 11g 数据库运行直接的 select 语句，然后如果它是 12g 数据库，则对该 SQL 语句进行稍微修改的版本。结果应该输出到屏幕上。在我添加 SQL 语句之前，下面的代码一直有效。我尝试了“立即执行”，但没有奏效，然后使用了游标——但对于多列和多行来说，这不能作为答案，因为它太冗长了。我在这里想念什么？语句可以是任何东西，例如：

--11g
select NAME,OPEN_MODE,DBID from v$database; 
--12c
select NAME,OPEN_MODE,DBID,con_id from v$database;

我只想知道如何在这样的代码块中处理任何 SQL 选择语句（返回多行和多列）。谢谢。

DECLARE
    v_dbver    BINARY_INTEGER;
BEGIN
    SELECT TO_NUMBER(SUBSTR(version,1,2)) INTO v_dbver FROM v$instance;
    DBMS_OUTPUT.PUT_LINE('Version ='||v_dbver);
IF v_dbver < 12 THEN
    DBMS_OUTPUT.PUT_LINE('Running against 11g');
    <SQL statement>
ELSIF v_dbver >= 12 THEN
    DBMS_OUTPUT.PUT_LINE('Running against 12c');
    <SQL statement>
END IF;
END;
/

我是 Oracle DB 的新手，来自 MS SQL Server。

我创建了这个脚本：

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;
  /

当我在 SqlPlus 中运行它时，如下所示：

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'

我收到此错误消息：

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

有人可以指出我正确的方向吗？我究竟做错了什么？

感谢您的回答。

编辑

我在另一个脚本中有完全相同的代码，并且在那里运行良好：

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
...

编辑 2

如果我将变量放在 and 之间DECLARE并没有什么区别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 DB 的新手（来自 MS SQL Server）。

首先，我为 Oracle Database 18c XE 下载了一个 Docker 容器。

当我运行该容器时，在创建可插入数据库后，我似乎无法与我创建的新用户连接：

据我所知，我提供了所有必要的信息，让这些用户做好准备。但我错过了什么？我不明白，即使在阅读了几十个文档和帮助页面之后。

这是lsnrctl status, 在容器中运行的输出：

> lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 25-MAY-2021 11:57:33

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                25-MAY-2021 11:11:15
Uptime                    0 days 0 hr. 46 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/5854b834e19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=5854b834e19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "c323f0bfe62f0aaee053030011ac1ec5" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "c323f7dc51bd0b68e053030011acc9a8" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "mobydick" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

这是我的tnsnames.ora文件内容：

# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))  

XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))    
     )
     (CONNECT_DATA =
       (SID = PLSExtProc)
       (PRESENTATION = RO)
     )
  )



mobydick =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mobydick)
    )
  )

我创建了类似于这样的可插拔数据库：

ALTER SESSION SET CONTAINER=CDB$ROOT;

CREATE PLUGGABLE DATABASE "mobydick" ADMIN USER "sa" IDENTIFIED BY "abc123" ROLES=(dba) FILE_NAME_CONVERT=('/opt/oracle/product/18c/dbhomeXE/dbs/mobydick/') STORAGE UNLIMITED

ALTER PLUGGABLE DATABASE mobydick OPEN READ WRITE

该过程总是立即编译，我很确定我没有任何无限循环或其他一些错误。在 SQL Developer 中运行该过程时，它有时会立即完成（应该如此，每个表只有 5 行），而其他时候它永远不会停止运行。它与数据库或我的代码有关吗？对不起，如果代码很乱，这是我的第一个 PLSQL 程序。我正在我的大学提供的 Oracle DB 上运行该程序，因此我没有管理员权限。

CREATE OR REPLACE PROCEDURE integrate_data IS
    rowCount NUMBER;
    currId NUMBER;
    CURSOR ws1_customer_crsr IS SELECT * FROM ws1_customer;
    CURSOR ws1_product_crsr IS SELECT * FROM ws1_product;
    CURSOR ws1_order_crsr IS SELECT * FROM ws1_order o JOIN ws1_order_position p ON o.no = p.order_;
    CURSOR ws2_customer_crsr IS SELECT * FROM ws2_user u JOIN ws2_address a ON u.id = a.user_;
    CURSOR ws2_product_crsr IS SELECT * FROM ws2_product;
    CURSOR ws2_order_crsr IS SELECT * FROM ws2_purchase p JOIN ws2_entry e ON p.code = e.purchase; 
BEGIN
    SAVEPOINT beforeProcedure;
    DELETE FROM ts_transaction;
    DELETE FROM ts_customer;
    DELETE FROM ts_product;
    /* Insert customer data from ws1 */
    FOR ws1_customer_rec IN ws1_customer_crsr
    LOOP
        INSERT INTO ts_customer VALUES('ws1_' || to_char(ws1_customer_rec.id), ws1_customer_rec.email, ws1_customer_rec.address);
    END LOOP;
    /* Insert product data from ws1 */
    FOR ws1_product_rec IN ws1_product_crsr
    LOOP 
        INSERT INTO ts_product VALUES('ws1_' || to_char(ws1_product_rec.id), ws1_product_rec.name,
            ws1_product_rec.description, ws1_product_rec.price);
    END LOOP;
    /* Insert order data from ws1 */
    FOR ws1_order_rec IN ws1_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws1_' || to_char(ws1_order_rec.no) || '_' || to_char(ws1_order_rec.pos_no), ws1_order_rec.date_, 
            ws1_order_rec.quantity, 'ws1_' || to_char(ws1_order_rec.customer), 'ws1_' || to_char(ws1_order_rec.product));
    END LOOP;
    /* Insert customer data from ws2, if a customer has multiple addresses only the first one gets added */
    FOR ws2_user_rec IN ws2_customer_crsr
    LOOP
        currId := ws2_user_rec.user_;
        SELECT CASE 
            WHEN EXISTS(SELECT * FROM ts_customer c WHERE c.id = 'ws2_' || currId) THEN 1
            ELSE 0
        END
        INTO rowCount
        FROM DUAL;
        IF rowCount = 0 THEN
            INSERT INTO ts_customer VALUES('ws2_' || to_char(ws2_user_rec.user_), ws2_user_rec.email, ws2_user_rec.street || ' ' || to_char(ws2_user_rec.street_no) ||
                ', ' || to_char(ws2_user_rec.postal_code) || ' ' || ws2_user_rec.city);
        END IF;
    END LOOP;
    /* Insert product data from ws2 */
    FOR ws2_product_rec IN ws2_product_crsr
    LOOP
        INSERT INTO ts_product VALUES('ws2_' || to_char(ws2_product_rec.ean), ws2_product_rec.title, ws2_product_rec.description, ws2_product_rec.sales_price);
    END LOOP;
    /* Insert order data from ws2 */
    FOR ws2_purchase_rec IN ws2_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws2_' || to_char(ws2_purchase_rec.code) || '_' || to_char(ws2_purchase_rec.entry_no),
            ws2_purchase_rec.date_, ws2_purchase_rec.qty, 'ws2_' || to_char(ws2_purchase_rec.user_), 'ws2_' || to_char(ws2_purchase_rec.product));
    END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('An error has occurred, all work the procedure has done has been rolled backed');
        ROLLBACK TO beforeProcedure;
END;
/

给定一张表销售，商品以两种不同的货币出售，如何以欧元兑换

销售表：

所需表：

对于每件商品，我们将货币与欧元不同的金额除以欧元货币的金额

知道如何解决这个问题吗？

如果可以处理同一物品我没有欧元货币（例如美元和捷克克朗）的情况会很好，并且在前几行中我计算了美元的兑换，所以我可以计算出捷克克朗兑欧元吗？

我希望能够运行以下代码来选择记录：

所需的语法

select * from table(my_new_package.get_data(123))

我已成功创建可编译的代码，但无法使用上述语法检索数据。谁能确定我如何更新它以确保上述查询能够正常运行。

这是当前代码：

包装规格

create or replace package my_new_package is

  -- Types
  -------------
  type my_data_type is record (
      myfield1          VARCHAR2(10)
      ,myfield2         VARCHAR2(10)
    );
    
   type my_data_type_TABLE is table of my_data_type index by binary_integer;

   -- Functions
   -------------
   FUNCTION get_data(i_id_number varchar2) return my_data_type_table;

end my_new_package;

包体

create or replace package body my_new_package is

  function get_data(i_id_number varchar2) return my_data_type_table IS  
    current_row my_data_type;
    all_rows my_data_type_table;
    n integer;

    -- Will update the cursor to pull real data
    cursor cur is
      select '1' myfield1, '10' myfield2 from dual
      union all
      select '2' myfield1, '20' myfield2 from dual
    ;
  BEGIN    
    n := 0;
    for rec in cur LOOP
      n := n+ 1;
      current_row := rec;
      -- do some things to current row

      -- Add modified current row
      all_rows(n) := current_row;
    END LOOP;
    
    return all_rows;
  END;


end my_new_package;

上面的代码可以编译，但我无法使用所需的语法从数据中进行选择。

无论如何，在自定义类型仅嵌入包级别而不是在数据库级别创建的情况下，是否可以这样做？

这将适用于 Oracle 12（和 Oracle 19）。

有没有办法通过 Oracle SQL 将 HEX 或 RAW 值转换为有符号 2 的补码？

例子：

十六进制：8508 > 十进制：34046 > 十进制从有符号 2 的补码：-31480

我试图在我的 SQL 语句中得到的数字是 -31480。