Wednesday 24 October 2012

Basic PL/SQL Question

Difference b/w procedure and function? 
                              A procedure may return (one or more values using OUT & INOUT Parameters) or may not return a value. But a function has to return a single value and has the return clause in its definition. Function can be called in select statements but procedure can only be called in a pl/sql block.  Procedure's parameters can have IN or OUT or INOUT parameters. But function's parameters can only have IN parameters.

Difference b/w ROWID and ROWNUM?
                ROWID : It gives the hexadecimal string representing the address of a row.It gives the location in database where row is physically stored.
              ROWNUM: It gives a sequence number in which rows are retrieved from the database.

Give some examples of pseudo columns?
                     NEXTVAL, CURRVAL, LEVEL, SYSDATE
Difference b/w implicit cursor and explicit cursor?
                                        Implicit cursors are automatically created by oracle for all its DML stmts. Examples of implicit cursors:
 SQL%FOUND,
 SQL%NOTFOUND,
 SQL%ROWCOUNT,
 SQL%ISOPEN;
Explicit cursors are created by the users for multi row select stmts.

How to create a table in a procedure or function?
                 See the below piece of code:  Since create stmt can be used only at the sql prompt, we have used dynamic sql to create a table.            
DECLARE
                    L_STMT VARCHAR2(100);
BEGIN
                    DBMS_OUTPUT.PUT_LINE('STARTING ');
                    L_STMT := 'create table dummy1 (X VARCHAR2(10) , Y NUMBER)';
                    EXECUTE IMMEDIATE L_STMT;
                    DBMS_OUTPUT.PUT_LINE('end ');
END;
                  The above piece of code can be written In procedure and function DDL's can be used in function provided that function should be invoked in Begin-End block not from Select statement.

Explain the usage of WHERE CURRENT OF clause in cursors ?
      
Look at the following pl/sql code:
DECLARE
                   CURSOR wip_cur IS
                   SELECT acct_no, enter_date
                   FROM wip
WHERE enter_date < SYSDATE -7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
                   INSERT INTO acct_log (acct_no, order_date)
                   VALUES (wip_rec.acct_no, wip_rec.enter_date);

                   DELETE FROM wip
WHERE CURRENT OF wip_cur;
          END LOOP;
END;
"WHERE CURRENT OF" has to be used in concurrence with "FOR UPDATE"  in the cursor select stmt.
"WHERE CURRENT OF" used in delete or update stmts means, delete/update the current record specified by the cursor.
By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement.

What is the purpose of FORUPDATE?
       Selecting in FOR UPDATE mode locks the result set of rows in update mode, which means that row cannot be updated or deleted until a commit or rollback is issued which will release the row(s). If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.   
                                                                                            
Can we have commit/rollback in DB triggers?
                Having Commit / Rollback inside a trigger defeats the standard of whole transaction's commit / rollback all together. Once trigger execution is complete then only a transaction can be said as complete and then only commit should take place. If we still want to carry out some action which should be initiated from trigger but should be committed irrespective of trigger completion / failure we can have AUTONOMUS TRANSACTION. Inside Autonomous transaction block we can have Commit and it will act as actual commit.                                                        
                                        
Can we make the trigger an autonomous transaction?
                      This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred—that is to say that any changes are uncommitted and the autonomous transaction doesn’t see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn’t exist.

What is autonomous transaction?
                    Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.

What is a REF Cursor?
                     The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database.

What is the difference between ref cursors and normal pl/sql cursors?
   Declare
          type rc is ref cursor;
          cursor c is
          select * from dual;
          l_cursor rc;
       begin
          if ( to_char(sysdate,'dd') = 30 ) then
                   open l_cursor
                             for select * from emp;
          elsif ( to_char(sysdate,'dd') = 29 ) then
                   open l_cursor
                             for select * from dept;
          else
                   open l_cursor
                             for select * from dual;
          end if;
          open c;
      end;                                                                                                                   
Given that block of code you see perhaps the most "salient" difference, no matter how many times you run that block The cursor C will always be select * from dual.  The ref cursor can be anything.

Is Truncate a DDL or DML statement? And why?
                       Truncate is a DDL statement. Check the LAST_DDL_TIME on USER_OBJECTS after truncating your table. TRUNCATE will automatically commit, and it's not rollback able. This changes the storage definition of the object. That's why it is a DDL.

What are the actions you have to perform when you drop a package?
                              If you rename a package, the other packages that use it will have to be MODIFIED. A simple compilation of the new renamed package won't do. If you have toad, go to the "used by" tab that will show you the packages that call the package being renamed.

What is cascading triggers?
                              When a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers.

What are materialized views?
                             A materialized view is a database object that stores the results of a query (possibly from a remote database). Materialized views are sometimes referred to as snapshots.
Example
If the materialized view will access remote database objects, we need to start by creating a database link to the remote DB:

CREATE DATABASE LINK remotedb
CONNECT TO scott IDENTIFIED BY tiger
USING 'orcl';

Now we can create the materialized view to pull in data (in this example, across the database link):

CREATE MATERIALIZED VIEW items_summary_mv
 ON PREBUILT TABLE
 REFRESH FORCE  AS
 SELECT  a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID,
        sum(a.GMS)       GMS,
        sum(a.NET_REV)   NET_REV,
        sum(a.BOLD_FEE)  BOLD_FEE,
        sum(a.BIN_PRICE) BIN_PRICE,
        sum(a.GLRY_FEE)  GLRY_FEE,
        sum(a.QTY_SOLD)  QTY_SOLD,
        count(a.ITEM_ID) UNITS
FROM  items@remotedb a
GROUP BY  a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;

Materialized view logs:
Materialized view logs are used to track changes (insert, update and delete) to a table. Remote materialized views can use the log to speed-up data replication by only transferring changed records.
Example:
CREATE MATERIALIZED VIEW LOG ON items;
Commonly occurring Errors in Reports?
Some of the errors are defined below
1. There Exists uncompiled unit: When the report is not compiled before loading in the Oracle Applications.
2. Report File not found: When the rdf is not uploaded in proper directory
3. Width or margin is zero: When the repeating frame is not within proper frames
4. Not in proper group: When the repeating frame is not referred to proper group
What is the difference between Compile and Incremental Compile in oracle reports?
In Full compile all the PL/SQL within the reports are compiled but in incremental compile only the changed PL/SQL units are compiled.
When compiling the report for the first time, we should do the full compilation and not the Incremental compile.

How to compile Procedures and Packages?
ALTER <proc/package> <name>COMPILE;



LINK :  http://oracleapps4u.blogspot.in/2011/07/oracle-apps-interview-questions-and.html

Copy apex Application

Step 1: Create New Application
Step 2: click Page Rendering under Page Name
Step 3:Then Copy The application

Friday 19 October 2012

Differences between Oracle APEX 4.1.1 and 4.2

                      Having used Oracle APEX since its first appearance on the Early Adopter Platform I finally got my hands on the real thing and installed in on my test-system.
This Post tries to give you a summary of the big changes in Oracle APEX 4.2 (compared to 4.1.1) from a datamodel/view/API Point of view.
If you are coming from APEX 4.0 (or older) read the Posts about the differences in Oracle APEX 4.1 and the Patchset 4.1.1 at first.

