Retrieving Column Names and Data types in PLSQL

Question

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

score 3 · Accepted Answer · edited May 23 '17 at 12:17

3

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.

edited May 23 '17 at 12:17

Community

1
1

answered Aug 07 '12 at 01:53

Daniel Sh.

2,078
5
42
67

my major concern is how do I declare a variable that would accept values from your select statement , considering the fact that the data types could be date , Number , char varchar etc – user1576782 Aug 07 '12 at 01:55
I'm not sure what you're trying to accomplish with that variable. But I'd place the result of the data_type query in a plain simple string (convert or do what you have to) and the move forward from there. – Daniel Sh. Aug 07 '12 at 02:00
Well , my current query returns all the tables with columns , I wish to return the data types as well. I am confused as to how to use the select statement given by you. Can you give a coding example?? – user1576782 Aug 07 '12 at 02:03
When you Open cursor number 2, you FETCH get_columns into the column_name variable, you should add the other one too (data_type). Your cursor 2 definition selects 2 things, and you want to place those two things into one variable. – Daniel Sh. Aug 07 '12 at 02:20
exactly my question , what should be the datatype for v_data_type? I apologize if this is being stretched but its this small thing that I cant get hold of – user1576782 Aug 07 '12 at 02:34
if you want a single variable to hold all the data types inside, then you should declare it as string (varchar) and CAST every FETCH result to varchar. Then you move on. – Daniel Sh. Aug 07 '12 at 02:46
I think I figured it out , Thanks Daniel!! – user1576782 Aug 07 '12 at 02:47
In sqlDeveloper, it only lists a subset of the tables I have access to. does anyone know a work around? ty – 3pitt Nov 09 '17 at 16:13

score 3 · Answer 2 · answered Aug 07 '12 at 23:21

3

Good effort, but too much code. you need a short vacation :)

SELECT table_name,
       column_name,
       data_type,
       data_length,
       nullable
FROM cols
WHERE table_name='&YOUR_TABLE'
ORDER BY column_id

answered Aug 07 '12 at 23:21

Faruk AZAKLI

101
2

score 0 · Answer 3 · answered Aug 07 '12 at 17:15

Your block has way too much code. This is all you need:

begin
    for r in ( select column_name, data_type
               from    user_tab_columns 
               where table_name = upper('&&p_1')
               order by column_id )
    loop
        dbms_output.put_line(r.column_name ||' is '|| r.data_type );
    end loop;

end;

score 0 · Answer 4 · edited May 23 '17 at 12:09

0

I encountered a similar problem. It can be viewed here: Retrieving Table Structure with Dynamic SQL.

Of things to note, I made sure that if data_scale = 0 I indicated it was to be a Integer, and if it was >0, it was a Double.

edited May 23 '17 at 12:09

Community

1
1

answered Aug 18 '16 at 20:47

SandPiper

2,816
5
30
52

Retrieving Column Names and Data types in PLSQL

4 Answers4

Linked