问题描述
我想做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;
这篇关于数据库链接名称的变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!