Solved

Create Table in PL/SQL Store Procedure

Posted on 2006-11-09
28
478 Views
Last Modified: 2013-12-11
Hi Guys,

I really appreciate if you give me a hand on this. I'm working on a project on which I have to create 2 tables in PL/SQL Store Procedure; however I'm getting compilation errors. Also when I execute the procedure I got the following errors. Here is my code.
Thanks for your help!


CREATE OR REPLACE PROCEDURE CRPROCESS AS
      v_EMCR_T            VARCHAR2(25);
      v_EMCR_T_SLS        VARCHAR2(25);
      v_CommID_T1          VARCHAR2(10);
      v_CommID_T2          VARCHAR2(10);
      v_CampID_T1          VARCHAR2(10);
      v_CampID_T2          VARCHAR2(10);
      v_StartDate      VARCHAR2(15);
      v_EndDate                      VARCHAR2(15);
      

BEGIN
      v_EMCR_T       := &EMCR_T;
      v_CommID_T1        := &CommID_T1;
      v_CommID_T2        := &CommID_T2;
      v_CampID_T1     := &CampID_T1;
      v_CampID_T2      := &CampID_T2;
      v_EMCR_T_SLS        := &EMCR_T_SLS;
      v_StartDate        := &StartDate;
      v_EndDate          := &EndDate;

      


execute immediate 'Drop Table v_EMCR_T;';

execute immediate 'Create Table v_EMCR_T nologging as'
|| 'Select /*+ Parallel (mc, 8)*/'
|| '       distinct mc.ACCOUNT_NUMBER Members_Treat'
|| 'From  member_campaign mc'
|| 'Where mc.COMM_CD IN ('v_CommID_T1','v_CommID_T2')'
|| 'and  mc.CAMP_ID IN ('v_CampID_T1','v_CampID_T2')';

Commit;



execute immediate  'Drop Table v_EMCR_T_SLS;';


execute immediate  'Create Table v_EMCR_T_SLS nologging as'
|| 'Select /*+ Parallel (ph, 8) Parallel (mc, 8) Parallel (t, 8) */'
|| '        distinct t.MEMBERS_TREAT Trans_Members,'
|| '        Count(distinct ph.PURCHASE_ID) Trans,'
|| '        Sum(ph.PURCHASE_QTY) Units,'
|| '        Sum(ph.SPENT_AMT) Sales'
|| 'From purchase_h ph, member_campaign mc, v_EMCR_T t'
|| 'Where ph.ACCOUNT_NUMBER = mc.ACCOUNT_NUMBER'
|| 'and   mc.ACCOUNT_NUMBER = t.MEMBERS_TREAT'
|| 'and ph.PURCHASE_TIME >= to_date('v_StartDate','dd-mon-yyyy')'
|| 'and ph.PURCHASE_TIME <  to_date('v_EndDate','dd-mon-yyyy')'
|| 'and mc.COMM_CD IN ('v_CommID_T1','v_CommID_T2')'
|| 'and mc.CAMP_ID IN ('v_CampID_T1','v_CampID_T2')'
|| 'Group by t.MEMBERS_TREAT';

Commit;


END;


Combo01.sql is the name of my store procedure file

This the Error I got when procedure is created and executed

Warning: Procedure created with compilation errors.
SQL> show errors PROCEDURE Combo01
No errors.

SQL> execute crprocess;
BEGIN crprocess; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object MySchema.CRPROCESS is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Thanks Again!!!





0
Comment
Question by:JoeSand2005
  • 14
  • 12
  • 2
28 Comments
 
LVL 18

Expert Comment

by:rbrooker
ID: 17911015
Hi,

just having a look at your exec immediate statements, you are missing a bunch of quotes...

eg :
execute immediate 'Create Table v_EMCR_T nologging as'
|| 'Select /*+ Parallel (mc, 8)*/'
|| '       distinct mc.ACCOUNT_NUMBER Members_Treat'
|| 'From  member_campaign mc'
|| 'Where mc.COMM_CD IN ('v_CommID_T1','v_CommID_T2')'
|| 'and  mc.CAMP_ID IN ('v_CampID_T1','v_CampID_T2')';

