export oracle objects into separate files from commandline

I need this to export regularly objects from command line and add them into svn
to monitor shanges in oracle database.

Idea:
Create sql file for sqlplus oracle tool that will create temporary file
that will do export

Name: oradump.sql

WHENEVER SQLERROR EXIT 1
SET SQLBLANKLINES ON
SET HEADING OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
--hope nobody produce one line longer then 2k
SET LINESIZE 2000
SET LONGCHUNKSIZE 2000
--hope this will be enough to export views...
SET LONG 64000
SET PAGESIZE 0
SET VERIFY OFF

--the schema to export
define p_schema="&1"
--the schema to export
define p_mask="&2"
--the target directory
define p_dir="&3"

define p_pcr="CREATE OR REPLACE"

select 'prepare export '||chr(10)||'objects like "&p_mask" from schema "&p_schema" into "&p_dir"' from dual
/
ALTER SESSION SET CURRENT_SCHEMA = &p_schema
/
--create temp export sctipt
SET TERMOUT OFF
spool tmp.sql
/
prompt WHENEVER SQLERROR EXIT 1
prompt SET SQLBLANKLINES ON    
prompt SET HEADING OFF         
prompt SET FEEDBACK OFF        
prompt SET TRIMSPOOL ON        
prompt SET LINESIZE 2000
prompt SET LONGCHUNKSIZE 2000
prompt SET LONG 64000
prompt SET PAGESIZE 0          
prompt SET VERIFY OFF
prompt SET TERMOUT OFF
prompt ALTER SESSION SET CURRENT_SCHEMA = &p_schema
prompt /

--create out dirs for sources
select 'host md "&p_dir'||chr(92)||type||'"' 
    from user_source
    where type like '&p_mask'
    group by type
    order by type
/


