And a fun Week in Seattle!!

Little background about this trip: This is one the corners of United States which has not been covered in the decade I had spent in here. I said yes the moment my wife told we are planning a summer vacation in Seattle with childhood friends in early March. She was exciting to meet the folks from her place 21 years back. I thought It will be an exciting weekend in Washington state meeting family and friends and planned few places to visit there other than spending time with friends. Kids are also very excited. So the time came and here is the details of my trip to Seattle.

What is Seattle – A city on Puget Sound in the Pacific Northwest, is surrounded by water, mountains and evergreen forests, and contains thousands of acres of parkland. Washington State’s largest city, it’s home to a large tech industry, with Microsoft and Amazon headquartered in its metropolitan area. The futuristic Space Needle, a 1962 World’s Fair legacy, is its most iconic landmark. So obviously the request from kids was to visit Space Needle and I was more interested in the Chihuly Garden and Glass other than visiting Microsoft HQ, Amazon HQ and Starbucks. And the story of Costco – originated from Kirkland City is also a neighboring suburb of Seattle.

Seattle is also called coffee capital and in today’s world coffee is alias to Starbucks. It all started in 1971 when the first Starbucks opened its doors in Seattle’s Pike Place Market. This coffee chain has since become a global phenomenon, with over 28,000 stores in more than 75 countries. Even if you’re not a fan of Starbucks coffee, it’s worth checking out the original store just to see what all the fuss is about. It’s usually packed with tourists taking photos and buying souvenirs. Enough for now about Seattle.

Day 1 – Lets get back to my trip.

So this time we were travelling from Austin, TX to Seattle-Tacoma, WA. its a 4 hours 30min direct flight but we took a stopover at Los Angles. We boarded an early morning Delta Boeing 737-900 flight from Austin after a little rough start to our trip as Uber driver cancelled our planned advance ride few minutes before his arrival time. I would not blame him. Who wants to work early morning 4:45AM on Sunday 🙂 So a little tip – book your early morning flight either on weekdays or don’t depend on Uber for Sunday early mornings. Anyways we took our own car and drove to airport. Checked-in our luggage and cleared the security. It was exciting for the little one. He was traveling on the plane after a almost 2 years. Our flight took off around 6:30 am and we reached Los Angles. Took connecting flight to reach SEATAC(Seattle Tacoma International Airport) around 12:30 pm PST. Flight was comfortable and on time. I love Delta and Alaska for the same reason. They have one of the best experience and service in air.

So we were warmly received by our friend Sankalp and we headed to his home. He lives in Redmond which is a suburb to Seattle and home to Microsoft. We picked up cake from https://www.regentcakes.com on the way to to celebrate father’s day at home. The way to home so amusing filled with lush green pine and rainforest trees on both side. Its a Mid June and summer of Austin where temperature was touching 100*F to the place in Seattle where it was 56*F in day time with fresh air filling in the atmosphere. Felt the cool breeze while getting out of the car and here we are at the place. Its a beautiful ranch house if someone ask me located at center of the place surrounded by trees on all the side. We met Rahi and Ruchi waiting for us there.

We met them once when they were traveling to San Francisco 2 years back. Kids grow faster. We celebrated Father’s day in evening cutting the fruit cake.

We chatted till late in the night after dinner and went to sleep.

Day 2 – Itinerary – We planned to visit Seattle downtown and art museum. I woke up early and decided to take a walk outside. Saw a deer on the way back home on the road looking at me. Took Sam with me later to show him but it was gone by the time but he enjoyed walking on the empty wet road with fresh air.

We headed to the downtown by car taking I520-I405 and I 90 to downtown. You can park near to Mopop Museum just behind it. Its parking price is $20 per 2 hours and we walked to the space needle.

Space Needle – The Space Needle is an observation tower in Seattle, Washington, United States. Considered to be an icon of the city, it has been designated a Seattle landmark. Located in the Lower Queen Anne neighborhood, it was built in the Seattle Center for the 1962 World’s Fair, which drew over 2.3 million visitors.

