Commit Time for an Inserted Row in Oracle Mustafa, 2019-05-25 Hello everyone, This week, one of my customer have experienced a problem. They provide some Web Service to their suppliers and one of those web services creates some rows and then returns the response as successful operation but just after that, supplier queries the newly created those rows but they… Continue Reading
How to Get Your SID? USERENV vs V$MYSTAT! Mustafa, 2019-05-232020-07-05 Hello everyone, This is an easy question. How to find your own session SID and when you ask Google for it, you will find many answers. While reading some documents, I realized that some Gurus are using V$MYSTAT to get current SID: Oracle PL/SQL select sid from v$mystat where rownum=1; 1 select sid from v$mystat where rownum=1; I always prefer USERENV(‘SID’) context to get… Continue Reading
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
Hello world! Mustafa, 2018-06-26 Welcome to WordPress. This is your first post. Edit or delete it, then start writing! Continue Reading