sql - Inserting a resultset into a Table in Oracle -

Hey,

I have to write a procedure that inserts a select group in a selection statement Table One of my co-workers was earlier similar to copying from one table to another, their statement was similar:

  can be created or process co-worker (INT in PID) Choose Table1_PROCESSED to check in Table1 as * Start ID where the id = replace PID; Delete table id id = pid; And co-workers; /  

The two tables mentioned here get the same structure (actually the table 1_progress is just a copy of Table 1). So I thought, "Hey! I have got a result from my selection - so I do a bit to not adjust it!" So I created my table like this:

  Matt: TimeID (number) | NAME (varchar2 - 128) ----------------------------------- VALUE VALUE VALUE | VALUE VALUE | Price  

and thus my process:

  can create or reproduce the process at the date myProcedure (pdate, pJobtype number in default 3, the number is in default 4 PTasktype, numeral date pJobstatus 1, default date pTaskstatus 4) as default date; Select TO_DATE (TO_CHAR (TO_DATE (pdate, 'DD.MM.YYYYHH24: MI: SS'), 'DD.MM.YYYY', 'DD.MM.YYYY') BEGIN Dual to pformateddate; MyTable (TIMEID, name) Include in Function_GETTIMEID selection (TO_DATE (st, 'DD.MM.YYYY HH24') TIMEID, TO_CHAR (ext) (from (select TO_CHAR arch_job.exec_start name, 'DD.MM.YYYY HH24 ') st, file.name ext, left arch_task.job_id = arch_job.id where arch_task_file.Task_ID = arch_task.id on arch__file.file_id = file.id left in arch_task when joining arch_job in the file Join count (file.id) arch_task_file to cnt arch_job.exec_start & gt; pformateddate and arch_job.exec_end & lt; pformateddate + 1 and arch_job.jobtype_id = pJobtype and arch_job.jobstatus_id = pJobstatus and arch_task.Tasktype_ID = PTT type and arch_task.Taskstatus_ID = PTaskstatus group By file.name, to_char (arch_job.exec_start, 'ddmm.YYYYhh24')); And micecraft; /  

The results of large selection-details alone look like this:

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

But if I execute this process and give it a dummydate (date like sysdate-12 or '16 .7.2010 10:32:50 ') then my Todd- " The process is complete "My table remains empty ...! But as stated before the big selection-statement, the result should not be trying to get a blank result ...! Can anyone tell me why my process does not work?

Thanks for all useful answers =)

Greetz

PS:

  Choose TO_DATE (TO_CHAR (TO_DATE (pdate , 'DD.MM.YYYY HH24: MI: SS'), 'D.D.M.YYYYY', 'DD.MMYYYYY');  

It is necessary to reduce the pDate-value! I tested it so that it also works and you can ignore it in whole logic. It's just to give you a whole picture of the situation!

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

"I run my SXL in my Todd worksheet (or whatever) and it works. But when I include it in a different context - like A stored procedure - what does this work? "

What gives two statements neither is a wrong transcript, perhaps one has been omitted or Extra added. The most likely of errors is substitution in the worksheet with parameters in the stored procedure.

Obviously I can not tell you where the difference is. I can do whatever I can, closely inspect two SQL statements and urge you to find out the discrepancy.

If you can not really get any difference, then you have to debug your code. The fastest way to start is with the devil's debugger: add this line after the insert statement:

  dbms_output.put_line ('lines inserted' 'to_char (sql% rowcount)) ;  

You must enable DBMS_OUTPUT in the TOD; There is a tab somewhere for this, at least it will tell you whether the query is returning zero rows or your process lines are being cast and you are not seeing them for some reason these are two different problems. .