Oracle execute immediate

Question

Have oracle stuff that I can't figure out where is the problem, if you could help me out here

ORA-00911: invalid character
ORA-06512: at line 20

Line 20 is EXECUTE IMMEDIATE SQL_STMT INTO P_COUNT USING L_RB_OD, L_RB_DO;

I have like 10-15 tables, and each of them have similar names and column that is TableName_ID which contains sequence, so I made a procedure that will select certain amount of data (P_Bucket) from table (P_Table). It will also select total amount of rows selected into P_COUNT and return P_DATA sys_refcursor. Procedure compiles successfully but won't execute.

DECLARE
    P_PAR_01 VARCHAR2(5) := '2';
    P_BUCKET NUMBER := 200;
    P_TABLE VARCHAR2(15) := 'KB_FL_1';
    P_COUNT NUMBER;
    P_DATA SYS_REFCURSOR;
    L_RB_OD NUMBER;
    L_RB_DO NUMBER;
    L_RB NUMBER;
    SQL_STMT VARCHAR2(1000);
BEGIN
    L_RB := CAST(P_PAR_01 AS NUMBER);
    L_RB_DO := L_RB * P_BUCKET;
    L_RB_OD := L_RB_DO - (P_BUCKET - 1);

    SQL_STMT := 'SELECT COUNT(*)
                   FROM ' || P_TABLE || ' 
                   WHERE 1 = 1
                         AND ' || P_TABLE || '_ID BETWEEN :1 AND :2'; -- was :2;';

    EXECUTE IMMEDIATE SQL_STMT INTO P_COUNT USING L_RB_OD, L_RB_DO;

    OPEN P_DATA FOR 
    'SELECT * FROM ' || P_TABLE || '  
     WHERE 1 = 1
           AND ' || P_TABLE || ' _ID BETWEEN :1 AND :2
     ORDER BY ID_LICA' USING L_RB_OD, L_RB_DO;  -- was ORDER BY ID_LICA;'    
END;

EDIT: As comments says, dynamic SQL don't use semicolons, but even after removal of semicolons I get error invalid character at OPEN P_DATA FOR

The invalid character reported is `';'`. Dynamic SQL strings don't require it. — Maheswaran Ravisankar, Jan 30 '18 at 09:36
@MaheswaranRavisankar I removed the semicolon and you were right .... it was a problem :) had to be something like that ... but now I get `Invalid character` at `OPEN P_DATA FOR`, after i removed both semicolons — Veljko89, Jan 30 '18 at 09:37
that "semicolon" `:2;`, the one after ':2'. Semicolon is for PL/SQL, don't use it in SQL statements. That also ID_LICA;. Moreover you have a space in ' _ID BETWEEN ...' — shadowsheep, Jan 30 '18 at 09:38
Same for the dynamic cursor as well. Also the leading space `'_ID BETWEEN :1 AND :2'` — Maheswaran Ravisankar, Jan 30 '18 at 09:38
Writing dynamic SQL is hard because it turns compilation errors into runtime errors. You should get into the habit of building your dynamic statements in a variable. Then you can output the variable before you execute the string. This will make it astonishingly easy to spot the bloomer(s) you have made. — APC, Jan 30 '18 at 14:21

score 2 · Accepted Answer · answered Jan 30 '18 at 09:40

You have to remove semicolons from the dynamic SQL; also, you have to remove a space in the second dynamic query: ' _ID' should be '_ID'. This should work:

DECLARE
    P_PAR_01 VARCHAR2(5) := '2';
    P_BUCKET NUMBER := 200;
    P_TABLE VARCHAR2(15) := 'KB_FL_1';
    P_COUNT NUMBER;
    P_DATA SYS_REFCURSOR;
    L_RB_OD NUMBER;
    L_RB_DO NUMBER;
    L_RB NUMBER;
    SQL_STMT VARCHAR2(1000);
BEGIN
    L_RB := CAST(P_PAR_01 AS NUMBER);
    L_RB_DO := L_RB * P_BUCKET;
    L_RB_OD := L_RB_DO - (P_BUCKET - 1);

    SQL_STMT := 'SELECT COUNT(*)
                   FROM ' || P_TABLE || ' 
                   WHERE 1 = 1
                         AND ' || P_TABLE || '_ID BETWEEN :1 AND :2';
    EXECUTE IMMEDIATE SQL_STMT INTO P_COUNT USING L_RB_OD, L_RB_DO;

    OPEN P_DATA FOR 
    'SELECT * FROM ' || P_TABLE || '  
     WHERE 1 = 1
           AND ' || P_TABLE || '_ID BETWEEN :1 AND :2
     ORDER BY ID_LICA' USING L_RB_OD, L_RB_DO;  


END;

Oracle execute immediate

1 Answers1