VB Macro Programming

1. Validate data in excel

Tabs: data, reason, prod

Sub ValidateData()
Dim x As Integer

    'set error status and error message columns as Null
    Range("Data!CQ:CQ") = vbNullString
    Range("Data!CR:CR") = vbNullString

   
    'convert complete sheet to text format so that there are no scientific numerics
    Worksheets("Data").Activate
    Worksheets("Data").Range("A:CP").Select
    Selection.NumberFormat = "#"
   
    'Count Number of Rows in Sheet.
    Range("Data!A3").Select
    maxrow = 0
    Do Until ActiveCell.Value = vbNullString
        maxrow = ActiveCell.Row
        ActiveCell.Offset(1, 0).Select
    Loop
   
    'update number of rows in A1
    Cells(1, 1) = maxrow
    maxrow = maxrow + 1
    Range("Data!A1:CQ" & maxrow).Interior.ColorIndex = 15

    'Trx Date Validation Started ############################################
    Range("Data!E3").Select
    Do Until ActiveCell.Row = maxrow
        ' Check if the Trx Date is of Length 11 (DD-MON-YYYY)
        If Len(ActiveCell.Value) <> 11 Then
            ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
            'update the flag = E
            Cells(ActiveCell.Row, 95).Value = "E"
            Cells(ActiveCell.Row, 96).Value = Cells(ActiveCell.Row, 96).Value & "- Trx Date has to be of Length 11"
        Else
            ActiveCell.Interior.ColorIndex = 15 'Retain Grey Color
        End If
       
        ActiveCell.Offset(1, 0).Select
    Loop
    'Trx Date Validation Completed '#############################################

    '##########GL Date Validation Started ############################################
    Range("Data!F3").Select
    Do Until ActiveCell.Row = maxrow
        ' Check if the GL Date is of Length 11
        If Len(ActiveCell.Value) <> 11 Then
            ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
            'update the flag = E
            Cells(ActiveCell.Row, 95).Value = "E"
            Cells(ActiveCell.Row, 96).Value = Cells(ActiveCell.Row, 96).Value & "- GL Date has to be of Length 11"
        Else
            ActiveCell.Interior.ColorIndex = 15 'Retain Grey Color
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
    '#######GL Date Validation Completed '#############################################


 '##########Amount Validation Started ############################################
    Range("Data!AQ3").Select
    Do Until ActiveCell.Row = maxrow
   
    If IsNumeric(ActiveCell.Value) = True Then
   
                Dim av_pos As Integer
                av_pos = InStr(InStr(Cells(ActiveCell.Row, 8).Value, "_") + 3 _
                       , Cells(ActiveCell.Row, 8).Value _
                       , "_")
                        
                Dim av_trxType As String
                'extract CN, DN, GINV from trx type
                av_trxType = Mid(Cells(ActiveCell.Row, 8).Value, av_pos + 2, Len(Cells(ActiveCell.Row, 8).Value) - av_pos + 1)
               
        'MsgBox ("amount validation " & av_trxType)
        If av_trxType = "CN" And ActiveCell.Value > 0 Then
            ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
            'update the flag = E
            Cells(ActiveCell.Row, 95).Value = "E"
            Cells(ActiveCell.Row, 96).Value = Cells(ActiveCell.Row, 96).Value & "- for CN Amount should be < 0"
        Else
            ActiveCell.Interior.ColorIndex = 15 'Retain Grey Color
        End If
   
        If (av_trxType = "DN" Or av_trxType = "INV") And ActiveCell.Value < 0 Then
            ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
            'update the flag = E
            Cells(ActiveCell.Row, 95).Value = "E"
            Cells(ActiveCell.Row, 96).Value = Cells(ActiveCell.Row, 96).Value & "- for DN/inv Amount should be > 0"
        Else
            ActiveCell.Interior.ColorIndex = 15 'Retain Grey Color
        End If
   
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
  '#######Amount Validation Completed '#############################################


    '##########UNIT PRICE Validation Started ############################################
    Range("Data!AQ3").Select
    Do Until ActiveCell.Row = maxrow
   
    If IsNumeric(ActiveCell.Value) = True Then
   
                Dim vup_pos As Integer
                vup_pos = InStr(InStr(Cells(ActiveCell.Row, 8).Value, "_") + 3 _
                       , Cells(ActiveCell.Row, 8).Value _
                       , "_")
                        
                Dim vup_trxType As String
                'extract CN, DN, GINV from trx type
                vup_trxType = Mid(Cells(ActiveCell.Row, 8).Value, vup_pos + 2, Len(Cells(ActiveCell.Row, 8).Value) - vup_pos + 1)
               
   
        'MsgBox ("unit price validation " & vup_trxType)
        If vup_trxType = "CN" And ActiveCell.Value > 0 Then
            ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
            'update the flag = E
            Cells(ActiveCell.Row, 95).Value = "E"
            Cells(ActiveCell.Row, 96).Value = Cells(ActiveCell.Row, 96).Value & "- for CN Unit Price should be < 0"
        Else
            ActiveCell.Interior.ColorIndex = 15 'Retain Grey Color
        End If
   
        If (vup_trxType = "DN" Or vup_trxType = "INV") And ActiveCell.Value < 0 Then
            ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
            'update the flag = E
            Cells(ActiveCell.Row, 95).Value = "E"
            Cells(ActiveCell.Row, 96).Value = Cells(ActiveCell.Row, 96).Value & "- for DN/inv Unit price should be > 0"
        Else
            ActiveCell.Interior.ColorIndex = 15 'Retain Grey Color
        End If
  
   
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
  '#######UNIT PRICE Validation Completed '#############################################



 '##########Reason ############################################
    Range("Data!AA3").Select
    Dim xRow, yCol As Integer
   
    Do Until ActiveCell.Row = maxrow
    'xRow = ActiveCell.Row
    'yCol = ActiveCell.Column
   
    If Len(ActiveCell.Value) <> 0 Then
   
    ' Check if the Reason Code exists in list
        Dim vfound, vTrxTypefound As String
        vfound = vbNullString
        vTrxTypefound = vbNullString
       
        For Each c In Range("ReasonList!A1:A200")
                If c.Value = vbNullString Then
                    Exit For
                End If
           
            If c.Value = ActiveCell.Value Then
             
            vfound = vbNullString
            vTrxTypefound = vbNullString
             
              vfound = "Y"
              Dim trxTypeRef As String
              trxTypeRef = Range("ReasonList!B" & c.Row)
              
                'check if reason code is valid with doc type.
                Dim pos As Integer
                pos = InStr(InStr(Cells(ActiveCell.Row, 8).Value, "_") + 3 _
                       , Cells(ActiveCell.Row, 8).Value _
                       , "_")
                        
                Dim trxType As String
                'extract CN, DN, GINV from trx type
                trxType = Mid(Cells(ActiveCell.Row, 8).Value, pos + 2, Len(Cells(ActiveCell.Row, 8).Value) - pos + 1)
               
               'MsgBox ("trxtype " & trxType)
               'MsgBox ("trxTypeRef " & trxTypeRef)
                             
                             
               If trxType = "INV" Then
                   trxType = "DN"
               End If
              
               If trxType = trxTypeRef Then
                   vTrxTypefound = "Y"
                   'MsgBox ("matched trx types")
               Else
                   'MsgBox ("no match trx types")
                   vTrxTypefound = "N"
               End If
              
              Exit For
            End If
        Next c
       
        If vfound = vbNullString Then
            ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
            'update the flag = E
            Cells(ActiveCell.Row, 95).Value = "E"
            Cells(ActiveCell.Row, 96).Value = Cells(ActiveCell.Row, 96).Value & "- Reason Code Not Found"
        Else
            ActiveCell.Interior.ColorIndex = 15 'Retain Grey Color
        End If
       
        If vTrxTypefound = "N" Then
            ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
            Cells(ActiveCell.Row, 95).Value = "E"
            Cells(ActiveCell.Row, 96).Value = Cells(ActiveCell.Row, 96).Value & "- Reason Code for Trx Type Not Found"
        End If
       
       
       
    End If
   
    ActiveCell.Offset(1, 0).Select
    Loop
    '#######Reason Code Validation Completed '#############################################


 '##########ProductLine Code Validation Started ############################################
    Range("Data!AB3").Select
   
    Do Until ActiveCell.Row = maxrow
   
        If Len(ActiveCell.Value) <> 0 Then
       
        ' Check if the prodline Code exists in list
            Dim vProdLinefound As String
            vProdLinefound = vbNullString
           
            For Each c In Range("ProdList!A1:A1000")
               
                If c.Value = vbNullString Then
                    Exit For
                End If
               
                If c.Value = ActiveCell.Value Then
                  vProdLinefound = "Y"
                  Exit For
                End If
           
            Next c
           
            If vProdLinefound = vbNullString Then
                ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
                'update the flag = E
                Cells(ActiveCell.Row, 95).Value = "E"
                Cells(ActiveCell.Row, 96).Value = Cells(ActiveCell.Row, 96).Value & "- ProductLine Not Found"
            Else
                ActiveCell.Interior.ColorIndex = 15 'Retain Grey Color
            End If
        End If
       
        ActiveCell.Offset(1, 0).Select
    Loop
    '#######ProductLine Code Validation Completed '#############################################

