Can I use a bind arguments in CURSOR for a schema object? [message #306803] |
Sun, 16 March 2008 21:24  |
crazyzhou
Messages: 38 Registered: March 2008 Location: china
|
Member |
|
|
thanks for you help. the codes is :
DECLARE
v_Owner VARCHAR2(10) := 'CDC';
v_Linkname VARCHAR2(10) := 'RCDC';
v_Tabname VARCHAR2(50);
CURSOR Tabs_Cur(b_Linkname VARCHAR2, b_Owner VARCHAR2) IS
SELECT Table_Name
FROM All_Tables@b_linkname -- the problem is here!
-- FROM ALL_Tables@rcdc -- this statement can work well
WHERE Owner = Upper(b_Owner) AND rownum < 10;
BEGIN
FOR Tabs_Rec IN Tabs_Cur(v_Linkname, v_Owner) LOOP
IF Checkexists(Tabs_Rec.Table_Name, v_Owner) = 1 THEN -- check out whether the table exists.
EXECUTE IMMEDIATE 'drop table ' || Tabs_Rec.Table_Name;
ELSE
v_Tabname := v_Owner || '_' || Tabs_Rec.Table_Name; -- create the table.
EXECUTE IMMEDIATE
--dbms_output.put_line(
'create table ' || v_tabname ||
' AS SELECT * FROM ' || v_owner ||'.'||tabs_rec.table_name||
'@'||v_linkname;
END IF;
END LOOP;
END;
ORA-06550: 第 7 行, 第 5 列:
PL/SQL: ORA-00942: 表或视图不存在
ORA-06550: 第 7 行, 第 5 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 12 行, 第 20 列:
PLS-00364: 循环索引变量 'TABS_REC' 的使用无效
ORA-06550: 第 12 行, 第 5 列:
|
|
|
|
|
|
|
|