Home » SQL & PL/SQL » SQL & PL/SQL » If i am not specify data length in the parameters , how much it will take , please can u help me
If i am not specify data length in the parameters , how much it will take , please can u help me [message #571546] Tue, 27 November 2012 01:14 Go to next message
raghusql
Messages: 2
Registered: November 2012
Location: Hyderabad
Junior Member
CREATE OR REPLACE Procedure KCB.PROC_ReportDet_Save
(
dbIdReportDetail integer,
dbIdReport integer,
dbIdReportType integer,
dbXAxis varchar2,
dbYAxis varchar2,
dbSeries varchar2,
dbreportcolumns varchar2,
dbDataSource varchar2(255),
dbDataSourceId integer,
dbLevelId integer,
dbCreatedBy integer,
dbModifiedBy integer,
dbIdChartType integer,
cur_reportDetails OUT SYS_REFCURSOR
)
as
begin

IF(dbIdReportDetail = 0 OR dbIdReportDetail = '')
THEN
insert into Reports_Details
(
IdReportDetail,
IdReport,
IdReportType,
XAxis,
YAxis,
Series,
ReportColumns,
DataSource,
DataSourceId,
LevelId,
createdby,
--CreatedDate,
modifiedby,
-- ModifiedDate,
IdChartType
)
values
(
my_id_sequence.nextval,
dbIdReport,
dbIdReportType,
dbXAxis,
dbYAxis,
dbSeries,
dbReportColumns,
dbDataSource,
dbDataSourceId,
dbLevelId ,
dbcreatedby,
--SYSDATE,
dbmodifiedby,
--SYSDATE,
dbIdChartType
);

COMMIT;

open cur_reportDetails for select my_id_sequence.currval from dual;
ELSE
Update Reports_Details SET
IdReport=dbIdReport,
IdReportType=dbIdReportType,
XAxis=dbXAxis,
YAxis=dbYAxis,
Series=dbSeries,
ReportColumns=dbReportColumns,
DataSource=dbDataSource,
DataSourceId=dbDataSourceId,
LevelId=dbLevelId ,
createdby=dbcreatedby,
--CreatedDate=SYSDATE,
modifiedby=dbmodifiedby,
-- ModifiedDate=SYSDATE,
IdChartType=dbIdChartType
WHERE IdReportDetail=dbIdReportDetail;
COMMIT;
open cur_reportDetails for select dbIdReportDetail from dual ;
END IF;
END;
/

Re: If i am not specify data length in the parameters , how much it will take , please can u help me [message #571548 is a reply to message #571546] Tue, 27 November 2012 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 27 November 2012 08:11
...read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

And with any SQL or PL/SQL question, please, post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Re: If i am not specify data length in the parameters , how much it will take , please can u help me [message #571551 is a reply to message #571546] Tue, 27 November 2012 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot give a length in parameters type.
If you want to constrain the parameters, you have to create your own type.

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel

Re: If i am not specify data length in the parameters , how much it will take , please can u help me [message #571554 is a reply to message #571551] Tue, 27 November 2012 01:30 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

It will show compilation error when it finds any parameter with length.

Error: PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.

Regards,
Nathan

Re: If i am not specify data length in the parameters , how much it will take , please can u help me [message #571556 is a reply to message #571554] Tue, 27 November 2012 01:38 Go to previous message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 27 November 2012 08:22
You cannot give a length in parameters type.
If you want to constrain the parameters, you have to create your own type.

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel

Previous Topic: Connect By Query Problem
Next Topic: Finding the dependency in Oracle
Goto Forum:
  


Current Time: Thu Oct 23 08:57:15 CDT 2014

Total time taken to generate the page: 0.11272 seconds