Home » SQL & PL/SQL » SQL & PL/SQL » Can I use a bind arguments in CURSOR for a schema object? (oracle 10.2.0 , RHEL5)
Can I use a bind arguments in CURSOR for a schema object? [message #306803] Sun, 16 March 2008 21:24 Go to next message
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 列:
Re: Can I use a bind arguments in CURSOR for a schema object? [message #306804 is a reply to message #306803] Sun, 16 March 2008 21:58 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
00942, 00000, "table or view does not exist"
Re: Can I use a bind arguments in CURSOR for a schema object? [message #306851 is a reply to message #306803] Mon, 17 March 2008 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Re: Can I use a bind arguments in CURSOR for a schema object? [message #306856 is a reply to message #306851] Mon, 17 March 2008 02:11 Go to previous messageGo to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
Thanks michel for your kindly advice. I got it.
but it seems I cannot change the format now.
Re: Can I use a bind arguments in CURSOR for a schema object? [message #306862 is a reply to message #306856] Mon, 17 March 2008 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use a variable for db link name, table name, column name and so on, only for values.

Regards
Michel
Re: Can I use a bind arguments in CURSOR for a schema object? [message #307133 is a reply to message #306862] Mon, 17 March 2008 22:56 Go to previous message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
Ok, thanks. Michel
Previous Topic: number precision
Next Topic: Get count of first 5000 rows from the result set
Goto Forum:
  


Current Time: Mon Dec 05 19:09:30 CST 2016

Total time taken to generate the page: 0.17008 seconds