What I did to find all those changes is mainly doing a DIFF (using Mac’s FileMerge Tool) of the apex_views.sql File which is located in the /core folder of the APEX Zip-File. I found this method the easiest one to get a good overview, because when you see what changed in the public APEX Views, you also can guess what changed in the Application Builder and in the API’s.
To get in deeper I also did a Schema-Compare of APEX_040100 and APEX_040200, but that gives you a mix of internal and public objects and they also use other names on their packages internally. e.g. the public APEX_UTIL Package is just a synonym on HTMLDB_UTIL . In the end this can get pretty confusing.
Here we go, a summary of more or less important findings. Please note that this isn’t a complete list, there is always more to find out :-)

General :-

  • All Scripts in the Installation Process give now more and better information when installing (prompt…), so if anything would go wrong you should have all the information you need to fix it or request support.
  • Security fixes like prefixing SYS-Objects with the Schema-Name (e.g. SYS.DUAL)
  • Many Views got a COMPONENT_SIGNATURE added, with a Help-Text that sounds very promising for future enhancements: Identifies attributes defined at a given component level to facilitate application comparisons

New Concepts:-

User Interfaces
  • Within a single Application you can have Desktop and Mobile Pages with individual Themes each
  • That means an Application can have multiple active Themes
  • Pages and Themes know which User-Interface-Type they belong to
  • In the future this can be enhanced with other UI-Types, e.g. Smart-TV
Grid Layout
  • With Grid Layouts you can achieve “Responsive Web Design” as demonstrated by Shakeeb at Open World
  • This gives you a better control over how to structure a Page Template
  • A famous Library of CSS Grids is Twitter Bootstrap
Display Points
  • a Display Point is a placeholder in a Template (ie. a Position where a Region is displayed)
  • the new thing here is that you can define a readable Name (e.g. Sidebar) for a Placeholder (e.g. REGION_POSITION_03) and define how this Display Point behaves in a Grid
  • this also replaces the former Region-Template-Flag “Render Form Items in Table” (introduced in APEX 4.1). If you migrate an Application which used that flag it will be a Region-Display-Point instead
  • that all sounds very vague right now, but I’m sure this will be enhanced to User-Defined Placeholders in the Future (next Version)
Debug
  • can now be fine-grained by setting LEVEL1 – LEVEL9 instead of just YES/NO in the URL
  • complete overhaul with a new Package (APEX_DEBUG instead of APEX_DEBUG_MESSAGE)
  • see below at API for more Info

Changed Views: -

  • APEX_WORKSPACES shows the Consumer Group (Resource Management) and if Restful-Services are allowed
  • APEX_WORKSPACE_USERS finally gives you first/last name and a flag showing if the Account is locked
  • new: APEX_WORKSPACE_UI_TYPES gives you the available Userinterface Types (Desktop or Mobile)
  • APEX_APPLICATIONS got many Interface changes, some columns have been changed and wont return values in special cases anymore (HOME_LINK, PAGE_TEMPLATE, ERROR_PAGE_TEMPLATE,…), others where added (NLS_SORT, NLS_COMP, CONTENT_DELIVERY_NETWORK,…) that all reflects the conceptual enhancements for Applications. ie having multiple UI-Types within an Application. And of course all the little improvements like DEEP_LINKING, HTML_ESCAPING_MODE…
  • new: APEX_APPL_USER_INTERFACE returns all those information that previously was in APEX_APPLICATIONS but isn’t unique within an App-Id anymore when you use multiple User-Interfaces (Desktop and Mobile) in one App: HOME_LINK, PAGE_TEMPLATE, ERROR_PAGE_TEMPLATE,…
  • APEX_APPLICATION_PAGES shows which UI-Type is used and renamed the former “Page Zero” to “Global Page”, because (UI-Types) you can have multiple Global Pages in an Application now (with any number you want). It also shows the new Page Attributes for JAVASCRIPT_FILE_URL, CSS_FILE_URLS, INLINE_CSS and – of course – the new Readonly-Section on Page Level (you can switch all fields on a Page to readonly)
  • APEX_APPLICATION_PAGE_ITEMS benefit from HTML5 Improvements (PLACEHOLDER) and the new Grid-Layout (NEW_GRID, NEW_GRID_ROW, …) which replaces the old BEGINS_ON_NEW_ROW (which is still there)
  • APEX_APPLICATION_PAGE_REGIONS also show the new Grid Settings (NEW_GRID, NEW_GRID_COLUMN, …). And Regions now can be shown Above or Below the Display Point chosen. Regions also now have Readonly Settings and support up to 25 Plugin-Attributes.
  • APEX_APPLICATION_PAGE_PROC now got rid of the Deprecated Process Points “Before/After Showing Page Items”
  • APEX_APPLICATION_PAGE_BRANCHES now support BRANCH_NAME, a convenient enhancement because up to now you couldn’t set a name for a branch. That should make organization of bigger Pages with many branches easier.
  • APEX_APPLICATION_PAGE_BUTTONS -> Grid Layout Support and CSS_CLASS, plus a new Condition Type “Button Readonly” so that you can hide a Button (alternative to disable) when the Region is Readonly
  • APEX_APPLICATION_PAGE_DA now shows a CONDITION_PAGE_ITEM and 2 new Attributes for Event binding (WHEN_EVENT_STATIC_CONTAINER and WHEN_EVENT_CUSTOM_NAME). That means easy use for Custom-Event-Names, not limited to the ones provided by jQuery (or some Plugin) anymore.
  • APEX_APPLICATION_PAGE_DA_ACTS shows AFFECTED_BUTTON and AFFECTED_BUTTON_ID and let you decide if the Action should wait until it is finished (WAIT_FOR_RESULT) before giving control to the next Action.
  • APEX_APPLICATION_ITEMS can now be global or local, identified by SCOPE.
  • APEX_APPLICATION_LISTS don’t return TEMPLATE anymore because Lists don’t have a default Template anymore. The Template used for showing the List is defined in the Region only.
  • APEX_APPLICATION_THEMES provides the UI-Type and some mobile enhancements: DEFAULT_HEADER/FOOTER_TEMPLATE, DEFAULT_PAGE/POPUP_TRANSITION. All the with 4.1 introduced (never used, though) attributes starting with MOBILE_ have been dropped again.
  • new: APEX_APPLICATION_THEME_STYLES identifies the CSS file URLs which should be used for a theme
  • new: APEX_APPL_THEME_DISPLAY_POINTS identify the available display points in page and region templates – for future use
  • APEX_APPLICATION_TEMP_PAGE has some new settings for Javascript and Css File-Urls, Inline Code and onload Code. There there is a bunch new Attributes for Grid Layouts and a flag to globally turn off/on Edit-Links. Column MOBILE has been dropped (was never used).
  • new: APEX_APPL_TEMP_PAGE_DP identify the available display points in a page template
  • APEX_APPLICATION_TEMP_REGION removed RENDER_FORM_ITEMS_IN_TABLE, added LAYOUT and some new DEFAULT_-Attributes (field, label, button, …)
  • new: APEX_APPL_TEMP_REGION_DP identify the available display points in a region template
  • APEX_APPLICATION_TEMP_REPORT added the column-heading-sort-Attributes to the View-Interface.
  • APEX_APPLICATION_TEMP_CALENDAR can now have AGENDA-Templates/-Formats
  • APEX_APPLICATION_TEMPLATES shows “Grid” as new TEMPLATE_TYPE
  • APEX_DICTIONARY reflects the changes to all Dictionary-Views
  • APEX_APPL_PLUGINS lets you decide which UI-Types a Plugin supports and if Plugin Settings are subscribed. All ATTRIBUTE_xx-Columns are marked as obsolete, as they are now available through APEX_APPL_PLUGIN_SETTINGS.
  • APEX_APPL_PLUGIN_ATTRIBUTES got a COLUMN_DATA_TYPES to determine which data-types are supported for Region SQL Statement Column.
  • new: APEX_APPL_PLUGIN_SETTINGS contains now the Attributes of Plugins (internal DM change, moved to own table)
  • APEX_APPLICATION_PAGE_FLASH5 now has a CHART_RENDERING column to decide between Flash and HTML5
  • APEX_APPLICATION_PAGE_FLASH5_S : the Chart-Series is conditional now and also supports Authorization Scheme and Build-Options
  • APEX_APPLICATION_PAGE_IR supports a From-Email-Address and customer-icon-views
  • APEX_APPLICATION_PAGE_IR_COL was enhanced by a HTML_EXPRESSION we already know (and love) from Classic Reports
  • APEX_APPLICATION_PAGE_IR_SUB now remembers the LANGUAGE and EMAIL_FROM
  • APEX_APPL_LOAD_TABLES got a VERSION_COLUMN_NAME and a COLUMN_NAMES_LOV_ID

