After 12c, unfortunately, we lost Enterprise Manager Console but we have pre-installed EM Express now. if you remember EM console you would install it with emca utility with many parameters. EM express save you from this because it is embedded. you just need to set a few things in your DB. if you already try […]
Author: Mustafa
Commit Time for an Inserted Row in Oracle
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 get “no rows”! When analyze […]
How to Get Your SID? USERENV vs V$MYSTAT!
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:
|
1 |
select sid from v$mystat where rownum=1; |
I always prefer USERENV(‘SID’) context to get SID and curious about which […]
Goodbye Log Triggers Welcome Flashback Data Archive
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 new features more than what […]
How to call a Web Soap Service or API from PLSQL
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
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; |
Useful 12c new features for developers
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 an ordered dataset we would […]
Hello world!
Welcome to WordPress. This is your first post. Edit or delete it, then start writing!