The Space Needle was once the tallest structure west of the Mississippi River, standing at 605 ft (184 m). The tower is 138 ft (42 m) wide, weighs 9,550 short tons (8,660 metric tons), and is built to withstand winds of up to 200 mph (320 km/h) and earthquakes of up to 9.0 magnitude, as strong as the 1700 Cascadia earthquake.

The Space Needle features an observation deck 520 ft (160 m) above ground, providing views of the downtown Seattle skyline, the Olympic and Cascade Mountains, Mount Rainier, Mount Baker, Elliott Bay, and various islands in Puget Sound. Visitors can reach the top of the Space Needle by elevators, which take 41 seconds. On April 19, 1999, the city’s Landmarks Preservation Board designated the tower a historic landmark.

Back to present from History. I felt the ticket to be bit overprice. Anyways we entered the building and waited over the lift to take us to the top.

Some of the views of tower are amazing.

A night view and a view from ferry

But the best from the top of the top 2 floor of the tower

After spending almost 2 hours at deck we started for one place I wanted to visit the most.

Its an amazing place – how can one imagine to torn and bend silicon into glass and glass into so many beautiful sculptures. Glass artist Dale Chihuly famously said, “I never met a color I didn’t like.” It’s a phrase that seems to sum up the internationally renowned artist whose life has been characterized by bold hues and fantastically colored glass sculptures. You can see his work at the Chihuly Collection in downtown St. Pete, in a building designed specifically to house the artist’s work. 

While heading back we visited Mopop on the way back to parking. Formerly known as the Experience Music Project (EMP), the nonprofit MoPOP is one of the most popular attractions in the Seattle Center, home to other renowned sites like the Space Needle and Pacific Science Center. The museum was designed by famous architect Frank Gehry, known for his bold designs and sheet metal work constructing iconic monuments like the Walt Disney Concert Hall and the Guggenheim Museum Bilbao. The exterior is a piece of art itself, an eccentric building made up of metal shapes that many, including Gehry himself, say resemble a smashed electric guitar, marking the perfect space for the MoPOP to reside. Inside is 140,000 square feet of space, including the museum’s centrally-located Sky Church, a venue paying homage to music legend Jimi Hendrix.

And that’s how our second day trip was coming over to the end. We went to McD to grab a burger for kids and headed back to home.

Day 3 – I had to do some office work and kids plan to visit near by place and we waited for our 3rd team who was heading to Seattle from Virginia. We met them in 2018 while visiting DC and stayed at their place for a night – to what Gagan say a stopover. Gagan and Swati have 2 beautiful daughters. This get together will bring 3 teenage girls together after a period of 21 years when all of them have grown up to be beautiful ladies. Its a feeling no one can explain when you met your buddies after a long gap.

We took a rental car from the airport and Sankalp received them from airport and we headed back to our base. We chatted and had dinner together and went to sleep as kids were tiered of the trip. Next few days are going to be fun and full of excitement.

Day 4 – Trip to hiking trail to Cherry Creek falls.

We met 2 beautiful girls Raina and Alisha. Raina is of same age as Sam and they quickly intermingled and got busy with activities. They were head to head in every steps and we enjoyed their fighting and crying and playing together.

Lets get back to the hiking the trail to cherry creek falls – Originally the site of a logging camp in the first part of the 20th century, Cherry Creek Falls is now a popular place for locals (and visitors) to cool off after the hike in. The 25-foot falls are the final waterfall along Cherry Creek. The falls drop over a wide, rocky ledge into a large pool. This pool is inviting to hikers, but as always with waterfalls, be careful when splashing and playing in rocky areas.

Initially we lost the way to trail head but Gagan helped us to the right location. We hiked with two 5-year-old and two 10 years old. It was great experience. We separated in mid due to keeping pace with 10yrs old who seems to be enjoying the hike so far. There were a couple spots to park on the shoulder, with plenty of movement in and out. If you stick to the widest/best kept trail at every fork, you’ll make it to the falls. That said, some places were only a foot wide between brush with some washouts and rock/trees in the way. Quite a few hikers, quite a few pups. Most were well-behaved. We didn’t have any troubles getting through, even though the kiddo hadn’t been on a hike longer than two miles before. The falls were fantastic!

