Solved

Finding max column length returned from Perl DBI Oracle query

Posted on 2011-03-09
7
478 Views
Last Modified: 2012-05-11
I'm using Perl DBI to access an Oracle database.

I'm trying to get the actual maximum size of the columns returned from a query, so I can better format the output. I'm working with user-entered queries, so I don't have the luxury of knowing tables and columns in advance. I know that this:
$sth->{PRECISION}[$i]

Open in new window

will return the declared length of a field, but not the max size from the answer set.

This query:
select max(length(<col>)) from <table>

Open in new window

returns the largest entry for a given column which is close to what I need. I'm working with user-entered data, though, and I'm not sure how to get the tables and columns, to use in the above query.

Has anyone had success doing this? My last resort is to load the entire answer set in an array and find the max size by brute force, but that will be really inefficient with a large answer set.
0
Comment
Question by:jkfrench
  • 4
7 Comments
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 500 total points
ID: 35090759
Something like:

SQL> ed
Wrote file afiedt.buf

  1  SELECT empno, ename, job, max(length(ename)) over() mx_length
  2  FROM Scott.emp
  3* WHERE deptno = 10
SQL> /

     EMPNO ENAME                          JOB                          MX_LENGTH
---------- ------------------------------ --------------------------- ----------
      7782 CLARK                          MANAGER                              6
      7839 KING                           PRESIDENT                            6
      7934 MILLER                         CLERK                                6

SQL> ed
Wrote file afiedt.buf

  1  SELECT empno, ename, job, max(length(ename)) over() mx_length
  2  FROM Scott.emp
  3* WHERE deptno = 20
SQL> /

     EMPNO ENAME                          JOB                          MX_LENGTH
---------- ------------------------------ --------------------------- ----------
      7369 SMITH                          CLERK                                5
      7566 JONES                          MANAGER                              5
      7788 SCOTT                          ANALYST                              5
      7876 ADAMS                          CLERK                                5
      7902 FORD                           ANALYST                              5

SQL> ed
Wrote file afiedt.buf

  1  SELECT empno, ename, job, max(length(ename)) over() mx_length
  2  FROM Scott.emp
  3* WHERE deptno = 30
SQL> /

     EMPNO ENAME                          JOB                          MX_LENGTH
---------- ------------------------------ --------------------------- ----------
      7499 ALLEN                          SALESMAN                             6
      7521 WARD                           SALESMAN                             6
      7654 MARTIN                         SALESMAN                             6
      7698 BLAKE                          MANAGER                              6
      7844 TURNER                         SALESMAN                             6
      7900 JAMES                          CLERK                                6

Open in new window

0
 

Author Comment

by:jkfrench
ID: 35112637
Thanks for the reply, riazpk. Sorry for my late reply. I was out of the office in meetings.

That looks similar to what I described above with "select max(length(<col>)) from <table>". The problem is since I'm working with user-entered queries, I don't know in advance what tables and columns will be included in the select.

I'm looking for either how I can get that information back from my Perl DBI (and DBD::Oracle) result set, or a way to get the table.column fields to use in that query without having to completely parse the user input.

I just came across Perl's SQL::Abstract module. Maybe that will help with the parsing.
0
 

Accepted Solution

by:
jkfrench earned 0 total points
ID: 35140241
I ended up solving this by using Perl::DBI to fetch the results into an array of array refs, then making a pass through the array to find the max length of each column. For the size of query results we normally get, this is still pretty fast.

Here is the Perl snippet:

my $dataref = $sth->fetchall_arrayref();
@colLenghts = maxColumnLength(@$dataref);

sub maxColumnLength {
  my @len = ();

  foreach my $rowref (@_) {
    for my $i (0..scalar(@$rowref)-1) {
      my $itemlen = length($rowref->[$i]);
      $len[$i] = $itemlen if ($itemlen > $len[$i]);
    }
  }
  return @len;
}

Open in new window


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:Suhas .
ID: 37300627
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 

Author Comment

by:jkfrench
ID: 37300628
I posted an answer to my own question. Should I go ahead and accept it as the solution?
0
 

Author Closing Comment

by:jkfrench
ID: 37335715
I ended up going about this a different way, but want to award points to riazpk for offering a solution.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now