Home » SQL & PL/SQL » SQL & PL/SQL » Re: Return ColumnNames in lowercase. (split from highjacked thread http://www.orafaq.com/forum/t/175479/0/unread/#msg_648450 by bb)
Re: Return ColumnNames in lowercase. (split from highjacked thread http://www.orafaq.com/forum/t/175479/0/unread/#msg_648450 by bb) [message #648450] Tue, 23 February 2016 08:15 Go to next message
sss111ind
Messages: 636
Registered: April 2012
Location: India
Senior Member

Hi All,

How can the result header returned in lower case. For example
ename ,empno,sal instead of ENAME,EMPNO,SAL. P

Please suggest.

set define off;
CREATE OR replace PACKAGE test_pkg1 
IS 
  TYPE emp_type IS RECORD ( 
    "ename" VARCHAR2(20), 
    "empno" NUMBER, 
    "sal" NUMBER ); 
  TYPE emp_table 
    IS TABLE OF EMP_TYPE; 
  FUNCTION Emp_reverse 
  RETURN EMP_TABLE pipelined; 
END; 

/ 
CREATE OR replace PACKAGE BODY test_pkg1 
AS 
  FUNCTION Emp_reverse 
  RETURN EMP_TABLE pipelined 
  IS 
    out_rec_emp EMP_TYPE; 
    CURSOR emp_cur IS 
      SELECT ename, 
             empno, 
             sal 
      FROM   emp; 
  BEGIN 
      OPEN emp_cur; 

      LOOP 
          FETCH emp_cur INTO out_rec_emp; 

          EXIT WHEN emp_cur%NOTFOUND; 

          pipe ROW(out_rec_emp); 
      END LOOP; 
  END; 
END test_pkg1; 

/ 



select * from table(test_pkg1.emp_reverse);


Regards,
Nathan

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648451 is a reply to message #648450] Tue, 23 February 2016 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
Senior Member
?
It does return in lower case.

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648452 is a reply to message #648451] Tue, 23 February 2016 08:33 Go to previous messageGo to next message
sss111ind
Messages: 636
Registered: April 2012
Location: India
Senior Member
Thanks for response, I am getting values of the column header in upper case but the same header value I need in lower case.


ENAME   EMPNO    SAL
------------------------
MARTIN	7654	1250
BLAKE	7698	3135
CLARK	7782	2695
SCOTT	7788	3000
KING	7839	5000
TURNER	7844	1500
ADAMS	7876	1100

[Updated on: Tue, 23 February 2016 09:37]

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648459 is a reply to message #648452] Tue, 23 February 2016 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once more COPY AND PASTE what do and get like that:
SQL> select * from table(test_pkg1.emp_reverse);
ename                     empno        sal
-------------------- ---------- ----------
SMITH                      7369        800
ALLEN                      7499       1600
WARD                       7521       1250
JONES                      7566       2975
MARTIN                     7654       1250
BLAKE                      7698       2850
CLARK                      7782       2450
SCOTT                      7788       3000
KING                       7839       5000
TURNER                     7844       1500
ADAMS                      7876       1100
JAMES                      7900        950
FORD                       7902       3000
MILLER                     7934       1300

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648486 is a reply to message #648459] Wed, 24 February 2016 01:12 Go to previous messageGo to next message
sss111ind
Messages: 636
Registered: April 2012
Location: India
Senior Member
Thanks Michel,

You are getting the column header in lower case on running the query whereas I am getting in upper case only.
How can I get the column header in lower case.

ENAME	EMPNO	SAL
SMITH	7369	800
ALLEN	7499	1600
WARD	7521	1250
JONES	7566	2975
MARTIN	7654	1250
BLAKE	7698	2850
CLARK	7782	2450
SCOTT	7788	3000
KING	7839	5000
TURNER	7844	1500
ADAMS	7876	1100
JAMES	7900	950
FORD	7902	3000
MILLER	7934	1300

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648487 is a reply to message #648486] Wed, 24 February 2016 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You didn't post what you did.
Anyway, whatever you did, as it works for me and not for you, you did it wrong.

[Updated on: Wed, 24 February 2016 01:17]

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648489 is a reply to message #648487] Wed, 24 February 2016 01:55 Go to previous messageGo to next message
sss111ind
Messages: 636
Registered: April 2012
Location: India
Senior Member
Hi Michel,

I have just compiled the package and running the below query
select * from table(test_pkg1.EMP_reverse);

I wonder what is the possible cause of the difference in the output.

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648490 is a reply to message #648489] Wed, 24 February 2016 01:57 Go to previous messageGo to next message
sss111ind
Messages: 636
Registered: April 2012
Location: India
Senior Member
To achieve this I have modified the query as

select ename "ename", empno "empno", sal "sal" from (select * from table(test_pkg1.emp_reverse));

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648491 is a reply to message #648489] Wed, 24 February 2016 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I have just compiled the package and running the below query


If you way so, should I believe you?
Prove it and COPY AND PASTE what you do.
I want to see if what you say is actually what you do.

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648494 is a reply to message #648491] Wed, 24 February 2016 02:28 Go to previous messageGo to next message
sss111ind
Messages: 636
Registered: April 2012
Location: India
Senior Member
Hi Michel ,

Please find the details.

SQL> set define off;
CREATE OR REPLACE PACKAGE           test_pkg1
is
 TYPE emp_type
  IS
    RECORD
    (  "ename"       varchar2(20),
      "empno"   number,
      "sal"   number
      );

  TYPE emp_table
  IS
    TABLE OF emp_type;

  function emp_reverse
    RETURN emp_table pipelined;

end;
/


CREATE OR REPLACE PACKAGE BODY            test_pkg1 AS

    function emp_reverse
    RETURN emp_table pipelined is

    out_rec_emp emp_type;
    cursor emp_cur is
      SELECT ename "ename",empno "empno",sal "sal" from emp;
  BEGIN
      OPEN emp_cur;

      LOOP
          FETCH emp_cur INTO out_rec_emp;

          EXIT WHEN emp_cur%NOTFOUND;

          pipe ROW(out_rec_emp);
      end loop;
  end;

  end test_pkg1;
/
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19
Package created.

SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22
Package body created.

SQL> select * from table(test_pkg1.EMP_reverse);

ENAME                     EMPNO        SAL
-------------------- ---------- ----------
SMITH                      7369        800
ALLEN                      7499       1600
WARD                       7521       1250
JONES                      7566       2975
MARTIN                     7654       1250
BLAKE                      7698       2850
CLARK                      7782       2450
SCOTT                      7788       3000
KING                       7839       5000
TURNER                     7844       1500
ADAMS                      7876       1100

ENAME                     EMPNO        SAL
-------------------- ---------- ----------
JAMES                      7900        950
FORD                       7902       3000
MILLER                     7934       1300

14 rows selected.

SQL>


Regards,
Nathan

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648495 is a reply to message #648494] Wed, 24 February 2016 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 

show parameter cursor

Report message to a moderator

Re: Return ColumnNames in lowercase. [message #648496 is a reply to message #648495] Wed, 24 February 2016 02:52 Go to previous message
sss111ind
Messages: 636
Registered: April 2012
Location: India
Senior Member
 
NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
cursor_sharing                                     string      EXACT                                                                                                
cursor_space_for_time                              boolean     FALSE                                                                                                
open_cursors                                       integer     2000                                                                                                 
session_cached_cursors                             integer     20

Report message to a moderator

Previous Topic: ORA-00001: unique constraint (UNKNOWN.obj#=179690) violated
Next Topic: General question about temporary tables
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue May 07 09:46:11 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.