| Today I came across different space issue in 12c DB (merged 2) [message #660919] |
Wed, 01 March 2017 06:36  |
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  |
Solomon Yakobson
Messages: 3273
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 |
 |
BlackSwan
Messages: 26766
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 |
Bill B
Messages: 1971
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
