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 Go to next message
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 #634202 is a reply to message #634201] Thu, 05 March 2015 20:53 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Oh! For the record, this has tables created from another .sql file. It has a table named 'TEAM' with the columns Team_ID, Tname, Rank, City, Championships"
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634203 is a reply to message #634201] Thu, 05 March 2015 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
We can't run, test, or debug you code since we don't have your tables.

You obviously read the Posting Guidelines for which I thank you.

sqlplus easily gets confused when extraneous blank lines occur within otherwise valid multi-line statements.
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634204 is a reply to message #634201] Thu, 05 March 2015 21:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
ELSIF expects another condition to follow it. Just change your ELSIF to ELSE. That is just the first error that it is encountering. There may or may not be other errors.

[Updated on: Thu, 05 March 2015 21:02]

Report message to a moderator

Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634206 is a reply to message #634204] Thu, 05 March 2015 21:06 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
I changed it to ELSE and now I have this error

21/5     PLS-00103: Encountered the symbol "ADD" when expecting one of the
         following:
         if


And thats it. Which I hope is progress?

EDIT:But what is wrong with ending it on line 21 that says END add; ?

Edit2:WOW I AM STUPID! Sorry, let me edit some more my code

[Updated on: Thu, 05 March 2015 21:23]

Report message to a moderator

Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634207 is a reply to message #634206] Thu, 05 March 2015 21:17 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
ENDIF; should be END IF;
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634208 is a reply to message #634206] Thu, 05 March 2015 21:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Frequently, the problem is on the line above, not the line number that is listed. It is saying that it encountered something when it was expecting something else. The something else was on the line above. ENDIF should be END IF with a space between END and IF. There may still be other errors. This is progress. This is how you debug, by working through one error at a time.
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634209 is a reply to message #634207] Thu, 05 March 2015 21:28 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Ok I changed it, and now
0/0      PL/SQL: Compilation unit analysis terminated
1/14     PLS-00304: cannot compile body of 'TEAM_PKG' without its
         specification

Can someone please tell me what the second one means, and first?
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634210 is a reply to message #634209] Thu, 05 March 2015 21:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You 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
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634211 is a reply to message #634210] Thu, 05 March 2015 21:51 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Barbara Boehmer wrote on Thu, 05 March 2015 21:47
You 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 #634212 is a reply to message #634211] Thu, 05 March 2015 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/Package
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634213 is a reply to message #634211] Thu, 05 March 2015 22:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Assuming 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.
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634214 is a reply to message #634213] Thu, 05 March 2015 22:06 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Barbara Boehmer wrote on Thu, 05 March 2015 22:01
Assuming 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. Sad

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 #634215 is a reply to message #634201] Thu, 05 March 2015 22:10 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
OMG I GOT THE PACKAGE SPECIFICATION TO COMPILE!!!! Shocked Shocked Shocked Shocked
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634216 is a reply to message #634215] Thu, 05 March 2015 22:13 Go to previous messageGo to next message
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 #634217 is a reply to message #634216] Thu, 05 March 2015 22:19 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Oh wait am I supposed to use a cursor to put that information into a record and put that record into the table?
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634218 is a reply to message #634217] Thu, 05 March 2015 22:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I don't know what you have changed, so please post a current copy of your complete code, including package specification and package body. Please try to post a copy and paste of a run of your code from SQL*Plus complete with line numbers and error messages, something like the example below.


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> 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    BEGIN
  9  	 RETURN NULL;
 10    END add;
 11  END Team_pkg;
 12  /

Package body created.

SCOTT@orcl12c> SHOW ERRORS
No errors.

Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634219 is a reply to message #634218] Thu, 05 March 2015 22:35 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Ok Smile
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 Go to previous messageGo to next message
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 #634222 is a reply to message #634220] Thu, 05 March 2015 23:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is a link to some information and examples of different types of cursors:

http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634225 is a reply to message #634222] Fri, 06 March 2015 00:01 Go to previous messageGo to next message
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 #634226 is a reply to message #634225] Fri, 06 March 2015 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

2 good free books for you:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634227 is a reply to message #634226] Fri, 06 March 2015 00:37 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Michel Cadot wrote on Fri, 06 March 2015 00:30

2 good free books for you:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals


Thank you Smile

Also, I am thinking, re-reading back my homework, that I shockingly understood it wrong. Sad I think I am supposed to only use the cursor when I am given a Team_ID. Otherwise, I dont have to use a cursor nor a 'record'.
Sad
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634263 is a reply to message #634201] Fri, 06 March 2015 15:46 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Sorry to say I am back. I am supposed to make a displayinfo procedure, which uses 'teamRecord' record. Sad So I just tried to lightly code it, but I cant seem to be able to figure out how to declare this on this procedure? I tried to look if you can use 'declare' on procedure but I dont see it. Sad