should be
execute immediate 'Create Table v_EMCR_T nologging as '
|| 'Select /*+ Parallel (mc, 8)*/ '
|| '       distinct mc.ACCOUNT_NUMBER Members_Treat '
|| 'From  member_campaign mc '
|| 'Where mc.COMM_CD IN (''v_CommID_T1'',''v_CommID_T2'') '
|| 'and  mc.CAMP_ID IN (''v_CampID_T1'',''v_CampID_T2'') ';

note the changes :
1) a space at the end of each line else you will get : create table v_emcr_t nologging asselect ...... Members_Treatfrom memvber_campaign mcwhere ....
2) when using a single quote within a string, you need to put TWO single quotes.  eg a string JOHN'S MANOR HOUSE enclosed in single quotes would be 'JOHN''S MANOR HOUSE' ( 2 single quotes in JOHN'S )

make these changes and try again :)
0
 

Author Comment

by:JoeSand2005
ID: 17911467
rbrooker,

I use single quotes on Comm_ID and CampID because I will enter a number eg: v_CommID_T1 = 795, v_CommID_T2 = 796 and v_CampID_T1 = 225 abd v_CampID_T2 = 226. I use Varchar2() on these variables since that's the datatype on member_campaig.Comm_CD field as well as Camp_ID. I'm not using Char for Input. Now I'm confuse. Is that the only error you found?
Thanks
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17912140
ok, in that case, the statement should be :

execute immediate 'Create Table v_EMCR_T nologging as '
|| 'Select /*+ Parallel (mc, 8)*/ '
|| '       distinct mc.ACCOUNT_NUMBER Members_Treat '
|| 'From  member_campaign mc '
|| 'Where mc.COMM_CD IN (' || v_CommID_T1 || ',' || v_CommID_T2 || ') '
|| 'and  mc.CAMP_ID IN (' || v_CampID_T1 || ',' || v_CampID_T2 || ')';

and having another look, you need to pass the variables as variables eg :
create or replace procedure my_proc( arg1 in varchar2, arg2 in varchar2, arg3 in varchar2 ... ) as
not :

v_arg1 := &arg1;
you can reference the parameters directly :
execute immediate '.....' || arg1 || '.....';


looking at you r code, you have 8 var := &var; statements, you will need 8 parameters.
this way, you do not need to define each of the 8 variables.

good luck :)
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17912570
>> Commit;

It's no sense to issue commit between DDL's - they cause implicit commit.

>> execute immediate 'Drop Table v_EMCR_T;';

Do not put semicolon at the end of Your dynamic statement.

>> execute immediate 'Create Table v_EMCR_T nologging as'
|| 'Select /*+ Parallel (mc, 8)*/'
|| '       distinct mc.ACCOUNT_NUMBER Members_Treat'
|| 'From  member_campaign mc'
|| 'Where mc.COMM_CD IN ('v_CommID_T1','v_CommID_T2')'
|| 'and  mc.CAMP_ID IN ('v_CampID_T1','v_CampID_T2')';

do not concatenate You statement! Bind variables instead!

execute immediate 'Create Table v_EMCR_T nologging as'
|| 'Select /*+ Parallel (mc, 8)*/'
|| '       distinct mc.ACCOUNT_NUMBER Members_Treat'
|| 'From  member_campaign mc'
|| 'Where mc.COMM_CD IN (:1,:2)'
|| 'and  mc.CAMP_ID IN (:3,:4)'
using v_CommID_T1,v_CommID_T2,v_CampID_T1,v_CampID_T2;

BTW. You'll get rid of the quotes with this technique and won't kill Your SGA.
0
 

Author Comment

by:JoeSand2005
ID: 17914793
rbrooker,