End Sub

001 Learning OOP, Data Structure and Algo Approach

First understand what is OOP, whatever we see in physical word can be replicated in virtual world of coding.

Example Car, Tree, Person, Aeroplane

a. Every physical entity, we need to create it's blueprint, and which you call as Class in OOP b. Each Entity is born with a particular characteristic, which you call as Class Instantiation.

You can think of Tree as Class, which has a root, branches and leafs.

-> when you create it as class, you need to define it's ingredients, and their related data, and behavior of class.

Ingredients -> root, branch, leaf
data-> depth, number of branches, number of leaf
behavior -> add branch, remove branch, add leaf ....

You once you define such a class which can be used to solve problem in real world, and are reusable, may be utilized in data structures.

Stack, Queues, Lists are example of all such physical worlds entities which help us to solve a problem.

 Algo's are the way behaviors are understood and implemented for a data structure in OOP.

Upload Lookup Values

--- API to upload data into FND_LOOKUP_VALUES in R12
DECLARE
    v_error   VARCHAR2(4000);
    CURSOR get_lookuptype_details IS
    SELECT
        ltype.application_id,
        ltype.customization_level,
        ltype.creation_date,
        ltype.created_by,
        ltype.last_update_date,
        ltype.last_updated_by,
        ltype.last_update_login,
        tl.lookup_type,
        tl.security_group_id,
        tl.view_application_id,
        tl.description,
        tl.meaning
    FROM
        fnd_lookup_types_tl   tl,
        fnd_lookup_types      ltype
    WHERE
        ltype.lookup_type = 'XX_LOOKUP_TYPE'
        AND ltype.lookup_type = tl.lookup_type
            AND language = 'US';

    CURSOR get_lookcode_details IS
    SELECT DISTINCT
        lookup_code,
        meaning,
        description
    FROM
        xx_lookup_values
      --WHERE LOOKUP_CODE = '1'
     -- WHERE upload_status <> 'DONE'
        ;

    l_rowid   VARCHAR2(100) := 0;
