ora-6510 ora-6512 ora-1403 errors

[samdba]

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.

[tamilselvan]

INSERT INTO table_B from cursor....

What is this?

Tamil

[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)
;

[samdba]

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;

[DaPi]

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.

[samdba]

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.

[DaPi]

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)
;

[samdba]

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,

[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:

 . . . 
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;
/

[samdba]

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:

 . . . 
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,