Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 11g,sql developer (view datatype issue)
Oracle 11g,sql developer [message #589761] Wed, 10 July 2013 10:15 Go to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member

CREATE TABLE MST_CD_CNVRSN"
( "POR_CD" CHAR(2) NOT NULL ENABLE,
"PRDCTN_FMLY_CD" CHAR(6) NOT NULL ENABLE,
"END_ITM_MDL_CD" CHAR(18) NOT NULL ENABLE,
"MDL_GRP_CD" CHAR(15),
"LCL_MDL_DSCRPTN" VARCHAR2(30),
"MDL_YR" CHAR(4),
"PRDCTN_YR_FRM" CHAR(4),
"PRDCTN_YR_TO" CHAR(4),
"ORDRBL_FLG" CHAR(1),
"CRTD_BY" CHAR(20),
"CRTD_DT" TIMESTAMP (6),
"UPDTD_BY" CHAR(20),
"UPDTD_DT" TIMESTAMP (6),
"PRDCTN_YR" VARCHAR2(255 CHAR)

);


CREATE TABLE "SCSK_DEV"."MST_SPC_CR_SRS"
( "POR_CD" CHAR(2) NOT NULL ENABLE,
"PRDCTN_FMLY_CD" CHAR(6) NOT NULL ENABLE,
"END_ITM_MDL_CD" CHAR(18) NOT NULL ENABLE,
"PLN_YM" CHAR(6) NOT NULL ENABLE,
"WK_NO" CHAR(2) NOT NULL ENABLE,
"CAR_SRS" CHAR(5) NOT NULL ENABLE,
"FCTRY_CD_1" CHAR(2),
"FCTRY_CD_2" CHAR(2),
"FCTRY_CD_3" CHAR(2),
"MNFCTRNG_MTHD_CD" CHAR(1) NOT NULL ENABLE,
"CRTD_BY" CHAR(20),
"CRTD_DT" TIMESTAMP (6),
"UPDTD_BY" CHAR(20),
"UPDTD_DT" TIMESTAMP (6)
);


DROP view MDL_GRP_VIEW_NEW;
create view MDL_GRP_VIEW_NEW AS
SELECT DISTINCT B.MDL_GRP_CD,A.CAR_SRS
FROM MST_SPC_CR_SRS A,
MST_CD_CNVRSN B
WHERE A.POR_CD = B.POR_CD
AND A.PRDCTN_FMLY_CD = B.PRDCTN_FMLY_CD
AND A.END_ITM_MDL_CD = B.END_ITM_MDL_CD
AND B.POR_CD =
(SELECT VL_1 FROM MST_PRMTR WHERE SEQ=1 AND KEY_1 = 'POR_DOM'
)
UNION ALL
SELECT DISTINCT A.MDL_GRP_CD,A.MDL_GRP_CD
FROM MST_CD_CNVRSN A
WHERE A.POR_CD <>
(SELECT VL_1 FROM MST_PRMTR WHERE SEQ=1 AND KEY_1 = 'POR_DOM'
);


Here MDL_GRP_CD char(15) and CAR_SRS char(5).So this view having datatype as varchar(15) for CAR_SRS.I need to change this varchar(15) to char(15) in view.how can i proceed?

MDL_GRP_VIEW_NEW COLUMNS:

COLUMN NAME DATA TYPE NULLABLE DATA DEFAULT
MDL_GRP_CD CHAR(15 BYTE) Yes 1
CAR_SRS VARCHAR2(15 BYTE) Yes 2
Re: Oracle 11g,sql developer [message #589764 is a reply to message #589761] Wed, 10 July 2013 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
use of CHAR() datatype should always be avoided.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Oracle 11g,sql developer [message #589765 is a reply to message #589764] Wed, 10 July 2013 10:30 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi,
Client asked to use char datatype.
Re: Oracle 11g,sql developer [message #589768 is a reply to message #589765] Wed, 10 July 2013 10:54 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
>Client asked to use char datatype.
Then have client provide solution for this problem that the client created.


IMO, client have NO say regarding column datatypes.
Re: Oracle 11g,sql developer [message #589801 is a reply to message #589761] Thu, 11 July 2013 00:09 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
They are using another application.That uses char datatype.Our app will support the existing one.So we forced to create char datatype.
Re: Oracle 11g,sql developer [message #589805 is a reply to message #589801] Thu, 11 July 2013 00:21 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What problems do they expect? VARCHAR2 to CHAR might cause more problems than vice versa, I guess.
Re: Oracle 11g,sql developer [message #589807 is a reply to message #589801] Thu, 11 July 2013 00:43 Go to previous message
Lalit Kumar B
Messages: 2079
Registered: May 2013
Location: World Wide on the Web
Senior Member
siraj.accet@gmail.com wrote on Thu, 11 July 2013 10:39
They are using another application.That uses char datatype.Our app will support the existing one.So we forced to create char datatype.


A CHAR is nothing different but a VARCHAR that is blank padded to its maximum length. Just read this sentence as many times till you understand it's consequences. Having said that, do let your client know that changing from VARCHAR to CHAR might seem an easy solution now, however, changing CHAR back to VARCHAR would simply become impossible. For that matter, I wouldn't even use CHAR(1) ever...never ever...

In short, you would do nothing good to DB by just blank padding your table columns to the maximum length.

[Updated on: Thu, 11 July 2013 00:44]

Report message to a moderator

Previous Topic: case statement
Next Topic: UTL_FILE
Goto Forum:
  


Current Time: Thu Aug 21 19:04:11 CDT 2014

Total time taken to generate the page: 0.61286 seconds