
*) Demo Directory BASE

/Users/daust/Dropbox/Konferenz/2016-06-ODTUG-KScope16-Chicago/Oracle-APEX-Scripting-101/demo/

*) Demo Directory sqlcl

/Users/daust/Dropbox/Konferenz/2016-06-ODTUG-KScope16-Chicago/Oracle-APEX-Scripting-101/demo/sqlcl


*) open a command shell

## java -jar will be in German => change language setting to english
export JAVA_TOOL_OPTIONS="-Duser.language=en"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LANG=en_US.UTF-8
locale

cd /Users/daust/Dropbox/Konferenz/2016-06-ODTUG-KScope16-Chicago/Oracle-APEX-Scripting-101/demo/

---------------------------------------------------------------------
-- APEXExport
---------------------------------------------------------------------

*) Export - different Use Cases

cd /Users/daust/Dropbox/Konferenz/2016-06-ODTUG-KScope16-Chicago/Oracle-APEX-Scripting-101/demo/APEXExport

open ./apex-export-applications.sh
./apex-export-applications.sh
open .

*) Import - Restore Application 102

open ./restore-application-102.sh
./restore-application-102.sh

-------------------------------
-- RETURN TO PRESENTATION
-------------------------------

--------------------------------------------------------------------
-- Demo: Display the instance settings (report_instance_configuration.sql)
---------------------------------------------------------------------

*) show logfile of report_instance_configuration.sql

open /Users/daust/Dropbox/Konferenz/2016-06-ODTUG-KScope16-Chicago/Oracle-APEX-Scripting-101/demo/Ressourcen/apex_configuration.log

-------------------------------
-- RETURN TO PRESENTATION
-------------------------------

--------------------------------------------------------------------
-- Demo: Build Options
---------------------------------------------------------------------

*) start apex sample database application 102

*) show that the following objects are bound to the build option: 
  - Navigation Menu entry "Products"
  - Region "Top Products" on the Home Page

*) Show how to attach it to an object

*) Show the build option and the utilization

*) Turn the build option "OFF"

-------------------------------
-- RETURN TO PRESENTATION
-------------------------------

--------------------------------------------------------------------
-- Demo: Network connectivity
---------------------------------------------------------------------

*) go to sqlcl demo directory

cd /Users/daust/Dropbox/Konferenz/2016-06-ODTUG-KScope16-Chicago/Oracle-APEX-Scripting-101/demo/sqlcl
sql ordstest/ordstest@vm1:1521/xe

*) show jdbc

show jdbc

*) tnsping

tnsping vm1_xe

---------------------------------------------------------------------
-- Demo: Editor commands
---------------------------------------------------------------------

*) enter statement

set sqlformat ansiconsole;

select object_name, object_id, object_type, 
       created, last_ddl_time, status 
  from user_objects
 order by 1;
 
- now edit edit and extend with: 
where object_type='PACKAGE' 

- use keys:  ctrl-a, ctrl-e, ctrl-s und ctrl-w to jump at the beginning of line/block

---------------------------------------------------------------------
-- Aliases
---------------------------------------------------------------------

*) which tables are in the current schema?

tables

*) Aliase anzeigen 

alias
alias list tables

*) create alias with parameter

alias ll=select object_name,object_type,last_ddl_time 
from user_objects
where object_name like :1||'%'
order by 1;

ll OEHR

*) save aliases and load them again

alias save aliases.xml
host cat aliases.xml
alias load aliases.xml

---------------------------------------------------------------------
-- DDL extraction
---------------------------------------------------------------------

*) first the regular export

host cat sql/spool_oehr_customers.sql
@sql/spool_oehr_customers.sql

*) now change the configuration using dbms_metadata and create an alias for that

begin
 dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'SQLTERMINATOR', TRUE );
 dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false) ; 
 DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'EMIT_SCHEMA', false );  
 DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_CREATION', false );  
 DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
end; 
.
/


@sql/spool_oehr_customers.sql

*) When exporting multiple tables (with inderdependent foreign keys) we should generate the tables withouth the foreign keys initially

-- generation without foreign keys:
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
ddl oehr_orders

*) after that we can generate the ref constraints separately: 

SELECT dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name)
  FROM user_tables t
WHERE table_name IN ('OEHR_ORDERS', 'OEHR_CUSTOMERS')
  AND EXISTS (SELECT 1
                FROM user_constraints
              WHERE table_name = t.table_name
                AND constraint_type = 'R');

host cat sql/spool_oehr_ref_constraints.sql
@sql/spool_oehr_ref_constraints.sql

*) ddl does also work for other types: view, package, index, ...

---------------------------------------------------------------------
-- create insert scripts
---------------------------------------------------------------------

*) create insert scripts

info xlib_conf_values

info+ xlib_conf_values

*) output as INSERT statements

select /*insert*/
       conf_app_ctx,
       conf_domain,
       conf_key,
       conf_value,
       conf_comment
  from xlib_conf_values;

*) output as CSV

set sqlformat CSV

select conf_app_ctx,
       conf_domain,
       conf_key,
       conf_value,
       conf_comment
  from xlib_conf_values;