Changed API’s

That is a list of all the public APIs.
APEX_APPLICATION
  • plenty global variables representing all the new Settings on Application Level (nls-sort, global-page, javascript-file-urls, user-interface, …)
  • c_max_size_vc2 is set to 32767 for DB Version 12c, below it is 4000
  • Procedures show got two new arguments: “j” and “XMLCallDate”
  • Procedure accept also got 100 new item arguments -> the max number Page Items was increased to 200
  • some new internal helper functions, not for public use
APEX_APPLICATION_INSTALL
  • new function PUBLISH_APPLICATION to create a translated version (!Mapping and seeded data must already exist!)
APEX_AUTHENTICATION
  • it finally found its way into the API Reference Documentation !
  • This API is meant for creating your own Authentication Plugins and provides some helpful Functions
new: APEX_DEBUG
  • replaces Package APEX_DEBUG_MESSAGE (which is still available as synonym, but everyone should change that to APEX_DEBUG)
  • all the old Procedures are still there, your code wont break. But you’ll get Messages saying that this function is deprecated
  • The new Interfaces as described in the API Reference Documentation now support different Log-Levels (from 1=Error to 9=Trace) and Placeholders in the Message-Text.
  • You should take a couple minutes and get used to the new possibilities, this is way more powerful now !
new: APEX_ESCAPE
  • new API bringing helper Functions for escaping Strings in various Situations
  • available for LDAP, JS, HTML
  • check the API Reference Documentation for more Info
APEX_ERROR
  • Package was splitted into a public and an internal one, so some functions you shouldn’t have used are now gone
  • new Function GET_INTERNAL_ERROR to get hold of APEX engine error messages
APEX_INSTANCE_ADMIN
  • Documentation got improved
  • new Procedure REMOVE_SUBSCRIPTION to remove a specific IR Subscription
new: APEX_IR
  • Finally a Package decicated to manage Interactive Reports via PL/SQL
  • Use this Package to get an interactive report runtime query, add filters, reset or clear report settings, delete saved reports and manage subscriptions
  • check the API Reference Documentation for more Info
APEX_JAVASCRIPT
  • Procedure ADD_LIBRARY has been enhanced
  • can now set Internet-Explorer Conditions
  • use Procedure ADD_3RD_PARTY_LIBRARY_FILE to add jQuery or jQuery-UI from Directory or CDN
APEX_LDAP
  • supports nested-members now
  • support for “uid=xxx” RDNs
APEX_PAGE
  • (yet) Undocumented, but public, API
  • Functions to get Infos about the current Page (IS_PAGE_READONLY, IS_DESKTOP_UI, IS_JQM_SMARTPHONE_UI, IS_JQM_TABLET_UI)
  • Procedure to Purge the Page Cache: PURGE_CACHE
APEX_PLUGIN
  • Region Plugins support up to 25 Attributes now -> Type t_region extended
APEX_PLUGIN_UTIL
  • new Function GET_ELEMENT_ATTRIBUTES used to get a consistent HTML-Input/Select/Textarea Element
  • additional Interface for PRINT_DISPLAY_ONLY which accepts the t_page_item record as input
  • new Function GET_DATA_TYPE to map p_sql_handler.column_list(x).col_type to one of the APEX_PLUGIN_UTIL Constants
  • new Function GET_COLUMN_NO that returns the index of the specified column alias in p_sql_handler.column_list
  • additional Interface for GET_DATA and GET_DATA2 which accepts a SEARCH_COLUMN_NAME (instead of the _NO), but watch out for parameters which aren’t defaulted anymore. You might need to update your Plugin Code.
  • GET_DISPLAY_DATA, GET_SQL_HANDLER and PREPARE_QUERY can now do auto-binding of a given bind-list
APEX_REGION
  • (yet) Undocumented, but public, API
  • Function IS_READONLY returns true/false
  • Use Procedure PURGE_CACHE to purge the cache for a certain region
APEX_UTIL
  • CREATE_USER can now set a default-date-format and set some privileges for that user (allow app building, sql workshop, websheet development, …)
  • IR_FILTER, IR_RESET and such have been deprecated, use the new Package APEX_IR instead
  • new function GET_BUILD_OPTION_STATUS
Javascript API
  • Most important change here is the slight movement of functions to Legacy status
  • Your Application has a switch where you can decide whether Legacy-JS should be supported or not
  • Depending on that switch the legacy.js is included
  • Check the API Reference Documentation for a list of the Deprecated JS Methods

Wednesday 17 October 2012

Importing Export Files in APEX

               Importing Export Files

Once you export an application and any related files, you need to import them into the target Oracle Application Express instance before you can install them. Note that you cannot import an application created in a specific release into an earlier version. As a general rule, always import the application first and then the related files. See "How to Move an Application to Another Development Instance".
Tip:
You can simplify the steps needed to deploy an application by creating a packaged application. See "How to Create a Packaged Application".
Topics:

Importing an Application, Page or Component Export

To import an Application, Page or Component Export into a target Oracle Application Express instance:
  1. Navigate to the Import page:
    1. On the Workspace home page, click the Application Builder icon.
    2. On the Application Builder home page, click Import.
  2. For Specify File, specify the following:
    1. Import file - Click Browse and navigate to the file.
    2. File Type - Select Application, Page, or Component Export.
    3. Verify that File Character Set is correct.
    4. Click Next.
    Once you import a file, you have the option to install it.
  3. To install an imported file, click Next.
    The Install Application wizard appears.
  4. In the Install Application wizard, specify the following:
    1. Parsing Schema - Select a schema.
      This is the schema against which all of the application's SQL and PL/SQL will be parsed.
    2. Build Status - Select one of the following:
      • Run Application Only - Users can only run an application.
      • Run and Build Application - Users can run an application and developers can both run and edit an application.
      Selecting Run Application Only is an effective way to protect an application from modifications from other developers.
      Tip:
      If you select Run Application Only, the only way to change this setting after you import the application is to log in to Oracle Application Express Administration Services. See "Changing Application Build Status Set During Deployment" in Oracle Application Express Administration Guide.
    3. Install As Application - Select one of the following:
      • Auto Assign New Application ID
      • Reuse Application ID From Export File
      • Change Application ID
      Use these options to avoid application ID conflicts. These options come in handy when you have two versions of the same application in the same instance. For example, you might be migrating an application to a production instance and still need to maintain the development version.
    4. Click Install.
      If you are installing a packaged application (that is, one for which you have defined Supporting Objects), the installer prompts you to install the packaged installation scripts. Follow the on-screen instructions.

