Thank you very much for educating me. This is the type of example that I have been looking for.

I am new to this web site and I am not sure if I should place my follow up question here.

I have been working with the code that you provided.  It compiles sucessfully.  However, when I run it I get the following message:

13:18:21 SQL> exec do_insert( to_date( '200001','yyyymm' ) );
BEGIN do_insert( to_date( '200001','yyyymm' ) ); END;

*
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at "ORASOPS.DO_INSERT", line 25
ORA-06512: at line 1

I have been trying to debug it and I am getting nowhere.  Please help.  I am very frustrated with myself.  Could it be an issue with the privileges set to the logon that I am using? 

Does this procedure also need to dynamically "add" a column on the fly -- for
the most recent month?


Yes. This code is part of a larger script. In the script I drop the table and recreate it following your example. Then I want to insert into it following your example.


CREATE OR REPLACE PROCEDURE DO_INSERT1 (P_START_MONTH )
AS
L_STMT LONG;
L_MONTH_TO DATE DEFAULT ADD_MONTHS( TRUNC(SYSDATE,'MONTH'), -2 );
L_CURR_MONTH DATE DEFAULT TO_DATE(P_START_MONTH,'YYYYMM');

BEGIN
L_STMT := 'CREATE TABLE LJC_TABLE (
ACM_MSTRACCOUNTKEY NUMBER(10),
ACM_MEDPROVNO VARCHAR2(14),
ACM_EXTPHYSRXID NUMBER(10),
SMPL_ID NUMBER(16),
SMPL_DESC_TEXT VARCHAR2(80),' || CHR(10);

