Forum Stats

  • 3,770,167 Users
  • 2,253,079 Discussions
  • 7,875,357 Comments

Discussions

PLS-00103: Encountered the symbol "CRITICAL" when expecting one of the following:

Rakesh119
Rakesh119 Member Posts: 105 Green Ribbon
edited Jul 28, 2020 6:48AM in SQL & PL/SQL

I have a procedure which i have a drop and create table statement, I have wrapped up both in a string and gave execute immediate command but in create table i am getting the above error.

Here is the procedure down below.

create or replace procedure Proc_1

IS

var1 varchar2(4000);

var2 varchar2(4000);

BEGIN

var1 := 'Drop Table Problem_Investigation';

EXECUTE IMMEDIATE var1;

var2 := 'create table Problem_Investigation

AS

WITH CTE as (

Select    PROBLEM_INVESTIGATION_ID as PROBLEM_INVESTIGATION_ERROR_ID ,

                        SERVICE,

                        ASSIGNED_GROUP,

                        COORDINATOR_GROUP,

                        PRIORITY,

                        STATUS,

                        SUMMARY,

                        cast(submit_date as timestamp) SUBMIT_DATE,

                        cast(LAST_MODIFIED_DATE as timestamp) LAST_MODIFIED_DATE,

                        cast(LAST_COMPLETED_DATE as timestamp) LAST_COMPLETED_CORRECTED_DATE,

                        ASSIGNED_SUPPORT_ORGANIZATION,

        EXTRACT(DAY FROM (cast(submit_date as timestamp) - cast(LAST_COMPLETED_DATE as timestamp))) REPAIR_INVESTIGATION_TIME,

                        CASE PAD.PRIORITY

            WHEN 'Critical' THEN '5'

            WHEN 'High' THEN '15'

            WHEN ('Medium') THEN '30'

            WHEN ('Low') THEN '30'

                        END TARGET_INVESTIGATION_REPAIR_TIME,

                        CASE PAD.PRIORITY

            WHEN 'Critical' THEN cast(submit_date+5 as timestamp)

                                     WHEN 'High' THEN cast(submit_date+15 as timestamp)

                                     WHEN 'Medium' THEN cast(submit_date+ 30 as timestamp)

             WHEN 'Low' THEN cast(submit_date+ 30 as timestamp)

                        END Target_Date,

        TAM.tower TOWER

from

[email protected]_QQITSMREP.BMW PAD

JOIN TOWER_ASSGNEE_MAPPING TAM ON PAD.ASSIGNED_GROUP = TAM.ASSIGNEE_GROUP_NAME

Where

  1. PAD.ASSIGNED_SUPPORT_ORGANIZATION like '%iaas%'

And PAD.STATUS  NOT IN ( 'Cancelled','Closed','Completed' )

And trunc(SUBMIT_DATE) >= to_date('2019-01-29','YYYY-MM_DD')

--AND PROBLEM_INVESTIGATION_ID = 'PBI000000297805'

)

select CTE.*,

CASE WHEN Business_Days(trunc(LAST_MODIFIED_DATE), trunc(SYSDATE)) > 5 THEN 'yes' ELSE 'No' END "not updated >5 days",

case when Target_Date between SYSTIMESTAMP and SYSTIMESTAMP  + interval '7' DAY then 'yes'  else 'No' end as "sla_in_7days",

case when Target_Date between SYSTIMESTAMP  and SYSTIMESTAMP  + interval '14' DAY then 'yes'  else 'No' end as "sla_in_14days",

Case when Target_Date < SYSTIMESTAMP  then 'yes' else 'NO' end "Already_Run_Out_of_SLA "

FROM CTE';

execute immediate var2;

END Proc_1;

Tagged:
Saubhik

Best Answer

  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited Jul 24, 2020 5:23AM Accepted Answer

    Hello Rakesh,

    -A- Maybe you might rather use a MATERIALIZED VIEW instead of using a procedure to do "drop table ...; create table ... as select ...".

    -B- Maybe you might create the table only one time, and replace "drop+create" of your procedure by "truncate + insert into ... select ..."

    -C- Try this: SELECT q'[single quote here isn't a problem!]' FROM dual;

    Best regards,

    Bruno Vroman.