BEGIN
    FOR i IN get_lookuptype_details LOOP
        FOR j IN get_lookcode_details LOOP
            l_rowid := NULL;
            BEGIN
                fnd_lookup_values_pkg.insert_row(x_rowid => l_rowid, x_lookup_type => i.lookup_type, x_security_group_id => i.security_group_id
                , x_view_application_id => i.view_application_id, x_lookup_code => j.lookup_code,
                                                 x_tag => '', x_attribute_category => NULL, x_attribute1 => NULL, x_attribute2 =>
                                                 NULL, x_attribute3 => NULL,
                                                 x_attribute4 => NULL, x_enabled_flag => 'Y', x_start_date_active => NULL, x_end_date_active
                                                 => NULL, x_territory_code => NULL,
                                                 x_attribute5 => NULL, x_attribute6 => NULL, x_attribute7 => NULL, x_attribute8 =
                                                 > NULL, x_attribute9 => NULL,
                                                 x_attribute10 => NULL, x_attribute11 => NULL, x_attribute12 => NULL, x_attribute13
                                                 => NULL, x_attribute14 => NULL,
                                                 x_attribute15 => NULL, x_meaning => j.meaning, x_description => j.description, x_creation_date
                                                 => sysdate, x_created_by => i.created_by,
                                                 x_last_update_date => i.last_update_date, x_last_updated_by => i.last_updated_by
                                                 , x_last_update_login => i.last_update_login);

                COMMIT;
                UPDATE xx_lookup_values
                SET
                    upload_status = 'DONE'
                WHERE
                    lookup_code = j.lookup_code;

            EXCEPTION
                WHEN OTHERS THEN
                    v_error := ( sqlerrm );
                    UPDATE xx_lookup_values
                    SET
                        upload_status = 'Inner Exception: ' || v_error
                    WHERE
                        lookup_code = j.lookup_code;

            END;

        END LOOP;
    END LOOP;