I don't know much about parameters but I think I got the Idea.  One thing I need to do as you noticed on my r code is that I want to be able to enter different values into those parameters, this is why I'm using variables so every time I run the procedure I'll enter different values for all variable I declared. Can you please tell me how I can work around this with parameters?
Thanks!
0
 

Author Comment

by:JoeSand2005
ID: 17914823
GGuzdziol,

I'm sorry, I kind of new with this. I don’t understand what you mean by BTW and SGA. I need to be able to input those values for every variable I declared. Can you explain please?
Thanks for you help.


0
 

Author Comment

by:JoeSand2005
ID: 17915070
rbrooker,

After I made changes on the v_CommID_T1, from ('v_CommID_T1',' v_CommID_T2')  to this (' || v_CommID_T1 || ',' || v_CommID_T2 || ')  I got the following error

LINE/COL ERROR
-------- -----------------------------------------------------------------
33/31    PLS-00103: Encountered the symbol "V_COMMID_T1" when expecting
         one of the following:
         . ( * @ % & = - + ; < / > at in mod not rem return returning
         <an exponent (**)> <> or != or ~= >= <= <> and or like
         between into using is null is not || is dangling

Can you or anyone help with this, I dont know what's going on.

Thanks!

0
 

Author Comment

by:JoeSand2005
ID: 17917312
Hey Guys,

This is the error I got now once I create the store procedure.

LINE/COL ERROR
-------- -----------------------------------------------------------------
16/2     PL/SQL: Statement ignored
16/15    PLS-00357: Table,View Or Sequence reference 'EMCR_T' not
         allowed in this context

21/2     PL/SQL: Statement ignored
21/20    PLS-00357: Table,View Or Sequence reference 'EMCR_T_SLS'
         not allowed in this context

Any Idea why creating table is not allow.

EMCR_T is the name of the first table, and
EMCR_T_SLS is the name of second table.


0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17926737
can you post your entire code please?
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17928750
BTW is an acronym for ,,by the way'', as You see it's not oracle-related.

SGA - http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c08memor.htm#17564
in short this is part of oracle's memory that contains, among others, shared pool. One of it's roles is caching of SQL statements so that they can be reused. It's important to bind variables whenever possible because it allows sql statement reusage. You should read carefully about this (i.e. here - http://asktom.oracle.com/ and search for ,,bind variables'').

I think You should also read about binding variables in native dynamic sql - http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/11_dynam.htm#13131
0
 

Author Comment

by:JoeSand2005
ID: 17932662
Initially if you read my codes from top I wanted to create a table base on the name I would enter, this name would be assign to v_EMCR_T and v_EMCR_T_SLS, but since I got this error msg
21/2     PL/SQL: Statement ignored
21/20    PLS-00357: Table,View Or Sequence reference 'EMCR_T_SLS'
         not allowed in this context
So, I decided to change the approach, and I will not enter the tables name, however I will only enter all CommID, CampID and Date ranges. At this point I was able to create the store procedure, but the problem is when I execute the procedure I get a error. This error says as follow:

SQL> execute crprocess;
BEGIN crprocess; END;

*
ERROR at line 1:
ORA-00922: missing or invalid option
ORA-06512: at "MySchemaName.CRPROCESS", line 19
ORA-06512: at line 1

Here is my new set of codes


CREATE OR REPLACE PROCEDURE CRPROCESS AS
      v_CommID_T1          VARCHAR2(10);
      v_CommID_T2          VARCHAR2(10);
      v_CampID_T1          VARCHAR2(10);
      v_CampID_T2          VARCHAR2(10);
      v_StartDate      VARCHAR2(15);
      v_EndDate      VARCHAR2(15);
      

BEGIN
      v_CommID_T1        := '&CommID_T1';
      v_CommID_T2        := '&CommID_T2';
      v_CampID_T1     := '&CampID_T1';
      v_CampID_T2      := '&CampID_T2';
      v_StartDate        := '&StartDate';
      v_EndDate          := '&EndDate';

execute immediate 'Drop Table Test_T';

execute immediate 'Create Table v_EMCR_T nologging as'
|| 'Select /*+ Parallel (mc, 8)*/'
|| '       distinct mc.ACCOUNT_NUMBER Members_Treat'
|| 'From  member_campaign mc'
|| 'Where mc.COMM_CD IN (v_CommID_T1,v_CommID_T2)'
|| 'and  mc.CAMP_ID IN (v_CampID_T1,v_CampID_T2)';

Commit;



execute immediate  'Drop Table Test_T_SLS';


execute immediate  'Create Table Test_T_SLS nologging as'
|| 'Select /*+ Parallel (ph, 8) Parallel (mc, 8) Parallel (t, 8) */'
|| '        distinct t.MEMBERS_TREAT Trans_Members,'
|| '        Count(distinct ph.PURCHASE_ID) Trans,'
|| '        Sum(ph.PURCHASE_QTY) Units,'
|| '        Sum(ph.SPENT_AMT) Sales'
|| 'From purchase_h ph, member_campaign mc, Test_T t'
|| 'Where ph.ACCOUNT_NUMBER = mc.ACCOUNT_NUMBER'
|| 'and   mc.ACCOUNT_NUMBER = t.MEMBERS_TREAT'
|| 'and ph.PURCHASE_TIME >= to_date(v_StartDate,dd-mon-yyyy)'
|| 'and ph.PURCHASE_TIME <  to_date(v_EndDate,dd-mon-yyyy)'
|| 'and mc.COMM_CD IN (v_CommID_T1,v_CommID_T2)'
|| 'and mc.CAMP_ID IN (v_CampID_T1,v_CampID_T2)'
|| 'Group by t.MEMBERS_TREAT';

Commit;


END;

Thanks again!

0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17932877
Hi,

try this one :

CREATE OR REPLACE PROCEDURE crprocess
(
    pi_commid_t1 IN VARCHAR2,
    pi_commid_t2 VARCHAR2,
    pi_campid_t1 VARCHAR2,
    pi_campid_t2 VARCHAR2,
    pi_startdate VARCHAR2,
    pi_enddate VARCHAR2
) AS

BEGIN

    EXECUTE IMMEDIATE 'Drop Table Test_T';
    EXECUTE IMMEDIATE 'Create Table v_EMCR_T nologging as'
            || ' Select /*+ Parallel (mc, 8)*/'
            || ' distinct mc.ACCOUNT_NUMBER Members_Treat'
            || ' From  member_campaign mc'
            || ' Where mc.COMM_CD IN (:1, :2)'
            || ' and  mc.CAMP_ID IN (:3, :4)'
    USING pi_commid_t1, v_commid_t2, pi_campid_t1, pi_campid_t2;
    COMMIT;

    EXECUTE IMMEDIATE 'Drop Table Test_T_SLS';
    EXECUTE IMMEDIATE 'Create Table Test_T_SLS nologging as'
            || ' Select /*+ Parallel (ph, 8) Parallel (mc, 8) Parallel (t, 8) */'
            || ' distinct t.MEMBERS_TREAT Trans_Members,'
            || ' Count(distinct ph.PURCHASE_ID) Trans,'
            || ' Sum(ph.PURCHASE_QTY) Units,'
            || ' Sum(ph.SPENT_AMT) Sales'
            || ' From purchase_h ph, member_campaign mc, Test_T t'
            || ' Where ph.ACCOUNT_NUMBER = mc.ACCOUNT_NUMBER'
            || ' and   mc.ACCOUNT_NUMBER = t.MEMBERS_TREAT'
            || ' and ph.PURCHASE_TIME >= to_date(:1,''dd-mon-yyyy'')'
            || ' and ph.PURCHASE_TIME <  to_date(:2,''dd-mon-yyyy'')'
            || ' and mc.COMM_CD IN (:3, :4)'
            || ' and mc.CAMP_ID IN (:5, :6)'
            || ' Group by t.MEMBERS_TREAT'
    USING pi_start_date, pi_end_date, pi_commid_t1, v_commid_t2, pi_campid_t1, pi_campid_t2;
    COMMIT;

END;

to call it, type :

exec crprocess( 795, 796, 225, 226, '01-JAN-2006', '31-JAN-2006' );

good luck :)
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17932892
you will need to change the first execute immediate as it is still for v_EMCR_T and not Test_T, and change the v_commid_t2 to be pi_commid_t2 in the USING ....

