What is the problem with this stored procedure?

Wallace · *nix forums beginner Joined: 19 Apr 2005 Posts: 20

Hai All,

Can anyone point out what is problem with this following stored
procedure :

CREATE OR REPLACE PROCEDURE PRINCE.PROC_EMP AS
DECLARE
TYPE collectionset IS TABLE OF Employee%ROWTYPE;
collections collectionset;
BEGIN
SELECT * BULK COLLECT INTO collections FROM Employee;
FOR i IN collections.FIRST .. collections.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(collections(i).ID);
END LOOP;
END;

when i compile i am getting the following errors:

1. Error(2,1): PLS-00103: Encountered the symbol "DECLARE" when
expecting one of the following: begin function package pragma
procedure subtype type use <an identifier> <a double-quoted
delimited-identifier> form current cursor external language The
symbol "begin" was substituted for "DECLARE" to continue.

2. Error(11,4): PLS-00103: Encountered the symbol "end-of-file" when
expecting one of the following: begin case declare end exception
exit for goto if loop mod null pragma raise return select update
while with <an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert open
rollback savepoint set sql execute commit forall merge pipe

I am new to OracleXE... Please help...

looking forward for the response...

LokalST · *nix forums beginner Joined: 11 May 2006 Posts: 2

"Wallace" <princevictor.moses@gmail.com> wrote in message
news:1147332345.314376.297250@j33g2000cwa.googlegroups.com...

Wallace · *nix forums beginner Joined: 19 Apr 2005 Posts: 20

Thanx for ur reponse....

Thats working fine....

Now it is telling error in "
DBMS_OUTPUT.PUT_LINE(collections(i).COSA_FORMAL_ID); "

Error :
1. Error(8,3): PL/SQL: Statement ignored
2. Error(8,39): PLS-00302: component 'COSA_FORMAL_ID' must be declared

Also tell me the diff b/t the AS DECLARE and IS...

looking forward for the response.....

LokalST · *nix forums beginner Joined: 11 May 2006 Posts: 2

"Wallace" <princevictor.moses@gmail.com> wrote in message
news:1147336269.084363.56450@j73g2000cwa.googlegroups.com...

Wallace · *nix forums beginner Joined: 19 Apr 2005 Posts: 20

Thank u very much....

Wallace · *nix forums beginner Joined: 19 Apr 2005 Posts: 20

Hai...

I am able to get a collection of rows using BULK COLLECT INTO.

When i try to insert this collection into another table as follows
" "INSERT INTO XPRS$UD_COSA_FORMAL_T(UD_COSA_FORMAL_ID,
COSA_FORMAL, KORT_BETEGNELSE, BETEGNELSE, STARTDATO, SLUTDATO,
UGYLDIG_JN, UDTREK_ID, OPDAT_BRUGER, OPDAT_DATO,
XPRS$UD_COSA_FORMAL_T.VERSION) VALUES(collections(i).COSA_FORMAL_ID,
collections(i).COSA_FORMAL, collections(i).KORT_BETEGNELSE,
collections(i).BETEGNELSE, collections(i).STARTDATO,
collections(i).SLUTDATO, 'N', 0, collections(i).OPDAT_BRUGER,
collections(i).OPDAT_DATO, 0); "

but it is telling
" ORA-02291: integrity constraint (PRINCE.UD_COSA_FORMAL_FK2)
violated - parent key not found
ORA-06512: at "PRINCE.PROC_COSA", line 9
ORA-06512: at line 2 "

UD_COSA_FORMAL_ID is the primary key...
What is the problem?

Thanx in advance...

Looking forward for the response..

Mark C. Stock · *nix forums Guru Joined: 05 May 2005 Posts: 730

"Wallace" <princevictor.moses@gmail.com> wrote in message
news:1147336269.084363.56450@j73g2000cwa.googlegroups.com...
: Thanx for ur reponse....
:
: Thats working fine....
:
: Now it is telling error in "
: DBMS_OUTPUT.PUT_LINE(collections(i).COSA_FORMAL_ID); "
:
: Error :
: 1. Error(8,3): PL/SQL: Statement ignored
: 2. Error(8,39): PLS-00302: component 'COSA_FORMAL_ID' must be declared
:
: Also tell me the diff b/t the AS DECLARE and IS...
:
: looking forward for the response.....
:

either AS or IS are OK.... the problem is DECLARE -- it is only used for
anonymous blocks, and is implied for stored procedures (actually replaced by
the procedure specification)

make sure you spend some time reading the PL/SQL user's guide, Application
Developer's Guides, and other items recently mentioned in a "Where to start"
post.

++ mcs

psoug · *nix forums Guru Joined: 15 May 2005 Posts: 3492

Wallace wrote:

Google