About Importing Application Groups

Application groups are exported with an application, application exports include an group ID, name, and comments. When importing an application, the application import follows these rules:
  • On import, an application uses an existing group if the ID matches.
  • If no application group exists with the same ID on the target machine, then application import looks for another group ID with the same name. If a name match is found, the application is imported with the ID corresponding to this application group name.
  • If no ID or name matches existing application groups on the target machine then a new application group is created. The new application group will use the application group ID if that ID is not used in that Oracle Application Express instance. If the group ID is being used, then a new ID will be generated.

Importing a Websheet

To import a Websheet into a target Oracle Application Express instance:
  1. Navigate to the Import page:
    1. On the Workspace home page, click the Application Builder icon.
    2. On the Application Builder home page, click Import.
  2. For Specify File, specify the following:
    1. Import file - Click Browse and navigate to the file.
    2. File Type - Select Websheet Application Export.
    3. Click Next.
    Once you import a file, you have the option to install it.
  3. To install an imported file, click Next.
    The Install Application wizard appears.
  4. In the Install Application wizard, specify the following:
    1. Install As Application - Select one of the following:
      • Auto Assign New Application ID
      • Reuse Application ID From Export File
      • Change Application ID
      Use these options to avoid application ID conflicts. These options come in handy when you have two versions of the same application in the same instance. For example, you might be migrating an application to a production instance and still need to maintain the development version.
    2. Click Install.

Importing Plug-ins

To import an exported plug-in file:
  1. Navigate to the Import page:
    1. On the Workspace home page, click the Application Builder icon.
    2. On the Application Builder home page, click Import.
  2. For Specify File, specify the following:
    1. Import file - Click Browse and navigate to the file.
    2. File Type - Select Plug-in.
    3. Verify that File Character Set is correct.
    4. Click Next.
    Once you import a file, you have the option to install it.
  3. To install an imported file, click Next.
  4. Click Install Plug-in.
Note:
If the plug-in with the same name already exists, a dialog displays requesting permission to write over the existing plug-in.

Importing Cascading Style Sheets

After you import an application into the target Oracle Application Express instance, you must import all related files.
To import a CSS Export file:
  1. Navigate to the Import page:
    1. On the Workspace home page, click the Application Builder icon.
    2. On the Application Builder home page, click Import.
  2. For Specify File, select the following:
    1. Import file - Click Browse and navigate to the file.
    2. File Type - Select CSS Export.
    3. File Character Set - Verify that File Character Set is correct.
    4. Click Next.
    Once you import a file, you have the option to install it.
  3. To install an imported file, click Next.
  4. Click Install CSS.

Importing Images

After you import an application into the target Oracle Application Express instance, you must import all related files.
To import an Image Export file:
  1. Navigate to the Import page:
    1. On the Workspace home page, click the Application Builder icon.
    2. On the Application Builder home page, click Import.
  2. On Import Definition, select the following:
    1. Import file - Click Browse and navigate to the file.
    2. File Type - Select Image Export.
    3. File Character Set - Verify that File Character Set is correct.
    4. Click Next.
    Once you import a file, you have the option to install it.
  3. To install an imported file, click Next.
  4. Click Install Image.

Importing Static Files

After you import an application into the target Oracle Application Express instance, you must import all related files.
To import a static file:
  1. Navigate to the Import page:
    1. On the Workspace home page, click the Application Builder icon.
    2. On the Application Builder home page, click Import.
  2. For Specify File, select the following:
    1. Import file - Click Browse and navigate to the file.
    2. File Type - Select File Export.
    3. File Character Set - Verify that File Character Set is correct.
    4. Click Next.
    Once you import a file, you have the option to install it.
  3. To install an imported file, click Next.
  4. Click Install Static Files.

Importing Themes

After you import an application into the target Oracle Application Express instance, you must import all related files.
To import a Theme Export file:
  1. Navigate to the Import page:
    1. On the Workspace home page, click the Application Builder icon.
    2. On the Application Builder home page, click Import.
  2. On Import Definition, select the following:
    1. Import file - Click Browse and navigate to the file.
    2. File Type - Select Theme Export.
    3. File Character Set - Verify that File Character Set is correct.
    4. Click Next.
    Once you import a file, you have the option to install it.
  3. To install an imported file, click Next.
  4. Click Install Theme.

Importing User Interface Defaults

User Interface Defaults enables you to assign default user interface properties to a table, column, or view within a specified schema.
After you import an application into the target Oracle Application Express instance, you must import all related files.
To import User Interface Defaults:
  1. Navigate to the Import page:
    1. On the Workspace home page, click the Application Builder icon.
    2. On the Application Builder home page, click Import.
  2. Select an application.
  3. On Import Definition, select the following:
    1. Import file - Click Browse and navigate to the file.
    2. File Type - Select User Interface Defaults.
    3. File Character Set - Verify that File Character Set is correct.
    4. Click Next.
    Once you import a file, you have the option to install it.
  4. To install an imported file, click Next.
  5. Click Install User Interface Defaults.
See Also:
"Managing User Interface Defaults" in Oracle Application Express SQL Workshop Guide and "Exporting User Interface Defaults"

Importing Feedback

To import Feedback:
  1. Navigate to the Import page:
    1. On the Workspace home page, click the Application Builder icon.
    2. On the Application Builder home page, click Import.
  2. Select Feedback.
  3. For File Character Set, verify that File Character Set is correct and click Next.
    Once you import a file, you have the option to install it.
  4. To install an imported file, click Next.
  5. Click Install Feedback.

Tuesday 16 October 2012

SQL Commands in APEX

Using SQL Commands

This section provides information on how to use SQL Commands to create, edit, view, run, and delete SQL commands.
This section contains the following topics:
See Also:

What is SQL Commands?

You can use SQL Commands to create, edit, view, run, and delete SQL commands. A SQL command can contain SQL statements or PL/SQL blocks.
When using SQL Commands, remember the following:
  • SQL commands created in the Query Builder can be accessed in SQL Commands.
  • Saved SQL commands must have names unique within a given workspace.
  • There is no interaction between SQL Commands and SQL Scripts.
  • You can cut and paste a SQL command from SQL Commands to run in the SQL Script Editor.

Accessing SQL Commands

To access SQL Commands:
  1. Log in to the Workspace home page.
    The Workspace home page appears.
  2. To view the SQL Commands home page you can either:
    • Click SQL Workshop and then SQL Commands to drill-down to the SQL Commands home page.
    • Click the down arrow on the right side of the SQL Workshop icon to view a drop down menu. Then select the SQL Commands menu option.
    Description of sql_commands.gif follows
    Description of the illustration sql_commands.gif

    Note:
    For the purposes of consistency, this document uses the primary navigation path (or drill-down approach) when explaining navigation.

About the SQL Commands Home Page