--EXCEPTION
--   WHEN OTHERS
--   THEN
--      update XX_COUNTRY set upload_status='Main Exception: ' || V_ERROR  WHERE LOOKUP_CODE=J.LOOKUP_CODE;
END;

SELECT
    COUNT(*)
FROM
    fnd_lookup_values
WHERE
    lookup_type = 'XX_LOOKUP_TYPE'
    AND language = 'US'

-------------------------------custom table create------------------------------

--drop table XX_LOOKUP_VALUES
CREATE TABLE xx_lookup_values (
    lookup_code     VARCHAR2(50 BYTE),
    meaning         VARCHAR2(200 BYTE),
    description     VARCHAR2(200 BYTE),
    upload_status   VARCHAR2(2000 BYTE)
)

Concurrent Programs

1. PLSQL Based Concurrent Programs:


CREATE OR REPLACE PACKAGE xxcust_pkg AS

   --     Script Name   : 
   --     Created by    :  Abhishek Agrawal
   --     Creation date : 
   --     Purpose       : 
   --     Program History :
   --     Date                Version           Author  Description
   --     ----------------------------------------------------------
   --
   -- **************************************************************
   -----------------------------------------------------------------
    PROCEDURE main (
        x_errbuf       OUT   VARCHAR2,
        x_retcode      OUT   NUMBER,
        p_org_id       IN    NUMBER,
        p_start_date   IN    VARCHAR2,
        p_end_date     IN    VARCHAR2
    );

END xxcust_pkg;

create or replace PACKAGE BODY xxcust_pkg AS
   --     Date                Version           Author               Description
   --     ----------------------------------------------------------------------
   -- **************************************************************************
   -----------------------------------------------------------------------------
    PROCEDURE main (
    x_errbuf           OUT   VARCHAR2,
    x_retcode          OUT   NUMBER,
    p_org_id           IN    NUMBER,
    p_start_date          IN    varchar2,
    p_end_date            in    varchar2
    )
    AS
    ld_start_date      DATE;
    ld_end_date        DATE;
    BEGIN

      fnd_file.put_line (fnd_file.log, '************     PARAMETERS    *******');
      fnd_file.put_line (fnd_file.log, 'p_org_id -  '     || p_org_id);
      fnd_file.put_line (fnd_file.log, 'p_start_date -  ' || p_start_date);
      fnd_file.put_line (fnd_file.log, 'p_end_date- '     || p_end_date);
      fnd_file.put_line (fnd_file.log, '**************************************');
      ld_start_date := fnd_date.canonical_to_date(p_start_date);
      ld_end_date   := fnd_date.canonical_to_date(p_end_date);

    EXCEPTION
        WHEN OTHERS THEN
            fnd_file.put_line(fnd_file.log,' MAIN error'|| ' - '|| sqlerrm);
    end main;

end xxcust_pkg;


Define Concurrent Program:
p_OrgID: Value : Default: Profile: ORG_ID
dates: value set: fnd_standard_date


2. Host Based Programs

