Home » SQL & PL/SQL » SQL & PL/SQL » DEBUG PIVOT CODE
DEBUG PIVOT CODE [message #262713] Tue, 28 August 2007 02:15 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
==============================================================
MY  PACKAGE SPECIFICATION
============================================================
PACKAGE pivot1 
     as
        type RefCursor is ref cursor ;
        type VColMaxObj   is ref  cursor ;
        type Array1 is table of varchar2(30)   ;
        Type myarray is table of varchar2(30) 
        index by binary_Integer ;
          procedure pivot(
                           VTable_Name      in varchar2 ,
                           P_Anchor         in Array1   ,
                           P_Pivot          in Array1   ,
                           P_cursor in out RefCursor 
                          );
 end ;
================================================
MY PACKAGE  BODY
==============================================
PACKAGE BODY pivot1  
    as
      procedure pivot( vtable_name      in varchar2 ,
                     p_anchor         in array1 ,
                     p_pivot          in array1 ,
                     p_cursor in out refcursor ) 
      as
       l_max_cols number ;
       l_query    long ;
       My_Query long ;
       L_Cnames   array1 ;
       colmax   VColMaxobj ;
       colmax1   VColMaxobj ;
       Total_Rows number ;
       k  binary_integer := 1 ;
       i number ;
       c1 refcursor ;
       my_array1  myarray ;
       field varchar2(30) ;
    begin
       -- figure out the number of columns we must support
       -- we either KNOW this or we have a query that can tell us
       -- Now, construct the query that can answer the question for us...
       -- start with the C1, C2, ... CX columns :
       l_query := 'select ' ;
       for i in 1 .. p_anchor.count
       loop
       	l_query := l_query || p_anchor(i) || ','  ;
       	end loop ;
       -- Now add in the C{x+1}... CN columns to be pivoted:
       -- the format is "max(decode(rn,1,C{X+1},null)) cx+1_1"
       --
       -- to check the number  of  
       --
       -- retrieve all the values of columns in an array
       --
       my_query := 'select ALL '|| p_pivot(1) || 'from ' || VTable_Name  ; 
       --
       --i := 1 ;
       --
       --raise_application_error(-20557, 'It''s ok here..............') ;
       OPEN colmax1 FOR my_query ;
       LOOP
       	FETCH colmax1 INTO  field  ;
       	EXIT WHEN colmax1%NOTFOUND  ;
       	--raise_application_error(-20558, 'value  is '||field)  ;
       	my_array1(k) := to_char(field)  ;
       	--copy(field , 'my_array(i)'); 
       	k := k + 1  ;
       END LOOP;
       CLOSE colmax1  ;
       --
       -- cursor  to find the total nmumber  of  rows
       --
       open ColMax for
        ' select count(*) from '|| vtable_name || ' group by '|| p_pivot(1)  ;
       fetch ColMax into Total_Rows   ;
       close ColMax   ; 
	     --
	     --raise_application_error(-20559, 'It''s ok here..............')  ;
	     for i in 1 .. Total_Rows
       loop
       	for j in 1 .. p_pivot.count  
         loop
           	 
               l_query := l_query ||
                   'sum(decode(p_pivot(j),'||my_array1(i)||','||
                               p_pivot(j)||',null)) ' ||
                               p_pivot(j) || '_' || i || ','   ;
                               
         end loop  ;
       end loop  ;
       --
       -- Now just add in the original query
       l_query := rtrim(l_query,',')||' from ( '||VTable_Name||') group by '  ;
       -- and then the group by columns.
       --
       for i in 1 .. p_anchor.count
       loop
           l_query := l_query || p_anchor(i) || ',' ;
       end loop ;
       l_query := rtrim(l_query,',')  ;
       -- and return it
       -- execute immediate 'alter session set cursor_sharing=force';
       open c1 for l_query  ;
       -- execute immediate 'alter session set cursor_sharing=exact';
       exception
       	when no_data_found then
       	dbms_output.put_line('@  error = '|| sqlcode ||':' || sqlerrm) ;
    end ;
 end ; 
============================================================
QUERY  TO  EXECUTE  THE  SCRIPT
==========================================================
begin
pivot1.pivot
     (  VTable_Name => 'EMP'  ,
        P_ANCHOR => pivot1.array1 ( 'JOB' , 'MGR' ) ,
        P_PIVOT  => pivot1.array1 ( 'SAL ') ,
        P_Cursor => :x
     );
END;
===================================
I   get  an error  after  i  execute  the  query.
please  debugg  it  for  me , i  am  tired  of testing  it  over and  over
==================================
THANX  AND REGARDS

PEACE


[mod-edit] removed illiterate IM speak

[Updated on: Tue, 28 August 2007 08:23] by Moderator

Report message to a moderator

Re: PLZ DEBUG PIVOT CODE [message #262714 is a reply to message #262713] Tue, 28 August 2007 02:17 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
and also i declare before query
x  variable refcursor
Re: PLZ DEBUG PIVOT CODE [message #262719 is a reply to message #262714] Tue, 28 August 2007 02:22 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I get an error after i execute the query.

What do you think is the next question that is going to be asked?

And by the way, look up the syntax for declaring a bind variable

[Updated on: Tue, 28 August 2007 02:23]

Report message to a moderator

Previous Topic: what is the datatype which supports jpeg files in oracle
Next Topic: Partition Query
Goto Forum:
  


Current Time: Sat Dec 03 22:03:11 CST 2016

Total time taken to generate the page: 0.14457 seconds