Returning back from hike was little tiresome, legs were cramping and everyone was done for the day. We get back to home and had rest and chat that evening and retired for the day.

Day 5 – Trip to MS Headquarter , golf course and Kirkland

Plan changed for this day and we adjusted to make relaxing schedule for the day. Sankalp had arranged a visit for us to MS visitor center. We went and enjoyed few hours there and headed to golf course for a fun evening play with kids.

Me and Gagan did a baby sit for young kiddos and got a free 2 hour play to ourselves without any disturbance. Ladies got the chance and hit to the beach while Sankalp was managing the grownup kids at home. This place was near to the Kirkland Costco so we thought why not visit the place from where Costco started.

We had some drink in the night and day ended with enjoyment and chatting with Friends.

A view of moon from dining table at night.

Day 6 – Trip to Snoqualmie Falls

Snoqualmie Falls is a 268-foot (82 m) waterfall in the northwest United States, located east of Seattle on the Snoqualmie River between Snoqualmie and Fall City, Washington. It is one of Washington’s most popular scenic attractions and is known internationally for its appearance in the television series Twin Peaks. More than 1.5 million visitors come to the Falls every year. Most of the river is diverted into the power plants, but at times the river is high enough to flow across the entire precipice, which creates an almost blinding spray. High water occurs following a period of heavy rains or snow followed by warm rainy weather. This can occur during the rainy season which lasts from November through March. During high water, the falls take on a curtain form.

For the Snoqualmie People, who have lived since time immemorial in the Snoqualmie Valley in western Washington, Snoqualmie Falls is central to their culture, beliefs, and spirituality. A traditional burial site, to the Snoqualmie, the falls are “the place where First Woman and First Man were created by Moon the Transformer” and “where prayers were carried up to the Creator by great mists that rise from the powerful flow. The mists rising from the base of the waterfall are said to serve to connect Heaven and Earth. Enough about history and geography.

We hiked the trail to the end of the fall which converts into a river. It was an awesome place to spend time with friends if you love nature.

We spent the day there and headed back to beach but has to drop the plan as there was huge traffic jam on the highway and we diverted to a place near by to have lunch and Sankalp surprised us with dhaba tea on the road.

BTW he makes a very good espresso filter coffee. All the days spent there, I got a chance to have a coffee by his hand. Thankyou for that. So after coffee sorry dhaba tea we returned back to home via local road and avoided the highway as it was still jammed. Had a drink with Gagan and watched Sultan 🙂 . He shared so many experience from his life. What a wonderful guy.

We talked till late in the night as this was the last night there and we had a early morning flight back to Austin. Said good night to everyone promising to meet next year some place all together again. Times flies like anything and for sure meeting in there in the near future.

Day 7 – Back to Austin.

We wakeup early morning around 4 am and got ready. Everyone was sleeping except Ruchi- she only woke me up else might have missed the flight. Thanks again for inviting and hosting us at your beautiful ranch house. We invited her to visit us soon in Austin.

Returned the rental at airport and took the flight back to Austin, It was a direct 4 hours flight. I slept on the way back being tired of last few days. We missed one of the places to visit from itinerary – Mount Rainier  – but there is always a next time. Hope to hike some trail next visit there.

But our friends from Virginia got a chance to visit the place next day !! Happy for them.

And that’s how one of our summer vacation trip to Seattle ended on fun note with so many experience and wonderful memories.

See you soon!!

A weekend visit to Sri Venkateshwara Temple of Austin – Cedar Park

At a distance while driving north from Austin downtown to Cedar park suburb for a 15mile. Cedar Park, Texas is one of Austin’s most popular and well-known suburbs. Known for its proximity to northwest Austin’s tech hub, excellent schools, and the H-E-B Center that hosts two professional sports teams, Cedar Park is one of the fastest-growing cities in the country. Temple is located on New Hope to Veterans Memorial Park, A white ornate tower and a beautifully landscaped grounds of the Sri Venkateswara Temple of Austin will welcome you. This beautiful Hindu Temple is located at 2509 West New Hope Drive, Cedar Park, TX 78613.

 

