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

Question

0.00/5 (No votes)

See more:

SQL

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 19:18pm

philiplobo

Updated 17-Oct-12 3:01am

Jörgen Andersson

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Jörgen Andersson · Answer 1 · 2012-10-17T03:10:00

Solution 1

You can't run DDL directly inside the procedure.

Try this construction instead:

SQL

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'
...

Posted 17-Oct-12 3:10am

Jörgen Andersson

Updated 17-Oct-12 20:31pm

v2

Comments

philiplobo 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 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.

manak chand · Answer 2 · 2012-10-17T03:14:00

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

SQL

CREATE TABLE ROWNUMBER (ROWNUMBER int, BUSINESS varchar(100) );

and if you want to create temprory table then

SQL

DECLARE @ROWNUMBER TABLE(ROWNUMBER int, BUSINESS varchar(100) );