#---------------------------------------------------------------------------
#Program Name   :
#REM $Header    :
#Program Type   : Shell Script
#File Name      :
#---------------------------------------------------------------------------
#Revision:
#Ver        Date                    Author              Description
#
#---------------------------------------------------------------
#Submitting a concurrent program to run generate the extract and insert it into staging table
# from LASR. These variables are $0-$6 respectively.
# $0 - Shellscript name
# $1 - Oracle username/password
# $2 - User_id
# $3 - LASR(Oracle Applications) username
# $4 - Concurrent request id
# $5 - Parameter 1 (start date)
# $6 - Parameter 2 (end date)

echo "1. appsUserP : "$1
echo "2. UserId    : "$2
echo "3. UserName  : "$3
echo "4. RequestID : "$4
echo "5. Param 1   : "$5
echo "6. ParamDt 2 : "$6
echo "7. ParamDt 3 : "$7
echo "8. RespKey   : "$8
echo "9. ApplShortName : "$9

#Calculate Dt Param
start_date=`echo $6 | cut -f1 -d" "`
end_date=`echo $7 | cut -f1 -d" "`
echo $start_date
echo $end_date

echo"Submitting a concurrent program to run generate the extract and insert it into staging table"
v_conc_request=`CONCSUB $1 ${9} '"'$8'"' $3  WAIT = 10 CONCURRENT <XXCUST> <CP_SHORT_NAME> $5 $start_date $end_date`

echo "v_conc_req: "$v_conc_request
v_conc_id=`echo $v_conc_request | cut -f3 -d" "`
echo "v_conc_id: "$v_conc_id

3. Submit a Program from PLSQL

DECLARE
                l_org_id                  VARCHAR2(10);
                l_request_id              NUMBER(15) := 0;
                l_rec_status_cnt          VARCHAR2(100) := 'X';
                l_user_id                 fnd_user.user_id%TYPE;
                l_resp_id                 fnd_responsibility_tl.responsibility_id%TYPE;
                l_resp_appl_id            fnd_responsibility_tl.application_id%TYPE;
                l_error_message           VARCHAR2(2000 BYTE);
                p_result                  VARCHAR2(10);
                lv_req_phase              VARCHAR2(200);
                lv_req_status             VARCHAR2(200);
                lv_req_dev_phase          VARCHAR2(200);
                lv_req_dev_status         VARCHAR2(200);
                lv_req_message            VARCHAR2(200);
                lv_req_return_status      BOOLEAN;
                l_chr_message_data        VARCHAR2(200);
                l_chr_ret_status          VARCHAR2(200);

begin


     BEGIN
         SELECT user_id INTO l_user_id FROM apps.fnd_user WHERE user_name = '<USER_NAME>';
     EXCEPTION
         WHEN OTHERS THEN dbms_output.put_line('l_user_id: ERROR'|| ':'|| sqlerrm);
     END;

     BEGIN
         SELECT application_id, responsibility_id INTO l_resp_appl_id, l_resp_id
         FROM fnd_responsibility_tl
         WHERE responsibility_name = '<responsibility_name>';

     EXCEPTION
         WHEN OTHERS THEN dbms_output.put_line('l_resp_appl_id: ERROR'|| ':'|| sqlerrm);
     END;

     IF ( l_user_id IS NOT NULL AND l_resp_id IS NOT NULL AND l_resp_appl_id IS NOT NULL ) THEN
         fnd_global.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);
         p_result := 'TRUE';
     ELSE
         p_result := 'FALSE';
     END IF;

     dbms_output.put_line('p_result:'|| ':'|| p_result);