Its co-located in the same complex along with another another Hindu Temple – Sri Shirdi Sai Baba Temple of Austin. Founded on the teachings of Sai Baba whose life was one of unity. He lived to unite the different castes and creeds together as one family and unite every person with God. He saw no difference between one religion and another, saying often, “Sabka Malik Ek,” “One God governs all.”

What is Deterministic function ?

A deterministic function is one that always returns the same resulting value as long as the parameters that are passed in are the same. This type of function can be useful for improving performance. The function will be executed only once for any given set of parameters. This means that if the same parameters are passed to this function in subsequent calls, then the function will be bypassed and return the cached value from the last execution using those parameters. This can really help in cases where
calculations are being performed and repeated calls to the function may take a toll on performance.

The DETERMINISTIC clause is required in a couple of cases. In the event that you are calling a function in an expression of a function-based index, you need to write the function as DETERMINISTIC, or you will receive errors. Similarly, a function must be made DETERMINISTIC if it is being called in an expression of a materialized view query or if the view is marked as ENABLE QUERY REWRITE or REFRESH FAST.

CREATE OR REPLACE FUNCTION manager_name(mgr_id IN NUMBER)
RETURN VARCHAR2
DETERMINISTIC IS
first_name employees.first_name%TYPE;
last_name employees.last_name%TYPE;
BEGIN
IF mgr_id IS NOT NULL THEN
SELECT first_name, last_name
INTO first_name, last_nameFROM EMPLOYEES
WHERE employee_id = mgr_id;
RETURN first_name || ' ' || last_name;
ELSE
RETURN 'N/A';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'N/A';
END;

Road Trip San Francisco, CA to Austin, TX

This is one of those few trips which are not planned in advance and you just pack you bags and start on the road. The distance by road from San Francisco to Austin is 1800 miles approx. which google map suggest to be covered in 1 day 4 hours on average. And when you travel with family with kids it can increase to few more days. We started from San Francisco around 10:00AM pacific time and targeted to reach our first stop which was Palm Spring taking a rest break in between at Bakers field. We passed Los Angles on the other side.

Palm Springs is a small desert city in southern California which is very popular for its hot springs, stylish hotels, golf courses and spas. It’s also noted for its many fine examples of midcentury-modern architecture. Its core shopping district along Palm Canyon Drive features vintage boutiques, interior design shops and restaurants. The surrounding Coachella Valley offers hiking, biking and horseback riding trails.

First day’s driving was pretty straight just following I5 till Bakersfield. We reached Palm Springs in evening around 8:00 PM and directly checked into hotel. It was very hot in evening as well reaching a temperature of 103* F. We did ream a little bit in evening but it was little deserted may be because of heat or off season.

Day 2 started with a well deserved continental free breakfast at hotel and a cup of coffee at Starbucks. We started from Palm Springs to our next destination which was in Arizona – Tuscon, AZ. This started on I10 East which start from west coast to east coast. We stop for refuel and lunch at Buckeye. It is a small town located approximately 30 miles west of downtown Phoenix in the Buckeye Valley. Interstate 10 passes through the central part of the city, north of the original town center. U.S. Route 80 once passed through the city, while Arizona State Route 85 skirts what was the city’s west edge.

We drove through a building up sand storm while passing by Phoenix. Visibility was very low on the I10 loop towards Tuscon. But after a quick downpour weather clears out. Road to Tuscon was hilly and passing by mountains.

Finally we reached Tuscon around 6:00 PM and checked into hotel. Driving today was not that tiring due to 2 reason. My partner drove from Palm Springs to Buckeye which covered almost 65% of todays distance and then I drove the rest of 35% and reached hotel. After taking a rest for a while, we decided to explore the downtown and went to dinner at an Indian restaurant called Saffron Indian Bistro. It is one of the most popular fine dining restaurant in the city.

