Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Create a Simple Anonymous PL/SQL Code Block

The following exercise is for you to measure your level of understanding of basic PL/SQL.  It is not graded.  After completing the exercise you should be able to create and execute PL/SQL on your PC.

Your task is to create a script called mailing_label.sql.  This script will be a PL/SQL anonymous code snippet that will perform the following functions:

Step 1 – Declare variables for store_address, store_city, store_state, and store_zip

set serveroutput on 

DECLARE
   v_store_address    store.store_address%type;

Step 2 – Populate the variables with a SELECT INTO, where store_name = ‘borders’.  Here is a partial query:

BEGIN
   select
      store_address,
   into
      v_store_address,
   from
      store
   where
      store_name = 'borders';

Step 3 – Change your SQL to change the case of the address data using the initcap and upper SQL BIFs.  Here is a partial query.

BEGIN
   select
      initcap(store_address),
   into
      v_store_address,
   from
      store
   where
      store_name = 'borders';


Step 4 – Display the address using dbms_output.put_line as follows:

Borders Bookstore                                                              

2300 West Winters St                                                            

Durham, NC 27666                

ANSWER

set serveroutput on 

DECLARE
   v_store_address    store.store_address%type;
   v_store_city       store.store_city%type;
   v_store_state      store.store_state%type;
   v_store_zip        store.store_zip%type;
BEGIN
   select
      initcap(store_address),
      initcap(store_city),
      upper(store_state),
      store_zip
   into
      v_store_address,
      v_store_city,
      v_store_state,
      v_store_zip
   from
      store
   where
      store_name = 'borders';
 
   dbms_output.put_line('Borders Bookstore');
   dbms_output.put_line(v_store_address);
   dbms_output.put_line(v_store_city||', '||v_store_state||' '||v_store_zip);
   dbms_output.put_line('              '||v_store_zip);
END;
/



For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.