TechiWarehouse.Com

Programming Languages


Computer Training Excuses - No ExcuseYou wouldn't go to a doctor who had never been to medical school, or hire a lawyer who never studied law. One side-effect of a world advancing as rapidly as ours is that fields are becoming more and more specialized and narrow. People can no longer get by on general knowledge in their careers, something I found out for myself not too long ago. I'd been out of high school for two years, scraping by on my own and picking up scraps of programming as I went. I saw all of the self-taught programmers breaking into the IT industry, and I hoped to do the same. After all, IT is one of the few industries out there where being creative and a quick learner is more important than a degree.
Divider Line

How to Install WordPress - Part II - WP cPanel...This page is called a WordPress Dashboard. Get to know this part because you will be using it a lot. That is the matrix of your Web page. All changes on your Web page will go through the Dashboard.
Divider Line

Which is the Easiest Programming Language? - Programming LanguagesIf you have some misconception in mind that the programming languages are easy or hard, I am afraid to say that you are mistaken. To tell you frankly about the programming languages, there is nothing such as easy or tough programming language. The only thing that you need to keep in mind while stepping into the world of programming languages is that which programming language would set your base by giving you in-depth knowledge of programming logics and the basics of programming techniques.
Divider Line

How to Install WordPress - WP Fantastico De LuxeWordPress is the most popular blogging platform and content managing system (CMS, Content Management System). It is an open source platform that offers a lot to adjust. WordPress is a free platform mostly used by blogger.
Divider Line

Joomla Tutorial: How To Install Joomla - JoomlaThis is the first in a line of new tutorials dedicated to Joomla! CMS. In the introductory tutorial we will explain the installation of Joomla! CMS, and later we will go through some basic administrations.This is a tutorial that is meant for the absolute beginners, it is simple and will explain the basics in a few words.
Divider Line

Entering into Programming World - Good Luck ProgrammingOnce you have learned some programming languages, you feel yourself completely prepared to enter into the programming world, and start working on the programming languages that you have learned. Many students also feel that they have learned extra things which might be helpful in their programming careers. The matter of fact is that the real world is entirely different from what you have been taught in the classrooms.
Divider Line

Best of the Web Hosting Show Guides - Those Arent the Moving PantsWhen moving from house to house, you have to pack up all of your belongings in the old house before you can move, right? The same could be said when you are moving from web host to web host. The first and most important thing you can do is backup your web site and get it ready for the move. Remember to grab all of your static files. This would be all of your non-dynamic pages, images, templates and more. The exact files that you do backup might change depending on how your site is setup.
Divider Line

Getting Started with WAMP - The tutorial answers many common questions people have about WAMP. The ultimate goal of this tutorial is to help you determine if WAMP is the solution you are looking for by providing the advantages and disadvantages of WAMP. WAMP is a powerful backend web development and hosting environment integrated from Apache, MySQL and PHP. Using Apache as a web server, MySQL as a database server, and PHP as a server side script engine, WAMP controls all these components using an intuitive interface.
Divider Line

How to Create a Simple Query Using SELECT - How to Create a Simple Query Using SELECTLearn how to display information that is stored n the database table in various ways with MySQL.
Divider Line

Creating a New Database Table - Creating a New Database TableLearn how to create a new table and specify the qualities of the various database fields in the table in MySQL. There are a number of types of fields that can be utilized when creating variables in a table.
Divider Line

Why Programmers Love the Night - Programming at night One famous saying says that programmers are machines that turn caffeine from coffee and Coca Cola into a programming code. And if you ask a programmer when does he like working the most and when is he most productive - he is probably going to say late at night or early in the morning, that is when he has the greatest energy and concentration to work.
Divider Line

Updating Database Tables - Updating Database TablesLearn how to insert, modify, and delete entries in a database table with mySQL. Occasionally, it becomes necessary to change the attributes of one of the variables or columns in a table. This is a frequent situation for a variable that might be declared VARCHAR(20) lets say, and then the user wants to add something that might be 25 characters in length.
Divider Line

TW Tech Glossary - Misplaced your bible? Well here it is - Tech Glosasary! This truly took a while to complete and should be used by all from beginners to advance techies.
Divider Line