IF p_result = 'TRUE' THEN
    dbms_output.put_line('l_user_id: ERROR'|| ':'|| sqlerrm);
    dbms_output.put_line('Submitting Concurrent Request: ****');
    BEGIN
        l_request_id := fnd_request.submit_request(application => 'XXCUST', program => 'CCXUST_CONC_PROG_SHORT_NAME', argument1=> p_param1);
        COMMIT;
        dbms_output.put_line('Submitting Concurrent Request: l_request_id ****'|| l_request_id|| fnd_message.get|| '".');
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Exception Concurrent Request: l_request_id ****'|| l_request_id|| sqlerrm);
    END;

    dbms_output.put_line('Submitting Concurrent Request: l_request_id ****' || l_request_id);
   
    --WAIT FOR REQ
    IF l_request_id = 0 THEN
        dbms_output.put_line('Submitting Concurrent Request: l_request_id eRR****' || l_request_id);
    END IF;
    IF ( l_request_id > 0 ) THEN
        LOOP
       
        --function wait_for_request(request_id IN number default NULL,
        --  interval   IN  number default 60,
        --  max_wait   IN  number default 0,
        --  phase      OUT NOCOPY varchar2,
        --  status     OUT NOCOPY varchar2,
        --  dev_phase  OUT NOCOPY varchar2,
        --  dev_status OUT NOCOPY varchar2,
        --  message    OUT NOCOPY varchar2) return  boolean;
         
            lv_req_return_status := fnd_concurrent.wait_for_request
            (l_request_id,
             60,  --interval IN SECOND
             0,   -- max_wait
             lv_req_phase,
             lv_req_status,
             lv_req_dev_phase,
             lv_req_dev_status,
             lv_req_message
             );

            EXIT WHEN upper(lv_req_phase) = 'COMPLETED' OR upper(lv_req_status) IN (
                'CANCELLED',
                'ERROR',
                'TERMINATED'
            );

        END LOOP;

        IF upper(lv_req_phase) = 'COMPLETED' AND upper(lv_req_status) = 'ERROR' THEN
            dbms_output.put_line('Submitting Concurrent Request: l_request_id ****' || 'FAILED');
        ELSIF upper(lv_req_phase) = 'COMPLETED' AND upper(lv_req_status) = 'NORMAL' THEN
            dbms_output.put_line('Submitting Concurrent Request: l_request_id ****' || 'COMPLETED');
        ELSE
            dbms_output.put_line('Submitting Concurrent Request: l_request_id ****' || 'ERROR');
        END IF;

    END IF; --l_request_id > 0

END IF; --P_RESULT

END;

END;


4. Submit a Concurrent Program from Host Program

5.

Calling a HTTP and HTTPS using PLSQL using oracle wallet

Steps: 
Installed oracle xe 18c on windows 64 bit
Oracle developer suite 10g
Oracle Wallet Manager
Website Refer:

http://jsonplaceholder.typicode.com/posts/1
https://jsonplaceholder.typicode.com/posts/1
Refer: https://oracle-base.com/articles/misc/utl_http-and-ssl


--step01 from sys
create user web_user IDENTIFIED BY web_user;
grant execute on utl_http to web_user;
grant create procedure to web_user;
grant create session to web_user;

step02 create acl
--run from sys
--acl => web_user_all_open.xml

BEGIN
    dbms_network_acl_admin.create_acl(
             acl            => 'web_user_all_open.xml'
            ,description    => 'web user acl blanket access'
            ,principal      => 'WEB_USER'
            ,is_grant       => true
            ,privilege      => 'connect'
            ,start_date     => systimestamp
            ,end_date       => NULL
            );
    commit;
END;

-----

BEGIN
    dbms_network_acl_admin.assign_acl(acl => 'web_user_all_open.xml',host => '*',lower_port => 1,upper_port => 9999);
    COMMIT;
END;

--
SELECT * FROM dba_network_acls;
SELECT * FROM dba_network_acl_privileges;

step03-- create a procedure

 CREATE OR REPLACE PROCEDURE show_html_from_url (
    p_url        IN VARCHAR2,
    p_username   IN VARCHAR2 DEFAULT NULL,
    p_password   IN VARCHAR2 DEFAULT NULL
) AS
    l_http_request    utl_http.req;
    l_http_response   utl_http.resp;
    l_text            VARCHAR2(32767);
BEGIN
  -- Make a HTTP request and get the response.
    l_http_request := utl_http.begin_request(p_url);

  -- Use basic authentication if required.
    IF
        p_username IS NOT NULL AND p_password IS NOT NULL
    THEN
        utl_http.set_authentication(l_http_request,p_username,p_password);
    END IF;

    l_http_response := utl_http.get_response(l_http_request);

  -- Loop through the response.
    BEGIN
        LOOP
            utl_http.read_text(l_http_response,l_text,32766);
            dbms_output.put_line(l_text);
        END LOOP;
    EXCEPTION
        WHEN utl_http.end_of_body THEN
            utl_http.end_response(l_http_response);
    END;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('exception'||sqlerrm);
        utl_http.end_response(l_http_response);
        RAISE;
