variabile varchar non è valorizzata correttamente ?

Question

Ciao a tutti,

spero possiate aiutarmi, ho questo script

DECLARE

  match_count INTEGER;

  v_owner VARCHAR2(255) :='SCOTT';

  v_data_type VARCHAR2(255) :='VARCHAR2';

  v_search_string VARCHAR2(4000) :='DH00';

  query_str varchar2(1000);

BEGIN

  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type and data_length =4)

  LOOP

    query_str := 'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name|| ' = ' ||v_search_string ;

    dbms_output.put_line(query_str);

    EXECUTE IMMEDIATE

    query_str

    INTO match_count

    USING v_search_string;

END

che mi restituisce ORA-00904: "DH00": invalid identifier

Non riesco a capire, la variabile è valorizzata correttamente

Grazie a tutti

//Enrico

Answer 1

Così dovrebbe funzionare:

set serveroutput on

DECLARE

  match_count INTEGER;

  v_owner VARCHAR2(255) :='SCOTT';

  v_data_type VARCHAR2(255) :='VARCHAR2';

  v_search_string VARCHAR2(4000) :='DH00';

  query_str varchar2(1000);

BEGIN

  FOR t IN (SELECT owner, table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type and data_length =4)

  LOOP

    query_str := 'SELECT COUNT(*) FROM ' || t.owner || '.' ||t.table_name||' WHERE '||t.column_name|| ' = ''' || v_search_string || '''';

    dbms_output.put_line(query_str);

    EXECUTE IMMEDIATE

    query_str

    INTO match_count;

    dbms_output.put_line(t.owner || '.' || t.table_name || '.' || t.column_name || ': ' || match_count);

  END LOOP;

END;

/