Oracle DBA FAQ - Introduction to Oracle SQL Developer

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

How To Export Data to an XML File?

If you want to export data from a table to a file in XML format, you can use the following steps:

Right-click the table name, EMPLOYEES, in the object tree view.
Select Export.
Select XML. The Export Data window shows up.
Click Format tab.
Select Format as: XML.
Enter File as: \temp\MyTeam.xml.
Click Columns tab.
Check columns: FIRST_NAME, LAST_NAME, MANAGER_ID
Click Where tab.
Enter Where clause as: MANAGER_ID=100.
Click Apply.

Your XML file, \temp\MyTeam.xml, is ready. Open it, you will see:

<?xml version='1.0'  encoding='Cp1252' ?>
<results>
  <row>
    <FIRST_NAME><![CDATA[Gerald]]></FIRST_NAME>
    <LAST_NAME><![CDATA[Cambrault]]></LAST_NAME>
    <MANAGER_ID><![CDATA[100]]></MANAGER_ID>
  </row>
  <row>
    <FIRST_NAME><![CDATA[Lex]]></FIRST_NAME>
    <LAST_NAME><![CDATA[De Haan]]></LAST_NAME>
    <MANAGER_ID><![CDATA[100]]></MANAGER_ID>
  </row>
</results>

How To Create a Procedure Interactively?

If you want to create a stored procedure interactively, you can use the following steps:

Open you connection name, like Local_XE.
Right-click Procedures.
Select Create PROCEDURE. The Create Procedure window shows up.
Enter Name as: Hello
Click OK. The script area opens up with an empty procedure.
Place the empty template with the following procedure.

CREATE OR REPLACE PROCEDURE HR.HELLO AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello world!');
  DBMS_OUTPUT.PUT_LINE('Welcome to PL/SQL!');
END;

Click the Compile icon. The procedure is created.

How To Run a Stored Procedure Interactively?

If have an existing stored procedure and you want to run it interactively, the tutorial steps listed below will help you out:

Open you connection name, like Local_XE.
Open Procedures.
Right-click the procedure name: HELLO.
Select Run. The Run PL/SQL window shows up. SQL Developer creates a simple anonymous code block for you to run the stored procedure.
Click OK. SQL Developer runs the anonymous code block, which calls your store procedure.

You should see the following in the Running Log area:

Connecting to the database Local_XE.
Hello world!
Welcome to PL/SQL!
Process exited.
Disconnecting from the database Local_XE.

How To Run Stored Procedures in Debug Mode?

If have an existing stored procedure and you want to debug it interactively, you can use the debug feature provided in SQL Developer. The following exercise shows you how to start the debug mode:

Open you connection name, like Local_XE.
Open Procedures.
Right-click the procedure name: HELLO.
Select Debug. The Debug PL/SQL window shows up. SQL Developer creates a simple anonymous code block for you to run the stored procedure.
Click OK.

You may get the following errors. Read the next tutorial on how to fix the errors.

Connecting to the database Local_XE.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP(...)
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
This session requires DEBUG CONNECT SESSION and DEBUG ANY
  PROCEDURE user privileges.
Process exited.
Disconnecting from the database Local_XE.

(Continued on next part...)

Part: 1 2 3 4 5 6

More...