:\
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17932896
and the v_commid_t2 in the second create statement, that should be pi_commid_t2.
( its monday AGAIN!! )
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:JoeSand2005
ID: 17934194
Hi rbrooker,

I still getting an error on this. Here is the changes I made to the code according to yours

CREATE OR REPLACE PROCEDURE CRPROCESS
      (
      pi_CommID_T1 IN      VARCHAR2,
      pi_CommID_T2    VARCHAR2,
      pi_CampID_T1    VARCHAR2,
      pi_CampID_T2    VARCHAR2,
      pi_StartDate      VARCHAR2,
      pi_EndDate      VARCHAR2,
      ) AS
      

BEGIN

  execute immediate 'Drop Table Test_T';

  execute immediate 'Create Table Test_T nologging as'
      || 'Select /*+ Parallel (mc, 8)*/'
      || '       distinct mc.ACCOUNT_NUMBER Members_Treat'
      || 'From  member_campaign mc'
      || 'Where mc.COMM_CD IN (:1,:2)'
      || 'and  mc.CAMP_ID IN (:3,:4)'
  USING pi_CommID_T1, pi_CommID_T2, pi_CampID_T1, pi_CampID_T2;

  Commit;



  execute immediate  'Drop Table Test_T_SLS';


  execute immediate  'Create Table Test_T_SLS nologging as'
      || 'Select /*+ Parallel (ph, 8) Parallel (mc, 8) Parallel (t, 8) */'
      || '        distinct t.MEMBERS_TREAT Trans_Members,'
      || '        Count(distinct ph.PURCHASE_ID) Trans,'
      || '        Sum(ph.PURCHASE_QTY) Units,'
      || '        Sum(ph.SPENT_AMT) Sales'
      || 'From purchase_h ph, member_campaign mc, Test_T t'
      || 'Where ph.ACCOUNT_NUMBER = mc.ACCOUNT_NUMBER'
      || 'and   mc.ACCOUNT_NUMBER = t.MEMBERS_TREAT'
      || 'and ph.PURCHASE_TIME >= to_date(:5,"dd-mon-yyyy")'
      || 'and ph.PURCHASE_TIME <  to_date(:6,"dd-mon-yyyy")'
      || 'and mc.COMM_CD IN (:1,:2)'
      || 'and mc.CAMP_ID IN (:3,:4)'
      || 'Group by t.MEMBERS_TREAT'
  USING pi_StartDate, pi_EndDate, pi_CommID_T1, pi_CommID_T2, pi_CampID_T1, pi_CampID_T2;

  Commit;


