How to Get Table and Procedure Scripts in Oracle Server (Schema + Data Included)

Introduction

When working with Oracle Database, it’s often necessary to export both schema structure (tables, procedures) and data for backup, migration, or sharing. Unlike SQL Server, Oracle provides several tools and commands for this task, including Data Pump, DBMS_METADATA, and SQL Developer.

This guide will walk you through multiple ways to generate table scripts (with schema and data) and stored procedure scripts

1. Export Table Script (Schema + Data)

 Method 1: Using SQL Developer (GUI Tool)

? Steps:

1. Open Oracle SQL Developer.

2. Connect to your database.

3. In the Connections panel, expand the Tables node.

4. Right-click the desired table → Click Export.

5. Choose:

   • Format: SQL Insert

   • Options: Include schema, table structure, and data

6. Select output destination (.sql file)

7. Click Next → Finish

Result: SQL file with CREATE TABLE and INSERT INTO statements.

Method 2: Using DBMS_METADATA (SQL Query)

-- To get table structure
SELECT DBMS_METADATA.GET_DDL('TABLE', 'Table_name', 'Owner') FROM DUAL;

This gives the DDL script (Data Definition Language) for the specified table or procedure.

2. Export Stored Procedure Script

 Option 1: Using SQL Developer

1. In SQL Developer, go to Procedures.

2. Right-click the procedure → Select "Save To File".

3. Choose a .sql file to save the script.

-- To get stored procedure definition
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'PROCEDURE_NAME', 'OWNER') FROM DUAL;