数据库链接名称的变量 [英] Variable for Database Link Name

问题描述

我想做v$session@remotedatabase，其中remotedatabase是dblink地址的变量.有可能吗?

I want to do v$session@remotedatabase where remotedatabase is a variable for a dblink address. Is that possible?

我正在使用Apex 4，并试图在所有数据库上获得临时空间.

I'm using Apex 4 and trying to get temporary space on all databases.

当前查询

select 'Total temp space available in :Database is '||sum(bytes)/1024/1024 ||' mb' from v$tempfile@:Database

产生ORA-01729: database link name expected是因为未正确解析变量吗?

yeilds ORA-01729: database link name expected because the variable isn't resolved correcltly?

我对SQL还是很陌生

推荐答案

您需要使用动态SQL才能使数据库链接可变.在PL/SQL中，看起来像

You would need to use dynamic SQL in order to have the database link be variable. In PL/SQL, that would look something like

DECLARE
  l_sql_stmt varchar2(1000);
  l_tmp_space varchar2(1000);
  l_database  varchar2(100) := <<db link name>>;
BEGIN
  l_sql_stmt := 'select ''Total temp space available in ' ||l_database  || ' is '' || sum(bytes)/1024/1024 ||'' mb'' from v$tempfile@' || l_database;
  dbms_output.put_line( l_sql_stmt );
  EXECUTE IMMEDIATE l_sql_stmt
    INTO l_tmp_space;
  dbms_output.put_line( l_tmp_space );
END;

基本上，您需要构造一个包含SQL语句的字符串，然后执行该动态生成的字符串(当SQL语句本身像您在此处那样构建一个字符串时，这会更加复杂-这样就可以找出哪个单引号逃脱一点挑战.

Basically, you need to construct a string that contains the SQL statement and then execute that dynamically generated string (that's more complicated when the SQL statement itself is building up a string as yours is here-- that makes figuring out which single quotes to escape a bit of a challenge).

但是，根据您要在APEX中尝试执行的操作，您可能需要一些不同的东西.例如，如果您尝试基于此创建报告，则可以从SQL语句或返回SQL语句的函数填充常规(非交互式)报告.如果这是您要尝试的操作，那么您会想要类似

Depending on what you are trying to do in APEX, however, you may want something a little different. For example, if you are trying to create a report based on this, a regular (non-interactive) report can be populated either from a SQL statement or a function that returns a SQL statement. If that's what you're trying to do, you'd want something like

DECLARE
  l_sql_stmt varchar2(1000);
  l_tmp_space varchar2(1000);
  l_database  varchar2(100) := <<db link name>>;
BEGIN
  l_sql_stmt := 'select ''Total temp space available in ' ||l_database  || ' is '' || sum(bytes)/1024/1024 ||'' mb'' from v$tempfile@' || l_database;
  dbms_output.put_line( l_sql_stmt );
  RETURN l_sql_stmt;
END;

这篇关于数据库链接名称的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！