Re: How to do the equivalent of 'describe' with a SELECT?

On Mar 31, 5:03 pm, sybra..._at_hccnet.nl wrote:
> On 31 Mar 2007 11:22:11 -0700, "Ramon F Herrera" <r..._at_conexus.net>
> wrote:
>
>
>
> >On Mar 31, 12:05 pm, sybra..._at_hccnet.nl wrote:
> >> On 31 Mar 2007 07:50:06 -0700, "Ramon F Herrera" <r..._at_conexus.net>
> >> wrote:
>
> >> >The SQL code that I need is for a Pro*C/C++ application. Before my
> >> >compiled C program does its main thing, it should check the current
> >> >sizes of the variables in the target table.
>
> >> Why, do you think you can dynamically change the change of variables
> >> of a *compiled* program? That would be very weird.
>
> >> >I would like to be able to change the variables' sizes, for instance,
> >> >from:
>
> >> >VARCHAR(20);
>
> >> >to
>
> >> >VARCHAR(30);
>
> >> >without having to recompile all my C programs. Or at least, reduce the
> >> >impact of such resize.
>
> >> >TIA,
>
> >> >-Ramon
>
> >> In PL/SQL you would declare the variable as
>
> >> <variable> <table>.<column>%type
>
> >> but you would still need to recompile everything (automagically).
> >> May be something similar exists in Pro*C.
> >> Apart from that your task is impossible: you simply can't change
> >> dynamically the limits of *statically* declared variables, or you
> >> would need pointer variables everywhere.
> >> Such is the fate of a developer hardcoding everything and not using
> >> constants.
>
> >> --
>
> >> Sybrand Bakker
> >> Senior Oracle DBA
>
> >This is the situation, Sybrand.
>
> >My application creates delimited <tablename>.dat files and I want to
> >make sure that some fields are properly truncated (or perhaps there is
> >a way to tell SQL*Loader to do the truncation? I rather do it myself,
> >and spare sqlldr the trouble).
>
> >Currently the guy wearing the SQL/PL hat and the C developer hat is
> >just me, but maybe later on, some guy will change the sizes of
> >variables and break things.
>
> >I have some programs that read files from disk and place them in an
> >array. The typical solution is to declare a huge array size which will
> >hopefully never be outgrown.
>
> >I think this should be used:
>
> > if (numberOfLines > MAXLINES) {
> > printf("This program has to be recompiled\n");
> > printf("Change the value of `MAXLINES' to at least %d\n",
> >numberOfLines);
> > exit (99);
> > }
>
> >I am thinking of using a similar approach with the sizes of variables:
> >allow the PL/SQL guy to change them anytime, without having to tell
> >the C developer, who will find out about the change next time he tries
> >to run one of his programs.
>
> >-Ramon
>
> Your post is very contradictory.
> Your application creates .dat files by using SQL*Loader. SQL*Loader is
> suited to read files only!

My Pro*C/C++ application creates .dat files which are later read by SQL*Loader. Since the sqlldr loading process takes a lot of time and produces a lot of verbose output in the *.log file, I am trying to make the .dat files as predigested and clean as possible.

I receive many raw files, in every conceivable layout, upon which I have no control. The first thing I do is to run my application to create canonical .dat files (I receive lots of different format as input, and produce one single delimited file for sqlldr). My program is orders of magnitude faster than SQL*Loader, and therefore I try to do as much as I can in data checking (correct dates, etc.), truncating fields, etc. to offload work from sqlldr. I am even considering checking for uniqueness on my side (with a hash table), in order to create .dat files with the duplicates removed.

One reason I chose delimited records is because there is a lot of white space, zeros and empty fields in the raw files. Delimited files are much smaller.

-Ramon Received on Sat Mar 31 2007 - 20:13:48 CDT