Help in oracle procedure

[chella]

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

[amitpatel66]

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

[chella]

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

[amitpatel66]

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!!

[chella]

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

[amitpatel66]

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??

[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??

I am directly executing the code in SQL plus.

Regards,
Chella

[amitpatel66]

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?

[chella]

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

[amitpatel66]

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);

[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);

I hope the problem is with my server.

Thank You vary much for the replies.

Regards,
Chella

[amitpatel66]

I hope the problem is with my server.

Thank You vary much for the replies.

Regards,
Chella

You are Welcome :)

Regards,
Amit