user1576782 user1576782 - 3 months ago 16
SQL Question

Retrieving Column Names and Data types in PLSQL

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

ACCEPT p_1 PROMPT 'Please enter the Table Name'

DECLARE
v_table_name VARCHAR2(40) :='&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;


My error states PLS-00371: at most one declaration for 'V_DATA_TYPE' is permitted

Answer

Not a PLSQL guru but here's my grain.

Select data_type from user_tab_columns where TABLE_NAME = 'YourTableName'

Props to Eric, check this thread and his answer.

Remember you can use DESC command to describe an Oracle Table, View, Synonym, package or Function. It will give you name, data_type and lengh. And if this actually works for you, you should be able to get the data for all of your tables, although I'm not a huge fan of cursors, you should do fine.

Try this:

-- Open second cursor
            OPEN get_columns;
            LOOP
            FETCH get_columns INTO v_column_name, v_data_type;
            EXIT WHEN get_columns%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('  ' || v_column_name);
            END LOOP;
            CLOSE get_columns;
    END LOOP;

But be careful on the datatype you've chosen for v_data_type variable.

Comments