jknj72
asked on
Oracle Insert not working
Im trying to help a coworker figure out why this query isnt working in SQL Developer. Its a procedure in a package and the Insert doesnt work. The Select that is feeding the Insert brings back plenty of records but the Insert doesnt work. Here is the syntax
The first Insert into the Summary table works fine but the Insert into GROUP_QUART_POLDATA table doesnt work???
Any help would be appreciated
Thanks
JK
insert into ERROR_SUMMARY_LOG(LOGID,LOG_MSG, LOG_DATE,LOG_PROCESS_RESULT) values (ERR_ID_TAB_SEQ.NEXTVAL, 'START OF BLD_GROUPID_QUART_SUMMARIES. QUARTER: ' || QUARTERIN || ' ,QTRYEAR: ' || QTRYEARIN || ' ,GROUPID: ' || GROUPIDIN, systimestamp,'1');
commit;
begin
insert into GROUP_QUART_POLDATA ( GROUPID, ERR_ID, ERR_MSG, PERCENTAGE, ERROR_COUNT, ISADQERROR, REJECTEDERR)
select * from (
Select GROUPID
,AB.ERR_ID
,AB.ERR_MSG
,NULL as Error_Percentage
,SUM(AB.TOT_ERRORS) as ERRCOUNT
,AB.ISADQERROR
,AB.REJECTEDERR
from
(Select QTRYEAR
,QUARTER
,DCOCONO
,GROUPID
,ERR_CARNAME
,TOT_ERRORS
,ERR_ID
,ERR_MSG
,REJECTEDERR
,ISADQERROR
from ERROR_SUMMARY_QUARTERLY_TABLE
where
(ERROR_SUMMARY_QUARTERLY_TABLE.QUARTER = QUARTERIN and ERROR_SUMMARY_QUARTERLY_TABLE.QTRYEAR = QTRYEARIN)
and (ERROR_SUMMARY_QUARTERLY_TABLE.GROUPID = GROUPIDIN)) AB
GROUP BY
AB.GROUPID
,AB.ERR_ID
,AB.ERR_MSG
,AB.ISADQERROR
,AB.REJECTEDERR
order by ERRCOUNT desc);
commit;
EXCEPTION
when OTHERS then
P_SUCCESS := 0;
V_CODE := SQLCODE;
V_ERRM := SUBSTR(SQLERRM, 1, 2000);
return;
commit;
end;
The first Insert into the Summary table works fine but the Insert into GROUP_QUART_POLDATA table doesnt work???
Any help would be appreciated
Thanks
JK
Also confirm, don't just assume, that the user running the code has INSERT object privileges if they don't own the table.
ASKER
Taking the Exception out now and will let you know.
The user has permissions...
Thanks
JK
The user has permissions...
Thanks
JK
ASKER
no luck with taking out the exception.. It runs but returns no data to the table(which, by the way, is a temp table created at the beginning of the procedure)
Anything else I can try?
Thanks
JK
Anything else I can try?
Thanks
JK
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok thats why I couldnt see the data....I was looking at the temp table but then I assigned it to a cursor as the output and saw the records were there. Thanks for your help. You got me on the right path...
ASKER
Thats what he was using, Global Temporary table and I couldnt see the data so I thought the insert wasnt working. Now I can see, via the output that it did...
ASKER
thanks again
>>Thats what he was using, Global Temporary table
You create those once and use them forever. If it is created inside the procedure, you are doing it wrong.
>>and I couldnt see the data
If you are using a GTT, only the session that inserts the data can see the data.
You create those once and use them forever. If it is created inside the procedure, you are doing it wrong.
>>and I couldnt see the data
If you are using a GTT, only the session that inserts the data can see the data.
ASKER
I get it now..
A quick try: comment out the return in the exception handler.
Better yet, comment out the entire exception handler and rerun the code. See if it is generating an exception?