I want to do select from where column_name and table_name should be coming from cursor

Question

I want to do select column_name from table_name where column_name.

table_name should be coming from cursor.

Query:

DECLARE
COLUMN_NAME VARCHAR(50); 
TABLE_NAME VARCHAR(100); 
schema_name VARCHAR(100); 
A VARCHAR(100); 
B VARCHAR(100); 

CURSOR col_cursor IS 
  select col.owner as schema_name, 
       col.table_name, 
       col.column_name 
  from sys.all_tab_columns col 
  inner join sys.all_tables t 
    on col.owner = t.owner and 
       col.table_name = t.table_name 
where col.owner = 'PIYUSH1910_BEFORE' 
AND
      DATA_TYPE = 'NUMBER' 
AND
      DATA_PRECISION IS NULL 
AND
      col.TABLE_NAME NOT LIKE '%ER%'; 

BEGIN
 OPEN col_cursor; 

 LOOP
  FETCH col_cursor INTO schema_name,TABLE_NAME,COLUMN_NAME; 
  EXIT WHEN col_cursor%NOTFOUND;
  EXECUTE IMMEDIATE ' SELECT '||COLUMN_NAME ||' INTO A from ' || Table_Name || 'WHERE'||COLUMN_NAME||'- TRUNC('||COLUMN_NAME||',2) > 0'; 

  dbms_output.Put_line(A); 

END LOOP; 
CLOSE col_cursor; 
END

But its throwing this error:

Error report - ORA-00933: SQL command not properly ended. ORA-06512: at line 29 00933. 00000 - "SQL command not properly ended *Cause: *Action:

Above code is giving error like below Error report - ORA-00933: SQL command not properly ended. ORA-06512: at line 29 00933. 00000 - "SQL command not properly ended *Cause: *Action: — Piyush Ranjan, May 08 '20 at 14:39
Could you check the solution https://stackoverflow.com/questions/61663260/find-a-column-and-a-value-from-a-schema-scan-all-the-table-in-oracle/61663782?noredirect=1#comment109076196_61663782 — VN'sCorner, May 08 '20 at 14:49

score 1 · Answer 1 · answered May 08 '20 at 14:50

INTO clause must be at the end while using the EXECUTE IMMEDIATE as follows:

EXECUTE IMMEDIATE ' SELECT '||COLUMN_NAME ||' from ' 
        || Table_Name || ' WHERE '||COLUMN_NAME||'- TRUNC('||COLUMN_NAME||',2) > 0' 
        INTO A;

Also, space before and after WHERE keyword is added in the above solution.

score 0 · Answer 2 · answered May 08 '20 at 14:52

0

Change your EXECUTE IMMEDIATE to:

EXECUTE IMMEDIATE ' SELECT '|| COLUMN_NAME || ' from ' || Table_Name || 
                  ' WHERE ' || COLUMN_NAME || '- TRUNC('||COLUMN_NAME||',2) > 0'
  INTO A;

This moves the INTO clause to the correct position (in this case it's part of the EXECUTE IMMEDIATE rather than being part of the SELECT) and reformats the code slightly so that column names are separated from e.g. the WHERE keyword.

answered May 08 '20 at 14:52

Bob Jarvis - Слава Україні

48,992
9
77
110

I think you're reading that wrong. When the string substitutions are made it would produce `SELECT SOME_COL from SOME_TABLE WHERE SOME_COL - TRUNC(SOME_COL, 2) > 0`. The assumption is that `SOME_COL` is a numeric column - but there's certainly no arithmetic being performed on the name of the column. – Bob Jarvis - Слава Україні May 08 '20 at 17:58
Jarvis - indeed. Sign me -- "Embarrased". – EdStevens May 08 '20 at 18:00
De nada. It's Friday and we're all sitting at home in our underwear. ??? What do you mean, "Too Much Information"? :-) – Bob Jarvis - Слава Україні May 08 '20 at 18:01

I want to do select from where column_name and table_name should be coming from cursor

2 Answers2