Need help on rest api call

Hi Team,

I have an urgent requirement , I need to write a stored procedure which makes an rest api call and it gets an 100's of inerts statement as output. My question is
how to use the UTL_HTTP package to make this call and get the output. Any help is really appreciated. I am not able to figure out should i use XML soap or json .
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ask the web service owner.  if it's soap, there may be a wdl.
sam_2012Author Commented:
its an rest api
you still need to ask the web service owner.

Nobody here knows what service you are trying to invoke or what its inputs are, if any or what the formats of them are.

maybe you need to issue a POST of some json, maybe you just need to supply parameters in a url.

for example this is a simple REST invocation of Google's geocoding api which returns json, but does not require a POST or PUT

select httpuritype(',+San+Luis+Obispo+CA').getclob() from dual
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sam_2012Author Commented:
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'http_acl.xml',
                                    description => 'HTTP ACL',
                                    principal   => 'HR',
                                    is_grant    => true,
                                    privilege   => 'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'http_acl.xml',
                                       principal => 'HR',
                                       is_grant  => true,
                                       privilege => 'resolve');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'http_acl.xml',
                                    host => 'PATHNAME');

select httpuritype('URLpath').getclob() from dual;

Below is the output Iam getting

insert into some_table values ('abc01f','abc01f01',' ab1f3','abl','16-DEC-15');<br>insert into some_table values ('abc01p','abc01p01',' abc1f6','abcf03','01-APR-16');<br>insert into some_table values ('abc01s','abc01s02','abcf9','abcdb02' ,'01-APR-16');

which is a clob.

Iam storing it in a temp table . I need to break these statements into individual insert statements

I tried the below

Select replace (col_data ,ltrim('<Br>)', chr(10)||'union all' || chr(10) ) from dual.

But I need to run these as a single insert command , any help is really appreciated.
What you are asking for is kind of dangerous because it's just begging for a sql injection attack.
But, if your web service is a trusted source then you could try something like this...

Note the ending ';' on each insert isn't legal in an execute immediate, so we'll use RTRIM to remove them after parsing the lines out

    FOR x
        IN (SELECT RTRIM(COLUMN_VALUE, ';') insert_statement
              FROM TABLE(
    --EXECUTE IMMEDIATE x.insert_statement;  -- if you trust the statements, then remove the dashes at the beginning

Open in new window

The split clob function was pulled from here:

CREATE OR REPLACE FUNCTION split_clob(p_clob IN CLOB, p_delimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
    RETURN vcarray
    --                    .///.
    --                   (0 o)
    --  Sean D. Stuber
    --             oooO      Oooo
    --------------(   )-----(   )---------------
    --             \ (       ) /
    --              \_)     (_/

    c_chunk_limit   CONSTANT INTEGER := 32767;
    v_clob_length            INTEGER := DBMS_LOB.getlength(p_clob);
    v_clob_index             INTEGER;
    v_chunk                  VARCHAR2(32767);
    v_chunk_end              INTEGER;
    v_chunk_length           INTEGER;
    v_chunk_index            INTEGER;
    v_delim_len              INTEGER := LENGTH(p_delimiter);
    v_line_end               INTEGER;
    v_clob_length := DBMS_LOB.getlength(p_clob);
    v_clob_index := 1;

    WHILE v_clob_index <= v_clob_length
            Pull one 32K chunk off the clob at a time.
            This is because it's MUCH faster (approx 25 times faster) to use built in functions
            on a varchar2 type than to use dbms_lob functions on a clob.         
        v_chunk := DBMS_LOB.SUBSTR(p_clob, c_chunk_limit, v_clob_index);

        IF v_clob_index > v_clob_length - c_chunk_limit
            -- if we walked off the end the clob,
            -- then the chunk is whatever we picked up at the end
            -- delimited or not
            v_clob_index := v_clob_length + 1;
            v_chunk_end := INSTR(v_chunk, p_delimiter, -1);

            IF v_chunk_end = 0
                DBMS_OUTPUT.put_line('No delimiters found!');
            END IF;

            v_chunk := SUBSTR(v_chunk, 1, v_chunk_end);
            v_clob_index := v_clob_index + v_chunk_end + v_delim_len - 1;
        END IF;

            Given a varchar2 chunk split it into lines

        v_chunk_index := 1;
        v_chunk_length := NVL(LENGTH(v_chunk), 0);

        WHILE v_chunk_index <= v_chunk_length
            v_line_end := INSTR(v_chunk, p_delimiter, v_chunk_index);

            IF v_line_end = 0 OR (v_line_end - v_chunk_index) > 4000
                PIPE ROW (SUBSTR(v_chunk, v_chunk_index, 4000));
                v_chunk_index := v_chunk_index + 4000;
                PIPE ROW (SUBSTR(v_chunk, v_chunk_index, v_line_end - v_chunk_index));
                v_chunk_index := v_line_end + v_delim_len;
            END IF;
        END LOOP;

    WHEN no_data_needed
END split_clob;

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
awesome. Thanks a lot for your guidance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.