END;

This is the error msg:


ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "MySchemaName.CRPROCESS", line 12
ORA-06512: at line 1

I thought of creating a separate procedure just to drop table Test_T, and have this procedure to recreate that same table. Can I do Drop and Create Table in the same store procedure?

Thanks!


0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17934352
ok, 2 things.

one, the drop commands will fail if the table is not there, therefore :

begin
execute immediate 'drop table blah';
exception
when others then null;
end;

should have picked this up before.

two, the create statements are running together again, you need to consistently add a space to the end of each line, or the start of each line so that the words do not run into each other.
0
 

Author Comment

by:JoeSand2005
ID: 17939177
I added the exception at the bottom and the procedure works and when I exce it I got a msg
PL/SQL procedure successfully completed, However both tables don't exist. So the procedure is not creating the tables.
you know why?

here is what I did:


CREATE OR REPLACE PROCEDURE CRPROCESS
      (
      pi_CommID_T1 IN      VARCHAR2,
      pi_CommID_T2    VARCHAR2,
      pi_CampID_T1    VARCHAR2,
      pi_CampID_T2    VARCHAR2,
      pi_StartDate      VARCHAR2,
      pi_EndDate      VARCHAR2,
      ) AS
BEGIN

  execute immediate 'Drop Table Test_T';

  execute immediate 'Create Table Test_T nologging as '
      || 'Select /*+ Parallel (mc, 8)*/'
      || '       distinct mc.ACCOUNT_NUMBER Members_Treat'
      || 'From  member_campaign mc'
      || 'Where mc.COMM_CD IN (:1,:2)'
      || 'and  mc.CAMP_ID IN (:3,:4)'
  USING pi_CommID_T1, pi_CommID_T2, pi_CampID_T1, pi_CampID_T2;

  Commit;



  execute immediate  'Drop Table Test_T_SLS';


  execute immediate  'Create Table Test_T_SLS nologging as '
      || 'Select /*+ Parallel (ph, 8) Parallel (mc, 8) Parallel (t, 8) */'
      || '        distinct t.MEMBERS_TREAT Trans_Members,'
      || '        Count(distinct ph.PURCHASE_ID) Trans,'
      || '        Sum(ph.PURCHASE_QTY) Units,'
      || '        Sum(ph.SPENT_AMT) Sales'
      || 'From purchase_h ph, member_campaign mc, Test_T t'
      || 'Where ph.ACCOUNT_NUMBER = mc.ACCOUNT_NUMBER'
      || 'and   mc.ACCOUNT_NUMBER = t.MEMBERS_TREAT'
      || 'and ph.PURCHASE_TIME >= to_date(:5,"dd-mon-yyyy")'
      || 'and ph.PURCHASE_TIME <  to_date(:6,"dd-mon-yyyy")'
      || 'and mc.COMM_CD IN (:1,:2)'
      || 'and mc.CAMP_ID IN (:3,:4)'
      || 'Group by t.MEMBERS_TREAT'
  USING pi_StartDate, pi_EndDate, pi_CommID_T1, pi_CommID_T2, pi_CampID_T1, pi_CampID_T2;

  Commit;

