One of the good collections that I could able to gather is abt the evaluation of Shell command. Most of this extraction is directly from Classic Shell Scriptin-Oreilly. It's just like a tutorial, pls bear with me, I don't have much time to elaborate it. I think this is good enough. Though it is a fundamental concept, I don't prefer someone who is new to shell env to read this post. Unnecessarily it will create chaos. Ok....let us start.... Each line that the shell reads from the standard input or a script is called a pipeline; it contains one or more commands separated by zero or more pipe characters (). (Actually, several special symbols separate individual commands: semicolon, ;, pipe, , ampersand, &, logical AND, &&, and logical OR, .) For each pipeline it reads, the shell breaks it up into commands, sets up the I/O for the pipeline, and then does the following for each command, in the order shown: 1) Splits the command into tokens that are separated by the fixed set of metacharacters: space, tab, newline, ;, (, ), <, >, , and &. Types of tokens include words, keywords, I/O redirectors, and semicolons. It's a subtle point, but variable, command, and arithmetic substitution can be performed while the shell is doing token recognition. 2) Checks the first token of each command to see if it is a keyword with no quotes or backslashes. If it's an opening keyword (if and other control-structure openers, {, or (), then the command is actually a compound command. The shell sets things up internally for the compound command, reads the next command, and starts the process again. If the keyword isn't a compound command opener (e.g., is a control-structure middle like then, else, or do, an end like fi or done, or a logical operator), the shell signals a syntax error. 3) Checks the first word of each command against the list of aliases. If a match is found, it substitutes the alias's definition and goes back to step 1; otherwise it goes on to step 4. The return to step 1 allows aliases for keywords to be defined: e.g., alias aslongas=while or alias procedure=function. Note that the shell does not do recursive alias expansion: instead, it recognizes when an alias expands to the same command, and stops the potential recursion. Alias expansion can be inhibited by quoting any part of the word to be protected. 4) Substitutes the user's home directory ($HOME) for the tilde character (~) if it is at the beginning of a word. Substitutes user's home directory for ~user. Tilde substitution (in shells that support it) occurs at the following places:

• As the first unquoted character of a word on the command line
• After the = in a variable assignment and after any : in the value of a variable assignment
• For the word part of variable substitutions of the form ${variable op word}

5) Performs parameter (variable) substitution for any expression that starts with a dollar sign ($).

6) Does command substitution for any expression of the form $(string) or `string`. 7) Evaluates arithmetic expressions of the form $((string)). 8) Takes the parts of the line that resulted from parameter, command, and arithmetic substitution and splits them into words again. This time it uses the characters in $IFS as delimiters instead of the set of metacharacters in step 1. Normally, successive multiple input occurrences of characters in IFS act as a single delimiter, which is what you would expect. This is true only for whitespace characters, such as space and tab. For nonwhitespace characters, this is not true. For example, when reading the colon-separated fields of /etc/passwd, two successive colons delimit an empty field: while IFS=: read name passwd uid gid fullname homedir shell do ... done < /etc/passwd

After I came back from Vijayawada meeting my friends, yesterday, I started surfing aimlessly - truly aimless. But it was good time that I got to know about some interesting feature in Oracle. I never thought of this feature. Of course there was a reason for not thiniking about this feature, as Oracle already had a similar* feature. Last week my client had sent a request tp delete few records based on columnA, columnB combination. It must be very simple right?- just a simple delete statement. But, they sent around 200 such combination values. How do you do?. Do you execute Delete statement for 200 times?. I think it is time wasting process. Definitely it would have been naive implementation had I used Delete statement for 200 times :). As a simple method, I created a temporary table and loaded that table with the data that my client had provided. That's all, now both Main data and the Reference data are in database. Now write a single Delete stmt to delete the records from the Main table using the data in the Reference table. But, only question is how to load the external data into a Database table? - Answer is Oracle's sql loader tool. For loading the data from flat files into a table, we need to setup some environment variables(oracle_home, tns_admin...), need to create Control file. Control File(demo.ctl): LOAD DATA INFILE * INTO TABLE FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY (, , ) SQL Loader call... sqlldr userid=uwclass/uwclass control=c:\demo.ctl log=c:\demo.log I think we can all agree that this sql loader approach is better than deleting the records manually for each combination, delete from del_frm_table where cola, colb in (select cola, colb from del_using_table); All this was 1 week ago. Today I have come to know about another similar approach which is even better than SQL Loader approach. This is using External Tables. Sql loader loads the data from flat files into database tables. Also, it has relatively complex implementation (uses control files...). But, in External table method, data from flat files would not be loaded into Database, rather directly used from files. In effect, you could treat a text file as if it were a table, and thus join its contents with that of other tables, both ‘real’ and external. As such, they were intended to help those database users who routinely perform extraction, transformation and loading (ETL) operations: data cleansing exercises, in short. External Tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. This external table definition can be thought of as a view that allows running any SQL query against external data without requiring that the external data first be loaded into the database.You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and indexes cannot be created on external tables.(in 10g even DML operation is also possible) 1.Create a Directory Object where the flat files will reside SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\EXT_TABLES';Directory created. 2.Create metadata for the external table SQL> CREATE TABLE emp_ext ( empcode NUMBER(4), empname VARCHAR2(25), deptname VARCHAR2(25), hiredate date ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tables ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ) LOCATION ('emp_ext1.dat','emp_ext2.dat') ) REJECT LIMIT UNLIMITED; Table created. "The ORACLE_LOADER is an access driver for loading data from the external files into the tables." Another driver is ORACLE_DATAPUMP. ORACLE_LOADER is mainly for loading the data from flat files. Using ORACLE_DATAPUMP driver, table data can be dumped into a flat file(in driver proprietary binary format) and the same file can be used to load the data using same datapump driver. SQL> create table ext_e 2 organization external 3 (type oracle_datapump 4 default directory ext_dir 5 location ('tbl_e.etl')) 6 as 7 select * from e; Table created. whenever "as select..." clause is used with oracle_datapump, it will create binary data file(unload) from the contents of "select clause". Without "as select..." it will load the data from the binary file.

Finally, one interesting feature in SQL Loader... Use CONCATENATE when you want SQL*Loader to always combine the same number of physical records to form one logical record. In the following example, integer specifies the number of physical records to combine. CONCATENATE integer

Use CONTINUEIF if the number of physical records to be continued varies. The parameter CONTINUEIF is followed by a condition that is evaluated for each physical record, as it is read. CONTINUEIF THIS NEXT LAST condition Assume that you have physical records 14 bytes long and that a period represents a space: %%aaaaaaaa.... %%bbbbbbbb...... cccccccc.... %%dddddddddd.. %%eeeeeeeeee.... ffffffffff.. CONTINUEIF THIS (1:2) = '%%' Therefore, the logical records are assembled as follows: aaaaaaaa....bbbbbbbb....cccccccc.... dddddddddd..eeeeeeeeee..ffffffffff..