Oh by the way a little bit about Tuscon – Initially we decided to stop at this place because we planned to meet one of our friend but got to know later that he is out of town that weekend. Tuscon is the  home to the University of Arizona. It is the second largest city in Arizona behind Phoenix.

A lot of cactus trees can be seen on the side of the road while driving which diverts your eyes of the road to take a quick glimpse of the beauty of nature in a different way.

We celebrated raksha bandhan at hotel room after our dinner. Kiddos enjoyed tying rakhi to each other. This day ended with it and we were excited for the next day.

Day 3 of the trip was little longer then 8 hours drive from Tucson, AZ to Ft Stockton, TX. It will cross New Mexico and enter into Texas. Our time zone is also about get change while driving on the road. We had planned to start early from hotel so that we can cover the distance before dusk as the road after certain distance will be totally deserted and feels like no mans land. We had an early free continental breakfast – through we felt little bored eating the same kind of breakfast again on the third day. It was planned that my partner will drive the first 4 hours and then then will switch seats at around lunch time at El Paso, Tx.

The I10 East of this stretch is more of valley and desert. More variety of cactus can be seen around the road on both sides. You can see one of the iconic cactus flower being touched and experienced by him.

We took a rest break at iconic road runner at Las Cruses. Its one of the vista points from where whole city view can be seen. Las Cruces is a city in New Mexico, on the edge of the Chihuahuan Desert. 

We left New Mexico and entered into El Paso, Texas. This is border city with Mexico. El Paso stands on the Rio Grande across the Mexico–United States border from Ciudad Juárez, the most-populous city in the Mexican state of Chihuahua with over 1.5 million people.

We took a break at El Paso downtown and had a quick lunch at Chipotle and McDonald’s. Refuel car tank as the next stretch will be little long and deserted. We started from El Paso around 3:00PM. Suddenly I realized that speed limit on the I10 E on Texas is 80Miles/hour. Cars and Trucks are zooming fast side of us. Once you exit the city and drove for around 40-50min. There comes a checkpost on the highway and security will check passports and visas. After that the whole stretch was pretty deserted and baron with few valleys and mountains. We passed few mountains pass which were cut into 2 make way for I10 East. We crossed a mountain which looks like a pyramid with a table top on the way.

Finally we reached the 3rd day evening halt at Ft Stockton, Located deep in the heart of West Texas, the city is a living tribute to frontier life, when Comanche Indians, cattle rustlers, and American soldiers came to find water, buffalo, or just a stiff drink. It’s still a great place to get that drink, as well as eat delicious food and see some amazing sights. Fort Stockton’s history reflects the tale of the American frontier. In 1858, soldiers of the 1st and 8th Infantries arrived from nearby Fort Lancaster to garrison near the Comanche Springs at what was known as Camp Stockton. We experienced a wonderful sunset from our hotel window before we headed for dinner. Dinner was authentic to the Texas culture. Nachos with American cheese and Cheese burger with fries. We all were tiered and hit the bed early after a long drive for the day.

Day 4 or the final day of driving from Ft Stockton to Austin, TX. This was a 5 hours drive and as per the pattern from last few days. She was assigned to hit the road for the first half stretch and I will pick up the next one. We started early from hotel after again a continental breakfast which we were all bored by now and a fresh coffee around 9:00 AM. We roamed a little bit around the city and took some snaps.

Next stretch of drive was pretty mundane with 80miles/hours speed limit and no cars on the road. Road trip songs were continuously playing on repeat on Spotify by now. Kiddos were busy with their devices in the back seat and we were chatting here and there and suddenly I spotted OYO hotel in middle of now where. Aggarwal is really a thing if he is able to open one in here. I think its near to the outskirt of city called Junction which is also our next halt for lunch before we switch gears and I control the steering wheels. After a quick meal at Junction and refueling the tank, we started for the final stretch of our road trip.

This was the exit where we departed and signoff I10 E which was with us since last 3 days. We excited to state highway 290 E and one more step near to our final destination.