SQL Tutorial - SQL TutorialAfter a great deal of popular demand, TechiWarehouse presents SQL tutorial. As with almost all of our tutorials (with some exception), this tutorial too has been put into one single file for easy printing. This tutorial is concetrated in generality. In other words, it is meant to cover almost all things without getting into some very hardcore details. Yet, its detailed enough to get a newbie up and running in no time with SQL. Again, the focus is for readers to be able to learn SQL and be able to put thier own theories to test by the end of the tutorial and not rely on yet other tutorials as such.
Divider Line

Connecting to a Database Account - Connecting to a Database Account How to connect to a password protected database and access an existing table of values. Although the user had to have an account to access MySQL, there is a secondary action that requires that the user connect to a database.
Divider Line

WikiLeaks, The Technical Aspect - WikiLeaksWikileaks has been in the news for quite some time now as it has rocked many nations, embroiling itself in controversy over the fact that it has been releasing classified documents which have been termed as potentially harmful for national security as well as international diplomacy. Amidst all the confusion related to it's ethical and moral standards, people around the world have also speculated how the site functions technically, as a site which has been so controversial, might be technologically sound as well because various steps have already been taken in order to shut the Website down but none of them have availed any results.
Divider Line

Magento: Why, or Why Not? - Magento LogoThe following is a brief outline of the advantages and the disadvantages of Magento as a Shopping cart. It gives a detailed analysis of the reasons that one should choose Magento for, as well as things that they must be careful about while using Magento. . If the few disadvantages can be overcome, then it is surely one of the best shopping cart platforms.
Divider Line

What Programming Language To Learn - Programming LanguagesOne of the most common questions we hear from individuals hoping to enter the IT industry is, "What programming languages do I need to know?" Obviously this is a complex question, and the answer will depend on what field the questioner is going into. Many programming languages are taught during courses used to obtain a computer information science degree. However, those already in IT know that the greatest skill you can have is to be a jack-of-all-trades. A well-prepared worker can switch between computer programming jobs with only minimal training, thanks to a wide knowledge of multiple programming languages.
Divider Line

What is SQL?

SQL stands for structured query language. SQL is a standardized query language for requesting information from a database. The original version called SEQUEL (structured English query language) was designed by an IBM research center in 1974 and 1975. SQL was first introduced as a commercial database system in 1979 by Oracle Corporation.

SQL has been the favorite query language for database management systems running on minicomputer and mainframe however, SQL is being supported by PC database systems because it supports distributed databases (databases that are spread out over several computer systems). This enables several users on a local-area network to access the same database simultaneously.

Elapsed: 00:00:05.02


Tips

Tips

Deleting All Duplicates Except One

The method works for tables that have a logically unique index - composite or singular - that has not yet been placed on the table. Obviously you can't put the index or primary key constraint on the table if there are already duplicates in the table.

The idea is you use a correlated sub-query (see correlated sub-query elsewhere in this document) to find rows which have a matching unique key. The delete statement below deletes all rows that have a matching logical primary key except for the one row with the highest ROWID. ROWID is Oracle's unque row identifier for all rows in a table.
 

DELETE FROM SUBASSM_PART_SN O
WHERE ROWID < ( SELECT MAX(ROWID) FROM SUBASSM_PART_SN I
WHERE I.SN = O.SN
AND I.SUB_SN=O.SUB_SN)

Whe n are indexes used?

This section addresses rule based optimization only. All bets are off if we are discussing indexes and cost based optimization.

When an index has been defined and the SQL in question can use it. So another relevant question is: when can't an index be used? It turns out there are several simple rules which govern when an index cannot be used. They are:

1) An index cannot be used in a function is wrapped around the field in the where clause, for example:
 

select * from employee
where to_char(start_date) = '01-MAR-1997';

 
If we had an index on this the field 'start_date' in the 'employee' table example above the index could NOT be used.

This select statement could be converted as follows to use the index:
 

select * from employee
where start_date = to_date('01-MAR-1997');

2) If you have a composite index or primary key (just another index). Then you must use the left most fields of the composite index/key in order to use the other components. If the following index definition exists:
 

