Parsing CSV Lines and columns in PLSQL Mustafa, 2020-06-032020-06-03 Hi, Yesterday, one of my customers asked for help to tune a procedure and when I investigated the problem I find out that their method to get a specific “column” value in csv file was the problem. So we discuss the situation and adopt a new version of the code…. Continue Reading
How to convert a LONG column to CLOB directly in a query Mustafa, 2020-04-062020-04-06 Hi, This is a big problem since LONG is a very painful data type. You cannot search in it, use it in CTAS (create table as), pass it to a plsql object as parameter etc. Oracle strongly suggest not to use a LONG column anymore but unfortunately there are many… Continue Reading
Insert multi line text data using sqlldr Mustafa, 2019-07-052020-01-09 Hello Everyone, I just answered an OTN question about inserting multi line data using sqlldr and simply wanted to share it here (https://community.oracle.com/thread/4278952) so if have a data like this in your text file: Oracle PL/SQL 1,"this is two line data", 10 2,"this is three line data", 11 3,"this is one line data", 12 4,this, 13 1234567 1,"this istwo line data", 102,"this isthree linedata", 113,"this is one line data", 124,this, 13 you need to do some extra work to accomplish this problem. In my example I… 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