Answers

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Jul 24, 2020 3:39AM

    WHEN 'Critical' THEN --> All the single quote need to be escaped. By the way, creating table on the fly is a bad practice, try to avoid it as much as possible.

    Something like below:

    CREATE OR REPLACE PROCEDURE proc_1 IS

        var1  VARCHAR2(4000);

        var2  VARCHAR2(4000);

    BEGIN

        var1 := 'Drop Table Problem_Investigation';

        EXECUTE IMMEDIATE var1;

        var2 := 'create table Problem_Investigation

    AS

    WITH CTE as (

    Select    PROBLEM_INVESTIGATION_ID as PROBLEM_INVESTIGATION_ERROR_ID ,

                            SERVICE,

                            ASSIGNED_GROUP,

                            COORDINATOR_GROUP,

                            PRIORITY,

                            STATUS,

                            SUMMARY,

                            cast(submit_date as timestamp) SUBMIT_DATE,

                            cast(LAST_MODIFIED_DATE as timestamp) LAST_MODIFIED_DATE,

                            cast(LAST_COMPLETED_DATE as timestamp) LAST_COMPLETED_CORRECTED_DATE,

                            ASSIGNED_SUPPORT_ORGANIZATION,

            EXTRACT(DAY FROM (cast(submit_date as timestamp) - cast(LAST_COMPLETED_DATE as timestamp))) REPAIR_INVESTIGATION_TIME,

                            CASE PAD.PRIORITY

                WHEN ''Critical'' THEN ''5''

                WHEN ''High'' THEN ''15''

                WHEN (''Medium'') THEN ''30''

                WHEN (''Low'') THEN ''30''

                            END TARGET_INVESTIGATION_REPAIR_TIME,

                            CASE PAD.PRIORITY

                WHEN ''Critical'' THEN cast(submit_date+5 as timestamp)

                                         WHEN ''High'' THEN cast(submit_date+15 as timestamp)

                                         WHEN ''Medium'' THEN cast(submit_date+ 30 as timestamp)

                 WHEN ''Low'' THEN cast(submit_date+ 30 as timestamp)

                            END Target_Date,

            TAM.tower TOWER

    from

    [email protected]_QQITSMREP.BMW PAD

    JOIN TOWER_ASSGNEE_MAPPING TAM ON PAD.ASSIGNED_GROUP = TAM.ASSIGNEE_GROUP_NAME

    Where

        PAD.ASSIGNED_SUPPORT_ORGANIZATION like ''%iaas%''

    And PAD.STATUS  NOT IN ( ''Cancelled'',''Closed'',''Completed'' )

    And trunc(SUBMIT_DATE) >= to_date(''2019-01-29'',''YYYY-MM_DD'')

    --AND PROBLEM_INVESTIGATION_ID = ''PBI000000297805''

    )

    select CTE.*,

    CASE WHEN Business_Days(trunc(LAST_MODIFIED_DATE), trunc(SYSDATE)) > 5 THEN ''yes'' ELSE ''No'' END "not updated >5 days",

    case when Target_Date between SYSTIMESTAMP and SYSTIMESTAMP  + interval ''7'' DAY then ''yes''  else ''No'' end as "sla_in_7days",

    case when Target_Date between SYSTIMESTAMP  and SYSTIMESTAMP  + interval ''14'' DAY then ''yes''  else ''No'' end as "sla_in_14days",

    Case when Target_Date < SYSTIMESTAMP  then ''yes'' else ''NO'' end "Already_Run_Out_of_SLA "

    FROM CTE';

        EXECUTE IMMEDIATE var2;

    END proc_1;

  • RogerT
    RogerT Member Posts: 1,855 Gold Trophy
    edited Jul 24, 2020 3:54AM

    or (easier) make use of "user defined quote"

    var2 := q'{create table Problem_Investigation

    AS

    WITH CTE as (

    ...

    Case when Target_Date < SYSTIMESTAMP  then 'yes' else 'NO' end "Already_Run_Out_of_SLA "

    FROM CTE}';

    hth

    Saubhik
  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited Jul 24, 2020 5:23AM Accepted Answer

    Hello Rakesh,

    -A- Maybe you might rather use a MATERIALIZED VIEW instead of using a procedure to do "drop table ...; create table ... as select ...".

    -B- Maybe you might create the table only one time, and replace "drop+create" of your procedure by "truncate + insert into ... select ..."

    -C- Try this: SELECT q'[single quote here isn't a problem!]' FROM dual;

    Best regards,

    Bruno Vroman.

  • Mike Kutz
    Mike Kutz Member Posts: 5,822 Silver Crown
    edited Jul 24, 2020 9:05AM
    BrunoVroman wrote:Hello Rakesh,-A- Maybe you might rather use a MATERIALIZED VIEW instead of using a procedure to do "drop table ...; create table ... as select ...".-B- Maybe you might create the table only one time, and replace "drop+create" of your procedure by "truncate + insert into ... select ..."-C- Try this: SELECT q'[single quote here isn't a problem!]' FROM dual;Best regards,Bruno Vroman.

    Depending on the needs, might I suggest:

    -B.2- Maybe you might create a Global Temproary table only one time and use the data before you end the session/transaction.  (data is automatically removed at the end.)