END show_html_from_url;
/
  
step04
SET SERVEROUTPUT ON;

BEGIN
    show_html_from_url('http://jsonplaceholder.typicode.com/posts/1');
END;

--{
--  "userId": 1,
--  "id": 1,
--  "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
--  "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
--}
--

--PL/SQL procedure successfully completed.

step05

SET SERVEROUTPUT ON;

BEGIN
    show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
END;
/

--exceptionORA-29273: HTTP request failed
--
--
--Error starting at line : 3 in command -
--BEGIN
--    show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
--END;
--Error report -
--ORA-29273: HTTP request failed
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 37
--ORA-29024: Certificate validation failure
--ORA-06512: at "SYS.UTL_HTTP", line 380
--ORA-06512: at "SYS.UTL_HTTP", line 1148
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 11
--ORA-06512: at line 2
--29273. 00000 -  "HTTP request failed"
--*Cause:    The UTL_HTTP package failed to execute the HTTP request.
--*Action:   Use get_detailed_sqlerrm to check the detailed error message.
--           Fix the error and retry the HTTP request.


-- we need to do the oracle wallet manager:
--https://appsoracle-abhi.blogspot.com/2020/01/create-oracle-wallet-in-windows.html
--create wallet
--F:\DevSuiteHome_1\BIN>mkstore -wrl "C:\Users\abhishek\tmp_w" -create
--Enter password:
--add cerification in wallet
--F:\DevSuiteHome_1\BIN>orapki wallet add -wallet "C:\Users\abhishek\tmp_w" -trusted_cert -cert "F:\git-hub-repos\learnings\adv sql\plsql_steven feuerstein\ACL_HTTP_CHTTP\AddTrustExternalCARoot.crt"
--Enter wallet password:
--

refer links above for how to download the AddTrustExternalCARoot.crt file for a url

07: calling a https service or http over ssl
SET SERVEROUTPUT ON;

BEGIN
    UTL_HTTP.set_wallet('file:C:\Users\abhishek\tmp_w', 'abc@123');
    show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
END;
/

--ORA-29273: HTTP request failed
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 37
--ORA-28759: failure to open file--ORA-06512: at "SYS.UTL_HTTP", line 380
--ORA-06512: at "SYS.UTL_HTTP", line 1148
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 11
--ORA-06512: at line 3
--29273. 00000 -  "HTTP request failed"
--*Cause:    The UTL_HTTP package failed to execute the HTTP request.
--*Action:   Use get_detailed_sqlerrm to check the detailed error message.
--           Fix the error and retry the HTTP request.

--resolution
--Wallet Manager set permissions on the files, so they can only be read by the user who created them. Either modify the permissions or copy the files to another location.
--And then in your "set_wallet" reference the new location.
--I copied the wallet file from above location to another location, and in windows right click and unchecked read only on folders containting the the ewallet.p12 file.

SET SERVEROUTPUT ON;

BEGIN
    UTL_HTTP.set_wallet('file:F:\wallet\tmp_w', 'abc@123');
    show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
--    exception when others then
--    dbms_output.put_line('err'||sqlerrm);
END;
/
--
--{
--  "userId": 1,
--  "id": 1,
--  "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
--  "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
--}
--
--
--PL/SQL procedure successfully completed.

REFER:
1. https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1
2. https://oracle-base.com/articles/misc/utl_http-and-ssl
3. https://technology.amis.nl/2015/05/11/invoke-a-rest-service-from-plsql-make-an-http-post-request-using-utl_http-in-oracle-database-11g-xe/
4. https://jsonplaceholder.typicode.com/
5.https://www.nyquest.com/showpage/making-api-calls-from-oracle
6.https://www.dbasolutions.in/2017/02/understanding-dbmsnetworkacladmin-with.html
7.https://anjo.pt/wp/keyword-oracle/2019/09/