EXCEPTION
When Others then null;

END;

Thank you for your time,

0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17940195
ok, the exception handler is in the wrong place.  when the code tries to drop the table, the table does not exist, the code moves to the exception handler and drops out the bottom of the procedure.  what i was suggesting was :

CREATE OR REPLACE PROCEDURE crprocess
(
    pi_commid_t1 IN VARCHAR2,
    pi_commid_t2 VARCHAR2,
    pi_campid_t1 VARCHAR2,
    pi_campid_t2 VARCHAR2,
    pi_startdate VARCHAR2,
    pi_enddate VARCHAR2,
) AS
BEGIN

    BEGIN
        EXECUTE IMMEDIATE 'Drop Table Test_T';
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
    EXECUTE IMMEDIATE 'Create Table Test_T nologging as '
            || 'Select /*+ Parallel (mc, 8)*/ '
            || '       distinct mc.ACCOUNT_NUMBER Members_Treat '
            || 'From  member_campaign mc '
            || 'Where mc.COMM_CD IN (:1,:2) '
            || 'and  mc.CAMP_ID IN (:3,:4)'
        USING pi_commid_t1, pi_commid_t2, pi_campid_t1, pi_campid_t2;
    COMMIT;

    BEGIN
        EXECUTE IMMEDIATE 'Drop Table Test_T_SLS';
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
    EXECUTE IMMEDIATE 'Create Table Test_T_SLS nologging as '
            || 'Select /*+ Parallel (ph, 8) Parallel (mc, 8) Parallel (t, 8) */ '
            || '        distinct t.MEMBERS_TREAT Trans_Members, '
            || '        Count(distinct ph.PURCHASE_ID) Trans, '
            || '        Sum(ph.PURCHASE_QTY) Units, '
            || '        Sum(ph.SPENT_AMT) Sales '
            || 'From purchase_h ph, member_campaign mc, Test_T t '
            || 'Where ph.ACCOUNT_NUMBER = mc.ACCOUNT_NUMBER '
            || 'and   mc.ACCOUNT_NUMBER = t.MEMBERS_TREAT '
            || 'and ph.PURCHASE_TIME >= to_date(:5,"dd-mon-yyyy") '
            || 'and ph.PURCHASE_TIME <  to_date(:6,"dd-mon-yyyy") '
            || 'and mc.COMM_CD IN (:1,:2) '
            || 'and mc.CAMP_ID IN (:3,:4) '
            || 'Group by t.MEMBERS_TREAT'
        USING pi_startdate, pi_enddate, pi_commid_t1, pi_commid_t2, pi_campid_t1, pi_campid_t2;
    COMMIT;

