Home » SQL & PL/SQL » SQL & PL/SQL » COLUMN INTO ROWS AND VICE VERSA
COLUMN INTO ROWS AND VICE VERSA [message #36186] Thu, 08 November 2001 21:12 Go to next message
Rajarshi Dasgupta
Messages: 52
Registered: October 2001
Member
HOW CAN I MAKE COLUMNS INTO ROWS AND ROWS INTO COLUMNS FROM A TABLE INSIDE A PL/SQL BLOCK => UPDATE IN ANOTHER TABLE OR CREATE A VIEW BY THAT.

THANX.

----------------------------------------------------------------------
Re: COLUMN INTO ROWS AND VICE VERSA [message #36207 is a reply to message #36186] Fri, 09 November 2001 10:29 Go to previous message
Siva Ram
Messages: 22
Registered: November 2001
Junior Member
Hi Rajashri

It's very big code, if you are not able to see this properly, send an email to me and I'll be more than happy to send you the attachment.
CREATE OR REPLACE FUNCTION tabcount (
   sch IN VARCHAR2,
   tab IN VARCHAR2)
   RETURN INTEGER
IS

/*
|| Dependencies:
||    DBMS_SQL -- Oracle dynamic SQL package
*/
   cur     INTEGER  := DBMS_SQL.OPEN_CURSOR;
   ignore  INTEGER;
   retval  INTEGER;
BEGIN
   DBMS_SQL.PARSE (cur, 'SELECT COUNT(*) FROM ' || sch || '.' || tab, 
DBMS_SQL.NATIVE);
   DBMS_SQL.DEFINE_COLUMN (cur, 1, retval);
   ignore := DBMS_SQL.EXECUTE_AND_FETCH (cur);
   DBMS_SQL.COLUMN_VALUE (cur, 1, retval);
   DBMS_SQL.CLOSE_CURSOR (cur);
   RETURN retval;
END;
/

CREATE OR REPLACE PROCEDURE transpose (
   source_owner IN VARCHAR2,
   source_table IN VARCHAR2,
   target_owner IN VARCHAR2,
   target_table IN VARCHAR2,
   showaction IN BOOLEAN := FALSE
   )
IS
/*
|| Procedure to transpose a table (columns to rows,
|| rows to columns). In Oracle7, this means that the
|| table may not have more than 254 rows. In Oracle8,
|| the limit is raised to 1000 rows.
||
|| You must create the transpose table (target_table)
|| before you run this procedure. That way, you get to
|| determine the transposed column names.
||
|| Dependencies:
||    tabcount -- see above
||    DBMS_SQL -- Oracle dynamic SQL package
*/
   rcount  NUMBER := tabcount (source_owner, source_table);
   cur     INTEGER;
   ignore  INTEGER;
   stmt    VARCHAR2(32767);
   prefix  CHAR(1);

   CURSOR colcur (
      s_owner ALL_TAB_COLUMNS.owner%TYPE,
      s_table ALL_TAB_COLUMNS.table_name%TYPE
      )
   IS
      SELECT column_name
        FROM ALL_TAB_COLUMNS
       WHERE owner = UPPER (s_owner)
         AND table_name = UPPER (s_table)
       ORDER BY column_id;
BEGIN
   IF rcount > 0
   THEN
      cur := DBMS_SQL.OPEN_CURSOR;
      FOR colrec IN colcur (source_owner, source_table)
      LOOP
         prefix := NULL;
         stmt :=
            'INSERT INTO ' || target_owner || '.' || target_table || ' 
SELECT ';

         FOR rnum IN 1 .. rcount
         LOOP
            stmt :=
               stmt ||
               prefix ||
               'MAX(DECODE(ROWNUM,' ||
               rnum ||
               ',' ||
               colrec.column_name ||
               ',NULL))';

            IF rnum = 1
            THEN
               prefix := ',';
            END IF;
         END LOOP;

         stmt := stmt || ' FROM ' || source_owner || '.' || source_table;

         IF showaction
         THEN
            p.l (stmt);
         END IF;

         DBMS_SQL.PARSE (cur, stmt, DBMS_SQL.native);

         ignore := DBMS_SQL.EXECUTE (cur);

         IF showaction
         THEN
            p.l ('INSERT result', ignore);
         END IF;
      END LOOP;

      DBMS_SQL.CLOSE_CURSOR (cur);
   END IF;
END;
/

Thank you and good luck !
Siva Ram
Previous Topic: Row count
Next Topic: how to create a database link
Goto Forum:
  


Current Time: Thu Mar 28 16:42:58 CDT 2024