There are many obstructive dialogue windows require manual response, which does really slow down the automation process. In one special case, MS Access is used to construct and update a considerable huge data warehouse. The update automation macro consists of over a hundred queries. The dialogue windows effectively destroy the automation itself.
Many of these dialogue windows can be set in Access Options. However, a special window cannot be resolved through setting. The message is “There isn't enough disk space or memory to undo the data changes this action query is about to make.” This is caused by running action queries on a large table.
Support of Microsoft regards this as an error message: http://support.microsoft.com/kb/161329, which also provides a number of solutions. Method 3, Setting the UseTransaction Property in an Action Query is particularily helpful:
If a stored action query causes the error, you can set its UseTransaction property to No. Note that if you do this, you will not able to roll back your changes if there is a problem or an error while the query is executing:
1. Open the query in Design view.
2. On the View menu, click Properties.
3. Click an empty space in the upper half of the query window to display the Query Properties dialog box.
4. Set the UseTransaction property to No.
5. Save the query and close it.
Most important, you would need to set no warnings, as follows:
1. In your first Macro row, right click mouse to insert a new row.
2. Click "Show All Actions" in Show/Hide menu.
3. In Action of your inserted first row, select SetWarnings.
4. Change Arguments value of the first row to "No".
5. Click Save.
Now you are ok to run Macros without any warning.
A Good IT Certification Study Website
IT Exams is very good IT certification study website, if not the best. It links to a bunch of text books and dump tests, which are complete free. Unfortunately, it only has Java and Oracle certification information.
IT Exams' address is http://itexams.weebly.com/.
IT Exams' address is http://itexams.weebly.com/.
Temporarily Disable and Re-enable the Constraints in Oracle
SQL command script files to disable and enable all constraints:
Disable:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
Enable:
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/
Scripts in PL/SQL to disable and enable all constraints:
Disable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/
Enable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'DISABLED'
ORDER BY c.constraint_type
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/
Disable:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
Enable:
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/
Scripts in PL/SQL to disable and enable all constraints:
Disable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/
Enable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'DISABLED'
ORDER BY c.constraint_type
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/
Software Patch Installation Batch Files
The example here is to install an Oracle database application patch. The basic structure of patch installation scripts shall include two folders and two files:
Folder 1: Logs
Folder 2: Scripts
File 1: Readme.txt
File 2: Setup.bat (or other name)
Of course, there would be some SQL script files in Scripts folder. Log files in Logs folder would be generated by these script files in Scripts folder. The working procedure is to run installer batch file (here Setup.bat), which will call other batch files (we do not have here) and/or script files stored in Scripts folder. In there example, the batch file open sqlplus.exe followed by user id, password and the SQL scripts (in file), or package to run.
Following is the example of batch file:
@echo off
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: PLEASE REVIEW AND ADJUST ENVIRONMENT VARIABLES BETWEEN < >
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CLS
set NETALIAS=
set USERID=
set PASSWORD=
set COMMONID=
set COMPASS=
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: ORACLE_VERSION are 9.2 (for 9i) or 10.2.0 (for 10g)
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set ORACLE_VERSION=
set ORACLE_HOME=C:\oracle\product\%ORACLE_VERSION%\db_1
set SQLUTIL=%ORACLE_HOME%\bin\sqlplus.exe
set FOLDERPATH=%cd%
echo #####################################################
echo.
echo You are just about to run SCRAMBLING SCRIPT on database
echo.
echo [**** %NETALIAS% ****]
echo.
echo Schema
echo.
echo [**** %USERID% ****]
echo.
echo If this is not correct Database/Schema then
echo.
echo press CTL + C to cancel the script.....
echo.
echo #####################################################
pause
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Truncate.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Create_Tables.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.spec"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.body"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Cleanup.sql"
pause
Note: NETALIAS, USERID following “set” commend are variables. %...% is the way to use these variables.
Following is the Truncate.sql:
PROMPT Truncating data. Please wait...
TRUNCATE TABLE BATCH_BUF$RECON$DETAIL;
TRUNCATE TABLE BATCH_BUF$VALU$SUMMARY_PEN;
-- Enable/Disable primary, unique and foreign key constraints alter table BATCH_BUF$VALU$SUMMARY_PEN disable constraint BATCH_BUF$VALU$SUMMARY_PEN$FK1;
EXIT;
Folder 1: Logs
Folder 2: Scripts
File 1: Readme.txt
File 2: Setup.bat (or other name)
Of course, there would be some SQL script files in Scripts folder. Log files in Logs folder would be generated by these script files in Scripts folder. The working procedure is to run installer batch file (here Setup.bat), which will call other batch files (we do not have here) and/or script files stored in Scripts folder. In there example, the batch file open sqlplus.exe followed by user id, password and the SQL scripts (in file), or package to run.
Following is the example of batch file:
@echo off
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: PLEASE REVIEW AND ADJUST ENVIRONMENT VARIABLES BETWEEN < >
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CLS
set NETALIAS=
set USERID=
set PASSWORD=
set COMMONID=
set COMPASS=
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:: ORACLE_VERSION are 9.2 (for 9i) or 10.2.0 (for 10g)
:: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set ORACLE_VERSION=
set ORACLE_HOME=C:\oracle\product\%ORACLE_VERSION%\db_1
set SQLUTIL=%ORACLE_HOME%\bin\sqlplus.exe
set FOLDERPATH=%cd%
echo #####################################################
echo.
echo You are just about to run SCRAMBLING SCRIPT on database
echo.
echo [**** %NETALIAS% ****]
echo.
echo Schema
echo.
echo [**** %USERID% ****]
echo.
echo If this is not correct Database/Schema then
echo.
echo press CTL + C to cancel the script.....
echo.
echo #####################################################
pause
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Truncate.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Create_Tables.sql"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.spec"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\DATA_SCRAMBLE.body"
"%SQLUTIL%" %USERID%/%PASSWORD%@%NETALIAS% @"%FOLDERPATH%\Scripts\Cleanup.sql"
pause
Note: NETALIAS, USERID following “set” commend are variables. %...% is the way to use these variables.
Following is the Truncate.sql:
PROMPT Truncating data. Please wait...
TRUNCATE TABLE BATCH_BUF$RECON$DETAIL;
TRUNCATE TABLE BATCH_BUF$VALU$SUMMARY_PEN;
-- Enable/Disable primary, unique and foreign key constraints alter table BATCH_BUF$VALU$SUMMARY_PEN disable constraint BATCH_BUF$VALU$SUMMARY_PEN$FK1;
EXIT;
A Privacy Issue for Google AdSense
Google AdSense allows readers to gain the Publisher ID through viewing web page's source code in HTML. This may lead to privacy leaking to some degree.
Here is a case to show how people can explore such public available information. Blog site blog.dwnews.com is a popular political forum amongst overseas Chinese communities. The owner of this forum tries to create a forum without bias and welcomes people having different options to debate in the forum. As the result, the debates are always so ferocious between groups of pro-democracy and pro- Chinese Government. In aims to avoid the possible trouble with Chinese Government, many bloggers are anonymous there.
One blogger through Google AdSense' publisher ID found out several popular blogs were actually run by a same person, or presumably the same person because the AdSense Publisher IDs for these blogs are the same. He attached accordingly based on his finding, followed by a series of incidents. At the end of it, one of them declared he leaves the site permanently, and the account of the other one, ranked number one in current activities, had been cancelled by the webmaster. What lucky here is, both of them did not reside in China and we did not see someone ends up in the jail.
That was in June 2009, two months ago.
Here is a case to show how people can explore such public available information. Blog site blog.dwnews.com is a popular political forum amongst overseas Chinese communities. The owner of this forum tries to create a forum without bias and welcomes people having different options to debate in the forum. As the result, the debates are always so ferocious between groups of pro-democracy and pro- Chinese Government. In aims to avoid the possible trouble with Chinese Government, many bloggers are anonymous there.
One blogger through Google AdSense' publisher ID found out several popular blogs were actually run by a same person, or presumably the same person because the AdSense Publisher IDs for these blogs are the same. He attached accordingly based on his finding, followed by a series of incidents. At the end of it, one of them declared he leaves the site permanently, and the account of the other one, ranked number one in current activities, had been cancelled by the webmaster. What lucky here is, both of them did not reside in China and we did not see someone ends up in the jail.
That was in June 2009, two months ago.
A Practical Case of Transpose Query
Let us say a pension fund wants to generate a summary consists of total contributions had been made so far for each member, by their employers. The fund does not care about exact contribution made by each employers for each member. However, the fund wants to know the three employers paid the contributions most for each member. As the result, the summary table should have 5 columns: Member_Id (unique), Total_Contributions, Employer_1, Employer_2, Employer_3. If a member has less than three employers, leave it blank. Each member should have occupied one row only.
Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:
1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:
SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.
2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:
SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.
3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:
SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Put everything together:
WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Done.
Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:
1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:
SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.
2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:
SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.
3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:
SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Put everything together:
WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id
Done.
Koncord Applied Excel Functions
We have uploaded some applied Excel functions. These are some basic functions, but very much useful in day-to-day working. This Excel file include some functions of string parsing, dates, telephone number parsing, and coordinates parsing.
Following is the link:
Koncord Applied Excel Functions
Following is the Terms of Services of Koncord Partners: http://koncordpartners.blogspot.com/2010/06/terms-of-services.html
Following is the link:
Koncord Applied Excel Functions
Following is the Terms of Services of Koncord Partners: http://koncordpartners.blogspot.com/2010/06/terms-of-services.html
Subscribe to:
Posts (Atom)
Posts