PROCEDURE displayInfo(ID Team.Team_ID%type)
AS
TeamRecord Team%ROWTYPE;
CURSOR TeamCursor IS 
SELECT * FROM Team WHERE Team_ID = ID;
BEGIN
FETCH TeamCursor INTO TeamRecord;
DBMS_OUTPUT.PUT_LINE('Team ID: '|| TeamRecord.Team_ID || ', Team Name: ' || TeamRecord.Tname || ', Rank: ' || TeamRecord.Rank || ', City' || TeamRecord.City || ', Championships:'  || TeamRecord.Championships );
END displayInfo;
END;


I am trying to grab the row where the ID entered matches a team id in the table. If it matches, put that information into TeamRecord
Then output on the screen the teamrecord.
Can someone tell me how to declare this cursor and if I am using it properly with the teamRecord?
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634267 is a reply to message #634263] Fri, 06 March 2015 16:27 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #634276 is a reply to message #634274] Fri, 06 March 2015 20:36 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Thank you again. I just removed the line
'CURSOR TeamCursor RETURN TeamRecord' and it works now.
I feel like I take 2 steps forward and one step back in understanding how I am doing these parts. But I am making some progress that I am very proud of and really thank you for helping me each time!
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634277 is a reply to message #634276] Fri, 06 March 2015 21:21 Go to previous messageGo to next message
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 #634288 is a reply to message #634277] Sat, 07 March 2015 00:50 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

--TeamRecord emp2%rowtype;
--because of this the error is coming because you have declared twice 1.at return type and 2.at package body
create or replace 
PACKAGE           test_pkg
IS  
    TYPE TeamRecord IS record(ename emp.ename%TYPE,sal emp.sal%TYPE);
    TeamRecord1 TeamRecord;
    FUNCTION getTeam
(ID emp.empno%TYPE)
RETURN TeamRecord;
end;

create or replace 
PACKAGE BODY            test_pkg AS 

FUNCTION getTeam
(ID emp.empno%TYPE)
RETURN TeamRecord
AS

CURSOR TeamCursor IS SELECT ename,sal FROM emp WHERE empno = ID;
countVariable integer;
team_not_found EXCEPTION;
BEGIN
null;
OPEN TeamCursor;
SELECT count(*) INTO countVariable FROM emp WHERE empno = ID;
IF countVariable = 1 THEN
FETCH TeamCursor INTO TeamRecord1;
--TeamRecord:=teamrecord2;
RETURN TeamRecord1;
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;
 end test_pkg;

 --testing
DECLARE
  ID NUMBER;
  v_Return DEVAXISPI.TEST_PKG.TEAMRECORD;
BEGIN
  ID := 7839;

  v_Return := TEST_PKG.GETTEAM(
    ID => ID
  );

DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return.ename||v_return.sal);

END
 

Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634309 is a reply to message #634277] Sat, 07 March 2015 03:41 Go to previous messageGo to next message
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

Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634328 is a reply to message #634309] Sat, 07 March 2015 14:52 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
Wow thank you all so much! I think I got all of problem 1 done (and all of its each 10 parts).
Im on problem 2, but there is something it mentions and I dont know how to accomplish this?
It wants me to generate a 'player_id' but I can NOT use a sequence. it must happen INSIDE of the function where I will add a new 'player'.
How do I do that? I will keep googling for it, but I just want to ask incase anyone knows the exact name of it or what to google.
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634329 is a reply to message #634328] Sat, 07 March 2015 14:55 Go to previous messageGo to next message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
There is no sequence to generate new IDs for the player table, and the
function does not take in the ID either. But this function needs to be able to internally make new IDs that are unique.

[Updated on: Sat, 07 March 2015 15:04]

Report message to a moderator

Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634335 is a reply to message #634329] Sat, 07 March 2015 16:17 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
I can understand an instructor asking for something that is, shall we say, sub-optimal -- just to get the student familiar with various techniques, but it makes no sense to get familiar with a technique that has no reason to exist. SEQUENCES exist for the very purpose of generating a unique number. I fail to understand what could be learned by trying to get the same effect with some home-grown technique. I'd never do it.

I guess one could take systimestamp down to the nano-second and run it through dbms_crypto ... but I doubt that would mathematically guarantee uniqueness.
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634337 is a reply to message #634335] Sat, 07 March 2015 16:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Perhaps the instructor is expecting them to use sys_guid:

http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions153.htm
Re: SQLPLUS PLS-00103 Encountered 'INSERT' [message #634338 is a reply to message #634337] Sat, 07 March 2015 18:50 Go to previous message
MiraclePlease
Messages: 19
Registered: March 2015
Junior Member
I will try maybe emailing my professor about it/sys_guid and what he means and ignoring that part for now, maybe I can get the other parts of that assignment done.
Previous Topic: How to perform a checksum to a table?
Next Topic: How to restrict the inserting a record through a oracle trigger
Goto Forum:
  


Current Time: Thu Mar 28 13:44:17 CDT 2024