Create index EXP_IDX1 on Sales_Order_Detail (Sales_Order_Number, Line_Item);
The following select could NOT use this index:
select * from Sales_Order_Detail
where Line_Item = '2000';


The next select WILL use the index or at least partially - the left most component.
 

Select * from Sales_Order_Detail
where Sales_Order_Number = 'SO-01201';

And the last, this select will use the entire index, i.e. both components.
 

Select * from Sales_Order_Detail
where Sales_Order_Number = 'SO-01201'
and Line_Item = '2000';

Traversing Parent Child Relationships with Connect By

Traversing a parent child relationship is easy by using Oracle's connect by statement. If you had an EMPLOYEE table as defined below. A select statement identifying managers might look as follows:
 

select emp_name from EMPLOYEE
where emp_name='PAIGE SMITH'
connect by manager to emp_id;
Create table EMPLOYEE as ( emp_name varchar2(64), emp_id varchar2(32), manager varchar2(32);

 

The example above assumes only one employee named PAIGE SMITH. But more importantly shows all managers including managers managers and should terminate at the company president.

Explain Plan

Explain Plan is a utility provided by Oracle that gives you a look at how the SQL you issue is processed. It is useful for things like determining if an index is being used or not used, when a sort is being performed etc. The utility is used as follows:

You create a "plan_table" to contain the results of your query analysis. The script to create this table is provided by Oracle and is the file "UTLXPLAN.SQL" in your Oracle installations ADMIN directory

Run this script from an account that you have ability to create tables from and has visibility of the objects / tables you wish to hit in the SQL you want to analyze. Issue the "explain plan" command. This command syntax is as follows:
 

explain plan set statement_id='MY_STMT' for
select * from scott.employee where empnum='1234';
Substitute your SQL for "select * from dual".


When the above command is issued Oracle performs the analysis of the specified SQL and places that analysis into the plan table - usually cleverly named "PLAN_TABLE".
The analysis is written to this table as a series or records with a key of "STATEMENT_ID". Yes, the same statement id you specified when you issue the command. This allows you to analyze multiple SQL statements simultaneously.

You then view the results of the analysis by running a query against the plan table. Use this SQL script as a template. The results are usually pretty self explanatory.

Calling a PL/SQL function from a SQL statement

Version 2.1 and higher of PL/SQL (2.1 of PL/SQL is provided with 7.1 and higher of the Oracle Server DB) provides the ability to call a stored function from a SQL statement.
Simply write the PL/SQL stored function as normal and call it.

This is an extremely powerful capability as it greatly extends and simplifies what can be done with one SQL statement. It should be pointed out that only selects are allowed in the pl/sql. You are not allowed to update the database and you must commit to this via a PL/SQL pragma in the definition of the function.

SQL Server won't install on my laptop or my home PC. Why not?

A laptop is a great configuration for people who support or perform development for SQL Server at multiple locations. Lack of a NIC is the most common installation problem for laptop and standalone PC users because the SQL Server installation program looks for the default SQL Server Named Pipe (SSNMPN60,\\.\pipe\SQL\query) during installation. You can't create this pipe if NT's networking services fail to start, and the network won't start unless you've installed a NIC.

Fortunately, the problem has a solution. Even if you don't have a NIC, you can fool NT into starting the network by installing the Loopback Adapter, from the Control Panel, Network applet, as Screen 1 shows. The Loopback Adapter lets network-aware applications access local resources as if the local resources were on a network. Better yet, the adapter is as easy to install as a regular NIC.

More powerful tracing

Most people will be familiar with the 'alter session set sql_trace = true' commands to enable a trace file for your session. A quick hunt through $ORACLE_HOME/rdbms/admin also reveals a PL/SQL equivalent (dbms_session.set_sql_trace). This is fine for anyone who cares about performance of their OWN code.

A great boon for DBA's and anyone trying to track SQL performance of OTHER people's code is the use of dbms_system.set_sql_trace_in_session, typically only executable by SYS.

All of the above is "well" documented in the Oracle manuals and directories. A more powerful (and totally undocumented) routine is also available
 

dbms_system.set_ev( sid,serial#,event,level,name)

To initiate a trace for a connected session, simply issue:
 

dbms_system.set_ev( Sid,serial#,10046,8,'')

For a more detailed trace, which show waits and the value of bind variables, you can use:
 

dbms_system.set_ev( Sid,serial#,10046,12,'')

 

Avoiding memory problems with PL/SQL tables

An feature of PL/SQL tables is the ability to remove all records from the table using the TABLE_NAME.DELETE attribute. However, if you have had (say) 10,000 records in the table, then whilst the DELETE attribute will remove these entries, it does not free up the memory that was occupied by them.

The solution is to assign an empty table to reclaim the space.
 

declare
type mytable_type is
table of varchar2(100)
index by binary_integer;
the_table mytable_type;
empty_table mytable_type;
begin
...
...
... lots of stuff with 'the_table'
...
the_table := empty_table;
-- this will free up the memory
end;

Encryption in Oracle

As of Oracle 8.1.6 you can now perform thorough encryption using the built-in package DBMS_OBFUSCATION_TOOLKIT (and no, that wouldn't be my choice of name either!)

If you are not yet at 8.1.6, the following routine is very simple, gives very secure encryption and lets you choose your own key length. The nicest thing about this routine is that to reverse the encryption, you just call the SAME routine with the encrypted text.
 

function encipher(p_string varchar2, p_key varchar2) return varchar2 is
c_key_length number := 60;
v_key varchar2(2000) := substr(p_key,1,c_key_length*2);
v_result varchar2(2000);
v_each_byte number(3);
v_each_key_byte number(3);
begin
--
-- Make sure the key is at least 'c_key_length' chars long with some random junk
--
while length(v_key) < c_key_length loop
v_key := v_key || '@1Z8F$' || v_key;
end loop;
v_key := substr(v_key,1,c_key_length);
--
-- For each char in the string
--
for i in 1 .. length(p_string) loop
--
-- get the ascii vals of the char, and its equivalent in the key
--
v_each_byte := ASCII(substr(p_string,i,1));
v_each_key_byte := ASCII(substr(v_key,mod(i,c_key_length)+1,1));
--
-- xor the two ASCII values and convert back to a char byte
--
v_result := v_result ||
chr(v_each_byte + v_each_key_byte - 2*bitand(v_each_byte,v_each_key_byte));
end loop;
return v_result;
end;
/

Writing to the alert log

The following PL/SQL package can be used to add your own custom entries to the alert log.
 

dbms_system.ksdwrt(2,'A line of text');
Will write the text to the alert log.
Use 1 instead of 2 to write to the trace file
Use 3 to write to both.

Showing GIF's etc to the Web

To retrieve a GIF image from a database column and present it to a web page the following PL/SQL routine may be useful. We use DBMS_LOB to read the image content in 4k chunks and UTL_RAW.CAST_TO_VARCHAR2 to allow use within the standard HTP routines.
 

create or replace package image_get as
procedure gif( p_id in demo.id%type );
end;
/
create or replace package body image_get as
procedure gif( p_id in demo.id%type )is
l_lob blob;
l_amt number default 30;
l_off number default 1;
l_raw raw(4096);
begin
select theBlob into l_lob
from demo
where id = p_id;
owa_util.mime_header( 'image/gif' );
begin
loop
dbms_lob.read( l_lob, l_amt, l_off, l_raw );
htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
l_off := l_off+l_amt;
l_amt := 4096;
end loop;
exception
when no_data_found then
NULL;
end;
end;
end;

 

Explicit vs. Implicit cursors

Pick any tuning manual, and you'll often see a section about coding with explicit cursors as opposed to implicit ones. The argument seems sound, typically going along the lines of:

"With an explicit cursor, you can open, fetch and close, whereas with an implicit cursor must issue open, fetch, second fetch, close - the second fetch being needed to check for a TOO_MANY_ROWS exception.

However, since 7.3, those cunning people at Oracle have worked around this problem and the second fetch is no longer required. Because PL/SQL is interpreted (watch out for the new native compile option in 9i), implicit cursors will actually run FASTER than explicit since typically you are using less code to achieve the same task.

Using DES Encryption

The new DBMS_OBFUSCATION_TOOLKIT allows sophisticated encryption but insists that the text to be encrypted be a multiple of 8 bytes. As suggested by an Ask Tom post, a simple wrapper allows any length text to be encrypted whilst still satisfying this requirement.
 

Function
encrypt_data(p_text varchar2, p_key varchar2) return varchar2 is
v_text varchar2(4000);
v_enc varchar2(4000);
begin
v_text := rpad( p_text, (trunc(length(p_text)/8)+1)*8, chr(0));
sys.dbms_obfuscation_toolkit.desencrypt(
input_string => v_text,
key_string => p_key,
encrypted_string=>v_enc);
return v_enc;
end if;
end;
function decrypt_data(p_text varchar2,p_key varchar2) return varchar2 is
v_text varchar2(4000);
begin
sys.dbms_obfuscation_toolkit.desdecrypt(
input_string => p_text,
key_string => p_key,
decrypted_string=> v_text);
return rtrim(v_text,chr(0));
end;

Business days

As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. However, not many developers are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component. Cursors can also be defined in the same way as the following example shows:
 

CREATE PACKAGE my_pkg AS
CURSOR c1 RETURN emp%ROWTYPE;
...
END emp_stuff;
CREATE PACKAGE BODY my_pkg AS
CURSOR c1 RETURN emp%ROWTYPE IS
SELECT * FROM emp
WHERE hiredate < sysdate;
...
END emp_stuff;

 

Checksum function in PL/SQL

This is a port of a C function that did a 32bit, 2's complement checksum.
 

Function
checksum( p_buff in varchar2 ) return number
is
l_sum number default 0;
l_n number;
begin
for i in 1 .. trunc(length(p_buff||'x')/2) loop
l_n := ASCII(substr(p_buff||'x', 1+(i-1)*2, 1))*256 +
ASCII(substr(p_buff||'x', 2+(i-1)*2, 1));
l_sum := mod(l_sum+l_n,4294967296);
end loop;
while ( l_sum > 65536 ) loop
l_sum := bitand( l_sum, 65535 ) + trunc(l_sum/65536);
end loop;
return l_sum;
end checksum;

 

SELECT * and more

To select all columns of a table:
 

select * from table

However, to select all real columns, plus a pseudo-column like "user":
 

select table.*, user from table
The following does not work:
select *, user from table

 

Bitwise operations

There is an undocumented 'bitand()' routine you can call, to do logical AND on two numbers. If you need the others, a little boolean math should suffice
 

CREATE OR replace FUNCTION bitor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER AS
BEGIN
RETURN x + y - bitand(x,y);
END;
/
CREATE OR replace FUNCTION bitxor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER AS
BEGIN
RETURN bitor(x,y) - bitand(x,y);
END;
/

These functions are callable from SQL as well. Since this is all based on the PLS_INTEGER datatype, you are limited to numbers less than 2^31.

Faster SQL in PL/SQL

An upgrade to 9i gives benefits for PL/SQL called from SQL straight out of the box.

First we'll have a look at 8.1.7
 

SQL> set timing on
SQL> select count(*) from
2 ( select /*+ NO_MERGE */ func817(rownum) x from x$ksmmem
3 where rownum < 10000000 )
4 /

COUNT(*)
----------
9999999

Elapsed: 00:00:05.77
SQL> /

COUNT(*)
----------
9999999

Elapsed: 00:00:05.68
SQL> /

COUNT(*)
----------
9999999

Elapsed: 00:00:05.57


And then same experiment in 9i gives about a 10% improvement

SQL> set timing on
SQL> select count(*) from
2 ( select /*+ NO_MERGE */ func92(rownum) x from x$ksmmem
3 where rownum < 10000000 )
4 /

COUNT(*)
----------
9999999

Elapsed: 00:00:05.07
SQL> /

COUNT(*)
----------
9999999

Elapsed: 00:00:05.01
SQL> /

COUNT(*)
----------
9999999


Did You Know?

  • SQL became public domain in the late seventies. [This was something new even for us]




Premium Tutorials & Screencasts



Partners Links