asked on

Finding max column length returned from Perl DBI Oracle query

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.

SOLUTION

Ora_Techie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jkfrench

ASKER

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.

ASKER CERTIFIED SOLUTION

jkfrench

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Suhas .

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.

jkfrench

ASKER

I posted an answer to my own question. Should I go ahead and accept it as the solution?

jkfrench

ASKER

I ended up going about this a different way, but want to award points to riazpk for offering a solution.