Calling Web service from sql

==============================================
select
substr(x,instr(x,'ZONE')+5,1) timezone
from ( select utl_http.request('http://www.yyyyyyy.net//yyyy.asmx/GetInfoByZIP?USZip=97006') x from dual));
==============================================

Calling BPEL Process through PLSQL

===========================================
PROCEDURE call_bpel (p_so_number IN NUMBER, x_status OUT VARCHAR2) IS soap_request VARCHAR2 (30000);
soap_respond VARCHAR2 (30000);
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
launch_url VARCHAR2 (240);
BEGIN
--setdebug('...call_bpel +'); -------- soap_request := 'http://schemas.xmlsoap.org/soap/envelope/">http://us.pgarg.com/XXX_EBS_Create_ShipNotification">
'p_so_number''; /* --<>--
soap_request:=' http://schemas.xmlsoap.org/soap/envelope/"> http://us.pgarg.com/XXX_EBS_Create_ShipNotification"> ' p_so_number ' ';*/
UTL_HTTP.set_transfer_timeout (600);
http_req := UTL_HTTP.begin_request ('http://sv-pgargorasoadev1.xxxcs.net:7777/orabpel/o2s/XXX_EBS_Create_ShipNotification', 'POST', 'HTTP/1.0' );
UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml');
UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (soap_request)); UTL_HTTP.set_header (http_req, 'SOAPAction', 'process');
UTL_HTTP.write_text (http_req, soap_request);
http_resp := UTL_HTTP.get_response (http_req);
UTL_HTTP.read_text (http_resp, soap_respond);
UTL_HTTP.end_response (http_resp);
DBMS_OUTPUT.put_line (soap_respond);
----
x_status := SUBSTR (soap_respond, 1, 1000);
setdebug ('Bpel Respond ==>' x_status);
--setdebug('...call_bpel -');--
END call_bpel; ----

=========================================================

How to call shell unix command from plsql / sql

=============================
SELECT apps.host_command(‘ls -ltr’) FROM dual;
--
SELECT apps.host_command(‘chmod 777 /devel/appl/xxcus/1.0.0/bin/filename’) FROM dual;
--
CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 ) RETURN INTEGER IS STATUS NUMBER; errormsg VARCHAR2(80); pipe_name VARCHAR2(30);BEGIN pipe_name := ‘HOST_PIPE’; dbms_pipe.pack_message( cmd ); STATUS := dbms_pipe.send_message(pipe_name); RETURN STATUS;END;

=============================

Selecting Database instance through SQL Query

============================
Select name from v$database;
============================

Anaylitical Function Use

SELECT inventory_item_id, forecast_designator, organization_id,forecast_date, bucket_type,
quantity, request_id, rn,
MAX (quantity) OVER (PARTITION BY inventory_item_id ORDER BY inventory_item_id)maxq
FROM (
SELECT 'Y' fl, inventory_item_id, forecast_designator,organization_id, forecast_date, bucket_type,quantity, request_id,
ROW_NUMBER () OVER (PARTITION BY inventory_item_id ORDER BY forecast_date)rn
FROM xxgenascp_forecast_stg
ORDER BY inventory_item_id, forecast_date)
WHERE rn <= p_max_forecastweeks UNION SELECT inventory_item_id, forecast_designator, organization_id,forecast_date, bucket_type, quantity, request_id, rn, MAX (quantity) OVER (PARTITION BY inventory_item_id ORDER BY inventory_item_id)maxq FROM (SELECT 'N' fl, inventory_item_id, forecast_designator,organization_id, forecast_date, bucket_type,quantity, request_id, ROW_NUMBER () OVER (PARTITION BY inventory_item_id ORDER BY forecast_date)rn FROM xxgenascp_forecast_stgORDER BY inventory_item_id, forecast_date) WHERE rn > p_max_forecastweeks