在函式中執行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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 執行DDL語句 hang住了怎麼辦?MySql
- WorkBench,DELETE 標準語句失敗delete
- proton執行失敗
- 一條SQL語句在MySQL中如何執行的MySql
- 【ERROR】JOB執行DDL語句報錯ORA-06550 & PLS-00103Error
- 【SQL】Oracle sql語句 minus函式執行效率與join對比SQLOracle函式
- 一條sql語句在mysql中是如何執行的MySql
- 一條 SQL 語句在 MySQL 中是如何執行的?MySql
- 在JS中統計函式執行次數JS函式
- finally語句在return執行之後,return返回之前執行
- oracle檢視物件DDL語句Oracle物件
- Oracle中獲取TABLE的DDL語句的方法Oracle
- Java | 在 Java 中執行動態表示式語句: 前中字尾、Ognl、SpEL、Groovy、Jexl3Java
- mySQL 執行語句執行順序MySql
- MYSQL 中 exists 語句執行效率變低MySql
- 執行緒join為什麼在解構函式中執行緒函式
- GreatSQL執行Update失敗案例分析SQL
- 怎樣在sqlite3上執行SQL語句SQLite
- Java for迴圈中語句執行的順序Java
- SQL語句執行順序SQL
- MySQL語句執行分析(一)MySql
- MySQL語句執行分析(二)MySql
- sql語句如何執行的SQL
- Select語句執行順序
- mysql中建庫、建表、增刪改查DDL語句MySql
- 故障分析 | DDL 導致的 Xtrabackup 備份失敗
- 6.3. 基本SQL語句——6.3.1. DDLSQL
- MySQL入門---(一)SQL的DDL語句MySql
- MySQL DDL執行方式-Online DDL介紹MySql
- en_concat函式編譯失敗處理函式編譯
- 立即執行函式函式
- 自執行函式函式
- 查詢Oracle正在執行的sql語句及執行該語句的使用者OracleSQL
- 探索MySQL高階語句(數學函式、聚合函式、字串函式、日期時間函式)MySql函式字串
- PostgreSQL 函式獲取表DDLSQL函式
- sql語句執行緩慢分析SQL
- mysql的sql語句執行流程MySql
- SQL 語句的執行順序SQL
- mysql 語句的執行順序MySql