Home » SQL & PL/SQL » SQL & PL/SQL » SQLPLUS PLS-00103 Encountered 'INSERT'
SQLPLUS PLS-00103 Encountered 'INSERT' [message #634201] |
Thu, 05 March 2015 20:51 |
|
MiraclePlease
Messages: 19 Registered: March 2015
|
Junior Member |
|
|
So I am not sure if it is against rules to do this. I am working on a homework assignment. I do not want the answers on how to do everything, that isnt right, but I just could REALLY use some help on why I have the errors I have? I just cant seem to figure out what is wrong with my code.
CREATE OR REPLACE PACKAGE Team_pkq AS
name_in_use EXCEPTION;
Type TeamRecord IS RECORD(Team_ID, Tname, Rank, City, Championships);
FUNCTION add(name Team.Tname%type, city Team.City%type);
END Team_pkg;
/
CREATE OR REPLACE PACKAGE BODY Team_pkg AS
FUNCTION add(name Team.Tname%type, city Team.City%type) RETURN NUMBER
AS
theID NUMBER;
countVariable integer;
name_in_use EXCEPTION;
BEGIN
TeamRecord.name := name;
TeamRecord.city := city;
TeamRecord.ID := Team_seq.nextval;
SELECT count(*) INTO countVariable FROM TEAM WHERE Tname = name;
if countVariable = 1 THEN
RAISE name_in_use;
ELSIF
INSERT INTO Team
(Team_ID,Tname,Rank,City,Championships)
VALUES(TeamRecord.Team_ID, TeamRecord.Tname,TeamRecord.Rank, TeamRecord.City, TeamRecord.Championships);
theID :=TeamRecord.ID;
return theID;
ENDIF;
END add;
END;
/
DROP SEQUENCE Team_seq;
/
I hope to edit this and remove my code once someone can help me if thats ok?
I get this errors
LINE/COL ERROR
-------- -----------------------------------------------------------------
18/1 PLS-00103: Encountered the symbol "INSERT" when expecting one of
the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set
specification>
<an alternat
LINE/COL ERROR
-------- -----------------------------------------------------------------
21/1 PLS-00103: Encountered the symbol "THEID"
21/22 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
25/4 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
;
Why does it have issue with my insert? What is wrong with my variable?
I do not understand the last two errors at all.
|
|
|
|
|
|
|
|
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634211 is a reply to message #634210] |
Thu, 05 March 2015 21:51 |
|
MiraclePlease
Messages: 19 Registered: March 2015
|
Junior Member |
|
|
Barbara Boehmer wrote on Thu, 05 March 2015 21:47You have to compile the package specification before you compile the package body.
The package specification begins:
CREATE OR REPLACE PACKAGE Team_pkq AS
The package body begins;
CREATE OR REPLACE PACKAGE BODY Team_pkq AS
I'm sorry, I re-read what you wrote and looked at my code and I am confused what you mean. I know you seperate where you create the package head and create the package body. Which I did.
CREATE OR REPLACE PACKAGE Team_pkg AS
name_in_use EXCEPTION;
Type TeamRecord IS RECORD(Team_ID, Tname, Rank, City, Championships);
FUNCTION add
(
name Team.Tname%type,
city Team.City%type
)
RETURN NUMBER;
END Team_pkg;
/
CREATE OR REPLACE PACKAGE BODY Team_pkg AS
FUNCTION add(name Team.Tname%type, city Team.City%type) RETURN NUMBER
AS
theID NUMBER;
countVariable integer;
name_in_use EXCEPTION;
BEGIN
TeamRecord.name := name;
TeamRecord.city := city;
TeamRecord.ID := Team_seq.nextval;
SELECT count(*) INTO countVariable FROM TEAM WHERE Tname = name;
if countVariable = 1 THEN
RAISE name_in_use;
ELSE
INSERT INTO Team
(Team_ID,Tname,Rank,City,Championships)
VALUES(TeamRecord.Team_ID, TeamRecord.Tname,TeamRecord.Rank, TeamRecord.City, TeamRecord.Championships);
theID :=TeamRecord.ID;
return theID;
END IF;
END add;
END;
/
DROP SEQUENCE Team_seq;
/
is there something amiss there?
CREATE OR REPLACE PACKAGE Team_pkg AS
CREATE OR REPLACE PACKAGE BODY Team_pkg AS
[Updated on: Thu, 05 March 2015 21:52] Report message to a moderator
|
|
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634214 is a reply to message #634213] |
Thu, 05 March 2015 22:06 |
|
MiraclePlease
Messages: 19 Registered: March 2015
|
Junior Member |
|
|
Barbara Boehmer wrote on Thu, 05 March 2015 22:01Assuming that you are running this from SQL*Plus, use SHOW ERROS immediately after compilation:
CREATE OR REPLACE PACKAGE ...
END;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY ...
END;
/
SHOW ERRORS
If you do this, you will find that the package specificatoin, which you are referring to as the package header, does not compile without errors. You cannot compile the package body unti you have fixed the errors in the package specification.
oh! Oh my god thank you so much! I can finally see the issues! Apparently there is problems with my 'record'
Type TeamRecord IS RECORD(Team_ID, Tname, Rank, City, Championships);
This is what is causing I think all of my errors. However, I thought I did it exactly as my teacher said to do? I have tried googling to see examples of using records in SQLPLUS but I could only find Cursor examples. Does anyone have something of an example of a record in a package I could look at for help? This is specifically required for our class to use a Record, and use a Cursor that uses the record.
edit: oh if you want to see the errors:
3/34 PLS-00103: Encountered the symbol "," when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier> long
double ref char time timestamp interval date binary national
character nchar
3/69 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
. ( ) , * % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
[Updated on: Thu, 05 March 2015 22:07] Report message to a moderator
|
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634216 is a reply to message #634215] |
Thu, 05 March 2015 22:13 |
|
MiraclePlease
Messages: 19 Registered: March 2015
|
Junior Member |
|
|
The body is now giving me these errors
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/1 PL/SQL: Statement ignored
8/12 PLS-00302: component 'NAME' must be declared
9/1 PL/SQL: Statement ignored
9/1 PLS-00330: invalid use of type name or subtype name
10/1 PL/SQL: Statement ignored
10/12 PLS-00302: component 'ID' must be declared
15/1 PL/SQL: SQL Statement ignored
17/79 PL/SQL: ORA-00904: "TEAMRECORD"."CHAMPIONSHIPS": invalid
identifier
17/79 PLS-00330: invalid use of type name or subtype name
LINE/COL ERROR
-------- -----------------------------------------------------------------
18/1 PL/SQL: Statement ignored
18/20 PLS-00302: component 'ID' must be declared
I thought name was declared since its the variable that would be being sent into the add function, along with city?
[Updated on: Thu, 05 March 2015 22:16] Report message to a moderator
|
|
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634219 is a reply to message #634218] |
Thu, 05 March 2015 22:35 |
|
MiraclePlease
Messages: 19 Registered: March 2015
|
Junior Member |
|
|
Ok
This is what I have so far. What I am supposed to do is have a cursor, TeamCursor, that uses TeamRecord( A record). I was thinking I am to use the cursor to look through my database table, find the row I am looking for (if it is there), and insert it into my 'TeamRecord'. However, my cursor in PACKAGE BODY is causing me some errors.
CREATE OR REPLACE PACKAGE Team_pkg AS
name_in_use EXCEPTION;
Type TeamRecord IS RECORD
(
Team_ID integer,
Tname varchar(15),
Rank integer,
City varchar(20),
Championships integer
);
CURSOR TeamCursor return TeamRecord;
FUNCTION add(name Team.Tname%type, city Team.City%type) RETURN NUMBER;
END Team_pkg;
/
show errors
CREATE OR REPLACE PACKAGE BODY Team_pkg AS
FUNCTION add(name Team.Tname%type, city Team.City%type) RETURN NUMBER
AS
theID NUMBER;
countVariable integer;
name_in_use EXCEPTION;
CURSOR TeamCursor return TeamRecord is SELECT * FROM Team WHERE Tname=name;
BEGIN
SELECT count(*) INTO countVariable FROM TEAM WHERE Tname = name;
if countVariable = 1 THEN
RAISE name_in_use;
ELSE
fetch TeamCursor into TeamRecord.Team_ID,TeamRecord.Tname,TeamRecord.Rank,TeamRecord.City,TeamRecord.Championships;
INSERT INTO Team
(Team_ID,Tname,Rank,City,Championships)
VALUES(TeamRecord.Team_ID, TeamRecord.Tname,TeamRecord.Rank, TeamRecord.City, TeamRecord.Championships);
theID :=TeamRecord.ID;
return theID;
END IF;
END add;
END;
/
show errors
DROP SEQUENCE Team_seq;
/
Then I get these errors
Errors for PACKAGE BODY TEAM_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
11/8 PLS-00323: subprogram or cursor 'TEAMCURSOR' is declared in a
package specification and must be defined in the package body
13/1 PL/SQL: SQL Statement ignored
13/23 PLS-00330: invalid use of type name or subtype name
14/1 PL/SQL: SQL Statement ignored
16/79 PL/SQL: ORA-00904: "TEAMRECORD"."CHAMPIONSHIPS": invalid
identifier
16/79 PLS-00330: invalid use of type name or subtype name
17/1 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
17/20 PLS-00302: component 'ID' must be declared
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634220 is a reply to message #634219] |
Thu, 05 March 2015 23:23 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I really hate it when instructors require that things be done in an overly complicated manner. It looks like you are trying to create a packaged function that will accept a name and city as input variables, then check to see if that name already exists. If the name exists, then raise an error. If the name does not exist, then insert the record, returning the new id that comes from a sequence. The following demonstrates a simple method of doing that. I don't know what type of cursor or record your instructor wants used for what where. Please post the exact wording of the assignment. You might want to see if you can get something like below working first, then figure out how to modify it to use your cursor and record.
SCOTT@orcl12c> -- table:
SCOTT@orcl12c> CREATE TABLE Team
2 (Team_ID integer,
3 Tname varchar(15),
4 Rank integer,
5 City varchar(20),
6 Championships integer)
7 /
Table created.
SCOTT@orcl12c> -- sequence:
SCOTT@orcl12c> CREATE SEQUENCE team_seq
2 /
Sequence created.
SCOTT@orcl12c> -- package specification with function declaration:
SCOTT@orcl12c> CREATE OR REPLACE PACKAGE Team_pkg
2 AS
3 FUNCTION add
4 (name Team.Tname%type,
5 city Team.City%type)
6 RETURN NUMBER;
7 END Team_pkg;
8 /
Package created.
SCOTT@orcl12c> show errors
No errors.
SCOTT@orcl12c> -- package body with function:
SCOTT@orcl12c> CREATE OR REPLACE PACKAGE BODY Team_pkg
2 AS
3 FUNCTION add
4 (name Team.Tname%type,
5 city Team.City%type)
6 RETURN NUMBER
7 AS
8 countVariable INTEGER;
9 name_in_use EXCEPTION;
10 theID NUMBER;
11 BEGIN
12 SELECT count(*) INTO countVariable FROM TEAM WHERE Tname = name;
13 IF countVariable = 1 THEN
14 RAISE name_in_use;
15 ELSE
16 INSERT INTO Team
17 (Team_id, Tname, City)
18 VALUES
19 (team_seq.NEXTVAL, name, city)
20 RETURNING team_id INTO theid;
21 RETURN theID;
22 END IF;
23 EXCEPTION
24 WHEN name_in_use THEN
25 RAISE_APPLICATION_ERROR (-20001, 'This name is already in use');
26 END add;
27 END Team_pkg;
28 /
Package body created.
SCOTT@orcl12c> show errors
No errors.
SCOTT@orcl12c> -- test of valid inputs:
SCOTT@orcl12c> DECLARE
2 v_id INTEGER;
3 BEGIN
4 v_id := team_pkg.add ('Name1', 'City1');
5 DBMS_OUTPUT.PUT_LINE (v_id);
6 END;
7 /
1
PL/SQL procedure successfully completed.
SCOTT@orcl12c> DECLARE
2 v_id INTEGER;
3 BEGIN
4 v_id := team_pkg.add ('Name2', 'City2');
5 DBMS_OUTPUT.PUT_LINE (v_id);
6 END;
7 /
2
PL/SQL procedure successfully completed.
SCOTT@orcl12c> -- test of duplicate that should raise error:
SCOTT@orcl12c> DECLARE
2 v_id INTEGER;
3 BEGIN
4 v_id := team_pkg.add ('Name2', 'City2');
5 DBMS_OUTPUT.PUT_LINE (v_id);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-20001: This name is already in use
ORA-06512: at "SCOTT.TEAM_PKG", line 25
ORA-06512: at line 4
SCOTT@orcl12c> -- results:
SCOTT@orcl12c> SELECT * FROM team
2 /
TEAM_ID TNAME RANK CITY CHAMPIONSHIPS
---------- --------------- ---------- -------------------- -------------
1 Name1 City1
2 Name2 City2
2 rows selected.
|
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634225 is a reply to message #634222] |
Fri, 06 March 2015 00:01 |
|
MiraclePlease
Messages: 19 Registered: March 2015
|
Junior Member |
|
|
Wow thank you so much! I will try to look at this and test this for myself! This is copy pasted from my homework requirement
Function 'add( )'. This function takes as parameters a team name and city and attempts to add
this to the TEAM table. If the team name is already in the table, the new record will not be
inserted and the name_in_use exception is raised instead. Otherwise, if the team name is new,
then the call will succeed and the function will return an integer which is the new record's ID. To
generate new ID's, this function uses the sequence defined above.
EDIT: I also think he said we didnt have to make the "EXCEPTION" Part of it. Because my original code before I even posted here had that exception part and he said that wasnt needed in the code.
[Updated on: Fri, 06 March 2015 00:01] Report message to a moderator
|
|
|
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634267 is a reply to message #634263] |
Fri, 06 March 2015 16:27 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Your just need to open and close your cursor, as demonstrated below.
SCOTT@orcl12c> CREATE OR REPLACE PROCEDURE displayInfo
2 (ID Team.Team_ID%type)
3 AS
4 TeamRecord Team%ROWTYPE;
5 CURSOR TeamCursor IS SELECT * FROM Team WHERE Team_ID = ID;
6 BEGIN
7 OPEN TeamCursor;
8 FETCH TeamCursor INTO TeamRecord;
9 DBMS_OUTPUT.PUT_LINE
10 ('Team ID: ' || TeamRecord.Team_ID
11 || ', Team Name: ' || TeamRecord.Tname
12 || ', Rank: ' || TeamRecord.Rank
13 || ', City' || TeamRecord.City
14 || ', Championships:' || TeamRecord.Championships );
15 CLOSE TeamCursor;
16 END displayInfo;
17 /
Procedure created.
SCOTT@orcl12c> SHOW ERRORS
No errors.
SCOTT@orcl12c> EXEC displayInfo (1)
Team ID: 1, Team Name: Name1, Rank: , CityCity1, Championships:
PL/SQL procedure successfully completed.
SCOTT@orcl12c> EXEC displayInfo (2)
Team ID: 2, Team Name: Name2, Rank: , CityCity2, Championships:
PL/SQL procedure successfully completed.
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634269 is a reply to message #634267] |
Fri, 06 March 2015 16:57 |
|
MiraclePlease
Messages: 19 Registered: March 2015
|
Junior Member |
|
|
That seemed to have fixed it, but now I get this error.
11/8 PLS-00323: subprogram or cursor 'TEAMCURSOR' is declared in a
package specification and must be defined in the package body
And line 11 of my code is this
10 BEGIN
11 SELECT count(*) INTO countVariable FROM TEAM WHERE Tname = name;
But I didnt use TeamCursor there so I dont know what it's problem is. (That line is from the add function)
FUNCTION ADD(name team.tname%TYPE,
city team.city%TYPE)
RETURN NUMBER
AS
theid NUMBER;
countvariable INTEGER;
name_in_use EXCEPTION;
BEGIN
SELECT Count(*)
INTO countvariable
FROM team
WHERE tname = name;
IF countvariable = 1 THEN
RAISE name_in_use;
ELSE
INSERT INTO team
(team_id,
tname,
city)
VALUES (team_seq.NEXTVAL,
name,
city)
returning team_id INTO theid;
RETURN theid;
END IF;
EXCEPTION
WHEN name_in_use THEN
Raise_application_error (-20001, 'This name is already in use');
END ADD;
*BlackSwan formatted the code using URL below. Please do so yourself in the future.
http://www.orafaq.com/forum/t/174502/
[Updated on: Fri, 06 March 2015 17:18] by Moderator Report message to a moderator
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634274 is a reply to message #634269] |
Fri, 06 March 2015 19:10 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You are getting the error because you have some strange declaration of TeamCursor in your package, with no corresponding declaration in the package body.
You seem to be trying to mix and match different methods and using the same names and incorrect syntax that is causing confusion. The following deals with just the DisplayInfo procedure and eliminates everything else to avoid confusion.
You had this, which was working properly, with everything within the procedure:
SCOTT@orcl12c> CREATE OR REPLACE PACKAGE Team_pkg AS
2 PROCEDURE displayInfo(ID Team.Team_ID%type);
3 END Team_pkg;
4 /
Package created.
SCOTT@orcl12c> show errors
No errors.
SCOTT@orcl12c> CREATE OR REPLACE PACKAGE BODY Team_pkg
2 AS
3 PROCEDURE displayInfo(ID Team.Team_ID%type)
4 AS
5 TeamRecord Team%ROWTYPE;
6 CURSOR TeamCursor IS SELECT * FROM Team WHERE Team_ID = ID;
7 BEGIN
8 OPEN TeamCursor;
9 FETCH TeamCursor INTO TeamRecord;
10 DBMS_OUTPUT.PUT_LINE('Team ID: '|| TeamRecord.Team_ID || ', Team Name: ' || TeamRecord.Tname || ', Rank: ' || TeamRecord.Rank || ', City' || TeamRecord.City || ', Championships:' || TeamRecord.Championships );
11 CLOSE TeamCursor;
12 END displayInfo;
13 END Team_pkg;
14 /
Package body created.
SCOTT@orcl12c> show errors
No errors.
SCOTT@orcl12c> EXEC team_pkg.displayinfo (1)
Team ID: 1, Team Name: Name1, Rank: , CityCity1, Championships:
PL/SQL procedure successfully completed.
I don't know whether you are trying to unnecessarily complicate it or your instructor is having you do so, in order to have you use other methods, but you seem to be trying to add a cursor of some sort and record of some sort in the package specification. If you want to do that, then you need to do something like this, declaring the types in the package specification, then declaring variables of those types in the procedure.
SCOTT@orcl12c> CREATE OR REPLACE PACKAGE Team_pkg AS
2 Type TeamRecord_typ IS RECORD
3 (Team_ID integer,
4 Tname varchar(15),
5 Rank integer,
6 City varchar(20),
7 Championships integer);
8 TYPE TeamCursor_typ IS REF CURSOR RETURN TeamRecord_typ;
9 PROCEDURE displayInfo(ID Team.Team_ID%type);
10 END Team_pkg;
11 /
Package created.
SCOTT@orcl12c> show errors
No errors.
SCOTT@orcl12c> CREATE OR REPLACE PACKAGE BODY Team_pkg
2 AS
3 PROCEDURE displayInfo(ID Team.Team_ID%type)
4 AS
5 TeamCursor TeamCursor_typ;
6 TeamRecord TeamRecord_typ;
7 BEGIN
8 OPEN TeamCursor FOR SELECT * FROM Team WHERE Team_ID = ID;
9 FETCH TeamCursor INTO TeamRecord;
10 DBMS_OUTPUT.PUT_LINE('Team ID: '|| TeamRecord.Team_ID || ', Team Name: ' || TeamRecord.Tname || ', Rank: ' || TeamRecord.Rank || ', City' || TeamRecord.City || ', Championships:' || TeamRecord.Championships );
11 CLOSE TeamCursor;
12 END displayInfo;
13 END Team_pkg;
14 /
Package body created.
SCOTT@orcl12c> show errors
No errors.
SCOTT@orcl12c> EXEC team_pkg.displayinfo (1)
Team ID: 1, Team Name: Name1, Rank: , CityCity1, Championships:
PL/SQL procedure successfully completed.
If you have anything of the same names in other parts of your code, then you need to remove them or change the names to avoid conflicts.
|
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634277 is a reply to message #634276] |
Fri, 06 March 2015 21:21 |
|
MiraclePlease
Messages: 19 Registered: March 2015
|
Junior Member |
|
|
Mini update. I managed to do two other functions, which is great, but I am stuck again. I tried fiddling a bit with it, and I think I know what it is, but I dont know WHY that is?
Basically, I declared a 'getTeam' which you send in an ID and then it checks from Team_ID and is supposed to return a TEAMRECORD RECORD. Which, in the package specification it works perfectly!
FUNCTION getTeam
(ID Team.Team_ID%type)
RETURN TeamRecord;
But in the package body it doesnt work, I think its because of 'RETURN TeamRecord', but I dont know why that would be a problem if it works in the package specification. However I suppose it has to be since it keeps giving 'deformed' error.
Package body
FUNCTION getTeam(
ID Team.Team_ID%type)
RETURN TeamRecord
AS
TeamRecord Team%ROWTYPE;
CURSOR TeamCursor IS SELECT * FROM Team WHERE Team_ID = ID;
countVariable integer;
team_not_found EXCEPTION;
BEGIN
OPEN TeamCursor;
SELECT count(*) INTO countVariable FROM TEAM WHERE Team_ID = ID;
if countVariable = 1 THEN
FETCH TeamCursor INTO TeamRecord;
RETURN TeamRecord;
ELSE
RAISE team_not_found;
END IF;
EXCEPTION
WHEN team_not_found THEN
RAISE_APPLICATION_ERROR (-20002, 'This team was not found!');
CLOSE TeamCursor;
END getTeam;
and the errors
21/10 PLS-00323: subprogram or cursor 'GETTEAM' is declared in a
package specification and must be defined in the package body
84/1 PL/SQL: Item ignored
86/8 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
But I think the specification is fine? So I am guessing it's the 'malformed' and 'incomplete' part? Any help is loved of course!
|
|
|
|
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634309 is a reply to message #634277] |
Sat, 07 March 2015 03:41 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following applies sss111ind's example, with slight changes, using your team table and such, instead of the emp table and such.
SCOTT@orcl12c> CREATE TABLE Team
2 (Team_ID integer,
3 Tname varchar(15),
4 Rank integer,
5 City varchar(20),
6 Championships integer)
7 /
Table created.
SCOTT@orcl12c> INSERT INTO team VALUES (1, 'Name1', null, 'City1', null)
2 /
1 row created.
SCOTT@orcl12c> CREATE OR REPLACE PACKAGE team_pkg
2 AS
3 Type TeamRecord_typ IS RECORD
4 (Team_ID integer,
5 Tname varchar(15),
6 Rank integer,
7 City varchar(20),
8 Championships integer);
9 FUNCTION getTeam
10 (ID Team.Team_ID%type)
11 RETURN TeamRecord_typ;
12 END team_pkg;
13 /
Package created.
SCOTT@orcl12c> SHOW ERRORS
No errors.
SCOTT@orcl12c> CREATE OR REPLACE PACKAGE BODY team_pkg
2 AS
3 FUNCTION getTeam
4 (ID Team.Team_ID%type)
5 RETURN TeamRecord_typ
6 AS
7 countVariable integer;
8 CURSOR TeamCursor IS SELECT * FROM Team WHERE Team_ID = ID;
9 teamrecord teamrecord_typ;
10 team_not_found EXCEPTION;
11 BEGIN
12 SELECT count(*) INTO countVariable FROM TEAM WHERE Team_ID = ID;
13 IF countVariable = 1 THEN
14 OPEN TeamCursor;
15 FETCH TeamCursor INTO TeamRecord;
16 RETURN TeamRecord;
17 CLOSE TeamCursor;
18 ELSE
19 RAISE team_not_found;
20 END IF;
21 EXCEPTION
22 WHEN team_not_found THEN
23 RAISE_APPLICATION_ERROR (-20002, 'This team was not found!');
24 END getTeam;
25 END team_pkg;
26 /
Package body created.
SCOTT@orcl12c> SHOW ERRORS
No errors.
SCOTT@orcl12c> DECLARE
2 ID NUMBER;
3 v_Return TEAM_PKG.TEAMRECORD_TYP;
4 BEGIN
5 ID := 1;
6 v_Return := TEAM_PKG.GETTEAM (ID => ID);
7 DBMS_OUTPUT.PUT_LINE ('Team: ' || v_Return.tname);
8 DBMS_OUTPUT.PUT_LINE ('City: ' || v_return.city);
9 END;
10 /
Team: Name1
City: City1
PL/SQL procedure successfully completed.
SCOTT@orcl12c> DECLARE
2 ID NUMBER;
3 v_Return TEAM_PKG.TEAMRECORD_TYP;
4 BEGIN
5 ID := 3;
6 v_Return := TEAM_PKG.GETTEAM (ID => ID);
7 DBMS_OUTPUT.PUT_LINE ('Team: ' || v_Return.tname);
8 DBMS_OUTPUT.PUT_LINE ('City: ' || v_return.city);
9 END;
10 /
DECLARE
*
ERROR at line 1:
ORA-20002: This team was not found!
ORA-06512: at "SCOTT.TEAM_PKG", line 23
ORA-06512: at line 6
SCOTT@orcl12c>
[Updated on: Sat, 07 March 2015 03:41] Report message to a moderator
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 13:44:17 CDT 2024
|