I have a package definition, but i can not find the procedure body defined inside this package.
software used: pl/sql developer ide version 8, Oracle 11.2.0
I have googled and found below queries.
SELECT text FROM all_source WHERE name = 'myprocedure' ORDER BY line;
SELECT Text FROM User_Source WHERE Name ='PROCEDURENAME' ORDER BY Line;
however i dont see any result with above queries. what could i be missing. i am a newbie to database.
PACKAGE Pkg_xyz_selections is
-- Author : abc
-- Created : mm/dd/yyyy hh:mm:ss
-- Purpose : This will track xyz
-- Public function and procedure declarations
function upsert_xyz_data(a DATE, b VARCHAR2, c VARCHAR2, d VARCHAR2,
e VARCHAR2, f VARCHAR2, g VARCHAR2, h VARCHAR2, i VARCHAR2,
j VARCHAR2, k VARCHAR2, l VARCHAR2) return BOOLEAN;
procedure populate_xyz_data;
end Pkg_xyz_selections;
A package has two parts - a definition of the API (the *package*) and the contents (the "package body") which conceals the actual code.
eg
SQL> create or replace
2 PACKAGE Pkg_xyz_selections is
3
4 function upsert_xyz_data(a DATE, b VARCHAR2, c VARCHAR2, d VARCHAR2,
5 e VARCHAR2, f VARCHAR2, g VARCHAR2, h VARCHAR2, i VARCHAR2,
6 j VARCHAR2, k VARCHAR2, l VARCHAR2) return BOOLEAN;
7
8 procedure populate_xyz_data;
9
10 end Pkg_xyz_selections;
11 /
Package created.
SQL>
SQL>
SQL> create or replace
2 PACKAGE body Pkg_xyz_selections is
3
4 function upsert_xyz_data(a DATE, b VARCHAR2, c VARCHAR2, d VARCHAR2,
5 e VARCHAR2, f VARCHAR2, g VARCHAR2, h VARCHAR2, i VARCHAR2,
6 j VARCHAR2, k VARCHAR2, l VARCHAR2) return BOOLEAN is
7 begin
8 null;
9 return true;
10 end;
11
12 procedure populate_xyz_data is
13 x int;
14 begin
15 x := 123;
16 end;
17
18 end Pkg_xyz_selections;
19 /
Package body created.
But your queries to see the source are more or less correct, eg
SQL> select type, text from all_source
2 where name = 'PKG_XYZ_SELECTIONS'
3 order by type, line;
TYPE TEXT
------------ ------------------------------------------------------------
PACKAGE PACKAGE Pkg_xyz_selections is
PACKAGE
PACKAGE function upsert_xyz_data(a DATE, b VARCHAR2, c VARCHAR2, d V
ARCHAR2,
PACKAGE e VARCHAR2, f VARCHAR2, g VARCHAR2, h VARCHAR2, i VARCHAR2,
PACKAGE j VARCHAR2, k VARCHAR2, l VARCHAR2) return BOOLEAN;
PACKAGE
PACKAGE procedure populate_xyz_data;
PACKAGE
PACKAGE end Pkg_xyz_selections;
PACKAGE BODY PACKAGE body Pkg_xyz_selections is
PACKAGE BODY
PACKAGE BODY function upsert_xyz_data(a DATE, b VARCHAR2, c VARCHAR2, d V
ARCHAR2,
PACKAGE BODY e VARCHAR2, f VARCHAR2, g VARCHAR2, h VARCHAR2, i VARCHAR2,
PACKAGE BODY j VARCHAR2, k VARCHAR2, l VARCHAR2) return BOOLEAN is
PACKAGE BODY begin
PACKAGE BODY null;
PACKAGE BODY return true;
PACKAGE BODY end;
PACKAGE BODY
PACKAGE BODY procedure populate_xyz_data is
PACKAGE BODY x int;
PACKAGE BODY begin
PACKAGE BODY x := 123;
PACKAGE BODY end;
PACKAGE BODY
PACKAGE BODY end Pkg_xyz_selections;
Hope this helps