And finally we saw the first sign board for Austin on the road. It was cloudy and we are welcomed with a light rain. We reached our destination and that’s how our first 4 day long trip with family come to an end with lot of wonderful memories and driving experience on the road.

PL/SQL fundamental – Obtain environment variable and session information

Make use of the SYS_CONTEXT built-in function to query the database for the user’s information. Once you
have obtained the information, then store it into a local variable. At that point, you can do whatever
you’d like with it, such as save it in a logging table. The following code block demonstrates this
technique:

<<obtain_user_info>>
DECLARE
username varchar2(100);
ip_address varchar2(100);
BEGIN
SELECT SYS_CONTEXT('USERENV','SESSION_USER'), SYS_CONTEXT('USERENV','IP_ADDRESS')
INTO username, ip_address
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('The connected user is: ' || username || ', and the IP address
is ' ||
ip_address);
END;

PL/SQL fundamentals-1

Problem
You want to write a script that prompts the user for some input. You want your PL/SQL code to then use that input to generate some results.

The variable following the & character is known as a substitution variable. It is important to note that a
substitution variable is meaningful to SQLPlus. Substitution variables are not “seen” by the database engine. SQLPlus actually replaces the variable reference with the text that the user entered. As far as the
database is concerned, the solution code contains the following WHERE clause:
WHERE department_id = 40;
If you want to reference the same substitution variable at a different point in your code, you can
place two ampersands in front of the first to tell SQLPlus that you want to retain that value for use at a later time. For instance, the following code block first obtains the value from the keyboard using &&variable_name, and then it prints that value out using &variable_name:

DECLARE emp_count NUMBER; BEGIN SELECT count()
INTO emp_count
FROM employees
WHERE department_id = &&department_id;
DBMS_OUTPUT.PUT_LINE('The employee count is: ' || emp_count ||
' for the department with an ID of: ' || &department_id);
END;

When using substitution variables, it is imperative to pay attention to the type of value the user will
be entering at the keyboard. If a value will be a variable character (VARCHAR2) type, then the substitution
variable must be surrounded by single quotes, or you will receive an error when the input is processed.
Similarly, if a value should be a numeric (NUMBER) type, then there should not be single quotes placed
around the substitution variable. Here’s an example:

<<outer_block>>
DECLARE
mgr_id NUMBER(6) := '&current_manager_id';
dept_count number := 0;
BEGIN
SELECT count(*)
INTO dept_count
FROM departments
WHERE manager_id = outer_block.mgr_id;
IF dept_count > 0 THEN
<<inner_block>>
DECLARE
dept_name VARCHAR2(30);
mgr_id NUMBER(6):= '&new_manager_id';
BEGIN
SELECT department_name
INTO dept_name
FROM departments
WHERE manager_id = outer_block.mgr_id;
UPDATE departments
SET manager_id = inner_block.mgr_id
WHERE manager_id = outer_block.mgr_id;
DBMS_OUTPUT.PUT_LINE
('Department manager ID has been changed for ' || dept_name);
END inner_block;
ELSE
DBMS_OUTPUT.PUT_LINE('There are no departments listed for the manager');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There are no departments listed for the manager');
END outer_block;

You want to execute a script in SQLPlus that contains elements that appear to be substitution variables, but you do not intend them to be substitution variables. You want the interpreter to ignore them instead of prompting the user for input.

Solution #1

One solution is to precede the & character with an escape character. The escape character tells SQLPlus
that what follows is not intended to be a variable reference.
In the following code, an escape character is used to tell SQLPlus to ignore the & character when it is encountered and to treat “& Receiving” as simple text within a string: SQL> SET ESCAPE '\' SQL> INSERT INTO DEPARTMENTS VALUES( 2 departments_seq.nextval, 3 'Shipping \& Receiving', 4 null, 5 null); 1 row created.

Solution #2

Another solution is to completely disable the substitution variable feature. The next example uses the SET DEFINE OFF command to tell SQLPlus that it should ignore all substitution variables:
SQL> SET DEFINE OFF
INSERT INTO DEPARTMENTS VALUES(
departments_seq.nextval,
'Importing & Exporting',
null,
null);
1 row created.

