Inserting a resultset into a Table in Oracle

Question

Heyho,

I've gotta write a Procedure which Inserts a resultset from a select-statement into a table. A co-worker of mine did something similar before to copy values from one table to another. His statement looks like this:

CREATE OR REPLACE PROCEDURE Co-Worker(
    pId IN INT
)
AS
BEGIN
    INSERT INTO Table1_PROCESSED
    SELECT * FROM Table1
    WHERE ID = pId;

    DELETE FROM Table1
    WHERE ID = pId;

END Co-Worker;
/

The two tables mentioned here got the same structure (in fact table1_processed is just a copy of table 1). So I thought like "Hey! I get a resultset from my select-satement too! So why I just don't adjust it a bit do to the same!" So I created my Table like this:

MyTable:
TIMEID (number) | NAME (varchar2 - 128)
-----------------------------------
VALUE       | VALUE
VALUE       | VALUE
VALUE       | VALUE

and my Procedure like this:

CREATE OR REPLACE procedure MyProcedure(
pdate in date,
pJobtype in number  default 3,
pTasktype in number default 4,
pJobstatus in number default 1,
pTaskstatus in number default 4
)
AS
    pformateddate date;
BEGIN
    Select to_date(to_char(to_date(pdate, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY'), 'DD.MM.YYYY') 
    into pformateddate 
    from dual;
Insert into MyTable (TIMEID, NAME)
Select Function_GETTIMEID(to_date(st, 'DD.MM.YYYY HH24')) TIMEID
       ,to_char(ext) NAME 
from(
    Select to_char(arch_job.exec_start, 'DD.MM.YYYY HH24') st
           ,file.name ext
           , count(file.id) cnt
    from 
         arch_task_file 
             left join file on arch_task_file.File_ID = file.ID
             left join arch_task on arch_task_file.Task_ID = arch_task.ID
             left join arch_job on arch_task.Job_ID = arch_job.ID
    where 
        arch_job.exec_start > pformateddate 
        and arch_job.exec_end <pformateddate + 1 
        and arch_job.jobtype_id = pJobtype 
        and arch_job.jobstatus_id = pJobstatus 
        and arch_task.Tasktype_ID = pTasktype 
        and arch_task.Taskstatus_ID = pTaskstatus
     group by 
         file.name,
           to_char(arch_job.exec_start, 'DD.MM.YYYY HH24'
       )
    );
End MyProcedure;
/

the Result for the large Select-Statement ALONE looks like this:

TIMEID      | NAME
-----------------------------------
VALUE       | VALUE
VALUE       | VALUE
VALUE       | VALUE

But If I execute this procedure and give it a dummydate (sysdate - 12 or a date like '16.07.2010 10:32:50') my Toad-gives my a message "Procedure completed" my table stays empty...! But as said before the large Select-Statement gives results so there shouldn't be a try to insert an empty resultset...! Can anyone tell me why my procedure ain't work?

Thx for every useful answer. =)

Greetz!

P.S.: The

Select to_date(to_char(to_date(pdate, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY'), 'DD.MM.YYYY') 
into pformateddate 
from dual;

is required to shorten the pDate-value! i tested it, so that works too and you can ignore it in the whole logic. It's just here to give you a complete picture of the situation!

Answer 1

You would need to COMMIT in the Toad session where you ran this procedure before you could see that data in the table in any other session, such as the table browser. Did you remember to do that?

Answer 2

Not really relevant to your problem but this

Select to_date(to_char(to_date(pdate, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY'), 'DD.MM.YYYY') 
into pformateddate 
from dual;

is just a long winded way of removing the time element from the passed parameter. This does the same thing:

Select trunc(pdate) 
into pformateddate 
from dual; 

Or indeed as Tony points out, a straightforward assignment:

pformateddate := trunc(pdate);

Answer 3

This is a very common pattern in SQL forums. The pattern is the OP says

"I run this SQL in my TOAD worksheet (or whatever) and it works. But when I include it in a different context - such as a stored procedure - it doesn't work. What gives?"

What gives is that the two statements are not the same. Somewhere there is a mis-transcription. Perhaps a join has been omitted or an extra one added. The most likely source of errors is the replacement of literals in the worksheet with parameters in the stored procedure.

Obviously I cannot tell you where the difference lies. All I can do is urge you to closely inspect the two SQL statements and figure out the discrepancy.

If you really cannot find any difference then you will need to debug your code. The quickest way to start is with the Devil's Debugger. After the insert statement add this line:

dbms_output.put_line('Rows inserted = '||to_char(sql%rowcount));

You'll need to enable DBMS_OUTPUT in TOAD; there's a tab for it somewhere. This will at least tell you whether the query really is returning zero rows or your procedure is inserting rows and you're not seeing them for some reason. Those are two different problems.