Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Access ALL_TAB_COLUMNS in a procedure

OrlandoDiasMay 14 2009 — edited May 15 2009
I wrote the follwing procedure:
PROCEDURE pColuna
(e_owner IN VARCHAR2,
e_nmTab IN VARCHAR2,
e_nmCol IN VARCHAR2,
s_deTip OUT VARCHAR2,
s_nuTam OUT VARCHAR2)
IS
v_data_length VARCHAR2(30);
v_data_precision VARCHAR2(30);
s_demsgrerro varchar2(1000);
BEGIN
SELECT DATA_TYPE, DATA_LENGTH, DATA_PRECISION
INTO s_deTip, v_data_length, v_data_precision
FROM ALL_TAB_COLUMNS
WHERE OWNER = e_owner
AND TABLE_NAME = e_nmTab
AND COLUMN_NAME = e_nmCol;

IF s_deTip = 'VARCHAR2' THEN
s_nuTam := v_data_length;
ELSE
s_nuTam := v_data_precision;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
s_deTip := 'VARCHAR2';
s_nuTam := 10;
END;

For all values passed, the exception NO_DATA_FOUND is raised, even when the row exists in the view.

What can I do?

Tks in advance.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 12 2009
Added on May 14 2009
11 comments
2,722 views