Home » SQL & PL/SQL » SQL & PL/SQL » Today I came across different space issue in 12c DB (merged 2) (PL/SQL Release 12.1.0.2.0 - Production)
Today I came across different space issue in 12c DB (merged 2) [message #660919] Wed, 01 March 2017 06:36 Go to next message
saini006
Messages: 9
Registered: July 2008
Location: hyderabad
Junior Member
HI ALL,
Today I came across different issue in 12c DB, when we run below block Security value is returning with 40 character actual is 10 characters but when I run same block in 11g DB its working fine.
Seems issue in 12c Data base.
The issue is replicating when WITH Clause + Group function in FOR loop, when we run normal query its showing proper value with 10 charaters, issue getting when we run this in for loop, Can some one please help me on this how to handle.

CREATE TABLE PF_TEST (PORTFOLIO VARCHAR2(12), SECURITY CHAR(10));


Insert into PF_TEST (PORTFOLIO,SECURITY) values ('001011','AAPL');
INSERT INTO PF_TEST (PORTFOLIO,SECURITY) VALUES ('001011','ABX');
Insert into PF_TEST (PORTFOLIO,SECURITY) values ('001011','IBM');
Insert into PF_TEST (PORTFOLIO,SECURITY) values ('001011','CM');


set serveroutput on
BEGIN 
FOR REC IN (
WITH PFSECSQRY
      AS (SELECT PORTFOLIO,MAX(SECURITY) SECURITY
           FROM PF_TEST WHERE PORTFOLIO in ('001011')
           group by PORTFOLIO) 
  select * from pfsecsqry


) loop

dbms_output.put_line(rec.security||' '||length(rec.security));

END LOOP;
END;



Thanks in Advance
Re: Today I came across different space issue in 12c DB [message #660922 is a reply to message #660919] Wed, 01 March 2017 06:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Works fine for me:

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 1 07:51:56 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: scott@pdb1sol12
Enter password:
Last Successful login time: Tue Feb 28 2017 13:36:14 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> CREATE TABLE PF_TEST (PORTFOLIO VARCHAR2(12), SECURITY CHAR(10));

Table created.

SQL> Insert into PF_TEST (PORTFOLIO,SECURITY) values ('001011','AAPL');

1 row created.

SQL> INSERT INTO PF_TEST (PORTFOLIO,SECURITY) VALUES ('001011','ABX');

1 row created.

SQL> Insert into PF_TEST (PORTFOLIO,SECURITY) values ('001011','IBM');

1 row created.

SQL> Insert into PF_TEST (PORTFOLIO,SECURITY) values ('001011','CM');

1 row created.

SQL> set serveroutput on
SQL> BEGIN
  2  FOR REC IN (
  3  WITH PFSECSQRY
  4        AS (SELECT PORTFOLIO,MAX(SECURITY) SECURITY
  5             FROM PF_TEST WHERE PORTFOLIO in ('001011')
  6             group by PORTFOLIO)
  7    select * from pfsecsqry
  8  ) loop
  9  dbms_output.put_line(rec.security||' '||length(rec.security));
 10  END LOOP;
 11  END;
 12  /
IBM        10

PL/SQL procedure successfully completed.

SQL>

SY.
Re: Today I came across different space issue in 12c DB [message #660923 is a reply to message #660922] Wed, 01 March 2017 07:32 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
it works for me.

[oracle@vbgeneric dbs]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 1 08:30:33 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 28 2017 15:38:12 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE TABLE PF_TEST (PORTFOLIO VARCHAR2(12), SECURITY CHAR(10));


Insert into PF_TEST (PORTFOLIO,SECURITY) values ('001011','AAPL');
INSERT INTO PF_TEST (PORTFOLIO,SECURITY) VALUES ('001011','ABX');
Insert into PF_TEST (PORTFOLIO,SECURITY) values ('001011','IBM');
Insert into PF_TEST (PORTFOLIO,SECURITY) values ('001011','CM');

Table created.

SQL> SQL> SQL> 
1 row created.

SQL> 
1 row created.

SQL> 
1 row created.

SQL> 
1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
set serveroutput on
BEGIN 
FOR REC IN (
WITH PFSECSQRY
      AS (SELECT PORTFOLIO,MAX(SECURITY) SECURITY
           FROM PF_TEST WHERE PORTFOLIO in ('001011')
           group by PORTFOLIO) 
  select * from pfsecsqry


) loop

dbms_output.put_line(rec.security||' '||length(rec.security));

END LOOP;
END;
SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16  
 17  /
IBM	   10

PL/SQL procedure successfully completed.

SQL> 

Re: Today I came across different space issue in 12c DB [message #660924 is a reply to message #660923] Wed, 01 March 2017 07:46 Go to previous messageGo to next message
Bill B
Messages: 1799
Registered: December 2004
Senior Member
It works for me too. What is the full version number of your 12c database? Also why are you using CHAR(10) for the SECURITY column? When you select length(security) it will ALWAYS be 10. CHAR columns are space padded to the maximum length. You really should be using VARCHR2 or NVARCHAR2 and never CHAR.
Re: Today I came across different space issue in 12c DB [message #660925 is a reply to message #660919] Wed, 01 March 2017 07:59 Go to previous messageGo to next message
joy_division
Messages: 4899
Registered: February 2005
Location: East Coast USA
Senior Member
saini006 wrote on Wed, 01 March 2017 07:36
HI ALL,
Today I came across different issue in 12c DB
Different from what?
Re: Today I came across different space issue in 12c DB [message #660926 is a reply to message #660925] Wed, 01 March 2017 09:18 Go to previous message
Flyby
Messages: 187
Registered: March 2011
Location: Belgium
Senior Member
Perhaps NLS_LENGTH_SEMANTICS was changed from BYTE TO CHAR ?
If you create a column of type CHAR,VARCHAR2,NVARCHAR2 you can specify its length in "byte" or "char"
Depending on your NLS-settings 10 char can be 40 bytes
Ask Tom
Previous Topic: Complex date differences - Bet you can't figure this one out.
Next Topic: Carriage return in column data for spool
Goto Forum:
  


Current Time: Tue Oct 16 02:59:23 CDT 2018