'ORA-00905: missing keyword' problem 1

[cadoltt]

Hi Everybody,


Here is the simplified sutiation. I have two tables, t1 and t1 having three similar (same data type) columns each. First two colunms serve as row identifiers, and the third column contain some value.

t1:
T1_C1 T1_C2 T1_C3
---------- ---------- ----------
1 1 1.1
1 2 1.2
1 3 1.3
2 1 2.1
2 2 2.2
2 3 2.3
3 1 3.1
3 2 3.2
3 3 3.3


t2:

T2_C1 T2_C2 T2_C3
---------- ---------- ----------
1 2 1.2
2 2 2.2
3 2 3.2



I am writing a procedure to fill in t2 with rows from t1 (only the rows which are absent in t2).


Here it is:

1  CREATE OR REPLACE PROCEDURE p
2  (
3    vc_in_t2_name          in VARCHAR2,
4    vc_in_t2_col2_name     in VARCHAR2,
5    vc_in_t2_col3_name     in VARCHAR2,
6    vc_in_t1_col3_name     in VARCHAR2
7   )
8
9  AS
10
11    vc_L_SQL_str           VARCHAR2(1024);
12    nm_L_t1_c1             NUMBER(2);
13    nm_L_t1_c2             NUMBER(2);
14    nm_L_t1_c3             NUMBER(3,1);
15    nm_L_n_of_records      NUMBER;
16    
17    nm_crs_t1              NUMBER;
18    nm_tmp                 NUMBER;
19
20    
21  BEGIN
22
23    
24    vc_L_SQL_str := 'SELECT t1_c1,t1_c2,t1_c3 FROM t1';
25
26    --DBMS_OUTPUT.put_line(vc_L_SQL_str); --debug
27
28    nm_crs_t1 := DBMS_SQL.OPEN_CURSOR;
29    DBMS_SQL.PARSE(nm_crs_t1,vc_L_SQL_str,DBMS_SQL.NATIVE);  
30
31    DBMS_SQL.DEFINE_COLUMN(nm_crs_t1, 1, nm_L_t1_c1); 
32    DBMS_SQL.DEFINE_COLUMN(nm_crs_t1, 2, nm_L_t1_c2); 
33    DBMS_SQL.DEFINE_COLUMN(nm_crs_t1, 3, nm_L_t1_c3); 
34
35    nm_tmp := DBMS_SQL.EXECUTE(nm_crs_t1); 
36
37
38    LOOP
39     
40      -- read a line from t1
41      IF DBMS_SQL.FETCH_ROWS(nm_crs_t1) > 0 THEN 
42
43        DBMS_SQL.COLUMN_VALUE(nm_crs_t1, 1, nm_L_t1_c1); 
44        DBMS_SQL.COLUMN_VALUE(nm_crs_t1, 2, nm_L_t1_c2); 
45        DBMS_SQL.COLUMN_VALUE(nm_crs_t1, 3, nm_L_t1_c3); 
46
47        -- checks if such a record exists in t2
48        vc_L_SQL_str := 'SELECT count(t2_c1) INTO nm_L_n_of_records FROM ' ||
49        vc_in_t2_name || ' WHERE t2_c1=' || TO_CHAR(nm_L_t1_c1) ||
50        ' AND ' || vc_in_t2_col2_name || '=' || TO_CHAR(nm_L_t1_c2);
51
52        DBMS_OUTPUT.put_line(vc_L_SQL_str); --debug
53        EXECUTE IMMEDIATE(vc_L_SQL_str);
54
55        -- if value in source tbl exists and no such record in target table yet
56        IF nm_L_t1_c3 IS NOT NULL AND nm_L_n_of_records = 0 THEN 
57
58          DBMS_OUTPUT.put_line('New record for #: ' || TO_CHAR(nm_L_t1_c1) || ',' || TO_CHAR(nm_L_t1_c2)); --debug
59
60          vc_L_SQL_str := 'INSERT INTO ' || vc_in_t2_name ||
61          ' (t2_c1, ' || vc_in_t2_col2_name || ',' || vc_in_t2_col3_name ||
62          ') VALUES (' || TO_CHAR(nm_L_t1_c1) || ',' || TO_CHAR(nm_L_t1_c2) ||
63          ',' || TO_CHAR(nm_L_t1_c3);
64
65          DBMS_OUTPUT.put_line(vc_L_SQL_str); --debug
66          EXECUTE IMMEDIATE(vc_L_SQL_str);
67        END IF;
68
69      ELSE 
70
71        -- No more rows to copy: 
72        EXIT; 
73
74      END IF; 
75
76    END LOOP;
77
78    DBMS_SQL.CLOSE_CURSOR(nm_crs_t1); 
79
80    COMMIT;
81
82  EXCEPTION 
83    WHEN OTHERS THEN 
84      IF DBMS_SQL.IS_OPEN(nm_crs_t1) THEN 
85        DBMS_SQL.CLOSE_CURSOR(nm_crs_t1); 
86      END IF; 
87      RAISE; 
88   
89  END p;

