Dynamic PL-SQL programming - Issue with DBMS_SQL.PARSE

Question

I wrote a query to fetch the details using some business logic. However, I got stuck at the below line

  DBMS_SQL.PARSE(CUR, SQLSTR, DBMS_SQL.NATIVE);

And I got the below error. I checked the syntax again and again and it is perfectly fine. So, could you guys help me in fixing this? Errors below.

 ORA-00933: SQL command not properly ended
 ORA-06512: at "SYS.DBMS_SQL", line 1134
 ORA-06512: at line 35
 0093.00000 - "SQL command not properly ended"

Code is below.

DECLARE 

SQLSTR VARCHAR2(30000);
CUR INTEGER;
SER INTEGER;
WS  VARCHAR2(10);
REFCUR SYS_REFCURSOR;
TYPE ERMAS IS TABLE OF DUAL%ROWTYPE;
SERULT ERMAS;
VAL MRUL.OWL%TYPE;

CURSOR TION IS
SELECT RUD.COL, RUD.VAL, RUD.OPR, RUD.RU3
FROM RMAP RUM, RDET RUD 
WHERE RUM.RU3 = RUD.RU3
AND RUD.RU3 IN ('60', '61', '62');

 BEGIN

CUR := DBMS_SQL.OPEN_CURSOR;

SQLSTR := 'SELECT * '||CHR(13);
SQLSTR := SQLSTR || 'FROM DUAL '||CHR(13);    
SQLSTR := SQLSTR || 'WHERE MRUL = : OWL AND (';

FOR DONC IN TION
LOOP
    SQLSTR := SQLSTR || DONC.COL || DONC.OPR||':'|| DONC. RU3 ||'OR ';
END LOOP;

SQLSTR := REGEXP_REPLACE(SQLSTR, 'OR ', ')');

DBMS_SQL.PARSE(CUR, SQLSTR, DBMS_SQL.NATIVE);

SELECT OWL
INTO VAL
FROM MRUL
WHERE RU2 = '20';

DBMS_SQL.BIND_VARIABLE(CUR, ':OWL', WS);

FOR DONC IN TION
LOOP
  DBMS_SQL.BIND_VARIABLE(CUR, ':'|| DONC.RU3, DONC. VAL);
END LOOP;

SER := DBMS_SQL.EXECUTE(CUR);  
REFCUR := DBMS_SQL.TO_REFCURSOR(CUR);

FETCH REFCUR BULK COLLECT INTO SERULT;


END;

Updating the create and insert queries.

 CREATE TABLE RDET
 (
 COL VARCHAR2(50) NOT NULL,
 VAL VARCHAR2(50) NOT NULL,
 OPR VARCHAR2(50) NOT NULL,
 RU3 VARCHAR2(50) NOT NULL
 );

 CREATE TABLE RMAP
 (
 RU2 VARCHAR2(50) NOT NULL,
 RU3 VARCHAR2(50) NOT NULL
 );

 CREATE TABLE MRUL
 (
 OWL VARCHAR2(50) NOT NULL,
 RU2 VARCHAR2(50) NOT NULL
 );

 INSERT INTO RDET
 VALUES (‘DELHI’, ‘CITY’, ‘=’, ‘60’);
 INSERT INTO RDET
 VALUES (‘SHIMLA’, ‘VILLAGE’, ‘<>’, ‘61’);
 INSERT INTO RDET
 VALUES (‘NOIDA’, ‘TOWN’, ‘=’, ‘62’);

 INSERT INTO RMAP
 VALUES (‘20’, ‘60’);
 INSERT INTO RMAP
 VALUES (‘21’, ‘61’);
 INSERT INTO RMAP
 VALUES (‘21’, ‘62’);

 INSERT INTO MRUL
 VALUES (‘COUNTRY’, ‘20’);
 INSERT INTO MRUL
 VALUES (‘CONTINENT, ‘21’);
 INSERT INTO MRUL
 VALUES (‘AREA’, ‘22’);
 INSERT INTO MRUL
 VALUES (‘AREA’, ‘23’);

Well, what certainly IS wrong is column names you used. `3RU` is an invalid column name in Oracle, unless you used double quote while creating it. Fix that first (or post test case - CREATE TABLE and INSERT INTO statements - so that we'd know what we're dealing with). — Littlefoot, Aug 25 '21 at 18:30
@Littlefoot I put create table and insert table statements in order to better understand the requirement. — Cool_Oracle, Aug 25 '21 at 19:20
A few things: (1) please ensure your `CREATE TABLE` and `INSERT INTO` statements work: you have used 'smart' quotes throughout the `INSERT INTO` statements and one line is missing a quote, (2) just to be clear, the error is with the syntax of the dynamic SQL query, not with the code generating it, (3) to better debug the generation of this query, use `DBMS_OUTPUT` to write out the value of `SQLSTR` before you attempt to parse it. That way you can see what the query you're trying to run is. — Luke Woodward, Aug 25 '21 at 20:49
The issue was that the SQL statement was incorrected suffixed with 'OR'. So, did an RTRIM to remove it and the procedure worked. — Cool_Oracle, Aug 31 '21 at 16:21

score 0 · Accepted Answer · answered Aug 31 '21 at 16:22

0

The issue was that the SQL statement was incorrectly suffixed with 'OR'. So, did an RTRIM to remove it and the procedure worked.

answered Aug 31 '21 at 16:22

Cool_Oracle

311
1
4
15

Dynamic PL-SQL programming - Issue with DBMS_SQL.PARSE

1 Answers1