END;


also, you need to remember spaces!!!

execute immediate 'abc'
|| 'def'
|| 'hij'
is the same as
execute immediate 'abcdefhij'

whereas
execute immediate 'abc '
|| 'def '
|| 'hij'
becomes
execute immediate 'abc def hij'

YOU NEED SPACES to stop the words of your create statements running into each other. :)
0
 

Author Comment

by:JoeSand2005
ID: 17947705
I don't know what else to do now. I made the changes you suggested and I got a error as follow:

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "MySchemaName", line 21
ORA-06512: at line 1

Line 21 is pointing to the statement
EXECUTE IMMEDIATE 'Create Table Test_T nologging as  '  

Should I place an EXCEPTION on the Create statements?

Thanks!

0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17949153
Hi,

run the sql yourself as a standalone statement.  if this error is the create statement, then probably, the table you are selecting FROM does not exist, or is in another schema and you do not have privilege?

Select /*+ Parallel (mc, 8)*/ distinct mc.ACCOUNT_NUMBER Members_Treat From  member_campaign mc Where mc.COMM_CD IN (795,796) and  mc.CAMP_ID IN (225,226)

does this statement work? if not, the issue is selecting from member_campaign, if it does, then the issue is with how you have formatted the execute immediate ( i would take out the concatenation and have the statement on one line ).

good luck :)
0
 

Author Comment

by:JoeSand2005
ID: 17996056
Hi rbrooker,

I'm sorry for dilate. I did ran the sql as a standalone statement and they both work good. I don't know what else to do. The store procedure works fine but it doesn't create the tables.
Any Idea?
I will add some more point on this, since it looks, it's getting hard to solve.
Thanks again for your time.


0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17997764
can you paste your exact code?

and maybe the source table definitions, i will see if i can replicate it here.
0
 

Author Comment

by:JoeSand2005
ID: 18133579
Hi rbrooker,

I apologize for taking so long. I still struggle with this.