Problem
You have a variable and a column sharing the same name. You want to refer to both in the same SQL
statement.
For example, you decide that you’d like to search for records where LAST_NAME is not equal to a last
name that is provided by a user via an argument to a procedure call. Suppose you have declared a
variable LAST_NAME, and you want to alter the query to read as follows:
SELECT first_name, last_name, email
INTO first, last, email
FROM employees
WHERE last_name = last_name;

How does PL/SQL know which LAST_NAME you are referring to since both the table column name and
the variable name are the same? You need a way to differentiate your references.


You can use the dot notation to fully qualify the local variable name with the procedure name so that
PL/SQL can differentiate between the two. The altered query, including the fully qualified
procedure_name.variable solution, would read as follows:
CREATE OR REPLACE PROCEDURE retrieve_emp_info(last_name IN VARCHAR2) AS
first VARCHAR2(20);
last VARCHAR2(25);
email VARCHAR2(25);
BEGIN
SELECT first_name, last_name, email
INTO first, last, email
FROM employees
WHERE last_name = retrieve_emp_info.last_name;
DBMS_OUTPUT.PUT_LINE(
'Employee Information for ID: ' || first || ' ' || last_name || ' - ' || email);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee matches the last name ' || last_name);
END;

Translation of custom OAF pages.

We can translate the language of a custom OAF page using XLIFF file.

Below is the command to extract the XLIFF File.

java oracle.jrad.tools.trans.extractor.XLIFFExtractor /xx/oracle/apps/po/webui/PoPrintReportPG -languages JA-JP -mmd_dir $OA_HTML/jrad -root $JAVA_TOP/xx/ -xliff_dir $JAVA_TOP/xx/ -source db -username apps -password <apps password> -dbconnection “(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SID=<sid>)))”

The XLIF file will be exported to the specified -xliff dir ($JAVA_TOP/xx/).

Once you get the .xlf file edit the file and add the translations.

         <trans-unit id=”.xx.oracle.apps.po.webui.PoPrintReportPG..mainRN…windowTitle” translate=”yes” maxbytes=”4000″ maxwidth=”30″ size-unit=”char”>

            <source>Print Report</source>

            <target>帳票出力</target>

            <prop-group name=”ora_untranslatable”>

               <prop prop-type=”tagName”>pageLayout</prop>

               <prop prop-type=”attributeName”>windowTitle</prop>

            </prop-group>

         </trans-unit>

Add the translated text in <target> section.

Move the file to any working directory and run the XLIFF import command.

The XLIFF Importer takes a translated XLIFF file and imports it into the MDS Repository.

Below is the command to import the XLIFF File.

 

java oracle.jrad.tools.trans.imp.XLIFFImporter $JAVA_TOP/xx/oracle/apps/po/webui/PoPrintReportPG.xlf -username apps -password <apps password> -dbconnection “(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=<host>)(Port=<port>))(CONNECT_DATA=(SID=<sid>)))”

Differences between Pessimistic and Optimistic Locking

Locking is an RDBMS feature that prevents users from different transactions from causing data conflicts. When locking is acquired on a row, it prevents other transactions from changing that row until the transaction ends. In this post I will explain the differences between pessimistic and optimistic locking in the context of ADF framework.

pessimistic locking:

Most Oracle developers are already familiar with pessimistic locking, which was the default locking in BC4J (now optimistic is the  default in 11.1.2.x)). This means that the row is locked in advance once one of  its attribute is changed through a call to setAttribute() method. If anyone else attempts to acquire a lock of the same row during this process, he will be forced to wait until the first transaction has completed. This is achieved by using the familiar SELECT…FOR UPDATE syntax. This is the safest locking mode because two transactions will never make inconsistent change to the same row. However, this locking mode has disadvantages such that:

  1. If a user selects a record for update, and then leaves for lunch without
    finishing or aborting the transaction. All other users that need to update that record are forced to wait until the user returns and completes the transaction, or until the DBA kills the offending transaction and releases the lock.
  2. The Deadlock – Users A and B are both updating the database at the same time. User A locks a record and then attempt to acquire a lock held by user B – who is waiting to obtain a lock held by user A.