The SQL Commands home page is divided into two sections: a command editor and a display pane. You use the command editor to execute SQL commands and the display pane to view output, saved command lists, and history lists.
Description of sql_com_top.gif follows
Description of the illustration sql_com_top.gif

The top of the SQL Commands home page features a command editor and the following controls:
  • Autocommit. If available, click the Autocommit check box to enable autocommit and disable transactional commands. See "About Transactions in SQL Commands".
  • Display. Make a selection from the Display list to specify the number of rows of output to display at one time up to a maximum of 100,000. All rows of DBMS Output are displayed regardless of the Display list setting.
  • Clear Command icon. The Clear Command icon resembles a pencil with an eraser. Use this icon to clear the text in the command editor.
  • Find Tables icon. The Find Tables icon resembles a flashlight. Click this icon to view tables and views. See "Using the Find Tables Icon".
  • Save. Click the Save button to save the contents of the command editor, or the currently highlighted content to a file. You are prompted to enter a name and an optional description. The new command appears in the Saved SQL list. See "Saving a SQL Command".
  • Run. Click the Run button (or press Ctrl+Enter) to run the command in the command editor, or the currently highlighted command in the command editor. See "Running a SQL Command".
Selecting a Schema
A schema is a logical container for database objects. To access objects in another schema, make a selection from the Schema list in the upper right side of the page.
Switching to Another SQL Workshop Component
You can navigate to another SQL Workshop component by selecting one of the following from the Component list located on the upper right side of the page:
About the Display Pane
A display pane displays at the bottom of the SQL Commands home page.
Description of sql_com_bottom.gif follows
Description of the illustration sql_com_bottom.gif

The display pane features five tabs:
  • Results. Click the Results tab to see the results from the last successfully executed SQL command. Click DBMS Output at the bottom of the displayed results to display lines of DBMS output. This control only appears when there is DBMS output to display. Click Download to export results to a comma-separated file on your local file system. See "Viewing Results".
  • Explain. Click the Explain tab to examine the execution plan used by the optimizer for statements that make changes to the database. Objects in the output are linked to the Object Browser. Click the linked object to view its properties in the Object Browser. See "Using Explain Plan".
  • Describe. Enter Describe object_name and click Run to display column definitions for a table or view, or specifications for a function or procedure in the Describe tab. Select links in the Describe results to write that information into the command editor. For example, click a table name to add owner.table, click a column name to add the column name, click a procedure or function name to add the object call with parameters, or click a package name to add the package call.
  • Saved SQL. Click the Saved SQL tab to display a list of all SQL commands saved in the current workspace. Click the command title to load it into the command editor. See "Using Saved Commands"
  • History. Click the History tab to list your recently executed commands. Your last 200 executed commands are saved. See "Using SQL Command History".

Using the Command Editor

You use the command editor in SQL Commands to execute SQL commands within Application Express.
Topics in this section include:

Running a SQL Command

To execute a SQL Command:
  1. On the Workspace home page, click SQL Workshop and then SQL Commands.
    The SQL Commands page appears.
  2. Enter the SQL command you want to run in the command editor.
  3. Click Run (Ctrl+Enter) to execute the command.
    Tip:
    To execute a specific statement, select the statement you wish to run and click Run.
    The results appear in the Results pane.
  4. To export the resulting report as a comma-delimited file (.csv) file, click the Download link.

About Transactions in SQL Commands

To disable transactional SQL commands in SQL Commands, check the Autocommit check box. Attempting to use any transactional SQL commands such as COMMIT or ROLLBACK when transactional mode is disabled returns an error message.
To enable transactional SQL commands, clear the Autocommit check box. Oracle Application Express verifies that the necessary system resources are available before entering the transactional mode. If resources are unavailable, an error message is displayed.
Transactional mode is a stateful transaction mode where you can, for example, perform an update, select data for review, and COMMIT or ROLLBACK changes. It is implemented using DBMS_JOBS.
Consider the following behavior in transactional mode:
  • Actions are not committed to the database until you enter an explicit COMMIT command.
  • Exiting SQL Commands terminates and rolls back the current transaction.
  • A session timeout terminates and rolls back the current transaction.
    Note that the Environment Setting, SQL Commands Maximum Inactivity in minutes, sets the time before an inactive session times out. The default timeout is 60 minutes. See "Configuring SQL Workshop".
  • The CSV Export option is not available.

About Unsupported SQL*Plus Commands

SQL Commands does not support SQL*Plus commands. If you attempt to enter a SQL Command Line command such as SET ECHO or DEFINE in SQL Commands, an error message displays.

About Command Termination

You can terminate a command in SQL Commands using a semicolon (;), a forward slash (/), or with nothing. Consider the following valid alternatives:
SELECT * from emp;

SELECT * from emp
/

SELECT * from emp

The first example demonstrates the use of a semicolon (;), the second example demonstrates the use of forward slash (/), and the final example demonstrates a command with no termination.

Using Bind Variables

Bind variables are supported. You are prompted to enter values for bind variables during command execution. Bind variables are prefixed with a colon.
For example
SELECT * from emp where deptno = :dept

In earlier versions of Oracle Application Express, you could check your Workspace ID by running the command:
SELECT :WORKSPACE_ID FROM dual

In this release, run the following SQL command to check your Workspace ID:
SELECT v('WORKSPACE_ID') FROM dual

Saving a SQL Command

You can save commands you enter in SQL Commands.
To save a SQL command:
  1. On the Workspace home page, click SQL Workshop and then SQL Commands.
    The SQL Commands page appears.
  2. Enter the command in the command editor.
  3. Click Save to save the command.
    You are prompted to enter a name and description for the command.
  4. Click Save, or click Cancel to return to the command editor without saving.
    The saved command is listed in the display area.

Copying a Command

To copy a SQL command:
  1. On the Workspace home page, click SQL Workshop and then SQL Commands.
    The SQL Commands page appears.
  2. Click the Saved SQL tab.
    The Saved SQL list of commands appears in the display pane.
  3. Click the title of the command to load it into the command editor
  4. Click Save to save the command.
  5. Enter a new name for the command in the Name field and click Save.
    The command is copied to the new name.

Using Saved Commands

You can access the commands you save and commands saved by other users in the same workspace. You can also access SQL commands you and other users of the same workspace saved from the Query Builder.
Topics in this section include:

Accessing Saved Commands

To access saved SQL commands:
  1. On the Workspace home page, click SQL Workshop and then SQL Commands.
    The SQL Commands page appears.
  2. Click the Saved SQL tab.
    The Saved SQL list of commands appears in the display pane.
  3. Click the title of the command to load it into the command editor.
    The command appears in the editor.
  4. Click Run to execute the command.

About the Saved SQL Pane

The Saved SQL pane displays a list of all commands saved under the current workspace. The list displays commands saved from SQL Commands and SQL commands saved from Query Builder. Saved SQL commands must have unique names in the current workspace. The same name cannot be used in the Query Builder and SQL Commands.
Each command entry shows the owner name, the command name, the first characters of the SQL command, a description if it exists, who last updated the command and when.
Description of proc_savedsql.gif follows
Description of the illustration proc_savedsql.gif

