Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Oracle Exception table
create or replace
PROCEDURE USP_SAVE_BUSINESS
AS 
Ids varchar2(100);
BEGIN  
 
 Select MAX(TO_NUMBER(SUBSTR(BusinessId,2,LENGTH(BusinessId)))) into  Ids 
from ph_mstbusiness ;
 
 CREATE temporary TABLE ROWNUMBER
   (ROWNUMBER int(10) ,
   BUSINESS varchar2(100) );
    ON COMMIT PRESERVE ROWS;
/  
    
    
CREATE SEQUENCE ROWNUMBER_SEQ;
INSERT INTO RowNumber (ROWNUMBER, Business) values (ROWNUMBER_SEQ.nextval, SELECT DISTINCT BUSINESS FROM PH_MSTEMPLOYEE WHERE BUSINESS NOT IN (SELECT BUSINESSNAME FROM PH_MSTBUSINESS) 
 
INSERT INTO PH_MSTBUSINESS
(
BUSINESSID,
BUSINESSNAME
)
VALUES
(
select (case when ((Id + temp.RowNumber) <= 9 
	and (Id + temp.RowNumber) > 0 ) then 
	'B00' + cast( Id+ temp.RowNumber as varchar) 
 
	when ((Id + temp.RowNumber) <= 99 
	and (Id + temp.RowNumber) > 9 ) then 
	'B0' + cast( Id+ temp.RowNumber as varchar) 
	else 'B'+ cast( Id+ temp.RowNumber as varchar) 
 
	 end) as RowID,	
	a.Business
	from (Select Distinct Business from ph_mstemployee WHERE BUSINESS not in (select Businessname from ph_mstbusiness) ) a
		join RowNumber temp on a.Business = temp.Business;
 

)
END usp_save_business ;
can somebody tell me whats wrong with this code its giving error PLS-00103.
Posted 16-Oct-12 20:18pm
Edited 17-Oct-12 4:01am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can't run DDL directly inside the procedure.
 
Try this construction instead:
 
create or replace
PROCEDURE USP_SAVE_BUSINESS
AS 
Ids varchar2(100);
BEGIN  
 
 Select MAX(TO_NUMBER(SUBSTR(BusinessId,2,LENGTH(BusinessId)))) into  Ids 
from ph_mstbusiness ;
 
EXECUTE IMMEDIATE 'CREATE temporary TABLE ROWNUMBER
   (ROWNUMBER int(10) ,
   BUSINESS varchar2(100) );
    ON COMMIT PRESERVE ROWS'
    
EXECUTE IMMEDIATE 'CREATE SEQUENCE ROWNUMBER_SEQ'
...
  Permalink  
v2
Comments
philiplobo at 18-Oct-12 1:52am
   
the error is shown near the create temporary table line . can u giv me the reason why that error is shown there leave apart evrything else that is wrong in there just answer me this first.
Jörgen Andersson at 18-Oct-12 2:36am
   
Updated example code.
The reason for the error is that you can not use DDL such as CREATE or ALTER TABLE directly in a procedure.
I've shown you one way around it.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

try to comment out the below code
 
CREATE SEQUENCE ROWNUMBER_SEQ;
 
as ROWNUMBER_SEQ muse be already created and can directly be used in insert query.
Above code of creating sequence is wrong. please refer below code for creating sequence...
 
CREATE SEQUENCE ROWNUMBER_SEQ
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;
 

Correct syntax for creating table is
 CREATE TABLE ROWNUMBER (ROWNUMBER int, BUSINESS varchar(100) );
and if you want to create temprory table then
DECLARE @ROWNUMBER TABLE(ROWNUMBER int, BUSINESS varchar(100) );
  Permalink  
v4
Comments
philiplobo at 18-Oct-12 1:52am
   
the error is shown near the create temporary table line . can u giv me the reason why that error is shown there leave apart evrything else that is wrong in there just answer me this first.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 277
1 Maciej Los 210
2 BillWoodruff 195
3 DamithSL 195
4 Richard MacCutchan 178
0 OriginalGriff 5,130
1 DamithSL 4,197
2 Maciej Los 3,670
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,821


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 18 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100