What working with almost no sleep can do ...

12/18/04 | by Clarise | Categories: In real life

Thursday evening till morn, I worked trying to finish as much work as I can to meet deadlines due before 2005 knocks at the door. If some people are stressed with holiday shopping, you can say I am overwhelmed by the amount of work that needs to be done by the end of the year.

Anyway, yesterday morning, Friday, with only an hour of sleep, our project team was conducting a proof of concept run. I wrote a script that had the object of producing a flat file with one record with fields placed in an exact postion for upload to the mainframe. The script extracts data from an Oracle table. Simple right? We ran the script and viola... the script produced the flat file but the field positions were off by 1. The script goes:

set termout off
set hea off
set pagesize 0
spool H:\BART_LBS\pclink32\UpLoads\k4201.PRN

select CORP||'|',ITMNBR||'|',WHSE||'|',PRIMBNLOC||'|',SECBNLOC
from k42
where ITMNBR like '62-40-00200%' AND WHSE like 'WS%';

spool off

I scratched my sleepy head kept awake by coffee 88|. I checked the field lengths of the columns of my table and they were correct. So, field length inaccuracy was not the cause... Darn, don't you hate it when the simplest of things don't work right?

Here's the corrected SQL statement. The bright-eyed and :idea: alert, Lee, found the culprit.

select CORP||'|'||ITMNBR||'|'||WHSE||'|'||PRIMBNLOC||'|'||SECBNLOC||'|'
from k42
where ITMNBR like '62-40-00200%' AND WHSE like 'WS%';

Yes, the culprit was the " , " that produced the extra space. Toast to you, Lee. This blog entry is for you. &#59;D

2 feedbacks »
 

PL/SQL FAQ for Newbies

12/17/04 | by Clarise | Categories: Databases

1. What is PL/SQL? How is it different from SQL*Plus?
PL/SQL is Oracle's extension to SQL incorporating Procedural Language. It allows the data manipulation and query statements of SQL to be included in logical program blocks and procedural units of code.
SQL*Plus is an Oracle Server tool that recognizes and executes SQL statements.

2. What is the basic Structure of the PL/SQL Block?
Every unit of PL/SQL comprises one or more blocks. These may be entirely separate or nested. The general structure of the block is:
HEADER: Used only for named blocks
DECLARE: Defines the PL/SQL Objects to be used in this block
BEGIN
/* start of executable actions */
EXCEPTION
/* Error Handlers; Defines what to do if an executable action produces exceptions or errors */
END;

3. What are some of the control statements in PL/SQL?
- IF Statement
- GOTO statement
- Basic Loops
- FOR Loops
- WHILE Loops

4. How do you process PL/SQL blocks in SQL*Plus?
- Define a block in the SQL buffer and then run the buffer
- Define a block as part of a SQL*Plus script and then run the file

Example PL/SQL

5. While using PL/SQL in SQL*Plus, how can one print to screen?
Issue:
SQL> set serveroutput on;
then run your PL/SQL block

6. How do you show errors while compiling from SQL*Plus?
issue:
SQL> show errors

7. How do you issue create table from PL/SQL?
Starting with 8i, DDL statements e.g. CREATE, TRUNCATE and DROP can be called within PL/SQL by using EXECUTE IMMEDATE.
Example:
begin
EXECUTE IMMEDIATE 'CREATE TABLE SAMPLETABLE(ID NUMBER)';
end;

 

SF Chronicle Article: Free TiVos to Comcast Customers

12/16/04 | by Clarise | Categories: General Thoughts

If you work and/or live around Alviso, California or are from the Bay Area and don't mind going to Alviso :D, the article in today's (December 16,2004) Business Section of the San Francisco Chronicle, Free TiVos to Comcast Customers may be of interest. 40-hour Tivo recorders will be given away on Friday from 11 am to 1 pm. I have been a Tivo user since Tivo's early days and I'm very happy with it.

 

Bay Bridge Savings on the new Design.. A Joke?

12/14/04 | by Clarise | Categories: In real life

Bay Bridge

The San Francisco Chronicle's article "Governor Picks Simpler Bay Bridge Plan" leaves one thinking...

Let's be realistic here. Factor in the cost to redo the work done to date, rising costs of everything, cost of the delay and all other costs associated with resources including time. Who are they kidding? Do you think they'll really save $300 million to $500 million with the new design? Ok, during these hard times for most Bay Area residents, I agree on saving money if there are savings involved. Yeah, yeah, yeah... no tax increases as promised during campaign period but increase in tolls, increase in this and that....

The date is not April Fools Day, is it?

 

Common Oracle DBA Technical Questions

12/13/04 | by Clarise | Categories: Databases

Here are common Oracle DBA Questions I have encountered:

1. What is the format of an Oracle Release Number?
Example: 10.1.0.3.0
10 - Major database release number
1 - Database maintenance release number
0 - Application server release number
3 - Component specific release number
0 - Platform specific release number

2. What is a mutating table?

A mutating table is a table that is currently being modified by an update, delete, or insert statement. If an application has a row trigger that reads or modifies the mutating table, the common ORA-04091 error is generated.

ORA-04091: "table %s.%s is mutating, trigger/function may not see it"
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.

3. What is the difference between deadlock and blocking lock?
A deadlock is a condition where two or more users are waiting for data locked by each other.
A blocking lock is held by the blocking session.

4. What are the data structures used for database recovery?
- Redo Logs
- Rollback and Undo Segments
- Control Files

5. When monitoring Oracle databases, what are the common V$ used?
V$PROCESS
V$SESSION
V$SESS_IO
V$SESSION_LONGOPS
V$SESSION_WAIT
V$SYSSTAT
V$RESOURCE_LIMIT
V$SQLAREA
V$LATCH

6. What are some Initialization Parameters that affect performance?
- DB_BLOCK_SIZE: database block size
- DB_CACHE_SIZE: size of the buffer cache in the SGA
- SHARED_POOL_SIZE: size of the shared pool in the SGA
- PROCESSES: maximum number of processes that can be started by that instance
- SESSIONS: set by default from the value of processes
- JAVA_POOL_SIZE: If using Java stored procedures, this parameter should be set depending on the memory requirements for the Java environment
- LOG_ARCHIVE_XXX: Enables redo log archiving.
- ROLLBACK_SEGMENTS: Allocates one or more rollback segments by name to this instance

7. What is the difference between Suspending a database and Quiescing a database?
Starting with 9i, the concepts of Suspending and Quiescing a database were introduced.
Suspending a database : database suspend/resume feature
- ALTER SYSTEM SUSPEND statement suspends a database by halting all input and output (I/O) to datafiles and control files
- ALTER SYSTEM RESUME statement resumes normal database operation
Quiescing a database: By putting a database on quiesced state, only DBA transactions, queries, or PL/SQL statements are allowed
- ALTER SYSTEM QUIESCE statement puts the database into a quiesced state

 

Pages: 1 ... 10 11 12 13 14 15 ...16 ...17 19 21

June 2019
Mon Tue Wed Thu Fri Sat Sun
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
 << <   > >>
This blog contains thoughts that range from non-technical to technical. Its name is derived from "Yakity Blah Blah" a column I once had that discussed a cornucopia of ideas. Who am I? I'm Clarise Z. Doval Santos, providing Project Management and Technical Leadership for data management and analytic, data science, IoT and sensor analytics ecosystems 37.652951177164 -122.490877706959

Search

  XML Feeds

Content Mangement System