在函式中執行DDL語句失敗
測試函式中執行DDL語句,看piner的FAQ裡面說可以,但是我在執行的時候報錯,難道它說的PL/SQL不是指這些物件?
Create Or Replace Function Create_Cols(Ow In String, Tab_Name In String)
Return Varchar2 Is
Cols Varchar2(4000);
Num Number(8);
-- Len Number(8);
Cursor All_Cols Is
Select Column_Name
From All_Tab_Cols
Where Owner = Ow
And Table_Name = Tab_Name
Order By Internal_Column_Id;
Cursor Get_Sent Is
Select 'insert into len_cols Select ''' || Column_Name ||
''', Max(length(' || Column_Name || ')) From ' || Owner || '.' ||
Table_Name || ''
From All_Tab_Cols
Where Owner = Ow
And Table_Name = Tab_Name;
Type Studydate Is Table Of Varchar2(400) Index By Binary_Integer;
v_Studydate Studydate;
v_Get_Sent Studydate;
Begin
Select Count(Column_Name)
Into Num
From All_Tab_Cols
Where Owner = Ow
And Table_Name = Tab_Name;
Open All_Cols;
Open Get_Sent;
For i In 1 .. Num Loop
Fetch All_Cols
Into v_Studydate(i);
Fetch Get_Sent
Into v_Get_Sent(i);
End Loop;
For i In 1 .. Num Loop
/* Execute Immediate 'comment on column BUDGET_FILE.B_FILE_ID is ''eee''';*/
Dbms_Utility.Exec_Ddl_Statement('comment on column BUDGET_FILE.B_FILE_ID is ''eee''');
Dbms_Output.Put_Line(v_Get_Sent(i));
End Loop;
Select Substr(Cols, 1, Length(Cols) - 1) Into Cols From Dual;
Close All_Cols;
Close Get_Sent;
Return Cols;
End Create_Cols;
報錯:
ORA-14552在查詢或DML中無法執行DDL,提交或回退
Create Or Replace Function Create_Cols(Ow In String, Tab_Name In String)
Return Varchar2 Is
Cols Varchar2(4000);
Num Number(8);
-- Len Number(8);
Cursor All_Cols Is
Select Column_Name
From All_Tab_Cols
Where Owner = Ow
And Table_Name = Tab_Name
Order By Internal_Column_Id;
Cursor Get_Sent Is
Select 'insert into len_cols Select ''' || Column_Name ||
''', Max(length(' || Column_Name || ')) From ' || Owner || '.' ||
Table_Name || ''
From All_Tab_Cols
Where Owner = Ow
And Table_Name = Tab_Name;
Type Studydate Is Table Of Varchar2(400) Index By Binary_Integer;
v_Studydate Studydate;
v_Get_Sent Studydate;
Begin
Select Count(Column_Name)
Into Num
From All_Tab_Cols
Where Owner = Ow
And Table_Name = Tab_Name;
Open All_Cols;
Open Get_Sent;
For i In 1 .. Num Loop
Fetch All_Cols
Into v_Studydate(i);
Fetch Get_Sent
Into v_Get_Sent(i);
End Loop;
For i In 1 .. Num Loop
/* Execute Immediate 'comment on column BUDGET_FILE.B_FILE_ID is ''eee''';*/
Dbms_Utility.Exec_Ddl_Statement('comment on column BUDGET_FILE.B_FILE_ID is ''eee''');
Dbms_Output.Put_Line(v_Get_Sent(i));
End Loop;
Select Substr(Cols, 1, Length(Cols) - 1) Into Cols From Dual;
Close All_Cols;
Close Get_Sent;
Return Cols;
End Create_Cols;
報錯:
ORA-14552在查詢或DML中無法執行DDL,提交或回退
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/79499/viewspace-417619/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 使用自治事務在觸發器中執行DDL語句示例觸發器
- goldengate ddl_setup執行失敗處理Go
- 在事務中執行sql語句SQL
- 在nhibernate中執行SQL語句SQL
- MySQL 執行DDL語句 hang住了怎麼辦?MySql
- 執行oracle DDL語句要注意的問題Oracle
- 建立函式失敗函式
- WorkBench,DELETE 標準語句失敗delete
- proton執行失敗
- 再論執行oracle DDL語句要注意的問題Oracle
- 一條SQL語句在MySQL中如何執行的MySql
- 一條sql語句在mysql中是如何執行的MySql
- 一條 SQL 語句在 MySQL 中是如何執行的?MySql
- 在JS中統計函式執行次數JS函式
- 【SQL】Oracle sql語句 minus函式執行效率與join對比SQLOracle函式
- 在單獨執行緒中執行物件成員函式 (轉)執行緒物件函式
- javascript return語句只能用於函式中JavaScript函式
- 通過儲存過程執行通過DBLINK的查詢語句失敗-單個語句成功--ORA-00604儲存過程
- Oracle 儲存過程中的DDL語句Oracle儲存過程
- 從Export Dumpfile file 中讀出DDL 語句Export
- Oracle 獲取ddl語句Oracle
- PL/SQL與DDL語句SQL
- 執行緒join為什麼在解構函式中執行緒函式
- finally語句在return執行之後,return返回之前執行
- GreatSQL執行Update失敗案例分析SQL
- 通過flashback_transaction_query查詢最近執行過的所有DDL語句
- MYSQL 中 exists 語句執行效率變低MySql
- mySQL 執行語句執行順序MySql
- Java | 在 Java 中執行動態表示式語句: 前中字尾、Ognl、SpEL、Groovy、Jexl3Java
- JavaScript中的立即執行函式JavaScript函式
- Oracle中獲取TABLE的DDL語句的方法Oracle
- javascript中的自執行(立即執行)函式(function(){…})()JavaScript函式Function
- oracle rac中讓sql語句在指定的節點執行的方法OracleSQL
- 在ASP程式中執行SQL語句的安全性問題 (轉)SQL
- for語句執行順序
- sql語句批量執行SQL
- FORALL執行DELETE語句delete
- FORALL執行UPDATE語句