Search code examples
oracle-databasestored-proceduresinvokeexecute

ORA-01780: string literal required


When i invoke below procedure i get 'string literal required' exception, can any one help please, i'm sure that i'm writing (Execute Immediate) in wrong form :

PROCEDURE ADD_TAB_COL_COMMENT   (
                                    P_TAB_NAME  IN  VARCHAR,
                                    P_COL_NAME  IN  VARCHAR,
                                    P_COMMENT   IN  VARCHAR,
                                    P_LEVEL     IN  NUMBER
                                    )
    IS 
    BEGIN                 

        IF(P_LEVEL = 1)THEN 

            EXECUTE IMMEDIATE 'COMMENT ON TABLE ' || P_TAB_NAME || ' IS ' || P_COMMENT; 

        ELSIF(P_LEVEL = 2)THEN

            EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || P_TAB_NAME ||'.'|| P_COL_NAME || ' IS ' || P_COMMENT; 

        END IF;   

    END ADD_TAB_COL_COMMENT ;

Solution

  • You are embedding the unquoted value from P_COMMENT into your command. If you used dbms_output to display the command being executed, for a call like

     exec add_tab_col_comment ('T42', 'ID', 'Test', 2);
    

    you would see it trying to do:

    COMMENT ON COLUMN T42.ID IS Test
    

    The actual comment Test has to be enclosed in single quotes for that command. In your dynamic SQL you need to add those quotes, and also escape them:

    PROCEDURE ADD_TAB_COL_COMMENT   (
                                        P_TAB_NAME  IN  VARCHAR,
                                        P_COL_NAME  IN  VARCHAR,
                                        P_COMMENT   IN  VARCHAR,
                                        P_LEVEL     IN  NUMBER
                                        )
        IS 
        BEGIN                 
            IF(P_LEVEL = 1)THEN 
                EXECUTE IMMEDIATE 'COMMENT ON TABLE ' || P_TAB_NAME
                    || ' IS ''' || P_COMMENT || ''''; 
            ELSIF(P_LEVEL = 2)THEN
                EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || P_TAB_NAME ||'.'|| P_COL_NAME
                    || ' IS ''' || P_COMMENT|| ''''; 
            END IF;   
        END ADD_TAB_COL_COMMENT ;
    

    The same call would then generate the statement:

    COMMENT ON COLUMN T42.ID IS 'Test'
    

    which is now valid.

    Whenever you get an error with dynamic SQL, the simplest way to tell what's going on is to use dbms_output to display the actual command being executed, and if it isn't then obvious what the problem is, try running that displayed command manually. This approach will usually quickly reveal missing or mismatched quotes, missing whitespace, etc.