{"id":1785,"date":"2017-12-25T20:23:40","date_gmt":"2017-12-25T13:23:40","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=1785"},"modified":"2025-05-30T01:16:48","modified_gmt":"2025-05-30T08:16:48","slug":"plsql-package-specification","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/","title":{"rendered":"PL\/SQL Package Specification"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: In this tutorial, you will learn step by step how to create a PL\/SQL package specification by using the <code>CREATE PACKAGE<\/code> statement.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='introducing-the-package-specification'>Introducing the package specification <a href=\"#introducing-the-package-specification\" class=\"anchor\" id=\"introducing-the-package-specification\" title=\"Anchor for Introducing the package specification\">#<\/a><\/h2>\n\n\n\n<p>In PL\/SQL, a <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package\/\">package<\/a> has two parts:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Package specification <\/li>\n\n\n\n<li><a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-body\/\">Package body<\/a><\/li>\n<\/ul>\n\n\n\n<p>The package specification is where you declare public items. By default, the scope of package items is the schema of the package. In other words, you can access items declared in a package specification from anywhere in the schema e.g., you can access items in a package specification from other packages.<\/p>\n\n\n\n<p>A package specification does not contain any implementations of the public items. For example, in case of <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-procedure\/\">procedures<\/a> or <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-function\/\">functions<\/a>, the package specification contains their headers only, but not their bodies.<\/p>\n\n\n\n<p>A package specification can exist independently if their items do not require implementations.<\/p>\n\n\n\n<p>Typically, a package specification contains the following items:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-procedure\/\">Procedures<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-function\/\">Functions<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-cursor\/\">Cursors<\/a><\/li>\n\n\n\n<li>Types, <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-variables\/\">variables<\/a>, and constants<\/li>\n\n\n\n<li><a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-record\/\">Records<\/a><\/li>\n\n\n\n<li>Collections<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='creating-a-package-specification'>Creating a package specification <a href=\"#creating-a-package-specification\" class=\"anchor\" id=\"creating-a-package-specification\" title=\"Anchor for Creating a package specification\">#<\/a><\/h2>\n\n\n\n<p>To create a new package specification, you use the <code>CREATE PACKAGE<\/code> statement as shown below:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> &#91;<span class=\"hljs-keyword\">OR<\/span> <span class=\"hljs-keyword\">REPLACE<\/span>] <span class=\"hljs-keyword\">PACKAGE<\/span> &#91;schema_name.]&lt;package_name&gt; \n<span class=\"hljs-keyword\">IS<\/span> | <span class=\"hljs-keyword\">AS<\/span>\n    declarations;\n<span class=\"hljs-keyword\">END<\/span> &#91;&lt;package_name&gt;];<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Follow the <code>CREATE PACKAGE<\/code> clause is the name of the package which you want to create.<\/li>\n\n\n\n<li>The <code>OR REPLACE<\/code> option allows you to replace the package if it exists and recompile it.<\/li>\n\n\n\n<li>The <code>schema_name<\/code> is the schema to which the package belongs. By default, it is your schema.<\/li>\n<\/ul>\n\n\n\n<p>Between the&nbsp; <code>AS<\/code> and <code>END<\/code> keywords, you declare the public items of the package specification.<\/p>\n\n\n\n<p>See the following package specification example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">OR<\/span> <span class=\"hljs-keyword\">REPLACE<\/span> <span class=\"hljs-keyword\">PACKAGE<\/span> order_mgmt\n<span class=\"hljs-keyword\">AS<\/span>\n  gc_shipped_status  <span class=\"hljs-keyword\">CONSTANT<\/span> <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">10<\/span>) := <span class=\"hljs-string\">'Shipped'<\/span>;\n  gc_pending_status CONSTANT VARCHAR(10) := 'Pending';\n  gc_canceled_status CONSTANT VARCHAR(10) := 'Canceled';\n\n  <span class=\"hljs-comment\">-- cursor that returns the order detail<\/span>\n  CURSOR g_cur_order(p_order_id NUMBER)\n  IS\n    <span class=\"hljs-keyword\">SELECT<\/span>\n      customer_id,\n      <span class=\"hljs-keyword\">status<\/span>,\n      salesman_id,\n      order_date,\n      item_id,\n      product_name,\n      quantity,\n      unit_price\n    <span class=\"hljs-keyword\">FROM<\/span>\n      order_items\n    <span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> orders <span class=\"hljs-keyword\">USING<\/span> (order_id)\n    <span class=\"hljs-keyword\">INNER<\/span> <span class=\"hljs-keyword\">JOIN<\/span> products <span class=\"hljs-keyword\">USING<\/span> (product_id)\n    <span class=\"hljs-keyword\">WHERE<\/span>\n      order_id = p_order_id;\n\n  <span class=\"hljs-comment\">-- get net value of a order<\/span>\n  FUNCTION get_net_value(\n      p_order_id NUMBER)\n    RETURN NUMBER;\n\n  <span class=\"hljs-comment\">-- Get net value by customer<\/span>\n  FUNCTION get_net_value_by_customer(\n      p_customer_id NUMBER,\n      p_year        NUMBER)\n    RETURN NUMBER;\n\n<span class=\"hljs-keyword\">END<\/span> order_mgmt;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this example, the package name is <code>order_mgmt<\/code>. Let&#8217;s examine it in detail:<\/p>\n\n\n\n<p>First, declare three constants that represent the order status: shipped, pending, and canceled:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">gc_shipped_status  CONSTANT VARCHAR(<span class=\"hljs-number\">10<\/span>) := <span class=\"hljs-string\">'Shipped'<\/span>;\ngc_pending_status CONSTANT VARCHAR(<span class=\"hljs-number\">10<\/span>) := <span class=\"hljs-string\">'Pending'<\/span>;\ngc_canceled_status CONSTANT VARCHAR(<span class=\"hljs-number\">10<\/span>) := <span class=\"hljs-string\">'Canceled'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, define a cursor that returns the order details:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">CURSOR g_cur_order(p_order_id NUMBER)\n  IS\n    SELECT\n      customer_id,\n      status,\n      salesman_id,\n      order_date,\n      item_id,\n      product_name,\n      quantity,\n      unit_price\n    FROM\n      order_items\n    INNER JOIN orders USING (order_id)\n    INNER JOIN products USING (product_id)\n    WHERE\n      order_id = p_order_id;<\/code><\/span><\/pre>\n\n\n<p>Third, declare two functions that return the net value of an order and the net value of orders which belong to a specific customer:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">-- get net value of a order\n<span class=\"hljs-function\"><span class=\"hljs-keyword\">FUNCTION<\/span> <span class=\"hljs-title\">get_net_value<\/span><span class=\"hljs-params\">(p_order_id NUMBER)<\/span>\n    <span class=\"hljs-title\">RETURN<\/span> <span class=\"hljs-title\">NUMBER<\/span><\/span>;\n\n-- Get net value by customer\n<span class=\"hljs-function\"><span class=\"hljs-keyword\">FUNCTION<\/span> <span class=\"hljs-title\">get_net_value_by_customer<\/span><span class=\"hljs-params\">(\n      p_customer_id NUMBER,\n      p_year        NUMBER)<\/span>\n    <span class=\"hljs-title\">RETURN<\/span> <span class=\"hljs-title\">NUMBER<\/span><\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='compiling-a-package'>Compiling a package <a href=\"#compiling-a-package\" class=\"anchor\" id=\"compiling-a-package\" title=\"Anchor for Compiling a package\">#<\/a><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id='sql-developer'>SQL Developer <a href=\"#sql-developer\" class=\"anchor\" id=\"sql-developer\" title=\"Anchor for SQL Developer\">#<\/a><\/h3>\n\n\n\n<p>If you are using SQL developer, you can click the <strong>Run Script<\/strong> button to compile the package specification as shown in the following screenshot.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"475\" height=\"279\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/12\/PLSQL-package-specification-compile-using-SQL-Developer.png\" alt=\"PL\/SQL package specification compile using SQL Developer\" class=\"wp-image-1788\" title=\"PL\/SQL package specification compile using SQL Developer\" srcset=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/12\/PLSQL-package-specification-compile-using-SQL-Developer.png 475w, https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/12\/PLSQL-package-specification-compile-using-SQL-Developer-300x176.png 300w\" sizes=\"auto, (max-width: 475px) 100vw, 475px\" \/><\/figure>\n\n\n\n<p>Once the package is compiled successfully, you can find it under the <strong>packages<\/strong> node of the object list:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"293\" src=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/12\/PLSQL-package-specification-example.png\" alt=\"PL\/SQL package specification example\" class=\"wp-image-1791\" title=\"PL\/SQL package specification example\"\/><\/figure>\n\n\n\n<p>If you don&#8217;t see the package, you click the <strong>Refresh<\/strong> button to reload the object list.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='sqlplus'>SQL*Plus <a href=\"#sqlplus\" class=\"anchor\" id=\"sqlplus\" title=\"Anchor for SQL*Plus\">#<\/a><\/h3>\n\n\n\n<p>If you are using SQL*Plus to create a package, you use type forward slash (\/) as shown below:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">SQL&gt; <span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">package<\/span> test_package <span class=\"hljs-keyword\">as<\/span>\n  <span class=\"hljs-number\">2<\/span>  gc_status <span class=\"hljs-keyword\">constant<\/span> <span class=\"hljs-built_in\">varchar<\/span>(<span class=\"hljs-number\">10<\/span>) := <span class=\"hljs-string\">'Active'<\/span>;\n  3  <span class=\"hljs-keyword\">end<\/span>;\n  4  \/\n\nPackage created.<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this example, the forward-slash (\/) instructed Oracle to compile and create the <code>test_package<\/code> package.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='compile-a-package-from-the-source-file'>Compile a package from the source file <a href=\"#compile-a-package-from-the-source-file\" class=\"anchor\" id=\"compile-a-package-from-the-source-file\" title=\"Anchor for Compile a package from the source file\">#<\/a><\/h3>\n\n\n\n<p>Sometimes, you may use a source control like Git to manage the versions of packages and you want to create a package from a source file.<\/p>\n\n\n\n<p>To do this, you use the following syntax:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">@path_to_file<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>For example, a file <code>sample.txt<\/code> located in the&nbsp;<code>c:\\plsql<\/code> folder that contains the source code for creating the <code>sample<\/code> package:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">OR<\/span> <span class=\"hljs-keyword\">REPLACE<\/span> <span class=\"hljs-keyword\">PACKAGE<\/span> <span class=\"hljs-keyword\">sample<\/span>\n<span class=\"hljs-keyword\">AS<\/span>\n  gc_shipped  <span class=\"hljs-keyword\">CONSTANT<\/span> <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">10<\/span>) := <span class=\"hljs-string\">'Shipped'<\/span>;\n  gc_pending  CONSTANT VARCHAR(10) := 'Pending';\n  gc_canceled CONSTANT VARCHAR(10) := 'Canceled';\n  gv_status   VARCHAR(10) := gc_pending;         \n<span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">sample<\/span>; \n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To create the package from the <code>sample.sql<\/code> file, you use the following command:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">@c:\\plsql\\sample.sql<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In SQL Developer, you click the <strong>Run Script<\/strong> button to create the package.<\/p>\n\n\n\n<p>However, if you use SQL*Plus, you must type the forward slash (\/) to compile and create the package as shown below:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">SQL&gt; @c:\\plsql\\sample.txt\n  4  \/\n\nPackage created.<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='package-state'>Package state <a href=\"#package-state\" class=\"anchor\" id=\"package-state\" title=\"Anchor for Package state\">#<\/a><\/h2>\n\n\n\n<p>Once the package is created, you can reference its public items declared in the package specification. Oracle will create a separate instance of the package for each session that references to the package items.<\/p>\n\n\n\n<p>The values of constants, variables, and cursors declared in either package specification or body consists of its package state. If the package specification has at least one variable, constant, or cursor, the package is stateful; or else it is stateless.<\/p>\n\n\n\n<p>To refer to an item using the following syntax:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">package_name.item_name<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>For example, the following anonymous block assigns the variable <code>gv_status<\/code> of the package <code>sample<\/code> to the &nbsp;<code>gc_shipped<\/code> constant and prints the value.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> SERVEROUTPUT <span class=\"hljs-keyword\">ON<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n  sample.gv_status := sample.gc_shipped;\n  DBMS_OUTPUT.PUT_LINE(sample.gv_status);\n<span class=\"hljs-keyword\">END<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The block returns:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">Shipped<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you log in using a separate session and reference to the gv_status variable of the <code>sample<\/code> package, its value will be different because each session has its own instance of the package.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> SERVEROUTPUT <span class=\"hljs-keyword\">ON<\/span>;\n\n<span class=\"hljs-keyword\">BEGIN<\/span>\n  sample.gv_status := sample.gc_pending;\n  DBMS_OUTPUT.PUT_LINE(sample.gv_status);\n<span class=\"hljs-keyword\">END<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The result is:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">Pending<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the <code>CREATE PACKAGE<\/code> statement to a create a package specification.<\/li>\n<\/ul>\n\n\n\n<p>Let&#8217;s learn how to create a <a href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-body\/\">package body<\/a>.<\/p>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"1785\"\n\t\t\t\tdata-post-url=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/\"\n\t\t\t\tdata-post-title=\"PL\/SQL Package Specification\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"1785\"\n\t\t\t\tdata-post-url=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/\"\n\t\t\t\tdata-post-title=\"PL\/SQL Package Specification\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: In this tutorial, you will learn step by step how to create a PL\/SQL package specification by using the CREATE PACKAGE statement. Introducing the package specification # In PL\/SQL, a package has two parts: The package specification is where you declare public items. By default, the scope of package items is the schema of [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1418,"menu_order":30,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1785","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PL\/SQL Package Specification<\/title>\n<meta name=\"description\" content=\"This tutorial shows you step by step how to create a PL\/SQL package specification by using the CREATE PACKAGE statement.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PL\/SQL Package Specification\" \/>\n<meta property=\"og:description\" content=\"This tutorial shows you step by step how to create a PL\/SQL package specification by using the CREATE PACKAGE statement.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/\" \/>\n<meta property=\"og:site_name\" content=\"Oracle Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-05-30T08:16:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/12\/PLSQL-package-specification-compile-using-SQL-Developer.png\" \/>\n\t<meta property=\"og:image:width\" content=\"475\" \/>\n\t<meta property=\"og:image:height\" content=\"279\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-package-specification\\\/\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-package-specification\\\/\",\"name\":\"PL\\\/SQL Package Specification\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-package-specification\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-package-specification\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.oracletutorial.com\\\/wp-content\\\/uploads\\\/2017\\\/12\\\/PLSQL-package-specification-compile-using-SQL-Developer.png\",\"datePublished\":\"2017-12-25T13:23:40+00:00\",\"dateModified\":\"2025-05-30T08:16:48+00:00\",\"description\":\"This tutorial shows you step by step how to create a PL\\\/SQL package specification by using the CREATE PACKAGE statement.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-package-specification\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-package-specification\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-package-specification\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/wp-content\\\/uploads\\\/2017\\\/12\\\/PLSQL-package-specification-compile-using-SQL-Developer.png\",\"contentUrl\":\"https:\\\/\\\/www.oracletutorial.com\\\/wp-content\\\/uploads\\\/2017\\\/12\\\/PLSQL-package-specification-compile-using-SQL-Developer.png\",\"width\":475,\"height\":279,\"caption\":\"PLSQL package specification compile using SQL Developer\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/plsql-package-specification\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.oracletutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PL\\\/SQL Tutorial\",\"item\":\"https:\\\/\\\/www.oracletutorial.com\\\/plsql-tutorial\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PL\\\/SQL Package Specification\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.oracletutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.oracletutorial.com\\\/\",\"name\":\"Oracle Tutorial\",\"description\":\"Oracle Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.oracletutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PL\/SQL Package Specification","description":"This tutorial shows you step by step how to create a PL\/SQL package specification by using the CREATE PACKAGE statement.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/","og_locale":"en_US","og_type":"article","og_title":"PL\/SQL Package Specification","og_description":"This tutorial shows you step by step how to create a PL\/SQL package specification by using the CREATE PACKAGE statement.","og_url":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/","og_site_name":"Oracle Tutorial","article_modified_time":"2025-05-30T08:16:48+00:00","og_image":[{"width":475,"height":279,"url":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/12\/PLSQL-package-specification-compile-using-SQL-Developer.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/","url":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/","name":"PL\/SQL Package Specification","isPartOf":{"@id":"https:\/\/www.oracletutorial.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/#primaryimage"},"image":{"@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/#primaryimage"},"thumbnailUrl":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/12\/PLSQL-package-specification-compile-using-SQL-Developer.png","datePublished":"2017-12-25T13:23:40+00:00","dateModified":"2025-05-30T08:16:48+00:00","description":"This tutorial shows you step by step how to create a PL\/SQL package specification by using the CREATE PACKAGE statement.","breadcrumb":{"@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/#primaryimage","url":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/12\/PLSQL-package-specification-compile-using-SQL-Developer.png","contentUrl":"https:\/\/www.oracletutorial.com\/wp-content\/uploads\/2017\/12\/PLSQL-package-specification-compile-using-SQL-Developer.png","width":475,"height":279,"caption":"PLSQL package specification compile using SQL Developer"},{"@type":"BreadcrumbList","@id":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-package-specification\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.oracletutorial.com\/"},{"@type":"ListItem","position":2,"name":"PL\/SQL Tutorial","item":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/"},{"@type":"ListItem","position":3,"name":"PL\/SQL Package Specification"}]},{"@type":"WebSite","@id":"https:\/\/www.oracletutorial.com\/#website","url":"https:\/\/www.oracletutorial.com\/","name":"Oracle Tutorial","description":"Oracle Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.oracletutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/1785","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/comments?post=1785"}],"version-history":[{"count":0,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/1785\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/pages\/1418"}],"wp:attachment":[{"href":"https:\/\/www.oracletutorial.com\/wp-json\/wp\/v2\/media?parent=1785"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}