Home » SQL & PL/SQL » SQL & PL/SQL » Need help with an SP
Need help with an SP [message #278004] Thu, 01 November 2007 07:25 Go to next message
kshk123
Messages: 1
Registered: November 2007
Location: India
Junior Member
Hi frnds,

I am rather new in writing stored procedures. I havin problem with the following SP.

**************************************************
DROP PROCEDURE XXX ;
CREATE OR REPLACE TYPE YYY is table OF VARCHAR2(16);
CREATE OR REPLACE TYPE ZZZ is table OF VARCHAR2(21);
CREATE OR REPLACE TYPE WWW is table OF VARCHAR2(4);

CREATE OR REPLACE PROCEDURE InsertRecord (AAA IN YYY,
BBB IN ZZZ,
CCC IN WWW)
AS
BEGIN
DECLARE
ERRMSG VARCHAR2(300) ;
BEGIN

FORAll SerialCount IN AAA.FIRST .. AAA.LAST
--loop
InsRec (AAA(SerialCount),
BBB(SerialCount),
CCC(SerialCount)
--end loop;


EXCEPTION When OTHERS THEN
ERRMSG := 'Exception in InsertRecord '|| SUBSTR(SQLERRM, 1, 200) ;
INSERT INTO A_CHECKPOINT VALUES (ERRMSG, SYSDATE) ;
COMMIT ;
RAISE ;
END ;
END ;
/
**************************************************

The problem is when i try to compile the above SP, i get the following compilation error:

"PLS-00103: Encountered the symbol "CREATE" " for the 2nd CREATE after DROP (line 3).

I tried using execute immediate, but then i get another error
"PLS-00905: object ***.YYY is invalid" in the line "CREATE OR REPLACE PROCEDURE InsertRecord".

I need to solve this problem urgently. It wud be grt if someone can help.

Regards,
Kaushik
Re: Need help with an SP [message #278005 is a reply to message #278004] Thu, 01 November 2007 07:34 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

2/
create type statement ends with a / alone in a line

3/
Change that "when others": there is an error, so you commit the transaction. Don't you think there is a problem doing this?

By the way "FORALL" is not a loop, it is one statement.


Regards
Michel

[Updated on: Thu, 01 November 2007 07:36]

Report message to a moderator

Previous Topic: complex query
Next Topic: Order By help needed
Goto Forum:
  


Current Time: Sun Dec 11 04:23:05 CST 2016

Total time taken to generate the page: 0.08680 seconds