One case of DDL executed in PL/SQL
来源:互联网 发布:linux tar zip 编辑:程序博客网 时间:2024/06/09 07:12
Please help me in resolving the error below
Code:
=====
DECLARE --count NUMBER;
tname NVARCHAR2(255);
indexname NVARCHAR2(255);
tablename NVARCHAR2(255);
--tname:= 'TABLE_PART_ACTION';
CURSOR indexCursor IS
SELECT index_name,table_name
FROM user_indexes where table_name='TABLE_PART_ACTION';
BEGIN
OPEN indexCursor;
LOOP
FETCH indexCursor INTO indexname, table_name;
EXIT WHEN indexCursor%NOTFOUND;
--select count INTO count from user_indexes where table_name=tname;
IF (indexname!='SYS_C0014096') then
dbms_output.put_line('Testing......');
DROP index indexname;
END IF;
END LOOP;
CLOSE indexCursor;
END;
/
Error:
=====
ORA-06550: line 25, column 6:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:
begin case declare else elsif end exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe
===============
Solution
dineshmurs,
there's a couple of problems with your pl/sql block:
1.) As shammat already stated, you have to use "EXECUTE IMMEDIATE" for DDL statements in PL/SQL
2.) As it-iss stated, SYS_ named indexes should be avoided (they are not guaranteed to have the same name after i.e. exporting/importing a table)
3.) The "IF indexname != <whatever>" should be moved into the query for performance reasons
4.) In the query you should exclude LOB-indexes to avoid ORA-22864 errors
5.) Looping over cursors is one of the most efficient performance killers in PL/SQL.
The reason is, that every loop cycle generates 2 context switches (PLSQL->SQL and back).
Usually you will gain factors in performance by looping over a PL/SQL table instead.
6.) You should code this as stored procedure to avoid n SQL*Net roundtrips when deleting n indexes
My take on that (ignoring my own advise #6) would be:
Code:
-- tested on 11.2.0.1.0
DECLARE
type tIndArr is table of user_indexes%rowtype index by pls_integer;
cursor indexCursor IS
SELECT *
FROM user_indexes
WHERE table_name = 'TEST'
AND index_name != 'PK_TEST' -- index implementing the primary key, like your SYS_C0014096
AND index_type != 'LOB'; -- avoid ORA-22864 on LOB indexes
vIndArr tIndArr;
vCmdStr varchar2(4000);
BEGIN
OPEN indexCursor;
FETCH indexCursor bulk collect into vIndArr;
CLOSE indexCursor;
if vIndArr.COUNT > 0 then
for i in vIndArr.FIRST..vIndArr.LAST loop
vCmdStr := 'DROP INDEX ' || vIndArr(i).index_name;
dbms_output.put_line('About to execute: ' || vCmdStr);
execute immediate vCmdStr;
END LOOP;
end if;
END;
FROM
http://www.dbforums.com/oracle/1655591-getting-problem-while-executing-procedure.html
- One case of DDL executed in PL/SQL
- Using of REF CURSOR in PL/SQL
- Samples of PL/SQL in JDBC
- PL/SQL CASE
- pl/sql执行dml,ddl
- 92 Examine the output of the query that you executed to list the objects in the recycle bin: SQL> SE
- pl/sql case when then
- One case to use of the `this` pointer explicitly in template class inherit
- Just another way of debug logging in Oracle PL/SQL
- Reading sql_plan of executed sql via dbms_xplan.display_awr()
- One case:Latch free of oracle 9208 ?
- Overview of PL/SQL
- Array in PL/SQL
- PL/SQL 中 CASE 语句的使用
- Pl sql case语句应用实例
- pl/sql的case语句总结
- PL/SQL 中 CASE 语句的使用
- oracle pl sql case when case_not_found
- 从今天开始写点东西
- 函数和window对象
- 关于汇编的基本小程序——将输入字符串中的小写字母转换成大写字母并输出
- BBS总汇
- DDL, DML, DCL and TCL
- One case of DDL executed in PL/SQL
- 浅学设计模式之观察者<Observer>模式及在android中的应用
- JPEG驱动(一)
- 关于输入一个字符,判断其是否是数字的汇编程序
- windows 2008 NLB 配置(四)--添加无适配器的 vSphere 标准交换机
- UDP穿透NAT的原理与实现(附源代码)
- 关于dword ptr指令...
- 最具挑战太空任务
- VTK-1000:无法连接到Management Server