Problems creating a package using SQL Developer
So I never worked with stored procedures and didn't have a lot of experience with databases in general, and I was assigned a task requiring the creation of a package and I got stuck.
Using SQL Developer I am trying to create a package named JUMPTO with this code ...
create or replace package JUMPTO is
type t_locations is ref cursor;
procedure procGetLocations(locations out t_locations);
end JUMPTO;
When I run it, it spits out this block of PL / SQL code ...
DECLARE
LOCATIONS APPLICATION.JUMPTO.t_locations;
BEGIN
JUMPTO.PROCGET_LOCATIONS(
LOCATIONS => LOCATIONS
);
-- Modify the code to output the variable
-- DBMS_OUTPUT.PUT_LINE('LOCATIONS = ' || LOCATIONS);
END;
The tutorial I found said to output the comment for this second line. I tried with and without comment.
When I click "ok" I get an error ...
ORA-06550: line 2, column 32:
PLS-00302: component 'JUMPTO' must be declared
ORA-06550: line 2, column 13:
PL/SQL: item ignored
ORA-06550: line 6, column 18:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06512: at line 58
I really don't know what's going on, it's completely new territory for me. I tried to create a body that just fetched some things from the database, but nothing works as it seems in my head. Can anyone give me any idea about this?
source to share
First of all you need to declare the package body, for example:
create or replace package body JUMPTO is
procedure procGetLocations(locations out t_locations)
is
begin
locations := null; -- Need code here
end;
end JUMPTO;
To compile, you need the following:
DECLARE
LOCATIONS JUMPTO.t_locations;
BEGIN
JUMPTO.PROCGETLOCATIONS(
LOCATIONS => LOCATIONS
);
END;
source to share
Oracle PL / SQL package consists of two parts:
- Package specification (public part, where globally accessible constants, functions, procedures, variables, etc. are indicated).
- The body of the package (where the code is meant to implement the package specification).
Your first piece of code declared a package specification (JUMPTO). You have declared a type (t_locations) and a procedure (procGetLocations) that has no inputs, but outputs a single variable (locations) of type t_locations.
First compile the package spec (as you do), then compile the package body like so:
create or replace package body JUMPTO is
procedure procGetLocations(locations out t_locations) is
begin
locations := null; -- Your code goes here
end procGetLocations;
end JUMPTO;
You can now call procGetLocations in other PL / SQL blocks (anonymously or otherwise).
source to share