Home » SQL & PL/SQL » SQL & PL/SQL » Need function which takes input as sql query and returns column number
Need function which takes input as sql query and returns column number [message #254011] Wed, 25 July 2007 08:49 Go to next message
rajesh4851
Messages: 89
Registered: January 2007
Member
Hi,
I have a requirement as follows.
column "qry" in "My_table " contains all the SQL queries.
Now i want a function that accepts as input of "qry" and should return the number of columns.

eg: qry column contains a SQL query as "select empno,ename,deptno from emp".
If i pass this string to the function it should return "3".


Thanks & Regards,
Rajesh4591
Re: Need function which takes input as sql query and returns column number [message #254016 is a reply to message #254011] Wed, 25 July 2007 08:58 Go to previous messageGo to next message
kanalaramu
Messages: 5
Registered: March 2005
Location: Bangalore
Junior Member
DECLARE
L_curr_POS NUMBER := 1;
l_nxt_pos number := 0;
L_LEN NUMBER;
L_STR varchar2(500) := 'SELECT 1, 2, 3, 4, 5 FROM DUAL, DUAL';
l_cnt number := 0;
l_occ number := 0;
BEGIN
SELECT LENGTH( SUBSTR ('SELECT 1, 2, 3, 4, 5 FROM DUAL, dual', 1, INSTR('SELECT 1, 2, 3, 4, 5 FROM DUAL, dual', 'FROM')-1)) INTO L_LEN FROM DUAL;
DBMS_OUTPUT.PUT_LINE ('TOTAL length '||l_len);
FOR i in 1..l_len loop
SELECT INSTR( 'SELECT 1, 2, 3, 4, 5 FROM DUAL, dual', ',',1, l_occ+1)
INTO l_nxt_pos FROM DUAL;
DBMS_OUTPUT.PUT_LINE ('curr pos '||l_nxt_pos);
IF l_nxt_pos > 0 and l_nxt_pos < l_len THEN
l_cnt := l_cnt + 1;
l_occ := l_occ + 1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('TOTAL COLUMNS '||l_cnt+1);
END;
Re: Need function which takes input as sql query and returns column number [message #254107 is a reply to message #254016] Wed, 25 July 2007 14:39 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
declare
    l_theCursor     integer := dbms_sql.open_cursor;
    l_query         varchar2(1000) := 'select * from cat';
    l_colCnt        number := 0;
    l_descTbl       dbms_sql.desc_tab;
begin

    dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt,  l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_output.put_line( '"' || l_descTbl(i).col_name || '"' );
    end loop;
    dbms_output.put_line( 'num columns: '||to_char(l_colCnt) );
    dbms_sql.close_cursor(l_theCursor);
end;
/ 


"TABLE_NAME"
"TABLE_TYPE"
num columns: 2

Statement processed.


[Updated on: Wed, 25 July 2007 14:41]

Report message to a moderator

Re: Need function which takes input as sql query and returns column number [message #254344 is a reply to message #254107] Thu, 26 July 2007 09:33 Go to previous messageGo to next message
rajesh4851
Messages: 89
Registered: January 2007
Member
Thanks , its working.
Re: Need function which takes input as sql query and returns column number [message #254820 is a reply to message #254344] Sat, 28 July 2007 14:10 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
SQL> with t as (select 'select empno, ename,deptno from emp' qry from dual)
  2  select qry,
  3         length(substr(qry, 1, instr(qry, ' from ') - 1)) -
  4         length(replace(substr(qry, 1, instr(qry, ' from ') - 1), ',')) + 1 num_col,
  5         length(regexp_replace(regexp_replace(qry,'^(.*?) from.*$','\1'),'[^,]'))+1 num_col2
  6    from t
  7  /

QRY                                    NUM_COL   NUM_COL2
----------------------------------- ---------- ----------
select empno, ename,deptno from emp          3          3

SQ

the only case when it won't work properly -
if one of the columns is aliased like
" from "


possible workaround for regexp solution would be putting
regexp_replace(qry,'".*?"')

instead of qry - inside th inner regexp_replace

[Updated on: Sat, 28 July 2007 14:12]

Report message to a moderator

Re: Need function which takes input as sql query and returns column number [message #254831 is a reply to message #254820] Sun, 29 July 2007 00:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> with t as (select 'select empno, to_char(hiredate, ''dd-mm-yyyy''),deptno from emp' qry from dual)
  2  select qry,
  3         length(substr(qry, 1, instr(qry, ' from ') - 1)) -
  4         length(replace(substr(qry, 1, instr(qry, ' from ') - 1), ',')) + 1 num_col,
  5         length(regexp_replace(regexp_replace(qry,'^(.*?) from.*$','\1'),'[^,]'))+1 num_col2
  6  from t
  7  /

QRY                                                              NUM_COL   NUM_COL2
------------------------------------------------------------- ---------- ----------
select empno, to_char(hiredate, 'dd-mm-yyyy'),deptno from emp          4          4


Counting commas does not work.

SQL> declare
  2      l_theCursor     integer := dbms_sql.open_cursor;
  3      l_query         varchar2(1000) := 'select table_name, to_char(sysdate, ''dd-mm-yyyy''), table_type from cat';
  4      l_colCnt        number := 0;
  5      l_descTbl       dbms_sql.desc_tab;
  6  begin
  7
  8      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
  9      dbms_sql.describe_columns( l_theCursor, l_colCnt,  l_descTbl );
 10
 11      for i in 1 .. l_colCnt loop
 12          dbms_output.put_line( '"' || l_descTbl(i).col_name || '"' );
 13      end loop;
 14      dbms_output.put_line( 'num columns: '||to_char(l_colCnt) );
 15      dbms_sql.close_cursor(l_theCursor);
 16  end;
 17  /
"TABLE_NAME"
"TO_CHAR(SYSDATE,'DD-MM-YYYY')"
"TABLE_TYPE"
num columns: 3

PL/SQL procedure successfully completed.

DBMS_SQL does work
Re: Need function which takes input as sql query and returns column number [message #254839 is a reply to message #254831] Sun, 29 July 2007 02:43 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
Frank wrote on Sun, 29 July 2007 00:19

Counting commas does not work.

Ok, agree that for versions previous to 10g it would be much difficult, but when you have regular expressions in your toolbox, it becomes much easier:

SQL> with t as (select 'select empno, to_char(hiredate, ''dd-mm-yyyy''),deptno from emp' qry from dual)
  2    select qry,
  3           length(regexp_replace(regexp_replace(regexp_replace(qry,' from .*$'),'".*?"|\(.*?\)'),'[^,]'))+1 num_col
  4    from t
  5  /

QRY                                                              NUM_COL
------------------------------------------------------------- ----------
select empno, to_char(hiredate, 'dd-mm-yyyy'),deptno from emp          3

SQL> 

[Updated on: Sun, 29 July 2007 02:49]

Report message to a moderator

Re: Need function which takes input as sql query and returns column number [message #254874 is a reply to message #254839] Sun, 29 July 2007 22:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Didn't work for me on 10.1
  1  with t as (select 'select empno, to_char(hiredate, ''dd-mm-yyyy''),deptno from emp' qry from dual)
  2    select qry,
  3           length(regexp_replace(regexp_replace(regexp_replace(qry,' from .*$'),'".*?"|\(.*?\)'),'[^,]'))+1 num_col
  4*   from t

QRY                                                              NUM_COL
------------------------------------------------------------- ----------
select empno, to_char(hiredate, 'dd-mm-yyyy'),deptno from emp


But how would it go with
select a.empno, a.deptno FROM"EMP"a,"DEPT"b
select '(,")''?' as lotsofchars, empno from emp
select * from emp


Ross Leishman
Re: Need function which takes input as sql query and returns column number [message #254926 is a reply to message #254874] Mon, 30 July 2007 01:35 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
rleishman wrote on Sun, 29 July 2007 22:12
Didn't work for me on 10.1


That's because non-greedy operators are available starting from 10.2, but instead of
".*?"
, you can use
"[^"]*"

The idea is the same Cool

rleishman wrote on Sun, 29 July 2007 22:12

But how would it go with
select a.empno, a.deptno FROM"EMP"a,"DEPT"b
select '(,")''?' as lotsofchars, empno from emp
select * from emp



well, speaking about your additional examples - of course, you never be able to parse
'select * from emp'
, because there are no commas in it at all. In that case you should check the table structure, because it's not clear from the SELECT - how many columns are there in the table.

In that case you still can solve this problem with pure SQL, e.g. using XML:
SQL> var s varchar2(20);
SQL> exec :s:='select * from emp';

PL/SQL procedure successfully completed
s
---------
select * from emp

SQL> select :s sql_text, count(1) col_num from table(xmlsequence(extract(dbms_xmlgen.getxmltype(:s),'/ROWSET/ROW[1]/*')));

SQL_TEXT                                                                            COL_NUM
-------------------------------------------------------------------------------- ----------
select * from emp                                                                         7
s
---------
select * from emp

SQL>


If there are several rows with query texts in the table (as it was in the OP's first post):
SQL> with t as (select 'select * from emp' qry from dual union all
  2             select 'select 1 from dual' from dual)
  3       select qry sql_text, count(1) col_num from t,
  4       table(xmlsequence(extract(dbms_xmlgen.getxmltype(qry),'/ROWSET/ROW[1]/*')))
  5        group by qry
  6  /

SQL_TEXT              COL_NUM
------------------ ----------
select * from emp           7
select 1 from dual          1


[Updated on: Mon, 30 July 2007 01:55]

Report message to a moderator

Re: Need function which takes input as sql query and returns column number [message #254953 is a reply to message #254926] Mon, 30 July 2007 02:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Wow. Awesome demo of the power of the XML functions.

Worth noting however that the dbms_xmlgen.getxmltype call will run the statement in full. Might be a performance problem.

All in all, I'd stick with Andrew Again's solution if I were the OP. Clear, simple, robust, efficient, and compatible back to v7.3 (and possibly 7.0!).

Ross Leishman
Re: Need function which takes input as sql query and returns column number [message #254970 is a reply to message #254953] Mon, 30 July 2007 03:53 Go to previous message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
rleishman wrote on Mon, 30 July 2007 02:25

Worth noting however that the dbms_xmlgen.getxmltype call will run the statement in full. Might be a performance problem.


to fasten the query - you can wrap your query text with
'select * from ('||:s||') where rownum=1'

rleishman wrote on Mon, 30 July 2007 02:25

All in all, I'd stick with Andrew Again's solution if I were the OP. Clear, simple, robust, efficient, and compatible back to v7.3 (and possibly 7.0!).

Well, I just pointed out several other possibilities.
But what choice to take - depends on the perfomance, on the data which the author has.
Maybe I'd also prefer PLSQL solution ))
Previous Topic: "Having clause" restrict row & groups or groups only ?
Next Topic: reg: Integrity constraints
Goto Forum:
  


Current Time: Tue Dec 06 15:49:43 CST 2016

Total time taken to generate the page: 0.08506 seconds