dbXpert for Oracle FAQ

Questions

  1. How to execute multiple SQL queries?
  2. Can I change color scheme of dbXpert interface?
  3. How to view execution plan of SQL statement?
  4. How to use bind variables?
  5. When trying to connect to an Oracle Instance using sys account, the following error message "ORA-28009: connection to sys should be as sysdba or sysoper" results. This error occurs when connecting via an Oracle 9i.
  6. Cannot start debugging in PL/SQL Debugger when connecting to Oracle 10g database?
  7. How to run anonymous PL/SQL block?
  8. What is "SmarTab" in dbXpert?
  9. How to turn on "SmarTab"?

Answers

1. How to execute multiple SQL queries?

To execute several statements in the SQL window (separated by at least one blank line) , highlight all statements you intent to run, and press or click Execute button on the menu bar

2. Can I change color scheme of dbXpert interface?

"Color Scheme" is to set up background color. The color is set per database per login user. You can click the "Set Window Color" button on the menu bar to set the color scheme of the active window. Once you set the color theme, dbXpert will use the same color theme next time when you connect to the same database server using the same database user. You can also set the default color theme in the Options Window.

3. How to view execution plan of SQL statement?

To view the execution plan of a statement, place the cursor within the statement and then click Execution Plan button on the toolbar menu. A tree of execution plan is displayed on the result panel. You need to have PLAN_TABLE before you can generate execution plan.

4. How to use bind variables?

You can run SQL statements with bind variables. When the SQL statement that you run contains bind variables, a window will prompt up which allow you to enter the values of the corresponding bind variables.

5. When trying to connect to an Oracle Instance using sys account, the following error message "ORA-28009: connection to sys should be as sysdba or sysoper" results. This error occurs when connecting via an Oracle 9i.

By default, the 07_DICTIONARY_ACCESSIBILITY parameter is set to FALSE in Oralce 9i. Therefore, you cannot login as sys. You need to change the 07_DICTIONARY_ACCESSIBILITY parameter to TRUE in the init.ora file and restart the database.

6. Cannot start debug in PL/SQL Debugger when connecting to Oracle 10g database?

In Oracle 10g, user need to have the 'DEBUG CONNECT SESSION' privilege granted. If not granted, user will get error message when starting the debugging session.

7. How to run anonymous PL/SQL block?

You can run any anonymous PL/SQL block in the SQL Workspace. The anonymous PL/SQL block must not contain blank line within the PL/SQL block and must be end with ";". You can place the cursor within the statement, and press or click Execute button on the menu bar to execute the anonymous PL/SQL block. eg: BEGIN SCOTT.UPDATE_SALARY_BY(10); END;

8. What is "SmarTab" in dbXpert?

In the SQL Window and Procedure Builder Window, we built-in a smart technology called "SmarTab". SmarTab helps develpers to quickly get the required keywords or object names without having to key in the whole word. By default, the SmarTab is enabled, you can also disabled it in the Option Window. The following shows the usages of SmarTab technology:

SmarTab
Keystrokes
Example
Keywords <PREFIX of KEYWORD>  + <TAB> SEL + <TAB> => SELECT

SE + <TAB> => A popup list will be displayed for selection. The latest selected keywords will be at the top of the list. The remaining will be order by alphabetic value.

Current User Objects "." + <PREFIX of OBJECT NAME> + <TAB> EM + <TAB> => SCOTT.EMP     (Assume the current user is SCOTT)
user's objects <SCHEAMA> + "." + <PREFIX of OBJECT NAME> + <TAB> SYS.DBA_ + <TAB> => A popup list will be displayed with all the table/view with name starting with "DBA_"
user name ".."  + <PREFIX of USER NAME> + <TAB> ..S + <TAB> => A popup list will be displayed with all the username starting with "S" such as SCOTT, SYS, SYSTEM etc.

9. How to turn on "SmarTab"?

Open the Properties Window from the main menu (File->Properties). In the SmarTab section under the General tab, select the "Key - Tab". Then, click the apply button and restart dbXpert.