测试函数中执行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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/79499/viewspace-417619/