Home » SQL & PL/SQL » SQL & PL/SQL » Error while compliling the code (10.2.0.3.0)
Error while compliling the code [message #392192] Mon, 16 March 2009 23:58 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have table as follows.
Eno   Ename   Sal  Deptno
-------------------------
1234  asdf   9000  10
2134  jkiu   8900  10
3454  lfjd   787   20
453   hlll   7687  20
..
..

for retrieving the records for deptno 10 i have written code as follows.
declare
cursor c1 is 
select * from emp where deptno:=&no;
rec c1%rowtype;
begin
open c1;
loop
fetch c1 into rec;
dbms_output.put_line(rec.*)  ( please let me know in this table
                               I have more than 23 columns . I 
                               want to display all those 
                               columns. How can I give this ? 
                               rec.* values. Plese let me know)
end loop;
close c1;
end;

but I am getting the error.Please verify the code and give me suggestions for modifications)
Thank you

Re: Error while compliling the code [message #392193 is a reply to message #392192] Tue, 17 March 2009 00:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>but I am getting the error
ERROR? What Error. I don't see any error.
Re: Error while compliling the code [message #392194 is a reply to message #392193] Tue, 17 March 2009 00:13 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

[1]: (Error): ORA-06550: line 9, column 26: PLS-00103: Encountered the symbol "*" when expecting one of the following: <an identifier> <a double-quoted delimited-identifier> delete exists prior The symbol "<an identifier> was inserted before "*" to continue.

Please let me know what I need to give in "rec.*"
Re: Error while compliling the code [message #392197 is a reply to message #392192] Tue, 17 March 2009 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does the documentation on dbms_output allow such a syntax?

Regards
Michel
Re: Error while compliling the code [message #392199 is a reply to message #392197] Tue, 17 March 2009 00:22 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I am asking you to how to give if I want all those columns ?
that's why I put rec.*.

Thank you
Re: Error while compliling the code [message #392203 is a reply to message #392199] Tue, 17 March 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does the documentation say you can do?

Regards
Michel
Re: Error while compliling the code [message #392211 is a reply to message #392203] Tue, 17 March 2009 01:05 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

I have
schema : TEMP
VIEW : RF_VIEW
Column : date_exe

In pl/sql I define a variable "Last_Exec" with sysdate as default.
I am writing pl/sql code. Here I need to add the condition as
" select * from tempo.RF_VIEW 
           where rf_View.date_exec>=last_exec;


I am getting invalid identifier message . Please correct this query .

Thank you
Re: Error while compliling the code [message #392221 is a reply to message #392211] Tue, 17 March 2009 01:33 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

The code got successfully completed . But it's taking more time for execution. Please look into this.
declare
last_exec date :=sysdate;
cursor c1 is 
select * from temp.RF_VIEW 
         where rf_View.date_exec>=last_exec;
rec c1%rowtype;
begin
open c1;
loop
fetch c1 into rec;
dbms_output.put_line(rec.name||' '||rec.sal);
end loop;
close c1;
end;


thank you

[Updated on: Tue, 17 March 2009 01:48]

Report message to a moderator

Re: Error while compliling the code [message #392247 is a reply to message #392203] Tue, 17 March 2009 03:10 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

Please find the following code and let me know the mistakes.
It's takimg more time for running.
declare
last_exec date :=sysdate;
cursor c1 is 
select * from temp.RF_VIEW 
         where rf_View.date_exec>=last_exec;
rec c1%rowtype;
begin
open c1;
loop
fetch c1 into rec;
dbms_output.put_line(rec.name||' '||rec.sal);
end loop;
close c1;
end;


Thank you
Re: Error while compliling the code [message #392250 is a reply to message #392247] Tue, 17 March 2009 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
More time than what?
Your previous code didn't run at all.
Re: Error while compliling the code [message #392251 is a reply to message #392247] Tue, 17 March 2009 03:17 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Speed is really not important, you will run into an

ORU-10027: buffer overflow


or an

ORU-10028: Line length overflow


first pretty soon anyway, once you output all the columns you say you need.
Re: Error while compliling the code [message #392252 is a reply to message #392247] Tue, 17 March 2009 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More time than what?

Regards
Michel
Re: Error while compliling the code [message #392280 is a reply to message #392252] Tue, 17 March 2009 05:24 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I have created the following procedure.Can you please correct this.
  1  create or replace procedure PR
  2  (no number)
  3  as
  4  gs  p_gs.gs_id%type;
  5  cursor c1 is
  6  select * from s_write.p_gs where p_id:=&no;
  7  rec c1%rowtype;
  8   begin
  9  select gs_id into gs from p_gs;
 10  open c1;
 11   loop
 12  fetch c1 into rec;
 13  exit when c1%notfound;
 14   dbms_output.put_line(rec.GS_ID);
 15  end loop;
 16   close c1;
 17  end
 18* ;
SQL> /
Enter value for no: 1200
old   6: select * from s_write.p_gs where p_id:=&no;
new   6: select * from s_write.p_gs where p_id:=12001;

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE PR_SURPRISE_DELIGHT_LOTERY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1      PL/SQL: SQL Statement ignored
6/52     PL/SQL: ORA-00920: invalid relational operator


Re: Error while compliling the code [message #392283 is a reply to message #392280] Tue, 17 March 2009 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why := ?

Regards
Michel
Re: Error while compliling the code [message #392285 is a reply to message #392192] Tue, 17 March 2009 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
6/52     PL/SQL: ORA-00920: invalid relational operator


There's only 1 relational operator on line 6.
What is it and why are you using it?

Once you've fixed that, ask yourself why you've got an & on that line.

EDIT typo

[Updated on: Tue, 17 March 2009 05:37]

Report message to a moderator

Re: Error while compliling the code [message #392286 is a reply to message #392283] Tue, 17 March 2009 05:37 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I am getting following error if am not giving ":="
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/6      PL/SQL: Item ignored
4/6      PLS-00201: identifier 'P_GS.GS_ID' must be declared
9/1      PL/SQL: SQL Statement ignored
9/33     PL/SQL: ORA-00942: table or view does not exist


Thank you
Re: Error while compliling the code [message #392287 is a reply to message #392286] Tue, 17 March 2009 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because there are not only one error in your code.
This is a common error and FAQ, please for it.

Regards
Michel
Re: Error while compliling the code [message #392291 is a reply to message #392286] Tue, 17 March 2009 05:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is rudimentary pl/sql that you're having problems with.

:= is the Assignment operator

= is the comparison Equality operator
Re: Error while compliling the code [message #392307 is a reply to message #392287] Tue, 17 March 2009 06:30 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

If I am hardcoding
select * from s_write.p_gs where p_id:=&no;
select * from s_write.p_gs where p_id:=12001;

it's working fine. But I want to execute this statement dynamically. can you please help me out in this issue.

Thank you
Re: Error while compliling the code [message #392312 is a reply to message #392307] Tue, 17 March 2009 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it's working fine

Wrong! It could NOT.
Prove it and copy and paste your session.

Regards
Michel
Re: Error while compliling the code [message #392313 is a reply to message #392192] Tue, 17 March 2009 06:37 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Confused

You appear to have declared your procedure with a parameter:
  1  create or replace procedure PR
  2  (no number)
  3  as


Why don't you just use it?
Re: Error while compliling the code [message #392314 is a reply to message #392307] Tue, 17 March 2009 06:41 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
user71408 wrote on Tue, 17 March 2009 06:30
If I am hardcoding
select * from s_write.p_gs where p_id:=&no;
select * from s_write.p_gs where p_id:=12001;

it's working fine. But I want to execute this statement dynamically. can you please help me out in this issue.

Thank you


Quote:
I have created the following procedure.Can you please correct this.

1 create or replace procedure PR
2 (no number)
3 as
4 gs p_gs.gs_id%type;
5 cursor c1 is
6 select * from s_write.p_gs where p_id:=&no;
7 rec c1%rowtype;
8 begin
9 select gs_id into gs from p_gs;
10 open c1;
11 loop
12 fetch c1 into rec;
13 exit when c1%notfound;
14 dbms_output.put_line(rec.GS_ID);
15 end loop;
16 close c1;
17 end
18* ;
SQL> /
Enter value for no: 1200
old 6: select * from s_write.p_gs where p_id:=&no;
new 6: select * from s_write.p_gs where p_id:=12001;

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE PR_SURPRISE_DELIGHT_LOTERY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PL/SQL: SQL Statement ignored
6/52 PL/SQL: ORA-00920: invalid relational operator


is it working fine? if so why you realy posted it here?
Re: Error while compliling the code [message #392315 is a reply to message #392314] Tue, 17 March 2009 06:50 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
as
Quote:
cookiemonster



You appear to have declared your procedure with a parameter:

1 create or replace procedure PR
2 (no number)
3 as


Why don't you just use it?



use that with valid relational operator.(=)and call that procedure.

[Updated on: Tue, 17 March 2009 06:51]

Report message to a moderator

Re: Error while compliling the code [message #392320 is a reply to message #392314] Tue, 17 March 2009 07:09 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

Please find the following .
*** SCRIPT START :  Session:S_WRITE@DS.world(1)   17-Mar-2009 17:35:39 *** 
Processing ...
create or replace procedure PR
    as
    gsml p_gs.gs_id%type;
    cursor c1 is
    select * from s_write.p_gs
    where p_gs.p_id=12001;
    rec c1%rowtype;
    begin
    select gs_id into gsml from p_gs;
   open c1;
   loop
   fetch c1 into rec;
   exit when c1%notfound;
   dbms_output.put_line(rec.G_ID||' '||rec.GS_DN||' '||rec.GS_CRTET||
                        ' '||rec.MSG_ERROR);
   end loop;
   close c1;
   end;
   
PROCEDURE PR compiled successfully

*** SCRIPT END :  Session:S_WRITE@DS.world(1)   17-Mar-2009 17:35:39 *** 
It was executed in SQL navigator.

But If I execute it in SQL> I am getting the same error message.
Please give me the solution
Re: Error while compliling the code [message #392321 is a reply to message #392320] Tue, 17 March 2009 07:12 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
compare the code posted by yourself and soon you will get the solution.

forum already given you the hints so many times.....

compare code and last post again. you can use the paameter.USe it in a proper way.also use the '=' at the right position.

[Updated on: Tue, 17 March 2009 07:15]

Report message to a moderator

Re: Error while compliling the code [message #392322 is a reply to message #392315] Tue, 17 March 2009 07:14 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi all,

Thanks for giving ideas. Now it got resolved....Smile

Re: Error while compliling the code [message #392324 is a reply to message #392321] Tue, 17 March 2009 07:30 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

With hardcoding it's executing successfully. But if I define dynamically am geting error.
create or replace procedure PR
    as
    gsml p_gs.gs_id%type;
    cursor c1 is
    [COLOR=blue]select * from s_write.p_gs
                where p_gs.p_id=&no;[/COLOR]
    rec c1%rowtype;
    begin
    select gs_id into gsml from p_gs;
   open c1;
   loop
   fetch c1 into rec;
   exit when c1%notfound;
   dbms_output.put_line(rec.G_ID||' '||rec.GS_DN||' '||rec.GS_CRTET||
                        ' '||rec.MSG_ERROR);
   end loop;
   close c1;
   end;


Please let me know how to define dynamically.
Re: Error while compliling the code [message #392325 is a reply to message #392313] Tue, 17 March 2009 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 17 March 2009 11:37
Confused

You appear to have declared your procedure with a parameter:
  1  create or replace procedure PR
  2  (no number)
  3  as


Why don't you just use it?

Re: Error while compliling the code [message #392326 is a reply to message #392325] Tue, 17 March 2009 07:37 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@cookiemonster

i think he don`t know how to use that.or he wants forum to use that and send him the direct code.
Re: Error while compliling the code [message #392330 is a reply to message #392192] Tue, 17 March 2009 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
he's going to have to learn some time.
Re: Error while compliling the code [message #392331 is a reply to message #392330] Tue, 17 March 2009 08:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
./fa/449/0/

I won't hold my breath on that one. He is ultra-super-teflon clue resistant.
Re: Error while compliling the code [message #392343 is a reply to message #392326] Tue, 17 March 2009 09:56 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@user71408,

Please read and Understand Specifying Subprogram Parameter Modes

Hope this helps you to understand what @cookiemonster's clue was../fa/1605/0/

Regards,
Jo

[Updated on: Tue, 17 March 2009 09:57]

Report message to a moderator

Re: Error while compliling the code [message #392358 is a reply to message #392343] Tue, 17 March 2009 11:54 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Laughing
Previous Topic: user session is being timed out - no error are recorded
Next Topic: Sorting Problem while generating table dynamically ?
Goto Forum:
  


Current Time: Sun Dec 04 18:36:22 CST 2016

Total time taken to generate the page: 0.17141 seconds