SQL & PL/SQL

Hi All,
I'm trying to write a pl/sql procedure to call the google translate API. I paid for a key and the url itself works without any issues but I'm struggling to get my call working from pl/sql.

I created a function on my server as suggested by Billy Verreynne here: 1927304

This was hugely helpful as I can now see the url that is getting passed to google.

This is the code I am using to call Billy's function is:

set serveroutput ON
select * from TABLE(webbrowser('https://www.googleapis.com/language/translate/v2?key=mykeyblahblah&q=Automobile&source=en&target=ja')); 

1) my first question: Why am I getting prompted to enter bind variables by sqldeveloper? These are hardcoded in the url I'm passing so I'm not sure why I'm getting prompted for these.

Per Billy's function, this is what is getting sent to google:

HTTP: GET https://www.googleapis.com/language/translate/v2?key=mykeyblahblahAutomobile=Automobileen=enja=ja

I'm getting ORA-29268: HTTP client error 400 - Bad Request, obviously since the url getting passed to google makes no sense, i.e. the ampersands are all getting removed and the bind variables I entered are replacing the names of the parameters.

2) So my second question, what am I doing wrong and how can I get the URL to keep the ampersands and the parameter identifiers?

The first thing that's obvious is that I have a lot to learn here. There don't seem to be that many online tutorials for UTL_HTTP and even the Oracle docs are a bit sparse.

For what it's worth my wallet is ok and the proxy's are set properly, so I know I'm getting to google, I'm just passing a bad url.

This is not the final function, I'm just trying to move in baby steps so I just want to call the API from pl/sql and get a legible result back. If anyone can help point me in the right direction I would be very grateful.

If this helps, here is the function I created using Billy's code:

create or replace
function WebBrowser( url varchar2 ) return TStrings pipelined is
--
-- BASIC PL/SQL WEB BROWSER TEMPLATE
-- supports http, https and proxy servers
 
        -- fixed constants
        C_NO_PROXY_FOR  constant varchar2(4000) := 'localhost';
        C_WALLET        constant varchar2(4000) := 'file:/blahblah';
        C_WALLET_PASS   constant varchar2(4000) := 'Welcome';
 
        -- Proxy settings that can be made arguments in the WebBrowser() call
        proxyServer     varchar2(30) := 'www-proxy.com';
        -- not all proxy servers use authentication, but many corporate proxies do, in
        -- which case you need to specify your auth details here
        -- (make it nulls if not applicable)
        proxyUser       varchar2(50) := NULL;
        proxyPass       varchar2(50) := NULL;
 
        -- our local variables
        proxyURL        varchar2(4000);
        request         UTL_HTTP.req;
        response        UTL_HTTP.resp;
        buffer          varchar2(4000);
        endLoop         boolean;
begin
        -- our "browser" settings
        PIPE ROW( 'Setting browser configuration' );
        UTL_HTTP.set_response_error_check( TRUE );
        UTL_HTTP.set_detailed_excp_support( TRUE );
        UTL_HTTP.set_cookie_support( TRUE );
        UTL_HTTP.set_transfer_timeout( 30 );
        UTL_HTTP.set_follow_redirect( 3 );
        UTL_HTTP.set_persistent_conn_support( TRUE );
 
        -- set wallet for HTTPS access
        PIPE ROW( 'Wallet set to '||C_WALLET );
        UTL_HTTP.set_wallet( C_WALLET, C_WALLET_PASS );
 
        -- configure for proxy access
        if proxyServer is not NULL then
                PIPE ROW( 'Proxy Server is '||proxyServer );
                proxyURL := 'http://'||proxyServer;
 
                if (proxyUser is not NULL) and (proxyPass is not NULL) then
                        proxyURL := REPLACE( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );
                        PIPE ROW( 'Proxy URL modified to include proxy user name and password' );
                end if;
 
                PIPE ROW( 'Proxy URL is '|| REPLACE(proxyURL,proxyPass,'*****') );
                UTL_HTTP.set_proxy( proxyURL, C_NO_PROXY_FOR );
        end if;
 
        PIPE ROW( 'HTTP: GET '||url );
        request := UTL_HTTP.begin_request( url, 'GET', UTL_HTTP.HTTP_VERSION_1_1 );
 
        -- set HTTP header for the GET
        UTL_HTTP.set_header( request, 'User-Agent', 'Mozilla/4.0 (compatible)' );
 
        -- get response to the GET from web server
        response := UTL_HTTP.get_response( request );
 
        -- pipe the response as rows
        endLoop := false;
        loop
                exit when endLoop;
 
                begin
                        UTL_HTTP.read_line( response, buffer, TRUE );
                        if (buffer is not null) and length(buffer)>0 then
                                PIPE ROW( buffer );
                        end if;
                exception when UTL_HTTP.END_OF_BODY then
                        endLoop := true;
                end;
 
        end loop;
        UTL_HTTP.end_response( response );
 
        return;
 
exception when OTHERS then
        PIPE ROW( SQLERRM );
end;

thanks!
John