Link to home
Start Free TrialLog in
Avatar of jknj72
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

    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;

Open in new window


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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Does it fail consistently?

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?
Also confirm, don't just assume, that the user running the code has INSERT object privileges if they don't own the table.
Avatar of jknj72

ASKER

Taking the Exception out now and will let you know.
The user has permissions...

Thanks
JK
Avatar of jknj72

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72

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...
Avatar of jknj72

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...
Avatar of jknj72

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.
Avatar of jknj72

ASKER

I get it now..