By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,288 Members | 3,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,288 IT Pros & Developers. It's quick & easy.

Help in oracle procedure

P: 51
Hi,

Please help me in debugging the below procedure.

SQL> create or replace procedure pr_test_new
2 as
3 declare sql_stmt varchar2(100);
4 begin
5 sql_stmt:='select * from price';
6 execute immediate sql_stmt;
7 end;
8 /

I am using oracle 8i

While creating this procedure I get the following warnings.

Warning: Procedure created with compilation errors.

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/9 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char binary national character nchar
The symbol "<an identifier>" was substituted for "=" to continue.


6/19 PLS-00103: Encountered the symbol "SQL_STMT" when expecting one
of the following:
:= . ( @ % ; not null range renames default character


Please help me in solving the above problem.

Thanks in advance,
Chella
Oct 29 '07 #1
Share this Question
Share on Google+
11 Replies


amitpatel66
Expert 100+
P: 2,367
Hi,

Please help me in debugging the below procedure.

SQL> create or replace procedure pr_test_new
2 as
3 declare sql_stmt varchar2(100);
4 begin
5 sql_stmt:='select * from price';
6 execute immediate sql_stmt;
7 end;
8 /

I am using oracle 8i

While creating this procedure I get the following warnings.

Warning: Procedure created with compilation errors.

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/9 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char binary national character nchar
The symbol "<an identifier>" was substituted for "=" to continue.


6/19 PLS-00103: Encountered the symbol "SQL_STMT" when expecting one
of the following:
:= . ( @ % ; not null range renames default character


Please help me in solving the above problem.

Thanks in advance,
Chella
Remove the keyword "DECLARE" from your code. DECLARE should be used only in anonymous blocks or triggers and not while creating function,procedures
Oct 29 '07 #2

P: 51
Thank you for the reply.

But when I tried the following code,

declare sql_stmt varchar2(100);
begin
sql_stmt:='select * from parts';
execute immediate sql_stmt;
end;

I get the following error.

ERROR at line 5:
ORA-06550: line 5, column 9:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the
following:
:= . ( @ % ;

Please help me to make this code work. Actually where could be the problem?

Regards,
Chella
Oct 29 '07 #3

amitpatel66
Expert 100+
P: 2,367
Thank you for the reply.

But when I tried the following code,

declare sql_stmt varchar2(100);
begin
sql_stmt:='select * from parts';
execute immediate sql_stmt;
end;

I get the following error.

ERROR at line 5:
ORA-06550: line 5, column 9:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the
following:
:= . ( @ % ;

Please help me to make this code work. Actually where could be the problem?

Regards,
Chella
Are you sure the above code is erroring out?
It is working fine for me!!
Oct 29 '07 #4

P: 51
Yes the above code gives me the error.

I am using oracle 8i. Hope that is not a problem.

Will 8i support 'execute immediate'?

Regards,
Chella
Oct 29 '07 #5

amitpatel66
Expert 100+
P: 2,367
Yes the above code gives me the error.

I am using oracle 8i. Hope that is not a problem.

Will 8i support 'execute immediate'?

Regards,
Chella
Yes oracle 8i supports Dynamic SQL.firstly I checked that only.
Are you executing this code from a file or directly in SQL PLUS??
Oct 29 '07 #6

P: 51
Yes oracle 8i supports Dynamic SQL.firstly I checked that only.
Are you executing this code from a file or directly in SQL PLUS??

I am directly executing the code in SQL plus.

Regards,
Chella
Oct 30 '07 #7

amitpatel66
Expert 100+
P: 2,367
I am directly executing the code in SQL plus.

Regards,
Chella
Just curious,
can you put the declaration sql_stmt in the next line and then check if it executes?
Oct 30 '07 #8

P: 51
Just curious,
can you put the declaration sql_stmt in the next line and then check if it executes?

I have tried that too...But still I have problem in executing the code.

I have a doubt here. Does the error mentioned in the above threads anyway mean that the oracle version which I am using doesn't support dynamic sql?

Regards,
Chella
Oct 30 '07 #9

amitpatel66
Expert 100+
P: 2,367
I have tried that too...But still I have problem in executing the code.

I have a doubt here. Does the error mentioned in the above threads anyway mean that the oracle version which I am using doesn't support dynamic sql?

Regards,
Chella
The problem is its not taking as EXECUTE IMMEDIATE.

Instead, it is taking just EXECUTE command which is basically used to execute any procedure,functions etc.

Eg: Both are same:

EXECUTE proc1(a,b,c);
EXEC proc1(a,b,c);
Oct 30 '07 #10

P: 51
The problem is its not taking as EXECUTE IMMEDIATE.

Instead, it is taking just EXECUTE command which is basically used to execute any procedure,functions etc.

Eg: Both are same:

EXECUTE proc1(a,b,c);
EXEC proc1(a,b,c);
I hope the problem is with my server.

Thank You vary much for the replies.

Regards,
Chella
Oct 31 '07 #11

amitpatel66
Expert 100+
P: 2,367
I hope the problem is with my server.

Thank You vary much for the replies.

Regards,
Chella

You are Welcome :)

Regards,
Amit
Oct 31 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.