On the Saved SQL pane you can:
  • Show commands by owner. Make a selection from the Owner list to specify the user whose commands you want to display. To view all scripts select -All Users-.
  • Search for a command. Enter a command name or partial name, or enter a code snippet in the Find field and click Go. To view all scripts, leave the Find field blank and click Go. You control how many rows display by making a selection from the Rows list.
  • Set the Number of Output Rows. Make a selection from the Display list to specify the number of Saved SQL commands to display at one time.
  • Delete a command. Click the check box associated with each command you want to delete, and click Delete Checked.
  • Sort commands. Click a column heading to sort the listed commands by that column.

Using SQL Command History

Commands you have executed are stored in the command history regardless of whether you explicitly save them. You use SQL Command History to access commands you have executed in SQL Commands.
Topics in this section include:

Accessing a Command from Command History

To access history commands:
  1. On the Workspace home page, click SQL Workshop and then SQL Commands.
    The SQL Commands page appears.
  2. Click the History tab.
    The list of commands in History appears in the display pane.
  3. Click the partial command displayed in the SQL column.
    The command appears in the editor.

About the History Pane

The History pane displays a list of commands you have executed.
Description of proc_history.gif follows
Description of the illustration proc_history.gif

Each history entry shows the time the command was last executed, the first characters of the command, and the schema in which it was executed.
On the History pane you can:
  • Load a command. Click the partial command displayed in the SQL column to load the command into the command editor. When the command loads, it also sets the schema in which it was last executed.
  • Sort by time. Click the Time column heading to sort the command history by least recent or most recent.

Viewing Results

When you execute a SQL command, the results are displayed. The results of the last executed command are available until you execute another SQL command, or leave SQL Commands.
Topics in this section include:

Accessing the Results Pane

To display SQL command results:
  1. On the Workspace home page, click SQL Workshop and then SQL Commands.
    The SQL Commands page appears.
  2. Click the Results tab.
    Description of proc_results.gif follows
    Description of the illustration proc_results.gif

    The HTML formatted results appear in the display pane.
  3. Click DBMS Output to display plain text DBMS output results.
    The DBMS Output control only appears if there is DBMS output in addition to HTML formatted results. It does not appear if there is only DBMS output, or if there is only HTML formatted output.

About the Results Pane

The Results pane displays SQL command results as HTML formatted table. The number of rows returned appears at the end of the output, and the time taken. DBMS output appears as plain text after the HTML formatted results.
On the Results pane you can:
  • Display DBMS output. Click DBMS Output at the bottom of the displayed results to display lines of DBMS output. This control only appears when there is DBMS output to display.
  • Export results. Click CSV Export to export results to a comma-separated file on your local file system. You are prompted to enter a name and directory for the file.

Using Explain Plan

You can view the explain plan the Oracle Optimizer uses to run your SQL command. You do not need to execute the command to view the explain plan.
Description of explaintab.gif follows
Description of the illustration explaintab.gif

Topics in this section include:

Viewing an Explain Plan

To view the Explain Plan:
  1. On the Workspace home page, click SQL Workshop and then SQL Commands.
    The SQL Commands page appears.
  2. Enter or load the command whose plan you want to view.
  3. Click the Explain tab.
    The explain plan used by the optimizer appears in the display pane.

About Explain Plan Pane

The Explain Plan pane shows the plan used by the Oracle Optimizer to run your SQL command. It typically displays the Query Plan, Index Columns and Table Columns used.
On the Explain Plan pane you can:
  • View object definitions. Click the object name in Query Plan to display the object definition in the Object Browser.
  • View index definitions. Click the index name in Table Columns to display the index definition in the Object Browser.

Using SQL Scripts in APEX

Using SQL Scripts

This section provides information on how to use SQL Scripts to create, edit, view, run, and delete script files.
This section contains the following topics:

What is SQL Scripts?

A SQL script is a set of SQL commands saved as a file in SQL Scripts. A SQL script can contain one or more SQL statements or PL/SQL blocks. You can use SQL Scripts to create, edit, view, run, and delete script files.
When using SQL Scripts, remember the following:
  • SQL*Plus commands in a SQL script are ignored at run time.
  • There is no interaction between SQL Commands and SQL Scripts.
  • You can cut and paste a SQL command from the SQL Script editor to run it in SQL Commands.
  • SQL Scripts does not support bind variables.

Accessing SQL Scripts

To access SQL Scripts:
  1. Log in to the Workspace home page.
  2. To view SQL Scripts page you can either:
    • Click the SQL Workshop icon and then SQL Scripts to drill-down to the SQL Scripts page.
    • Click the down arrow on the right side of the SQL Workshop icon to view a drop down menu. Then select the SQL Scripts menu option.
    Description of scripts.gif follows
    Description of the illustration scripts.gif

    Note:
    For the purposes of consistency, this document uses the primary navigation path (or drill-down approach) when explaining navigation.

About the SQL Scripts Page

The SQL Scripts page display all SQL scripts created by the current user. You can control the appearance of the page by making a selection from the View list. The default view, Icons, displays each script as an icon. Details view displays each script as a row in a report.
Description of script_home.gif follows
Description of the illustration script_home.gif

The SQL Scripts page features the following controls:
  • Script. Search for a script by entering the script name, or a partial name, in the Script field and clicking Go. You control how many rows display by making a selection from the Display list.
  • Owner. Search for the owner of the script you want to view by entering the user name in the Owner field and clicking Go.
  • View. Change the appearance of the SQL Scripts page by making a selection from the View list and clicking Go. Available View options include:
    • Icons (default) displays each script as an icon identified by the script name. Click the Show Results check box to additionally display run results as icons identified by the script name.
    • Details displays each script as a line in a report. Each line includes a check box to enable the selection of scripts for deletion, an edit icon to enable the script to be loaded into the script editor, the script name, the script owner, when the script was last updated and by who, the size in bytes, the number of times the script has been run linked to the run results, and an icon to enable the script to be run.
      Details view offers the following additional controls:
      • Delete Checked. In Details view, select the check box associated with the script you want to delete and click Delete Checked. See "Deleting a SQL Script".
      • Sort. In Details view, click a column heading to sort the listed scripts by that column.
  • Upload. Click Upload to upload a script from your local file system into SQL Scripts. See "Creating a SQL Script".
  • Create. Click Create to create a new script in the Script Editor. See "Creating a SQL Script".
Switching to Another SQL Workshop Component
You can navigate to another SQL Workshop component by selecting one of the following from the Component list located on the upper right side of the page:
About the Tasks List
A Tasks list displays on the right side of the SQL Scripts page.
Description of script_tasks.gif follows
Description of the illustration script_tasks.gif

The Task list contains the following links:
  • Manage Results enables you to view, search, and display results. See "Viewing SQL Script Results".
  • Show Quotas displays the Script Quotas page. The Script Quotas page shows the maximum size of a single result, the maximum size of all results, the quota used and the quota free. It also shows the maximum size of a SQL Script.
  • Export enables you to export multiple scripts from the current SQL Script Repository for import into SQL Scripts in a different workspace. The scripts you select to export are encoded in a single export script written to your local file system. The export script is named workspace_name_script.sql by default. See "Exporting and Importing SQL Scripts".
  • Import enables you to import a script exported by this, or a different workspace. Import only imports scripts encoded in an export script created using Export. The export script to import must be accessible on your local file system. See "Exporting and Importing SQL Scripts".

Creating a SQL Script

You can create a new script in the Script Repository by:
  • Creating a new script in the Script Editor
  • Uploading a script from your local file system
Topics in this section include:

Creating a SQL Script in the Script Editor

