Goodbye Log Triggers Welcome Flashback Data Archive Mustafa, 2019-03-022020-10-25 Hello, I would like to talk about Flashback Data Archive (or Flashback Archive – FBA) in 12c. FBA was introduced in 11g. It is not new but it has very important new features that allow us to use FBA very efficiently and for free. I would like to talk about… Continue Reading
How to call a Web Soap Service or API from PLSQL Mustafa, 2019-02-272019-02-27 Hello Everyone, I would like to share one of my codes which allows you to call a web service (soap service) or API through PLSQL. I modified it time to time whenever something new comes up and I still do. I hope it helps you too. Oracle PL/SQL Create Or Replace Function Sf_Call_Web_Service(P_Webservis_Url Varchar2, P_Request_Xml Clob, P_Soap_Action Varchar2 := '', P_Service_Type Varchar2 := 'WEBSERVICE', P_Username Varchar2 := '', P_Password Varchar2 := '', P_Charset Varchar2 := '', P_Request_Content_Type Varchar2 := '') Return Xmltype As Req Utl_Http.Req; Resp Utl_Http.Resp; X_Result_Text Varchar2(32767); X_Result_Clob Clob; X_Result Xmltype; X_Content_Length Number := 0; Begin /* SET YOUR WALLET IF YOU NEED: Utl_Http.Set_Wallet('file:YOUR_WALLET_DIRECTORY_HERE', 'YOUR_WALLET_PASSWORD'); */ Utl_Http.Set_Transfer_Timeout(500); Req := Utl_Http.Begin_Request(P_Webservis_Url, 'POST', Utl_Http.Http_Version_1_1); If P_Username Is Not Null Then Utl_Http.Set_Authentication(Req,P_Username,P_Password); End If; If P_Service_Type = 'WEBSERVICE' Then Utl_Http.Set_Header (Req, 'User-Agent', 'Mozilla/4.0'); Utl_Http.Set_Header (Req, 'Content-Type',Nvl(P_Request_Content_Type, 'text/xml')|| Case When P_Charset Is Not Null Then ';charset='||P_Charset End); Utl_Http.Set_Body_Charset('UTF8'); Utl_Http.Set_Header (Req, 'SOAPAction', P_Soap_Action); Utl_Http.Set_Header ( Req, 'Transfer-Encoding', 'chunked' ); Elsif P_Service_Type = 'API' Then Utl_Http.Set_Header (Req, 'Keep-Alive', '900'); Utl_Http.Set_Header (Req, 'Connection', 'keep-alive'); Utl_Http.Set_Header (Req, 'Content-Type',Nvl(P_Request_Content_Type, 'application/x-www-form-urlencoded')); End If; -- Get Raw Request Length to support special chars like: ş Ğ æ etc. Declare X_Length Pls_Integer := Dbms_Lob.Getlength(P_Request_Xml); X_Data Varchar2(8000); X_Start_Pos Pls_Integer := 1; Begin While X_Start_Pos <= X_Length Loop X_Data := Dbms_Lob.Substr(P_Request_Xml, 4000, X_Start_Pos); X_Content_Length := X_Content_Length + -- my local charset is WE8ISO8859P9, so you can change it Utl_Raw.Length(Utl_Raw.Convert(Utl_Raw.Cast_To_Raw(X_Data),'american_america.al32utf8','AMERICAN_AMERICA.WE8ISO8859P9')); X_Start_Pos := X_Start_Pos + 4000; End Loop; End; Utl_Http.Set_Header (Req, 'Content-Length', X_Content_Length); -- Send Request By Chunks in case request is bigger than 32767 bytes Declare X_Length Pls_Integer := Dbms_Lob.Getlength(P_Request_Xml); X_Data Varchar2(32000); X_Start_Pos Pls_Integer := 1; Begin While X_Start_Pos <= X_Length Loop X_Data := Dbms_Lob.Substr(P_Request_Xml, 32000, X_Start_Pos); -- again my local charset is WE8ISO8859P9, so you can change it Utl_Http.Write_Raw(Req, Utl_Raw.Convert(Utl_Raw.Cast_To_Raw(X_Data), 'american_america.al32utf8', 'AMERICAN_AMERICA.WE8ISO8859P9')); X_Start_Pos := X_Start_Pos + 32000; End Loop; End; -- Get Response Resp := Utl_Http.Get_Response (Req); Dbms_Lob.Createtemporary(X_Result_Clob, True); X_Result_Text := Null; Begin Loop Utl_Http.Read_Text(Resp, X_Result_Text, 32767); Dbms_Lob.Writeappend(X_Result_Clob, Length(X_Result_Text), X_Result_Text); End Loop; Exception When Utl_Http.End_Of_Body Then Utl_Http.End_Response(Resp); End; X_Result_Clob := Ltrim(Ltrim(X_Result_Clob, Chr(10)), Chr(13)); /* just in case if somehow your response contains html codes instead of < or > you can replace them in here like: X_RESULT_CLOB := REPLACE(X_RESULT_CLOB, '<', '<'); X_RESULT_CLOB := REPLACE(X_RESULT_CLOB, '>', '>'); ... */ X_Result := Xmltype.Createxml(X_Result_Clob); Return X_Result; Exception When Others Then -- some logging procedure can be called here Raise; End Sf_Call_Web_Service; 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111 Create Or Replace Function Sf_Call_Web_Service(P_Webservis_Url Varchar2, P_Request_Xml Clob, P_Soap_Action Varchar2 := '', P_Service_Type Varchar2 := 'WEBSERVICE', P_Username Varchar2 := '', P_Password Varchar2 := '', P_Charset Varchar2 := '', P_Request_Content_Type Varchar2 := '') Return Xmltype As Req Utl_Http.Req; Resp Utl_Http.Resp; X_Result_Text Varchar2(32767); X_Result_Clob Clob; X_Result Xmltype; X_Content_Length Number := 0; Begin /* SET YOUR WALLET IF YOU NEED: Utl_Http.Set_Wallet('file:YOUR_WALLET_DIRECTORY_HERE', 'YOUR_WALLET_PASSWORD'); */ Utl_Http.Set_Transfer_Timeout(500); Req := Utl_Http.Begin_Request(P_Webservis_Url, 'POST', Utl_Http.Http_Version_1_1); If P_Username Is Not Null Then Utl_Http.Set_Authentication(Req,P_Username,P_Password); End If; If P_Service_Type = 'WEBSERVICE' Then Utl_Http.Set_Header (Req, 'User-Agent', 'Mozilla/4.0'); Utl_Http.Set_Header (Req, 'Content-Type',Nvl(P_Request_Content_Type, 'text/xml')|| Case When P_Charset Is Not Null Then ';charset='||P_Charset End); Utl_Http.Set_Body_Charset('UTF8'); Utl_Http.Set_Header (Req, 'SOAPAction', P_Soap_Action); Utl_Http.Set_Header ( Req, 'Transfer-Encoding', 'chunked' ); Elsif P_Service_Type = 'API' Then Utl_Http.Set_Header (Req, 'Keep-Alive', '900'); Utl_Http.Set_Header (Req, 'Connection', 'keep-alive'); Utl_Http.Set_Header (Req, 'Content-Type',Nvl(P_Request_Content_Type, 'application/x-www-form-urlencoded')); End If; -- Get Raw Request Length to support special chars like: ş Ğ æ etc. Declare X_Length Pls_Integer := Dbms_Lob.Getlength(P_Request_Xml); X_Data Varchar2(8000); X_Start_Pos Pls_Integer := 1; Begin While X_Start_Pos <= X_Length Loop X_Data := Dbms_Lob.Substr(P_Request_Xml, 4000, X_Start_Pos); X_Content_Length := X_Content_Length + -- my local charset is WE8ISO8859P9, so you can change it Utl_Raw.Length(Utl_Raw.Convert(Utl_Raw.Cast_To_Raw(X_Data),'american_america.al32utf8','AMERICAN_AMERICA.WE8ISO8859P9')); X_Start_Pos := X_Start_Pos + 4000; End Loop; End; Utl_Http.Set_Header (Req, 'Content-Length', X_Content_Length); -- Send Request By Chunks in case request is bigger than 32767 bytes Declare X_Length Pls_Integer := Dbms_Lob.Getlength(P_Request_Xml); X_Data Varchar2(32000); X_Start_Pos Pls_Integer := 1; Begin While X_Start_Pos <= X_Length Loop X_Data := Dbms_Lob.Substr(P_Request_Xml, 32000, X_Start_Pos); -- again my local charset is WE8ISO8859P9, so you can change it Utl_Http.Write_Raw(Req, Utl_Raw.Convert(Utl_Raw.Cast_To_Raw(X_Data), 'american_america.al32utf8', 'AMERICAN_AMERICA.WE8ISO8859P9')); X_Start_Pos := X_Start_Pos + 32000; End Loop; End; -- Get Response Resp := Utl_Http.Get_Response (Req); Dbms_Lob.Createtemporary(X_Result_Clob, True); X_Result_Text := Null; Begin Loop Utl_Http.Read_Text(Resp, X_Result_Text, 32767); Dbms_Lob.Writeappend(X_Result_Clob, Length(X_Result_Text), X_Result_Text); End Loop; Exception When Utl_Http.End_Of_Body Then Utl_Http.End_Response(Resp); End; X_Result_Clob := Ltrim(Ltrim(X_Result_Clob, Chr(10)), Chr(13)); /* just in case if somehow your response contains html codes instead of < or > you can replace them in here like: X_RESULT_CLOB := REPLACE(X_RESULT_CLOB, '<', '<'); X_RESULT_CLOB := REPLACE(X_RESULT_CLOB, '>', '>'); ... */ X_Result := Xmltype.Createxml(X_Result_Clob); Return X_Result; Exception When Others Then -- some logging procedure can be called here Raise; End Sf_Call_Web_Service; Continue Reading
Useful 12c new features for developers Mustafa, 2019-02-252019-02-27 Hello Everyone, I just upgraded one of my customers database to 12c R2 and it has really nice features so I wanted to share some of them for developers. Top N rows: this is a late feature but finally arrived. Before 12c if we want to first N rows from… Continue Reading