select 'spool "&p_dir'||chr(92)||type||chr(92)||name||'.sql"'||chr(10)
        ||'select decode(line,1,''&p_pcr '','''')||text from user_source where name='''||name||''' and type='''||type||''' order by line'||chr(10)
        ||'/'||chr(10)
        ||'spool off'
    from user_source 
    where type like '&p_mask'
    group by name,type 
    order by type,name
/


--create out dirs for views
select 'host md "&p_dir'||chr(92)||'VIEW'||'"' 
    from dual
    where 'VIEW' like '&p_mask'
/

select 'spool "&p_dir'||chr(92)||'VIEW'||chr(92)||v.view_name||'.sql"'||chr(10)
        ||'select ''&p_pcr VIEW '||v.view_name||' (''||chr(10) from dual'
            ||chr(10)||'/'||chr(10)
        ||'select ''   ''||column_name||decode(column_id,'||to_char(vc.colcount)||','''','','') '
            ||'from user_tab_cols where table_name='''||v.view_name||''' order by column_id'
            ||chr(10)||'/'||chr(10)
        ||'select '') AS''||chr(10) from dual'
            ||chr(10)||'/'||chr(10)
        ||'select text from user_views where view_name='''||v.view_name||''''
            ||chr(10)||'/'||chr(10)
        ||'select chr(10)||''/''||chr(10) from dual'
            ||chr(10)||'/'||chr(10)
        ||'spool off'
    from user_views v,(select max(column_id) colcount,table_name from user_tab_cols group by table_name) vc
    where v.view_name=vc.table_name
    and 'VIEW' like '&p_mask'
    and v.view_name like 'VACC%'
    order by v.view_name

/



prompt exit
spool off


exit
/

Now let's create command file to start this script:
Name: oradump.cmd

@echo off
cd "%~dp0"

set oradump_srv=MY_SERVER
set oradump_uid=MY_USER
set oradump_sch=MY_SCHEMA
set oradump_dir=.\output
set oradump_obj=%%

if "%oradump_pwd%"=="" set /P oradump_pwd=PASS (visible)

sqlplus -L -S %oradump_uid%/%oradump_pwd%@%oradump_srv% "@oradump.sql" %oradump_sch% "%oradump_obj%" "%oradump_dir%"
echo do export
sqlplus -L -S %oradump_uid%/%oradump_pwd%@%oradump_srv% "@tmp.sql"

del tmp.sql 2>nul

exit 0

Now create "output" directory,
define correct values for variables in oradump.cmd
and you can export objects (types, packages, procedures, functions, views)
into separate files

Transform Well (Table) Formatted XML into Table in Oracle

Let we have table-like xml with custom namespace and we want to transform it to the table (resultset)
to be used in select query.

Let we have function getXML() that returns the following XMLType value:

      <datas1:employmenttypeslist xmlns:datas1="http://mynamespace/">
         <datas1:employmenttypes>
            <datas1:id>676</datas1:id>
            <datas1:code>NOT_WORK</datas1:code>
            <datas1:text>Not working</datas1:text>
         </datas1:employmenttypes>
         <datas1:employmenttypes>
            <datas1:id>677</datas1:id>
            <datas1:code>OWN_BUSINESS</datas1:code>
            <datas1:text>Own business</datas1:text>
         </datas1:employmenttypes>
         <datas1:employmenttypes>
            <datas1:id>678</datas1:id>
            <datas1:code>PENSIONER</datas1:code>
            <datas1:text>Pensioner</datas1:text>
         </datas1:employmenttypes>
      </datas1:employmenttypeslist>

So, to transform the XMLType into a table we can use several methods.

Method #1

select 
extractValue(xmlt.COLUMN_VALUE,'*/p:id','xmlns:p="http://mynamespace/"') as id,
extractValue(xmlt.COLUMN_VALUE,'*/p:code','xmlns:p="http://mynamespace/"') as code,
extractValue(xmlt.COLUMN_VALUE,'*/p:text','xmlns:p="http://mynamespace/"') as text

from

TABLE(XMLSEQUENCE(

EXTRACT(
(
    select getXML() from dual
),
'/*/*'
)

)) xmlt

Method #2

select *
FROM XMLTABLE(XMLNAMESPACES ('http://mynamespace/' AS "p"),'/*/*'
         PASSING 
            getXML()
         COLUMNS
            --describe columns and path to them:
            id varchar2(10)    PATH '/*/p:id',
            code varchar2(10)  PATH '/*/p:code',
            text varchar2(255) PATH '/*/p:text'
     ) xmlt
;

Call Web Service from Oracle

There are a lot of samples how to call web service from Oracle.
but after a small research I make this choice because of speed and simplicity.

So, I create a package and here is a code example how to use it:

select ws_tools.callws(
    --endpoint url
    'http://myhost:9763/services/Dictionary.DictionaryHttpSoap11Endpoint/',
    --soap message payload
    XMLElement(
        "ns1:getEducationTypes",
        XMLAttributes('http://mynamespace/' as "xmlns:ns1"),
        XMLElement("ns1:id",null)
    )
    --optional WSSecurity UsernameToken parameters:
    'theusername',
    'thepassword'
) from dual

The package:

CREATE OR REPLACE PACKAGE WS_TOOLS
    IS
    FUNCTION callWS(p_url IN VARCHAR2,p_method IN XMLTYPE,p_user in VARCHAR2 :=null,p_pass in VARCHAR2 :=null) RETURN XMLTYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY WS_TOOLS
IS


--internal function to post http(s) request
FUNCTION httpPost
(
    p_url     IN VARCHAR2,
    p_data    IN CLOB,
    p_timeout IN BINARY_INTEGER DEFAULT 60
) 
    RETURN CLOB
IS
    --
    v_request  utl_http.req;
    v_response utl_http.resp;
    v_buffer   CLOB;
    v_chunk    VARCHAR2(4000);
    v_length   NUMBER;
    v_index    NUMBER;
BEGIN

    v_index := 1;
    v_length := nvl(length(p_data), 0);

    -- configure HTTP
    utl_http.set_response_error_check(enable => FALSE);
    utl_http.set_detailed_excp_support(enable => FALSE);
    utl_http.set_transfer_timeout(p_timeout);

    -- send request
    v_request := utl_http.begin_request(p_url, 'POST','HTTP/1.0');
    utl_http.set_header(v_request, 'Content-Type', 'text/xml');
    utl_http.set_header(v_request, 'Content-Length', v_length);
    WHILE v_index <= v_length LOOP
        utl_http.write_text(v_request, substr(p_data, v_index, 4000));
        v_index := v_index + 4000;
    END LOOP;

    -- check HTTP status code for error
    IF v_response.status_code <> utl_http.http_ok THEN   
        raise_application_error(-20400,v_response.status_code || ' - ' || v_response.reason_phrase);
    END IF;

    -- get response
    dbms_lob.createtemporary(v_buffer, FALSE);
    v_response := utl_http.get_response(v_request);
    BEGIN
        LOOP
            utl_http.read_text(v_response, v_chunk, 4000);
            dbms_lob.writeappend(v_buffer, length(v_chunk), v_chunk);
        END LOOP;
    EXCEPTION
        WHEN utl_http.end_of_body THEN NULL;
    END;
    utl_http.end_response(v_response);

    RETURN v_buffer;

END;


FUNCTION callWS
(
    p_url IN VARCHAR2,
    p_method IN XMLTYPE,
    p_user in VARCHAR2 :=null,
    p_pass in VARCHAR2 :=null

    --p_timeout IN NUMBER := 60
)
    RETURN XMLTYPE
IS
    -- calls the given SOAP service
    --cn_procedure_name CONSTANT VARCHAR2(30) := 'invoke';
    --
    v_envelope XMLTYPE;
    v_header XMLTYPE;
    v_response CLOB;
    v_fault XMLTYPE;
    v_sqlerrm VARCHAR2(2000);
BEGIN
    if p_user is not null then
        --prepare header
        SELECT
            XMLElement(
                "soap:Header",
                XMLElement(
                    "wsse:Security",
                    XMLAttributes(
                        'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd' AS "xmlns:wsse"
                    ),
                    XMLElement(
                        "wsse:UsernameToken",
                        XMLAttributes(
                            'UsernameToken-1' AS "wsu:Id",
                            'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' AS "xmlns:wsu"
                        ),
                        XMLElement(
                            "wsse:Username",
                            'cfront'
                        ),
                        XMLElement(
                            "wsse:Password",
                            XMLAttributes(
                                'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText' AS "Type"
                            ),
                            'cfrontpass'
                        )
                    )
                )
            )
        INTO
            v_header
        FROM
            dual;
    end if;


    -- wrap method in SOAP envelope
    SELECT
        XMLElement(
            "soap:Envelope",
            XMLAttributes(
                'http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soap"
            ),
            v_header,
            
            XMLElement(
                "soap:Body",
                p_method
            )
        )
    INTO
        v_envelope
    FROM
        dual;

    -- POST request
    v_response := httpPost(
        p_url,
        v_envelope.getClobVal(),
        60 -- 60 seconds
    );
    IF v_response IS NULL THEN
        raise_application_error(-20400,'empty response');
    END IF;

    -- parse response
    BEGIN
        v_envelope := XMLType(v_response);
    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error(-20400,'can''t parse xml: '||SQLERRM);
    END;

    -- check for a fault
    v_fault := v_envelope.extract(  
        '/soap:Envelope/soap:Body/soap:Fault', 
        'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
    );
    IF v_fault IS NOT NULL THEN
        v_sqlerrm := v_fault.extract('.//faultstring/text()').getStringVal();
        raise_application_error(-20400,'remote error: '||v_sqlerrm);
    END IF;

    -- the actual response is the child of the "soap:Body" element
    RETURN v_envelope.extract(
        '/soap:Envelope/soap:Body/*[position() = 1]', 
        'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
    );
END;

END;