To create a new SQL script in the Script Editor:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. Click the Create button.
    The Script Editor appears.
  3. Enter a name for the script in the Script Name field.
    Script name extensions are optional.
  4. Enter the SQL statements, PL/SQL blocks and SQL*Plus commands you want to include in your script.
    Remember that SQL Command Line commands are ignored at run time.
  5. Click Save to save your script to the repository.
    The SQL Scripts page appears listing your newly saved script.

Uploading a SQL Script

To upload a script from your local file system:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. Click the Upload button.
    The Upload Script dialog appears.
  3. To upload a script you can either:
    • Enter the name and path to the script you want to upload to the Script Repository.
    • Click Browse to locate the script you want to upload.
  4. Optionally rename the script by entering the new name in the Script Name field.
    This is the name given to the script in the Script Repository.
  5. Click Upload to add the script to the Script Repository.
    The SQL Scripts page appears listing your newly uploaded script.
    The script is parsed during upload. If it has a syntax error, an error icon appears in place of the run icon in the SQL Scripts page Details view.
    If a script of the same name exists in the Script Repository, you are prompted to rename it.

Using the Script Editor

You use the Script Editor to add content to a new script, to edit existing scripts, and to run and delete scripts in the script repository.
Topics in this section include:

Editing an Existing Script

To edit a SQL script:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. You can load a script into the editor as follows:
    • In Icons view, click the script icon.
    • In Details view, click the Edit icon.
    The Script Editor appears.
    Description of script_editor.gif follows
    Description of the illustration script_editor.gif

  3. Edit the script.
    Note that new lines are automatically indented to the previous line start column. Other features of the Script Editor include:
    • Search and Replace. Click Find to display the text and JavaScript regular expression find and replace options. Click Find again to hide the options. See "Searching and Replacing Text or Regular Expressions".
    • Line Selection. Click the line number on the left side of the Script Editor to select the associated line of your script for copying or deleting.
    • Cut, Copy, and Paste. Use standard edit controls to cut, copy and paste content in the Script Editor.
    • Auto indenting lines. New lines automatically indent to the previous line start column.
    You can test your script during editing by running the script to reveal errors. The Run Script dialog and the Script Results pages enable you to resume editing the script. See "Executing a SQL Script", and "Viewing SQL Script Results".
  4. Click Save to save your script to the Script Repository,
    The SQL Scripts page appears.

Searching and Replacing Text or Regular Expressions

Clicking the Find button in the Script Editor displays the Find and Replace with fields at the top of the page. Use these fields to search for and replace text strings and JavaScript regular expressions within a script. To exit Find mode, click Find again.
Description of script_find.gif follows
Description of the illustration script_find.gif

To access Find mode in the Script Editor:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. Select a script.
    The Script Editor appears.
  3. Click the Find button.
    The Find and Replace fields appear.
  4. In the Find field, enter the string you wish to find. In Replace with, enter the new string to be added and then click the appropriate button (Replace, Find Next, or Replace All.)
    To further refine your search, select the appropriate check box:
    • Match Case
    • Match Whole Words
    • Match Regular Expression
  5. To exit Find mode, click Find.

Summary of Script Editor Controls

Table 18-1describes the buttons and controls available within the Script Editor
Table 18-1 Buttons and Controls within the Script Editor
Button Descriptions
Cancel
Cancel the editing session and exit the Script Editor without saving changes made since the last save.
Download
Saves a copy of the current script to your local file system. Enter a name for the script on your local file system and a directory path.
Delete
Removes the current script from the Script Repository.
See Also: "Deleting a SQL Script"
Save
Save your changes to the current script to the Script Repository.
Run
Submits the script for execution.
See Also: "Executing a SQL Script"
Undo (Ctrl+Z)
Removes, or undoes, the most recent line edit made in the Script Editor.
Redo (Ctrl+Y)
Repeats the most recent line edit made in the Script Editor.
Find
Click Find to access search and replace mode. Click Find again to exit Find mode.
See Also: "Searching and Replacing Text or Regular Expressions"

Deleting a SQL Script

You can delete scripts from the Script Repository by deleting selected scripts from the SQL Scripts page, or deleting the current script in the Script Editor.
Topics in this section include:

Deleting Scripts from the SQL Scripts Page

To delete scripts from the SQL Scripts page.
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. From the View list, select Details and click Go.
    Details view appears.
  3. Select the scripts to be deleted.
    To select individual scripts, click the check box to the left of the Edit icon. To select all scripts visible in the current page, click the check box in the column heading.
  4. Click Delete Checked to permanently remove the selected scripts from the Script Repository. You are prompted to confirm this action before the script is deleted.
    The message "Script(s) deleted" appears above the updated list of Scripts.

Deleting a Script in the Script Editor

To delete a script in the Script Editor:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. Open the script you want to delete in the Script Editor.
  3. Click Delete to permanently remove the script from the Script Repository. You are prompted to confirm this action before the script is deleted.
    The SQL Scripts page appears. The message "Script(s) deleted" appears above the updated list of scripts.

Copying a SQL Script

You can copy a script in the Script Repository by saving it with a new name.
To copy a script:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. Load the script to copy into the editor.
  3. Enter a name for the copied script in the Script Name field.
  4. Click Save to save a copy of the script in the Script Repository.
    The SQL Scripts page appears listing the newly copied script.

Executing a SQL Script

You can execute scripts stored in the Script Repository. You can submit a script for execution either from the Script Editor, or from the SQL Scripts page.
When you submit a script for execution, the Run Script page appears. It displays the script name, when it was created and by who, when it was last updated and by who, the number of statements it contains, and its size in bytes. It also lists unknown statements such as SQL*Plus commands that it will ignore during execution.
Finally, it lists statements with errors. If there are errors, the Run control does not appear.
Topics in this section include:

Executing a SQL Script in the Script Editor

To execute a script in the Script Editor:
  1. Open the script you want to execute in the Script Editor. See "Using the Script Editor".
  2. Click Run in the Script Editor.
  3. The Run Script page appears.
    The Run Script page displays information about the script and lists statements in error preventing execution, or statements such as SQL*Plus commands that will be ignored when the script is executed.
    The Run Script page has three controls:
    • Cancel returns you to the SQL Scripts page without executing the script.
    • Edit Script loads the script into the Script Editor. Note that Edit Script appears instead of Run when a script has errors.
    • Run to submit the script for execution. Note that Run is not available if there are script errors.
  4. Click Run to submit the script for execution.
    The Manage Script Results page appears listing script results.
  5. To view script results, click the View icon under View Results.

Executing a SQL Script from the SQL Scripts Page

To execute a script from the SQL Scripts page:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. From the View list, select Details and click Go.
    Details view appears.
  3. Click the Run icon for the script you want to execute. The Run icon is located on the far right side adjacent to the script name.
  4. The Run Script page appears.
    The Run Script page displays information about the script and lists statements in error preventing execution, or statements such as SQL*Plus commands that will be ignored when the script is executed. The Run Script page has three controls:
    Cancel to return to the SQL Scripts page without executing the script.
    Edit Script to load the script into the Script Editor. Edit Script appears instead of Run when a script has errors.
    Run to submit the script for execution. Run is not available for scripts with errors.
  5. Click Run to submit the script for execution.
    The Manage Script Results page appears listing available results for the script.
  6. Click the View icon for the results you want to view. The View icon is at the right end of the scripts listed in the Manage Script Results page.

