Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to view oralce implied parameters and format output in SQLPLUS window". In daily operation, I believe many people have doubts about how to view oralce implied parameters and format output in SQLPLUS window. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to view oralce implied parameters and format output in SQLPLUS window". Next, please follow the editor to study!

/ * * function: used to query implicit parameter values in 10g Name In Varchar2 11g, mainly can be formatted in SQLPLUS window output Note Cn_Batch_Length value setting is not necessarily too large author: stotf time: 2020/3/28**/Create Or Replace Function f_Imp_Param (Name In Varchar2) Return Varchar2 Is Vc_Term Varchar2 (100);-- for parameter replacement Vc_Result Long -- return query result Vc_Sql Varchar2 (4000);-- define sql Cur_Kk Sys_Refcursor;-- reference cursor Cn_Batch_Length Constant Pls_Integer: = 300;-- define maximum number of query rows v_Num Number: = 0;-- Line number-- define column header Vc_Parameter Varchar2 (100): = 'PARAMETER'; Vc_Session_Value Varchar2 (100): =' SESSION_VALUE' " Vc_Instance_Value Varchar2: = 'INSTANCE_VALUE'; Vc_Is_Session_Modifiable Varchar2: =' IS_SESSION_MODIFIABLE'; Vc_Is_System_Modifiable Varchar2: = 'IS_SYSTEM_MODIFIABLE' -- maximum actual length Type Length_Col Is Record for recording columns (Para Number: = Length (Vc_Parameter), Sess Number: = Length (Vc_Session_Value), Inst Number: = Length (Vc_Instance_Value), Is_Sess_Mod Number: = Length (Vc_Is_Session_Modifiable), Is_Sys_Mod Number: = Length (Vc_Is_System_Modifiable)); Rec_Length Length_Col -- define the record Type Rec_Result Is Record (Parameter X$ksppi.Ksppinm%Type, Session_Value X$ksppcv.Ksppstvl%Type, Instance_Value X$ksppsv.Ksppstvl%Type, Is_Session_Modifiable Varchar2 (10), Is_System_Modifiable Varchar2 (10)) that matches the query column;-- define the table Type Tab_Result Is Table Of Rec_Result; Typ_Tab Tab_Result that stores the results Begin-processing parameter. If there is no underscore at the beginning, add If Substr (Name, 1,1) ='_ 'Then Vc_Term: =' /'| | Lower (Name); Else Vc_Term: ='/ _'| | Lower (Name); End If Vc_Sql: = 'Select a.Ksppinm "Parameter", b.Ksppstvl "Session Value", c.Ksppstvl "Instance Value", Decode (Bitand (a.Ksppiflg / 256,1), 1,' TRUE'',''FALSE'') Is_Session_Modifiable, Decode (Bitand (a.Ksppiflg / 65536, 3), 1,' IMMEDIATE'', 2 '' Deferred'', 3,''IMMEDIATE'',' 'FALSE'') Is_System_Modifiable From X$ksppi a, X$ksppcv b, X$ksppsv c Where a.Indx = b.Indx And a.Indx = c.Indx And a.Ksppinm Like: name Escape' /'' Open Cur_Kk For Vc_Sql Using Vc_Term | |'%';-- Loop the result to find the maximum length of the actual value of each column, Fetch Cur_Kk Bulk Collect Into Typ_Tab Limit Cn_Batch_Length; For i In 1. Typ_Tab.Count Loop If Length (Typ_Tab (I) .Parameter) > Rec_Length.Para Then Rec_Length.Para: = Length (Typ_Tab (I) .Parameter); End If; If Length (Typ_Tab (I) .Parameter) > Rec_Length.Sess Then Rec_Length.Sess: = Length (Typ_Tab (I) .Session_Value); End If If Length (Typ_Tab (I) .Instance_Value) > Rec_Length.Inst Then Rec_Length.Inst: = Length (Typ_Tab (I) .Instance_Value); End If; If Length (Typ_Tab (I) .Is_Session_Modifiable) > Rec_Length.Is_Sess_Mod Then Rec_Length.Is_Sess_Mod: = Length (Typ_Tab (I) .Is_Session_Modifiable); End If If Length (Typ_Tab (I) .Is_System_Modifiable) > Rec_Length.Is_Sys_Mod Then Rec_Length.Is_Sys_Mod: = Length (Typ_Tab (I) .Is_System_Modifiable); End If; End Loop; Close Cur_Kk; Dbms_Output.Put_Line ('total records:' | | Typ_Tab.Count) -- output column header Vc_Result: ='NO'| | Rpad (Vc_Parameter, Rec_Length.Para + 2,'') | | Rpad (Vc_Session_Value, Rec_Length.Sess + 2,'') | | Rpad (Vc_Instance_Value, Rec_Length.Inst + 2,'') | Rpad (Vc_Is_Session_Modifiable) Rec_Length.Is_Sess_Mod + 2,'') | | Rpad (Vc_Is_System_Modifiable, Rec_Length.Is_Sys_Mod + 2,''| | Chr (10)) -- output record For i In 1.. Typ_Tab.Count Loop v_Num: = v_Num + 1 -- A character followed by a space Prevent the influence of null values on length calculation Vc_Result: = Vc_Result | | Rpad (To_Char (v_Num), 6,'') | | Rpad (Typ_Tab (I) .Parameter | |', Rec_Length.Para + 2,'') | | Rpad (Typ_Tab (I). Session_Value | |', Rec_Length.Sess + 2) '') | | Rpad (Typ_Tab (I) .Instance_Value | |', Rec_Length.Inst + 2,'') | | Rpad (Typ_Tab (I) .Is_Session_Modifiable | |', Rec_Length.Is_Sess_Mod + 2 '') | | Rpad (Typ_Tab (I) .Is_System_Modifiable | |', Rec_Length.Is_Sys_Mod + 2,'') | | Chr (10) End Loop; Dbms_Output.Put_Line (Vc_Result); Return 'total number of records:' | | Sqlcode | | Sqlerrm;End; 'ERROR:' | Sqlcode | | Sqlerrm;End;, the study on "how to view the implicit parameters of oralce and format the output in the SQLPLUS window" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.