Home » SQL & PL/SQL » SQL & PL/SQL » How to stringify a generic set of columns in a ROWTYPE variable?
How to stringify a generic set of columns in a ROWTYPE variable? [message #219033] Mon, 12 February 2007 11:15 Go to next message
rasa
Messages: 45
Registered: February 2006
Member
Gurus:

I am having a predicament wherein for logging and reporting, I need to stringify my source-record that is stored in a %ROWTYPE variable. Now, if I know the MetaData information while coding (say, for e.g: 1st column is VARCHAR2, 2nd column is DATE, 3rd column is NUMBER), I can conflate those columns with a TO_CHAR on the 2nd and 3rd column info to stringify all the 3 columns like this:
-- Say a table is like this
CREATE TABLE SOMETABLE(
COLUMN1 VARCHAR2(64),
COLUMN2 DATE,
COLUMN3 NUMBER
);

-- In Declaration....
TYPE t_SOURCE_TYPE IS TABLE OF SOMETABLE%ROWTYPE;
v_SOURCE_REC t_SOURCE_TYPE;

v_STRING VARCHAR2(1024);

-- In Functional Block...
v_STRING := v_SOURCE_REC (I).COLUMN1 || ',' || TO_CHAR(v_SOURCE_REC(I).COLUMN2, 'DD-MON-YYYY HH24:MI:SS') || ',' || TO_CHAR(v_SOURCE_REC(I).COLUMN3);


However, is there something within Oracle that will allow me to get the MetaData Information like column1 is VARCHAR2, column2 is DATE, column3 is NUMBER so that I can write a dynamic function that will do the needed TO_CHAR conversions on those DATE, NUMBER columns before conjoining them as one string and returning it?

Kindly point me in the right direction. Please note that my data need not come from a SYS_REFCURSOR. It may be just data sitting in a %ROWTYPE variable.
Re: How to stringify a generic set of columns in a ROWTYPE variable? [message #219356 is a reply to message #219033] Wed, 14 February 2007 02:25 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the data is in a %Rowtype, then you can use the All_Tab_Columns or User_Tab_Columns to determine the data type.
If the data is in a user defined SQL type, then you can use User_Types to get the data.
If the data is in a type which is passed into the procedure as a parameter, you can (with some work) extract the information from the User_Arguments view.

Previous Topic: Restricting an user to access only stored procedures
Next Topic: Checking record collection type for existing data
Goto Forum:
  


Current Time: Mon Dec 05 04:57:10 CST 2016

Total time taken to generate the page: 0.08271 seconds