About the Run Script Page

On the Run Script page, you can:
  • Cancel the execution. Click Cancel to exit the Run Script page without executing the script. The SQL Scripts page appears.
  • Edit the script. Edit Script appears instead of Run when a script has errors. Click Edit Script to load the script into the Script Editor to remove the lines with errors.
  • Execute the script. Click Run to execute the script.

Viewing SQL Script Results

You use the Manage Script Results page to view and delete script results.
You can also select script results to view from the Icons view of the SQL Scripts page, and from the Results column of the SQL Scripts page Details view.
Topics in this section include:

Viewing Results from the SQL Scripts Page

To view script results from the SQL Scripts page:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. You can access the Manage Script Results page in the following ways:
    • On the Tasks list, click Manage Results.
    • In Details view, click the Results number for the script you want to display.
    • In Icons view, click the Show Results check box and then the appropriate icon. Results icons only appear in the Icons view if you click the Show Results check box.
    The Manage Script Results page appears, listing available results for the script. See "About the Manage Script Results Page".
    Description of script_manage_results.gif follows
    Description of the illustration script_manage_results.gif

  3. Click the View icon for the results you want to view. The View icons display on the far right side of page under the heading View Results.
    The Results page appears. See "About the Results Page".
About the Manage Script Results Page
On the Manage Script Results page you can:
  • Search for a result. Enter a result name or partial name in the Script field and click Go. To view all results, leave the Script field blank and click Go. You control how many rows display by making a selection from the Display list.
  • Change the Page View. You can change the appearance of the page by making a selection from the View list. Available View options include:
    • Icons displays each result as an icon identified by the script name, and time and date stamp.
    • Details displays each result as a line in a report. Each line includes a check box to enable the selection of results for deletion, the associated script name which is a link enabling it to be loaded into the Script Editor, who ran the script, when the run started, how long it took to run, whether the run is complete or not, the number of statements executed, the size in bytes, and a View icon to view the results.
  • Delete a result. In Details view, select the check box associated with each result you want to delete, and click Delete Checked.
  • Sort results. In Details view, click a column heading to sort the listed results by that column.

About the Results Page

The Results page displays the script name and status (Complete, Canceled, Executing or Submitted), and lists the statements executed.
Description of script_results.gif follows
Description of the illustration script_results.gif

On the Results page you can:
  • Choose the view. Click the Detail or Summary radio button and click Go to specify whether to display complete or summarized information.
  • Choose the number of rows to display. In Summary view, make a selection from the Display list and click Go to specify the number of rows displayed.
  • Sort the statement report. In Summary view, select a column heading to sort the listed values by that column.
  • Edit the script. Click Edit Script to load the script into the Script Editor. See "Using the Script Editor".

Exporting and Importing SQL Scripts

You can transfer scripts from your current Script Repository to a Script Repository in a different workspace by using the Export and Import tasks. Exported scripts are encoded in a single file on your local file system. Once exported, you then log in to another workspace and import the file. During import, the file is run to re-create the scripts in the current Script Repository.
By default, the Export SQL Scripts page lists all scripts in the Script Repository created by the current user. There are two panes on the Export SQL Scripts page, the Scripts pane and the Scripts to Export pane. You use the Scripts pane to select scripts to export. Then, you use the Scripts to Export pane to finalize the scripts to export, to choose a name for the export script, and to save the selected scripts in the export script on your local file system. You use the Import Scripts pane to select the export script containing the scripts to import.
Topics in this section include:

Copying Scripts to an Export Script

To copy scripts to an export script:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. On the Tasks list, click Export.
    The Export SQL Scripts page appears.
  3. Click the check box for each of the scripts you want to export. The check boxes display on the left side adjacent to the script name. To select all displayed scripts for export, click the column head check box.
  4. Click Add to Export to create a list of scripts to be added to the export script.
    The selected scripts are added to the list of scripts in the Scripts to Export pane.
  5. Enter a name for the export script in the File Name field.
    The default script name is workspace_name_script.sql.
  6. Click Export All to export the scripts to the export script.
    You are prompted to enter the directory where you want to save the export script.

About the Scripts Pane

Description of script_pane.gif follows
Description of the illustration script_pane.gif

In the Scripts pane you can:
  • Search for a script. Enter a script name or partial name in the Find field and click Go. To view all scripts, leave the Find field blank, select - All Users - from the Owner list and click Go. You control how many rows display by making a selection from the Display list.
  • Cancel the export. Click Cancel to return to the SQL Scripts page without exporting any scripts, or to return to the SQL Scripts page after saving an export script.
  • Selecting scripts to export. Click Add to Export to add scripts to the export script. Scripts added to the export script are no longer listed in the Script pane, but appear in the Scripts to Export pane.
  • Sort scripts. Click a column heading to sort the listed scripts by that column.

About the Scripts to Export Pane

Description of script_export.gif follows
Description of the illustration script_export.gif

In the Scripts to Export pane you can:
  • Rename the export script. Enter a name for the export script in the File Name field or leave the default script name.
  • Remove scripts. Click the check box adjacent to the scripts you want to remove f and click Remove Checked. Scripts removed are no longer listed in the Scripts to Export pane, but appear in the Scripts pane.
  • Save the export script. Click Export All to save the export script to your local file system. You are prompted to enter the directory where you want to save the export script.

Importing Scripts from an Export Script

To import scripts from an export script:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. On the Tasks list, click Import.
    The Import Scripts pane appears. See "About the Import Scripts Pane".
  3. Enter the name and path to the export script you want to import to the Script Repository, or click Browse to locate the export script you want to import.
  4. Click Next to list the scripts in the export script.
    The Action column indicates whether the imported script is new, or whether it will replace an existing script of the same name.
  5. Click Import Script(s) to import the listed scripts into the current Script Repository.
    The SQL Scripts page appears listing the imported scripts.

About the Import Scripts Pane

Description of script_import.gif follows
Description of the illustration script_import.gif

In the Import Scripts pane you can:
  • Enter the export script. Enter the name and path of the script to import in the Import file field, or click Browse to locate the script.
  • Cancel the import. Click Cancel to return to the SQL Scripts page without importing scripts.
  • Proceed with the import. Click Next to import the scripts in the specified export script. You can review the listed scripts to import.
  • Choose another export file. Click Previous to return to the Import Scripts file selection page to choose a different export script.
  • Import the scripts. Click Import Script(s) to import the scripts contained in the export script.

Viewing Script and Result Quotas

You can view the script limits in the current workspace on the Script Quotas page.
To view the Script Quotas page:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts.
    The SQL Scripts page appears.
  2. On the Tasks list, click Show Quotas.
    The Script Quotas page appears.
  3. Click OK to return to the SQL Scripts page.
About the Script Quotas Page
The Script Quotas page displays the following limits:
  • Result Quota in Bytes:
    • Maximum Script Result Size. The maximum size in bytes of a single script result.
    • Quota for All Script Results. The maximum size in bytes of all results in this workspace.
    • Used. The number of bytes currently used in this workspace.
    • Free. The number of bytes currently free in this workspace.
    • Quota. A usage bar illustrating the percentage of quota currently used.
  • Script Quota in Bytes:
    • Maximum Script Size. The maximum size in bytes of a single script. The size is set by the Oracle Application Express administrator and cannot be changed from within the Workspace.
    • Maximum Script Size. The maximum size in bytes of a single script.