Useful Scripts

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:

1,”this is

two line data”, 10

2,”this is

three line

data”, 11

3,”this is one line data”, 12

4,this, 13

you need to do some extra work to accomplish this problem. In my example I have 4 rows but 2 of them includes multi line character data. By default sqlldr uses “newline” character (actually maybe saying enter character could be more appropriate) and every new line will be accepted as new row! To solve this problem you need to change your “row delimiter/terminator” but this will bring some extra burden because your current data file (csv, txt etc) is needed to be changed and your “new” row delimiter must be added to end of every row!

this is my table:

create table tmp_x(
  id1 number, 
  my_char_data varchar2(80),
  some_number number);

create table tmp_x(

id1 number,

my_char_data varchar2(80),

some_number number);

This row delimiter could be more than one character and it should be because row delimiter must not be seen in your original data. for example if you have comma in your data you can not use comma as row delimiter because every comma will be count as end of row. In my example I will use $%&/| as row delimiter.

First change your data file and add new delimiter to your data:

1,”this is

two line data”, 10$%&/|

2,”this is

three line

data”, 11$%&/|

3,”this is one line data”, 12$%&/|

4,this, 13$%&/|

secondly, we need to specify this new delimiter in our control file:

LOAD DATA
infile a.csv "STR X'2425262F7C'"

truncate INTO TABLE tmp_x
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(id1,my_char_data,some_number)

LOAD DATA

infile a.csv "STR X'2425262F7C'"

truncate INTO TABLE tmp_x

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

(id1,my_char_data,some_number)

probably you already saw “STR X’2425262F7C'”. this is specifying new row delimiter. X means this is an hex code and 2425262F7C is the hex code of my delimiter $%&/|

how to produce this:

select UTL_RAW.cast_to_raw(‘$%&/|’) from dual;
————————
2425262F7C

so you can use any combination for your delimiter and just simply use utl_raw to generate hex code and use this in controlfile.

rest is same. just run sqlldr command with this control file and it will load the rows as expected.

select * from tmp_x;

       ID1 MY_CHAR_DATA               SOME_NUMBER
---------- -------------------------- -----------
         1 this is                    10
           two line data
         2 this is                    11
           three line
           data
         3 this is one line data      12
         4 this                       13

ID1 MY_CHAR_DATA SOME_NUMBER

---------- -------------------------- -----------

1 this is 10

two line data

2 this is 11

three line

data

3 this is one line data 12

4 this 13

please consider that your data must be in another file than control file! you can not use “infile *” and BEGINDATA in this structure.

I hope this helps.

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.

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, '&lt;', '<');
    X_RESULT_CLOB := REPLACE(X_RESULT_CLOB, '&gt;', '>');
    ...
    */
    
    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;

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;

No need to follow standards.

Useful Scripts

Insert multi line data using sqlldr

How to call a Web Soap Service or API from PLSQL