FOR I IN 0 .. MONTHS_BETWEEN( L_MONTH_TO, L_CURR_MONTH )
LOOP
L_STMT := L_STMT ||
'SAMPLES_MADE_''' ||
TO_CHAR(L_CURR_MONTH, 'YYYYMM' ) ||
'''NUMBER(5),' || CHR(10);

L_CURR_MONTH := ADD_MONTHS(L_CURR_MONTH,1);
END LOOP;

L_STMT := RTRIM(L_STMT,',') || ' )';

EXECUTE IMMEDIATE L_STMT;
END;




create or replace procedure do_insert( p_start_month in date )
as
l_stmt long;
l_month_to date default add_months( trunc(sysdate,'month'), -1 );
l_curr_month date default p_start_month;
begin
l_stmt := 'insert into ljc_table
select CM_MSTRACCOUNTKEY, ACM_MEDPROVNO,
ACM_EXTPHYSRXID, SMPL_ID, SMPL_DESC_TEXT,';

for i in 0 .. months_between( l_month_to, p_start_month )
loop
l_stmt := l_stmt ||
'sum((decode(year_month,''' ||
to_char(l_curr_month, 'yyyymm' ) ||
''', sum_samples,0))) samples_made_' ||
to_char(l_curr_month,'yyyymm') || ',';
l_curr_month := add_months(l_curr_month,1);
end loop;

l_stmt := rtrim(l_stmt,',') || ' FROM LJC_SAMPLES_PREPIVOT
WHERE SMPL_ID=1900000520
GROUP BY ACM_MSTRACCOUNTKEY, ACM_MEDPROVNO, ACM_EXTPHYSRXID, SMPL_ID,
SMPL_DESC_TEXT
ORDER BY ACM_MSTRACCOUNTKEY, ACM_MEDPROVNO, ACM_EXTPHYSRXID, SMPL_ID,
SMPL_DESC_TEXT';

execute immediate l_stmt;
end;


Something tells me that what you told me works for the second part (the inserting) but that for the creation of the table I need to do something different.

I now have it working.

Thank you so very much for your help!!! You are an Oracle Guardian Angel.

I have also learned that it helps if your logon is set up right. This is what my DBA wrote to me:
"One of the problems with Execute Immediate is you can use commands like create table only if the user is granted that privilege directly rather than through a role, which you already had. I gave create table privilege to you directly, which should take care of the problem. "

Tom:
Refering to the original question and your response:

I did a similar crosstab query sometime back. And I was faced with the same problem of dynamically generating the timeperiod for the insert. I created a function which returns the corresponding time period (current month, prev month, etc....), but I found that the query became sooo slow when using the function. The reason was the recursive SQL performed for the function. Eventhough the function was very simple, it was called millions of times which probably contributed to the slowdown (am I correct?). I worked around that using macro substitution in SQLPlus. But as soon as I saw your function, I adopted it.

It was little unexpected for me to have the query slow down somuch due to the usage of function. Can you please comment on that? Thanks again your beautiful solution!

Now my users want me to do the same thing at a day level instead of a month level.

Following your example, I have the below:

CREATE OR REPLACE PROCEDURE LJC_FAR_TEST
AS
STMT1 LONG;
P_START_DAY DATE DEFAULT TO_DATE( '20020101','YYYYMMDD');
L_DAY_TO DATE DEFAULT ADD_MONTHS( TRUNC(SYSDATE,'DAY'), 0 );
L_CURR_DAY1 DATE DEFAULT P_START_DAY;
---
BEGIN
---
STMT1 := 'CREATE TABLE LJC_FAR_SUM_TABLE_AT_TERRLEVEL (
TRM_NAME VARCHAR2(15),
CBU VARCHAR2(15),
ABM VARCHAR2(15),
RABM VARCHAR2(15),
DABM VARCHAR2(15),
DESCRIPTION VARCHAR2(80),';
FOR I IN 0 .. MONTHS_BETWEEN( L_DAY_TO, P_START_DAY )
LOOP
STMT1 := STMT1 ||
'DATE_' ||
TO_CHAR(L_CURR_DAY1, 'YYYYMMDD' ) ||
' NUMBER(5),';
L_CURR_DAY1 := ADD_MONTHS(L_CURR_DAY1,1);
END LOOP;
STMT1 := RTRIM(STMT1,',') || ' )';
---
EXECUTE IMMEDIATE STMT1;
---
COMMIT;
---
END;

Basically, I want to create date fields like the following:
DATE_20020801 NUMBER(5)
DATE_20020802 NUMBER(5)
DATE_20020803 NUMBER(5)
DATE_20020804 NUMBER(5)
DATE_20020805 NUMBER(5)
DATE_20020806 NUMBER(5)
DATE_20020807 NUMBER(5)
DATE_20020808 NUMBER(5)
DATE_20020809 NUMBER(5)
DATE_20020810 NUMBER(5)
DATE_20020811 NUMBER(5)
DATE_20020812 NUMBER(5)
DATE_20020813 NUMBER(5)
DATE_20020814 NUMBER(5)
DATE_20020815 NUMBER(5)
DATE_20020816 NUMBER(5)
DATE_20020817 NUMBER(5)
DATE_20020818 NUMBER(5)
DATE_20020819 NUMBER(5)
DATE_20020820 NUMBER(5)
DATE_20020821 NUMBER(5)
DATE_20020822 NUMBER(5)
DATE_20020823 NUMBER(5)
DATE_20020824 NUMBER(5)
DATE_20020825 NUMBER(5)
DATE_20020826 NUMBER(5)
DATE_20020827 NUMBER(5)
DATE_20020828 NUMBER(5)
DATE_20020829 NUMBER(5)
DATE_20020830 NUMBER(5)
DATE_20020831 NUMBER(5)
DATE_20020901 NUMBER(5)
DATE_20020902 NUMBER(5)
DATE_20020903 NUMBER(5)
DATE_20020904 NUMBER(5)
DATE_20020905 NUMBER(5)
DATE_20020906 NUMBER(5)
DATE_20020907 NUMBER(5)
DATE_20020908 NUMBER(5)
DATE_20020909 NUMBER(5)
DATE_20020910 NUMBER(5)
DATE_20020911 NUMBER(5)
DATE_20020912 NUMBER(5)
DATE_20020913 NUMBER(5)
DATE_20020914 NUMBER(5)
DATE_20020915 NUMBER(5)
DATE_20020916 NUMBER(5)
DATE_20020917 NUMBER(5)
DATE_20020918 NUMBER(5)
DATE_20020919 NUMBER(5)
DATE_20020920 NUMBER(5)
DATE_20020921 NUMBER(5)
DATE_20020922 NUMBER(5)


My code above obviously does not do it at the day level. I tried looking for functions to use. I found the NEXT_DAY and LAST_DAY functions. Would you use these? I am afraid that I am making this more difficult then it needs to be.

Thanks!!!

Tom,
If I wanted to search for a table in all_objects, if foound then do an insert append else do a CTAS supplying the tablespace and initial next.
do yu have elegant solution

Hi Tom,
I have to write a query which needs to give a report like a cross-tab report where the column is generated dynamically. The column is a date column where I know the startdate and end date and there is difference of 7 days.
I need qty data against those columns.

I want to give you the data but its coming coming in a format.
Please see the data format on the link.
</code> http://www.orafaq.com/forum/r/msg_id/204341/86219/ <code>

There are three other columns.
First column gives an ID, second gives respid and third gives description filed data. The data under date columns is the quantity data based on a date columnwise and on ID, respID and Descr row wise.
The dates column are created dynamically and the data needs to be displayed under them dynamically.
Please help me to work on this report.

Thanks,
Soni