1. Create a file named YourLastName_A.BAT. This file will give the user a choice to compile or run your stored procedures. Your file should do the following:
   • Prompt the user to Compile or Run (user will enter C or R) If they choose C (compile)

Run SQL script YourLastName_A_Compile.SQL (created in next steps)

Make sure to pause so you can see the output to verify the procedures compiled correctly.

After the user hits enter, terminate the batch file.

If they choose R (run)

Run the SQL script YourLastName_A_Run.SQL (created in next steps)

Do not pause - the batch file must terminate immediately when the SQL script completes.

• DOS commands should not display on the screen when batch file runs.

More hints for BAT File.

2. Create a file named YourLastName_A_Compile.SQL. This file will contain your stored procedures. You will be creating two procedures:

Procedure Get_Tables:

Purpose: Notes:	To process each table in your schema (use the USER_ System Catalog tables) and output table-related lines (see sample on last page, lines 1-6, 11-15 and 24-28).
	Tables must appear in alphabetical order.
	Procedure Get_Tables will call the procedure Extract_Columns for each Table.

More hints for Get Tables Procedure

Procedure Extract_Columns:

Purpose: Output column-related lines for the currently selected table in Get_Tables procedure (see sample on last page, lines 7-10 & 16-23).

Note: Columns must be listed in the same order as in the original CREATE Table statement.

More hints for Extract Columns Procedure

3. Create a file named YourLastName_A_Run.SQL. This file will contain the SQL code necessary to:
   • Run the Get_Tables stored procedure(described above)
   • Spool output to a file called Create_Tables_YourLastName.SQL (hint: SERVEROUTPUT size should be minimum 10,000).

BAT FILE

• MENU:
  • Must prompt the user to enter a C for compile or R for run: For example:
    • 'C'ompile
    • 'R'un
  • It should accept a lowercase or uppercase entry. Use the Ignore Case switch (/I )to accomplish this. Refer to your lecture notes if necessary.
  • If anything else is entered including a space or the ENTER button an error message should be displayed followed by the prompt again. For example: Invalid choice, please choose 'C' or 'R' Enter your choice
  • It is ok if you want to clear the screen before prompting again but you do not need to. Remember to keep it professional.
• COMMENTS
  • The bat file should contain opening comments:
    • Date:
    • Author:
    • Purpose:

RUN FILE SHOULD

• CLEAR the Screen
• Open Spool file
• Execute the stored procedure Get_Tables
• Close spool
• Exit

COMPILE FILE

• Get_Tables (receives no parameters)
  • In the AS section
    • Define a cursor to get all tables for your userid
    • Use SELECT Table_Name FROM User_Tables to load up the cursor
    • Define a composite variable to hold an individual row
  • In the BEGIN section
  • Use DBMS_OUTPUT.PUT_LINE statements for PAGE heading items
• • • Create a loop that will loop through the tables (remember you created a cursor and you need to loop through this cursor to get the table names)
      • Use DBMS_OUTPUT.PUT_LINE statements for table header items
      • First time through the loop you need to output the first table name hint: you will need to use the composite variable declared at the start
      • At this point, you should call the extract_columns procedure to take care of showing the rows for this particular table. You will need to send over the table name to the extract_columns procedure.
      • Use DBMS_OUTPUT.PUT_LINE statements for table footer items
    • Loop ends when tables the end of the cursor is reached
  • Use DBMS_OUTPUT.PUT_LINE statements for PAGE footer items

• Extract Columns, needs to accept the table name parameter being sent from the Extract Tables procedure
  • In the AS section
    • Define a cursor to get all columns for your userid
    • Use SELECT Column_Name FROM User_Tab_Columns to load up the cursor but we need to only find only the columns for the table name that was passed in. hint: Use a WHERE clause
    • Define a composite variable to hold an individual row
  • In the BEGIN section
    • Create a loop that will loop will print out the column names (remember you created a cursor and you need to loop through this cursor to get the column names) hint: you will need to use the composite variable declared at the start
  • HINTS: The next milestone we will add the datatypes. These must be aligned. Therefore:
    • Store the column name in a CHAR type variable using a size that you deem necessary. The CHAR datatype will automatically pad additional spaces at the end of the column name. This allows you to print the datatypes and sizes at the end of the column variable and ensure that they are properly aigned. For example

ColumnName datatype1(##)

AnotherColumnName datatype2(##)

• To print a line and include a line feed use DBMS_OUTPUT.PUT_LINE
• To continue printing on the same line that it is presently on use DBMS_OUTPUT.PUT

YourLastName A Compile.SQL File will compile the two procedures, Get TablesTables and Extract Columns. Output is on the screen only YourLastName_A.BAT. Asks user to press CorR YourLastName A Run.SQL File will Run (execute) the Get TablesTables procedure and send information to the output file Create Tables YourLastName .SQL Output file with output as shown on last page (should be exactly the same)