-
ora-6510 ora-6512 ora-1403 errors
Hi all,
I want to insert records in table B from table A if the primary key of table A is not found in table B. So, I wrote foll. PL/SQL code.
declare
l_request_id NUMBER;
g_end_program EXCEPTION;
CURSOR
reqs
IS SELECT REQUEST_ID FROM table_A
WHERE
ACTUAL_COMPLETION_DATE <= SYSDATE
AND STATUS_CODE = 'C'
AND PHASE_CODE = 'C'
AND PRINTER != 'noprint'
AND NUMBER_OF_COPIES > 0;
BEGIN
FOR cr IN reqs
LOOP
SELECT request_id INTO l_request_id
FROM table_B
WHERE request_id = cr.request_id;
if l_request_id is NULL
then
INSERT INTO table_B from cursor....
end if;
END LOOP;
EXCEPTION
when no_data_found then
raise g_end_program;
END;
/
But, it is giving me
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 26
ORA-01403: no data found
Pl. help.
Thanks in Adv.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
INSERT INTO table_B from cursor....
What is this?
Tamil
-
What's wrong with something like:
Insert Into table_B
SELECT REQUEST_ID, etc, etc
FROM table_A
WHERE
ACTUAL_COMPLETION_DATE <= SYSDATE
AND STATUS_CODE = 'C'
AND PHASE_CODE = 'C'
AND PRINTER != 'noprint'
AND NUMBER_OF_COPIES > 0
and not exists
(Select * From table_B
where table_B.request_id = table_A.request_id)
;
-
Originally posted by tamilselvan
INSERT INTO table_B from cursor....
What is this?
Tamil
if l_request_id is NULL
then
insert into table_B
( REQUEST_ID,
REQUESTED_BY,
PHASE_CODE,
STATUS_CODE,
PROGRAM_APPLICATION_ID,
CONCURRENT_PROGRAM_ID,
NUMBER_OF_COPIES,
PRINTER,
DESCRIPTION,
ACTUAL_COMPLETION_DATE,
ARGUMENT_TEXT)
values
(cr.REQUEST_ID,
cr.REQUESTED_BY,
cr.PHASE_CODE,
cr.STATUS_CODE,
cr.PROGRAM_APPLICATION_ID,
cr.CONCURRENT_PROGRAM_ID,
cr.NUMBER_OF_COPIES,
cr.PRINTER,
cr.DESCRIPTION,
cr.ACTUAL_COMPLETION_DATE,
cr.ARGUMENT_TEXT);
end if;
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
SELECT request_id INTO l_request_id
FROM table_B
WHERE request_id = cr.request_id;
if l_request_id is NULL
This does not do what you want (I think) - it will raise a no_data_found exception if the row doesn't exist.
-
Originally posted by DaPi
This does not do what you want (I think) - it will raise a no_data_found exception if the row doesn't exist.
My requirement is that if record is not found in table_B, then it should insert record with values from cursor.
There is a no_data_found exception handler in the procedure. Still why error occurs.
Guys, I am a beginner in development. Pl. help.
Thanks in Adv.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Originally posted by DaPi
What's wrong with something like:
Insert Into table_B
SELECT REQUEST_ID, etc, etc
FROM table_A
WHERE
ACTUAL_COMPLETION_DATE <= SYSDATE
AND STATUS_CODE = 'C'
AND PHASE_CODE = 'C'
AND PRINTER != 'noprint'
AND NUMBER_OF_COPIES > 0
and not exists
(Select * From table_B
where table_B.request_id = table_A.request_id)
;
-
Originally posted by DaPi
I can use this but I want to know that why my code is not working for the sake of knowledge.
Thanks,
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
The first time you find a row you would insert, you get a no_data_found, which jumps out of the loop, raises your g_end_program exception, which is not handled, so it dies.
Try something like:
Code:
. . .
BEGIN
FOR cr IN reqs
LOOP
BEGIN
SELECT request_id INTO l_request_id
FROM table_B
WHERE request_id = cr.request_id;
EXCEPTION
when no_data_found then
INSERT INTO table_B from cursor....
END;
END LOOP;
COMMIT; ? ? ?
END;
/
-
Originally posted by DaPi
The first time you find a row you would insert, you get a no_data_found, which jumps out of the loop, raises your g_end_program exception, which is not handled, so it dies.
Try something like:
Code:
. . .
BEGIN
FOR cr IN reqs
LOOP
BEGIN
SELECT request_id INTO l_request_id
FROM table_B
WHERE request_id = cr.request_id;
EXCEPTION
when no_data_found then
INSERT INTO table_B from cursor....
END;
END LOOP;
COMMIT; ? ? ?
END;
/
Hi,
With this logic, it gives the foll. error.
ERROR at line 62:
ORA-06550: line 62, column 7:
PLS-00201: identifier 'CR.REQUEST_ID' must be declared
ORA-06550: line 49, column 7:
PL/SQL: SQL Statement ignored
It seems that it is loosing the cursor out side the loop.
Pl. advise.
Thanks,
Last edited by samdba; 01-06-2005 at 10:33 AM.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|