Pessimistic locking, which is the default, should not be used for web applications as it creates pending transactional state in the database in the form of row-level locks. If pessimistic locking is set, state management will work, but the locking mode will not perform as expected. Behind the scenes, every time an application module is recycled, a rollback is issued in the JDBC connection. This releases all the locks that pessimistic locking had created.

An example about pessimistic locking based on well known hr schema, suppose user1 and user2 are two different users (two distinct transactions) using pessimistic locking, both of them try to change the same row of data as follows:

  1. User1 calls EmployeesImpl.setSalary(1000) on a particular row, so user1 immediately acquire a lock on that row.
  2. Now user2 calls EmployeesImpl.setSalary(2000) on the same row, user2 tries to acquire a lock on the row and receives oracle.jbo.AlreadyLockedException.

Optimistic Locking:

Optimistic locking assumes that multiple transactions can complete without affecting each other. Oracle recommends using optimistic locking for web applications. instead of locking a row as soon as it is changed, under optimistic locking, BC4J waits until changed row is posted before attempting to acquire a lock. An exception is not thrown until the conflicting transactions attempt to post their changes to the database.

An example about optimistic locking, suppose user1 and user2 are two different users (two distinct transactions) using optimistic locking, both of them try to change the same row of data as follows:

  1. User1 calls EmployeesImpl.setSalary(1000) on a particular row, user1 does not immediately acquire a lock on that row.
  2. User2 calls EmployeesImpl.setSalary(2000) on  the same row. User1 and User2 now have different entity cache for the same row.
  3. User2 calls commit() action, as part of the commit cycle the changed row is posted to the database. before the update can be executed, user2 acquires a lock on that row. The lock expires immediately, when the commit command is sent to the database.
  4. User1 now calls commit() action, BC4J tries to post the changed row to the database, right before posting it, it attempts to acquire a lock on that row. BC4J recognizes that the row has been changed by another user and that updating the row would overwrite another transaction’s changes, so it throws an oracle.jbo.RowInconsistentException.

Finally, Whatever you use, you can lock a row at any time by calling EntityImpl.lock() on the corresponding entity object instance, even if the locking mode is optimistic.

References:

[1] Oracle 9i Jdeveloper handbook, Peter Koletzeke, Dr. Paul Dorsey, Dr. Avrom Faderman.

[2] Fusion Developer’s Guide for Oracle Application Development Framework
11g Release 1 (11.1.1).

[3] http://www.orafaq.com/papers/locking.pdf

Limitation of BI Publisher report using RTF template having more than 63 columns and how to resolve it.

Limitation of BI Publisher report using RTF template having more than 63 columns.

 

Issue:

Microsoft word has a limitation to accommodate a maximum of 63 columns only in a given table.

However, we had a requirement to add more than 63 columns to a report. In such a case, the report was designed to have an outer table with 2 columns and then have nested tables with the first 63 report columns in the first column of the outer table and the rest of the columns in the second column of the outer table.

 

Although this approach works fine when there is small amount of data, it fails with java heap memory issues for a huge data volume.

 

Outer Table Column1 Outer Table Column2
Inner Table1 Col1 Inner Table1 Col2 …… …… ….. Inner Table1 Col63
           
Inner Table2 Col64 Inner Table2 Col65       Inner Table2

last Column

           

 

 

Solution:

To avoid such issues for reports with greater than 63 columns, Oracle recommends using XSL-FO templates.

 

Approach:

  • Have only one table with just 63 columns in the RTF template.
  • Open the ‘BI Publisher Template Viewer’ tool
  • Choose the working directory and select the RTF template with the 63 columns
  • With the RTF template selected, go to Tools -> Generate XSL File from… -> RTF Template
  • Choose a folder and provide the XSL file name
  • Open the XSL file and add the additional columns manually.
  • Register the file as an XSL-FO template.