CREATE OR REPLACE PROCEDURE crprocess
(
    pi_commid_t1 IN VARCHAR2,
    pi_commid_t2 VARCHAR2,
    pi_campid_t1 VARCHAR2,
    pi_campid_t2 VARCHAR2,
    pi_startdate VARCHAR2,
    pi_enddate VARCHAR2,
) AS
BEGIN

    BEGIN
        EXECUTE IMMEDIATE 'Drop Table Test_T';
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
    EXECUTE IMMEDIATE 'Create Table Test_T nologging as '
            || 'Select /*+ Parallel (mc, 8)*/ '
            || '       distinct mc.ACCOUNT_NUMBER Members_Treat '
            || 'From  member_campaign mc '
            || 'Where mc.COMM_CD IN (:1,:2) '
            || 'and  mc.CAMP_ID IN (:3,:4)'
        USING pi_commid_t1, pi_commid_t2, pi_campid_t1, pi_campid_t2;
    COMMIT;

    BEGIN
        EXECUTE IMMEDIATE 'Drop Table Test_T_SLS';
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
    EXECUTE IMMEDIATE 'Create Table Test_T_SLS nologging as '
            || 'Select /*+ Parallel (ph, 8) Parallel (mc, 8) Parallel (t, 8) */ '
            || '        distinct t.MEMBERS_TREAT Trans_Members, '
            || '        Count(distinct ph.PURCHASE_ID) Trans, '
            || '        Sum(ph.PURCHASE_QTY) Units, '
            || '        Sum(ph.SPENT_AMT) Sales '
            || 'From purchase_h ph, member_campaign mc, Test_T t '
            || 'Where ph.ACCOUNT_NUMBER = mc.ACCOUNT_NUMBER '
            || 'and   mc.ACCOUNT_NUMBER = t.MEMBERS_TREAT '
            || 'and ph.PURCHASE_TIME >= to_date(:5,"dd-mon-yyyy") '
            || 'and ph.PURCHASE_TIME <  to_date(:6,"dd-mon-yyyy") '
            || 'and mc.COMM_CD IN (:1,:2) '
            || 'and mc.CAMP_ID IN (:3,:4) '
            || 'Group by t.MEMBERS_TREAT'
        USING pi_startdate, pi_enddate, pi_commid_t1, pi_commid_t2, pi_campid_t1, pi_campid_t2;
    COMMIT;

END;


Table Definition:

member_campaign
Name                                                                     Type
Account_Number                                                 Not null Number PK
comm_cd                                                            varchar2(10)
camp_id                                                              not null Number


table name: purchase_h
Name
Account_number                                                 not null Number
purchase_id                                                        not null number
purchase_qty                                                      number
spent_amt                                                          number(15, 2)



Let me know if you need more info.
Thanks so much!

Joe
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 18133632
Hi,

have had a thought.  are you running this as a dba account?  or a joe blogs user ( sorry! ).  try having the priv "create table" granted explicitly, grants that are granted through roles are ignored by pl/sql.  either that, ir have the procedure created as :

CREATE OR REPLACE PROCEDURE crprocess
(
    pi_commid_t1 IN VARCHAR2,
    pi_commid_t2 VARCHAR2,
    pi_campid_t1 VARCHAR2,
    pi_campid_t2 VARCHAR2,
    pi_startdate VARCHAR2,
    pi_enddate VARCHAR2,
) AUTHID CURRENT_USER AS

this will bring all grants provided by roles into play.

just to refresh my memory, what is the error message you are getting?
0
 

Author Comment

by:JoeSand2005
ID: 18148256
Hi,

yeah! I'm a blogs user with rights to build store procedure to create, update, delete...etc
There is no error message. The problem is that the tables are not created. The store procedure works fine, and exec great no error, but I dont see the tables in my squima.
Thanks!
0
 
LVL 18

Accepted Solution

by:
rbrooker earned 150 total points
ID: 18156401
ok, so if the execute immediate statements work when you run them by hand, but not when you run them through the procedure...

try :

change the
    execute immediate 'create table....'
to
    execute immediate 'begin raise_application_error( -20001, ''Execute immediate statement #1'' ); end;';
and
    execute immediate 'begin raise_application_error( -20001, ''Execute immediate statement #2'' ); end;';
for each of the execute immediate statements.  they should both break the code with the error message being "Execute immediate statement #[1|2]".

NOTE, the double quotes around the error message are 2 single quotes, not one double quote.

if they do not break the code, then the execute immediates are not being executed.
if they do break the code, there is something wrong with the create table statements.

good luck :)
0
 

Author Comment

by:JoeSand2005
ID: 18288830
hi rbrooker,

I'm sorry, I was out for a while. I did try what you suggested and still dont work. I'll get back to you with the error message.
Thanks!
0
 

Author Comment

by:JoeSand2005
ID: 18479114
Hey rbrooker,

Thanks a lot for your help. I may hit you with another PL/SQL question

cheers!

Joe
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

709 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now