| Describing Record Type created in Package Specification [message #290711] |
Mon, 31 December 2007 03:47  |
amitmohangupta
Messages: 9
Registered: April 2007
Location: India
|
Junior Member |
 
|
|
Hi,
I was facing one problem. Problem was like I created one record type in a Package Specification and I wanted that user will get all the column name of a perticular type that I defined in a package.
CREATE OR REPLACE PROCEDURE get_columns_from_type_record(in_type_name VARCHAR2) IS
over dbms_describe.number_table;
posn dbms_describe.number_table;
levl dbms_describe.number_table;
arg dbms_describe.varchar2_table;
dtyp dbms_describe.number_table;
defv dbms_describe.number_table;
inout dbms_describe.number_table;
len dbms_describe.number_table;
prec dbms_describe.number_table;
scal dbms_describe.number_table;
n dbms_describe.number_table;
iodesc VARCHAR2(6);
qte CHAR := CHR(39);
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE PROCEDURE DUMMY_PROC_CSV(DUMMY '||IN_TYPE_NAME||') IS
BEGIN
NULL;
END;';
dbms_describe.describe_procedure('DUMMY_PROC_CSV', NULL, NULL,
over, posn, levl, arg, dtyp, defv, inout, len ,prec , scal, n, n);
FOR i IN 1..over.COUNT
LOOP
IF dtyp(i) <> 250 THEN
INSERT INTO csv_type_columns VALUES(arg(i));
END IF;
END LOOP;
EXECUTE IMMEDIATE 'DROP PROCEDURE DUMMY_PROC_CSV';
END;
This procedure creates one procedure in which I am passing the type for which user want to see colunm and then i insert the data into one table. I am doing this because DBMS_DESCRIBE package has few limitations.
|
|
|
|
| Re: Describing Record Type created in Package Specification [message #290718 is a reply to message #290711] |
Mon, 31 December 2007 04:31  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I don't know what is the business need you want to achieve with this but I'm sure this is not the correct way.
Actually I don't understand what you want to achieve.
In addition, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
