The intention for this blog is to provide a forum to exchange information, ideas and expertise about relational database and application development products from Oracle. My goal is to provide a professional environment to share this knowledge and experience with individuals from around the world.
SQL Developer Data Modeling offers a full spectrum of data and database modeling tools and utilities, including Entity Relationship modeling, Relational (Database Design), Data Type and Multidimensional modeling, full forward and reverse engineering and code generation. It includes importing from and exporting to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of Design Rules.
SQL Developer Data Modeling can connect to any Oracle Database version 9.2.0.1 and later, and is platform independent. Initially available as a standalone product, with future releases available as an extension to Oracle SQL Developer. The first Early Adopter release is stand alone and file-based only. For download and additional information, please click here
One of my colleague has circulated email for MediaWiki. Following is extract from his email :
For any of you that struggle with the wiki syntax, as I do, you might find it easier to use OpenOffice, a free alternative to Microsoft Office. Writer (their version of Word) will export in MediaWiki format. So you can write your document in OpenOffice, then export to MediaWiki. As you can also open Word documents in Writer then export them. This is quite handy if you want to create a document that you can send out, but also share on the wiki.
You can download it here: http://www.openoffice.org/
A couple of limitations I've found are:
1. Image support is, well, non existant really. If you have an image in your document, it'll put a placeholder in the wiki text, but no more. But it's a start I guess.
2. Tables are all set with a default class="prettytable". None of the border or other such formatting settings are created. However, the data is in the right places (which is always the bit that gives me a headache) so adding border="1" at the top etc isn't too traumatic once the table is there.
3. Certain text sections get exported wrapped in tags. The best example is that if you have a snippet of XML in a document, it'll wrap that in so that the XML tags aren't interpreted. This makes using the wiki code syntax highlighter* a little harder, but it's not too hard to strip out the and then wrap with a tag instead.
Note : The "sql" folder *MUST* contain at least following scripts : 1. declare.sql 2. rollout.sql 3. rollout_all.sql 4. recompile_invalid_objects.sql
Note : The "sql" folder *MUST* contain atleast one sub-folder with schema name: eg. SCOTT
SQL Scripts
sql/declare.sql
This script is used to declare database connect string and substitute passwords REM Defining Connection Details DEFINE connect_string = orcl10g.world DEFINE SCOTT_password = tiger DEFINE HR_password = hr
sql/rollout.sql
* 'rollout.sql' runs declare.sql which DEFINE's substitution variables for all passwords (and for the db connect string). It then connects to each database user in turn and runs the rollout scripts for each schema.
* This script is used to install SQL for particular release (Following example is for Release 2) @../../2007.12.2.0/sql/declare.sql spool ../../2007.12.2.0/sql/rollout.spl Prompt Deploying scripts for Release 2 connect SCOTT/&SCOTT_password@&connect_string sho user PROMPT Connected as SCOTT on &connect_string @../../2007.12.2.0/sql/SCOTT/rollout.sql @../../2007.12.2.0/sql/recompile_invalid_objects.sql connect HR/&HR_password@&connect_string sho user PROMPT Connected as HR on &connect_string @../../2007.12.2.0/sql/HR/rollout.sql @../../2007.12.2.0/sql/recompile_invalid_objects.sql PROMPT Scripts deployed for Release 2
sql/rollout_all.sql
* The @../../ in the paths works fine on both windows and unix. We have used @../../script.sql so that we can successfully rollout all the releases by running single 'rollout_all.sql' script.
* This script is used to install everything *upto* particular release.
Example of rollout_all.sql for release 2
In the following example, it will deploy SQL scripts for release 1 and 2. @../../2007.12.1.0/sql/rollout.sql PROMPT Press any key to deploy release 2 scripts or Ctrl+C to abort PAUSE @../../2007.12.2.0/sql/rollout.sql
Example of rollout_all.sql for release 3
In the following example, it will deploy SQL scripts for release 1,2 and 3. @../../2007.12.1.0/sql/rollout.sql PROMPT Press any key to deploy release 2 scripts or Ctrl+C to abort PAUSE @../../2007.12.2.0/sql/rollout.sql PROMPT Press any key to deploy release 3 scripts or Ctrl+C to abort PAUSE @../../2007.12.3.0/sql/rollout.sql
Note : Please remember to change 'declare.sql' before running above script.
sql/recompile_invalid_objects.sql
This script is used to do the following * To list invalid objects * Try to recompile all invalid objects * To list invalid objects which were not compiled successfully * To list Objects deployed / re-compiled today * To list compilation errors (if any) after deployment
prompt +--------------------------------------------+ prompt | List of invalid objects before compilation | prompt +--------------------------------------------+ column object_type format a30 column object_name format a30 column invalid_obj_cnt format 999 column error_text format a30 word_wrap set serveroutput on set pagesize 80 select object_type , count(*) invalid_obj_cnt from user_objects where status <> 'VALID' group by object_type order by 1 / prompt +--------------------------------------------+ prompt | Recompiling invalid objects | prompt +--------------------------------------------+ BEGIN FOR cur_rec IN (SELECT object_name, object_type, DECODE(object_type, 'PACKAGE', 1, 'PACKAGE BODY', 2, 2) AS recompile_order FROM user_objects WHERE status != 'VALID' ORDER BY 3) LOOP BEGIN IF cur_rec.object_type = 'PACKAGE BODY' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.object_name || '" COMPILE BODY'; ELSE EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||' "' || cur_rec.object_name || '" COMPILE'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(cur_rec.object_type || ' : ' || cur_rec.object_name ||' is INVALID'); END; END LOOP; END; / prompt +--------------------------------------------+ prompt | List of invalid objects after compilation | prompt +--------------------------------------------+ select object_type , count(*) invalid_obj_cnt from user_objects where status <> 'VALID' group by object_type order by 1 / prompt +---------------------------------------------+ prompt | List of objects deployed / recompiled today | prompt +---------------------------------------------+ select object_type , object_name from user_objects where subobject_name is null and (created > trunc(sysdate) or last_ddl_time > trunc(sysdate)) order by object_type, object_name / prompt +---------------------------------------------+ prompt | List of Errors | prompt +---------------------------------------------+ select name , type , 'Line '||line||' Position '||position||' - '||text error_text from user_errors -- where attribute = 'ERROR' /* Uncomment if deploying to Oracle Database 10g */ order by name /
sql/HR/rollout.sql
* Use @@ so that the scripts are picked up relative to the directory of the script being run
@@view.sql @@procedure.sql
sql/SCOTT/rollout.sql
* Use @@ so that the scripts are picked up relative to the directory of the script being run @@grants.sql
Example of Deployment Instructions
Deployment Instructions for Test Environment
1. Edit declare.sql and substitute the correct database connect string and correct passwords
2. At the command prompt: $ cd ../2007.12.1.0/sql $ sqlplus SCOTT/ValidPassword@ValidConnectString sql> @rollout.sql sql> exit
NB. The connect string on the command line has to work but is otherwise completely ignored
3. Examine the output (spool file): no errors or warnings should occur $ cat rollout.spl
Deployment Instructions for Live Environment
1. Edit declare.sql and substitute the correct database connect string and correct passwords. We must modify declare.sql of every releases. In the examples given above, we should modify following declare.sql ../2007.12.1.0/sql/declare.sql ../2007.12.2.0/sql/declare.sql ../2007.12.3.0/sql/declare.sql ..etc
2. At the command prompt: $ cd ../2007.12.3.0/sql -- Change directory to Last release $ sqlplus SCOTT/ValidPassword@ValidConnectString sql> @rollout_all.sql -- Script to rollout SQL scripts of all releases sql> exit
NB. The connect string on the command line has to work but is otherwise completely ignored
3. Examine the output (spool file): no errors or warnings should occur $ cat ../2007.12.1.0/sql/rollout.spl $ cat ../2007.12.2.0/sql/rollout.spl $ cat ../2007.12.3.0/sql/rollout.spl .. etc
SQL Script Guidelines * All SQL script *MUST* start with 'prompt .....' * SQL script for procedures, functions and packages *MUST* end with 'show error' * Script should be saved under unix format. So, if you are using UltraEdit to create / modify your script, please use 'File' -> 'Conversions' -> 'Dos to UNIX' * Name of the script should be lower case. The script name should match oracle object name when possible. * Package body and package definition should be saved separately. * Script file extension : Package Specification .pks Package Body .pkb All other scripts .sql
To Do / Can be done * We are creating declare.sql per release. Due to this, live deployment requires changes to all declare.sql. To avoid this, consider amending 'declare.sql' of first release for every subsequent deployment. * Consider creating 'PATCH' table per schema. Insert a row with patch_id, timestamp and userid if the patch gets deployed successfully (i.e. without errors). This will allow us to track database changes by querying single table. * Consider using Ant scripts for SQL deployment.
Following is the extract from Oracle SQL & PLSQL Forum (Courtesy Billy Verreynne)
You can store XML as CLOB or XMLType objects.
You can easily load XML files into the database as CLOBs using the DBMS_LOB PL/SQL package - it has a load-from-file procedural call that can be made. Very easy to use.
drop table xml_clob; drop table my_objects; -- create a table to store a large XML doc in raw format create table xml_clob ( doc CLOB ) / -- create such a raw XML document, using the ALL_OBJECTS -- table DECLARE CURSOR curxml IS SELECT xmlelement("ORA_OBJECT", xmlforest(object_id AS "ID", owner AS "OWNER", object_type AS "TYPE", object_name AS "NAME")) AS "XML_DATA" FROM all_objects WHERE rownum < 100001;
xml XMLTYPE; c CLOB; BEGIN dbms_lob.createtemporary(c, TRUE);
OPEN curxml;
LOOP FETCH curxml INTO xml;
EXIT WHEN curxml%NOTFOUND; DBMS_LOB.Append( c, xml.GetCLOBVal() ); -- dbms_lob.writeappend(c, LENGTH(xml.getclobval()), xml.getclobval()); END LOOP;
/ -- call proc with a raw XML clob and have it insert the contents -- into a relational table CREATE OR REPLACE PROCEDURE insertobjects( xmldoc IN OUT NOCOPY CLOB ) IS BEGIN INSERT INTO my_objects SELECT extractvalue(VALUE(xml), 'ORA_OBJECT/ID') , extractvalue(VALUE(xml), 'ORA_OBJECT/OWNER') , extractvalue(VALUE(xml), 'ORA_OBJECT/TYPE') , extractvalue(VALUE(xml), 'ORA_OBJECT/NAME') FROM TABLE(SELECT xmlsequence(EXTRACT(XMLTYPE(xmldoc), 'DATASET/*')) FROM dual) xml;
COMMIT; END; / -- the test (processing 10,000 XML rows): set timing on declare c CLOB; BEGIN SELECT doc INTO c FROM xml_clob;
insertobjects(c); END; /
set timing offselect count(*) from my_objects; select * from my_objects where rownum < 4; --eof
I have worked on different DSS and OLTP systems. The databases have ranged in size from a tiny 50 Mb to more than 3 Tb with transaction rates from 100 per day up to 80 million per day.