title: Retrieving Column Names and Data types in PLSQL
body: <p>I am writing a PL SQL block that retrieves all the columns and the data types of the tables in the database. I am able to get the columns , but not the datatypes. Looking for suggestions for a good approach. Any help would be appreciated. My code is as follows</p>
  
  <pre><code>ACCEPT p_1 PROMPT 'Please enter the Table Name'
  
  DECLARE
      v_table_name    VARCHAR2(40) :='&amp;p_1';
      -- First cursor 
      CURSOR get_tables IS    
          SELECT DISTINCT table_name 
          FROM user_tables 
          WHERE UPPER(table_name) = UPPER(v_table_name);
      --Second cursor 
      CURSOR get_columns IS
          SELECT DISTINCT column_name
          FROM user_tab_columns
          WHERE table_name = v_table_name;
      v_column_name   VARCHAR2(100);
      -- Third Cursor
      CURSOR get_types IS
          SELECT data_type 
          FROM user_tab_columns
          WHERE table_name = v_table_name;
  
      v_data_type user_tab_columns.data_type%type;
  BEGIN
      -- Open first cursor
      OPEN get_tables;
      FETCH get_tables INTO v_table_name;
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('Table = ' || v_table_name );
      DBMS_OUTPUT.PUT_LINE('=========================');
      CLOSE get_tables;
      -- Open second cursor
      OPEN get_columns;
      FETCH get_columns INTO v_column_name;
      WHILE get_columns%FOUND LOOP
          DBMS_OUTPUT.PUT_LINE('  ' || v_column_name);
          FETCH get_columns INTO v_column_name;
      END LOOP;
      CLOSE get_columns;
      --Open Third Cursor
      OPEN get_types;
      FETCH get_types into v_data_type;
      WHILE get_types%FOUND LOOP
          DBMS_OUTPUT.PUT_LINE(' ' || v_data_type );
          FETCH get_types into v_data_type;
      END LOOP;
  
      CLOSE get_types;     
  END;
  </code></pre>
  
  <p>My error states PLS-00371: at most one declaration for 'V_DATA_TYPE' is permitted</p>