Finding a Column Data Type

[jjob]

Hi,

I'm returning a field and its value from a weak cursor, but I want to process the data based on the data type. Is there any way I can determine the data type of a field returned from a weak cursor?

The cursor should only return a field called 'theField' and its data.

TIA

John

 

[SantaMufasa]

J,

Here is code that I use:

col a heading "Column Name" format a20
col b heading "Data Type" format a20
select column_name a, data_type b
from user_tab_columns
where table_name = 'S_EMP';

Column Name          Data Type
-------------------- ---------
ID                   NUMBER
LAST_NAME            VARCHAR2
FIRST_NAME           VARCHAR2
USERID               VARCHAR2
START_DATE           DATE
COMMENTS             VARCHAR2
MANAGER_ID           NUMBER
TITLE                VARCHAR2
DEPT_ID              NUMBER
SALARY               NUMBER
COMMISSION_PCT       NUMBER

11 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[jjob]

Thanks for the reply Santa ;-)

I'm just about to set off home from here in Cardiff, Wales, to Bristol England - so any reply I will have to pick up from home.

Your answer was fine, but I want to look at the field returned from a cursor, and I'm not sure that I explained sufficiently well what I want, so here it is in detail.

I have a 'report ungenerator'(!) which is PL/SQL, parameter driven, so that I can load a text file (which is simply a mainframe report dumped to file) into an Oracle table. I process these reports and extract the various columns and put them back into into data fields in an Oracle 'report table'. (I know this sounds odd, as we have the data in the existing system, but I am testing, and we are building a new system, so we produce an identically formatted report and the simplest way for us to automatically check is to 'ungenerate' reports back into single tables, rather than using the mainframe tables and comparing them to different tables in the new system!)


The data and column names vary depending upon which report has been sent through the processor. I want to save ANY of the available columns and its data to my single report tables (a different one per report).

In this code is a cursor to return a field and its data. This cursor is provided with a field name and a table and the data is always returned as 'the-field', and I want to test this, on return, to see if it is a NUMBER or VARCHAR2, but it needs to be done from the cursor, as the table may not exist in Oracle and may be built on the fly.

 PROCEDURE get_record ( p_field IN VARCHAR2, p_table IN VARCHAR2, p_where IN VARCHAR2,
              p_records OUT ref_curtype)
  IS

  BEGIN

    OPEN p_records FOR ' SELECT ' || p_field ||
                          'the_field FROM ' || p_table || ' ' || p_where;


  END;

the-field can be any one of the various fields from the input table.

 

[jjob]

Hi Santa,

Excuse my stupidity last week, I was tired, forget my second question, I can see that I only need a function to wqrap your code to get the info I want, as I already know the column and table.

Happy Christmas and thanks again.

John