I fetch a row from the source table t1 in line 41, then in lines 48-50 I compile a SELECT query to check whether such a record exits in the the target table (t2). That's where I am getting an error at runtime:


SQL> execute p('t2','t2_c2','t2_c3','t1_c3');
SELECT count(t2_c1) INTO nm_L_n_of_records FROM t2 WHERE t2_c1=1 AND t2_c2=1
BEGIN p('t2','t2_c2','t2_c3','t1_c3'); END;

*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "OPIS_RSRC.P", line 87
ORA-06512: at line 1

The resolved SELECT statement looks good to me, I can't get what that 'missing keyword' might be.

Can anyone help?

Thanks,

Alex

 

[SantaMufasa]

I am writing a procedure to fill in t2 with rows from t1 (only the rows which are absent in t2).

If that is your objective, Alex, then this highly simplified code will achieve your objective:

INSERT INTO T2 (SELECT * from t1 MINUS SELECT * from t2);

You can verify the rows that the above code will INSERT into T2 by simply running the SELECT portion of the code.

Let us know why this would not work in your case.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cadoltt]

Mufasa,


Thank you. I just tried and it works perfectly (so much to learn!).

But I still want to write such a procedure the way I did for other purposes. And I am curious what this error is about.

Any clue?


Thanks again!
(This time I don't forget to thank you officially via thankyou button )


Alex

 

[SantaMufasa]

SQL> execute p('t2','t2_c2','t2_c3','t1_c3');
SELECT count(t2_c1) INTO nm_L_n_of_records FROM t2 WHERE t2_c1=1 AND t2_c2=1
BEGIN p('t2','t2_c2','t2_c3','t1_c3'); END;

*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "OPIS_RSRC.P", line 87
ORA-06512: at line 1

Is the above code literally how things appear in the script that you are trying to run? If so, note that SQL*Plus commands never require a terminiating semi-colon (";") and that SQL commands always require a terminiating semi-colon (";").


Therefore, the semi-colon at the end of your (SQL*Plus) "execute" command is extraneous (but not problematic) and the lack of a semi-colon at the end of your subsequent (SQL) SELECT statement is a fatal error, resulting in the "missing keyword" diagnostic.

Let us know if this at least gets you past that diagnostic.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cadoltt]

Mufasa,


Thanks for your involvement. As you said the ';' at the end of the execute statement is extraneous, I tried, and without it it works as well.

You right, we have to put ';' after SQL statements in SQL*Plus. Semicolons in a PL/SQL program body is another thing. If we insert a straight SQL statement in PL/SQL then we end it with a semicolon, but I've always thought of this semicolon as of a PL/SQL feature (requiring to end any statement with a semicolon) which has nothing to do with the SQL*Plus requirement to end SQL statements with semicolons.

But in my case I assemble my SQL statement inside a string (meaning this is not a straight SQL statement in PL/SQL), thus I experimented with what you suggested anyway. So I made a little addition at the end of the line 50 of the code (semicolon in quotes):

' AND ' || vc_in_t2_col2_name || '=' || TO_CHAR(nm_L_t1_c2) || ';';


Now the script is:

SQL> execute p('t2','t2_c2','t2_c3','t1_c3')
SELECT count(t2_c1) INTO nm_L_n_of_records FROM t2 WHERE t2_c1=1 AND t2_c2=1;
BEGIN p('t2','t2_c2','t2_c3','t1_c3'); END;

*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "OPIS_RSRC.P", line 87
ORA-06512: at line 1


And the same thing happened when I modified your yesterday's statement (which as I told you yesterday worked OK).


Seems this not a ';' that causes my 'ORA-00905: missing keyword' error.

....Alex

 

[SantaMufasa]

Alex,

Is the above code literally how things appear in the script that you are trying to run?

Sorry for the confusion here. I asked the question, above, because, since I do not have access to your code, scripts, et cetera, I cannot tell from your post whether your post is/was displaying the content of your script or if it was the result of the error-message display.


I gather now that the SELECT appears, above, as the result of an error diagnostic during the execution of the "p" procedure.

With that clarification getting me back on track, I can clarify that there is a problem with your EXECUTE IMMEDIATE SQL statement construction...The "INTO" portion of the statement follows the construction of the SQL statement string, and should appear as follows:

vc_L_SQL_str := 'SELECT count(t2_c1) '
  || '/* INTO nm_L_n_of_records...notice the "INTO" is commented out */'
  || 'FROM ' ||
 vc_in_t2_name || ' WHERE t2_c1=' || TO_CHAR(nm_L_t1_c1) ||
 ' AND ' || vc_in_t2_col2_name || '=' || TO_CHAR(nm_L_t1_c2);
DBMS_OUTPUT.put_line(vc_L_SQL_str); --debug
EXECUTE IMMEDIATE(vc_L_SQL_str)[b] into nm_L_n_of_records[/b];

Try this construction for all "EXECUTE IMMEDATE" statements and let us know of the results.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cadoltt]

Now it works just fine, Mufasa!

A Purple Star !!!

Alex