-2

I'm trying to store strings and concatenate using variables in my sql code but I keep getting errors. This is what I tried: 

DECLARE 
    ENV VARCHAR(9999);
    MV  VARCHAR(999);
    MVNAME VARCHAR(999) := 'TEST2';
BEGIN
    -- GET ENVIRONMENT
    SELECT sys_context('USERENV','DB_NAME')
    INTO ENV
    FROM DUAL;

    MV := 'CREATE MATERIALIZED VIEW' || MVNAME ||
            'COMPRESS
            NOLOGGING
            PARTITION BY HASH (DATAID)
            PARTITIONS 16
            PARALLEL
            BUILD IMMEDIATE
            USING INDEX 
            REFRESH 
            NEXT trunc(SYSDATE, ''hh'') + 1/24        
            FAST 
            WITH ROWID 
            USING DEFAULT LOCAL ROLLBACK SEGMENT 
            ENABLE QUERY REWRITE AS ';

    IF ENV = 'CSTMP' THEN
        execute immediate MV || 'SELECT count(m.work_ownerid)
                        FROM MV_WWORK_SHRUNK m
                        WHERE WORK_STATUS = 2';   
    ELSIF ENV = 'PROD' THEN
        execute immediate MV || 'SELECT count(m.work_ownerid)
                        FROM MV_WWORK_SHRUNK m
                        WHERE WORK_STATUS = 2';             
    ELSE
        execute immediate MV || 'SELECT count(m.work_ownerid)
                        FROM MV_WWORK_SHRUNK m
                        WHERE WORK_STATUS = 2';
    END IF;
END;
Batman
  • 5,563
  • 18
  • 79
  • 155
  • One obvious observation is that you are missing spaces in the resulting strings. –  Mar 29 '18 at 20:10
  • I'm not sure I understand what that means. – Batman Mar 29 '18 at 20:13
  • Add a space before COMPRESS. Also after VIEW. – ArtBajji Mar 29 '18 at 20:16
  • Oh I see, there's no space between the concatenations – Batman Mar 29 '18 at 20:18
  • Classic way of debugging dynamic SQL is to use DBMS_OUTPUT.PUT_LINE instead of EXECUTE IMMEDIATE so that you'd actually see what you're going to execute. Things become much more clear, then. BTW, what's the purpose of creating a materialized view that way? What benefit do you see in that? – Littlefoot Mar 29 '18 at 20:20
  • When I do that, I just get procedure ran successfully – Batman Mar 29 '18 at 20:23
  • Why do you have a double-quote before compress? – access_granted Mar 29 '18 at 21:36
  • 1
    [I downvoted because lacking an MCVE makes it hard to answer](http://idownvotedbecau.se/nomcve/) and because you didn't supply the error message and because you didn't use the dbms_output method proposed by @Littlefoot – miracle173 Mar 29 '18 at 22:44
  • MCVE, I literally put the entire code in the OP? I said I tried the DBMS_OUTPUT method and I just got "Procedure ran successfully". The error Message is "Missing keyword". – Batman Mar 30 '18 at 15:48
  • @miracle173 https://stackoverflow.com/questions/7887413/printing-the-value-of-a-variable-in-sql-developer – Batman Mar 31 '18 at 22:51
  • 1) "I literally put the entire code in the OP": Please check the description of [MCVE](https://stackoverflow.com/help/mcve). Putting the entire code in the OP is definitely the wrong way. Is the first select statement relevant to your problem? Or do you get still the same error if you remove it? I assume it is not relevant, so remove it. There is no value assigned to `ENV`, and I assume the last branch of the if statement will be taken. If the if-statement is not relevant, remove it and simply execute the last branch. Which directives of your CREATE-statement are relevant? – miracle173 Apr 01 '18 at 20:06
  • 2) Is the 'PARALLEL' directive relevant? If not, remove it. So you may be able to remove a lot of these directives. Is it relevant that you pass the view name by a variable an concatenate strings? If not, remove this. Finally you should check if it is relevant to the problem if you use pl/sql or if you get the same error if you execute the create-statement directly as SQL, not using a PL/SQL block. So there are a lot of things to much – miracle173 Apr 01 '18 at 20:06
  • 3) I ran the statement in [dbfiddle](http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=971f4df1eb9509c5a6847fc781995948) and got the error messages `ORA-00905: missing keyword`, `ORA-06512: at line 23`. Didn't you get these error messages? If so, add this to the post and not only a part of it. The statement can never succeed if the base table does not exist. So supply a statement to create a base table. – miracle173 Apr 01 '18 at 20:08
  • 4) I realized that dbms_output.put_line didn't work in your environment. But it is an important tool. So make it work. If you don't find a solution here or anywhere else then post an appropriate question here and solve your problem. I you followed my previous comments you should already have found the reason for your error. If you haven't found the output will show you the reason for your problem. I hope my suggestions are useful to you. – miracle173 Apr 01 '18 at 20:08

1 Answers1

1

If you display the result of concatenation instead of executing it, you get something like this:

CREATE MATERIALIZED VIEWTEST2" COMPRESS
            NOLOGGING
            PARTITION BY HASH (DATAID)
            PARTITIONS 16
            PARALLEL
            BUILD IMMEDIATE
            USING INDEX 
            REFRESH 
            NEXT trunc(SYSDATE, 'hh') + 1/24        
            FAST 
            WITH ROWID 
            USING DEFAULT LOCAL ROLLBACK SEGMENT 
            ENABLE QUERY REWRITE AS SELECT count(m.work_ownerid)
                        FROM MV_WWORK_SHRUNK m
                        WHERE WORK_STATUS = 2

Correct the errors and retry.

William Robertson
  • 15,273
  • 4
  • 38
  • 44