*) output as CSV (with ";" as separator)

set sqlformat CSV ; " "

select conf_app_ctx,
       conf_domain,
       conf_key,
       conf_value,
       conf_comment
  from xlib_conf_values;

*) show that format is still active:

select user from dual;

*) reset format: 

set sqlformat default

*) all formats available: 

default
ansiconsole
csv
insert – lists resuts as an insert statement
loader – pipe-delimited
delimited – same as csv
xml
html
fixed – fixed width
text
json

SELECT /*csv*/ * FROM scott.emp;
SELECT /*xml*/ * FROM scott.emp;
SELECT /*html*/ * FROM scott.emp;
SELECT /*delimited*/ * FROM scott.emp;
SELECT /*insert*/ * FROM scott.emp;
SELECT /*loader*/ * FROM scott.emp;
SELECT /*fixed*/ * FROM scott.emp;
SELECT /*text*/ * FROM scott.emp;

*) load CSV file into table again

Loads a comma separated value (csv) file into a table
• The first row of the file must be a header row and the file must be encoded UTF8
• The load is processed with 50 rows per batch
• Usage:
     LOAD [schema.]table_name[@db_link] file_name

*) show compact script to unload data:

open /Users/daust/Dropbox/Projekt/prj_apex_diff_features/apex-diff/sql/main/_generate_data_script.sql
open /Users/daust/Dropbox/Projekt/prj_apex_diff_features/apex-diff/sql/main/opal_diff_data.sql

---------------------------------------------------------------------
-- formatted display using colors
---------------------------------------------------------------------

set sqlformat ansiconsole

with ts_details as
(
    select rpad(tablespace_name,30, ' ')||' '||lpad(floor(used_percent), 3, ' ') as ts_line,
        used_percent
    from dba_tablespace_usage_metrics
)
select
    case
        when used_percent > 70 then '@|bg_red '||ts_line||'|@'
        when used_percent < 1 then '@|bg_green '||ts_line||'|@'
        else '@|bg_yellow '||ts_line||'|@'
    end as ts_usage_percentage
from ts_details
;

select '@|red,bold,underline This is red,bold,underline|@' "@|red Colors|@"  from dual
union all
select '@|NEGATIVE_ON  This is negative|@'   from dual
union all
select '@|INTENSITY_FAINT  This is faint|@'   from dual
union all
select '@|INTENSITY_BOLD  This is my bold|@'   from dual
union all
select '@|ITALIC  This is italic|@'   from dual
union all
select '@|UNDERLINE  This is underline|@'   from dual
union all
select '@|BLINK_SLOW  This is blink_slow|@'   from dual
union all
select '@|BLINK_FAST  This is blink_fast|@'   from dual
union all
select '@|CONCEAL_ON  This is conceal|@'   from dual
union all
select '@|black  This is black|@'   from dual
union all
select '@|green  This is green|@'   from dual
union all
select '@|yellow This is yellow|@'   from dual
union all
select '@|blue  This is blue|@'   from dual
union all
select '@|magenta  This is magenta|@'   from dual
union all
select '@|cyan   This is cyan|@'   from dual
union all
select '@|white  This is white|@'   from dual
union all
select '@|bg_red  This is bg_red|@'   from dual
union all
select '@|bg_black  This is bg_black|@'   from dual
union all
select '@|bg_green  This is bg_green|@'   from dual
union all
select '@|bg_yellow This is bg_yellow|@'   from dual
union all
select '@|bg_blue  This is bg_blue|@'   from dual
union all
select '@|bg_magenta  This is bg_magenta|@'   from dual
union all
select '@|bg_cyan   This is bg_cyan|@'   from dual
union all
select '@|bg_white  This is bg_white|@'   from dual
union all
select '@|bg_red  This is bg_red|@'   from dual;

select '@|red,bold,underline,bg_white This is red,bold,underline with a white background|@' "@|red Colors|@"  from dual;

---------------------------------------------------------------------
-- export apex applications
---------------------------------------------------------------------

help apex
apex
  
---------------------------------------------------------------------
-- export REST definitions
---------------------------------------------------------------------

help rest
rest modules
rest schemas
rest privileges
rest export demo

---------------------------------------------------------------------
-- using Javascript to extend the base functionality
---------------------------------------------------------------------

Samples available on : https://github.com/oracle/oracle-db-tools/tree/master/sqlcl

open https://github.com/oracle/oracle-db-tools/tree/master/sqlcl

*) !!!! Java Environment MUST be Java 8 !!!

#/usr/libexec/java_home -V
export JAVA_HOME=`/usr/libexec/java_home -v 1.8.0_92`
java -version

---------------------------------------------------------------------
-- loading blobs
---------------------------------------------------------------------

*) Loading all BLOBs

http://krisrice.blogspot.de/2015/10/sqlcl-blob-loading-all-files.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+KrisBlog+(Kris'+blog)

script js/load_blobs ../images
host open js/load_blobs.js


*) show the OS-command find 

exit
find ../images -maxdepth 1 -type f

----------------------------
-- => RETURN TO PRESENTATION
----------------------------


