Home » SQL & PL/SQL » SQL & PL/SQL » PL/block not working (Win XP, 10G)
PL/block not working [message #324212] Sat, 31 May 2008 11:38 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

Can anyone tell me what this block is not working. I am trying to display the alphabets stored in a table through a table object.

create or replace type ob_tabalpha as table of char;


declare 

a ob_tabalpha := ob_tabalpha();

/* ALPHABETS table contains alphabets from A to Z */

cursor c1 is select * from alphabets;

type t is table of c1%rowtype;

b t;

n number;
i int;

begin

open c1;

loop

fetch c1 bulk collect into b ;

n := c1%rowcount;

a.extend(n);

forall i in 1..b.count

a(i) := b(i);

exit when c1%notfound;

end loop;

close c1;


for i in 1..n
loop
dbms_output.put_line(a(i));
end loop;

end;
Re: PL/block not working [message #324213 is a reply to message #324212] Sat, 31 May 2008 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above.

My car is not working.
Please tell me how to make my car go.
Re: PL/block not working [message #324214 is a reply to message #324213] Sat, 31 May 2008 11:52 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Excuse me for the incomplete data..I will try again to explain the exact problem

Here are the errors that I get when I run the block

Quote:
pls-00103 - Encountered the symbol "A" when expecting one of the following .(*@%&

The symbol"." was substituted for "A" to continue

pls-00103 Encountered the symbol "=" when expecting one of the following .(*@%&

pls-00103 Encountered the symbol ";" when expecting one of the following .(*@%&


I understand that the problem is with the table object's (ob_tabalpha) usage but not able to resolve it. Am I not using it properly?

Please let me know if you are able to figure it out.


Re: PL/block not working [message #324215 is a reply to message #324214] Sat, 31 May 2008 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we don't see where (at which lines) the errors are we can't help, use SQL*Plus and copy and paste your session.
And remove the useless empty lines, also indent the code.

Regards
Michel
Re: PL/block not working [message #324216 is a reply to message #324212] Sat, 31 May 2008 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Here are the errors that I get when I run the block
Forgive me, but I am NOT standing behind you & have no idea EXACTLY what you are doing to generate the errors.

Please use sqlplus, then CUT & PASTE the whole session.

>Am I not using it properly?
Obviously, you are doing something wrong or you would not be getting errors.
So far ONLY you really know what has been done.
Re: PL/block not working [message #324217 is a reply to message #324216] Sat, 31 May 2008 12:20 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
I know this might be getting annoying for you guys and completely understand if you don't want to take troubles to go through this piece of code

Here is the code right from the beginning. The purpose of this code is to display the contents of the alphabets table using the table type. The Alphabets table contains A to Z as contents

create or replace type ob_tabalpha as table of char


declare 
 [B]a ob_tabalpha := ob_tabalpha();[/B]
 cursor c1 is select * from alphabets;
 type t is table of c1%rowtype;
 b t;
 n number;
 i int;
begin

open c1;
  loop
  fetch c1 bulk collect into b ;
  n := c1%rowcount;
  [B]a.extend(n);[/B]
 
  forall i in 1..b.count
  [B]/*this might be the part which gives the error*/[/B]
  [B]a(i) := b(i);[/B]
  exit when c1%notfound;
  end loop;
  close c1;
 
  for i in 1..n
  loop
[B]  /* here is the assumption is that the contents of the alphabets table have been entered into the table type of which A is a variable */[/B]
  dbms_output.put_line(a(i));
  end loop;

end;


Quote:
The code is stored in test.sql file located in the D drive


Here are the error details

Quote:
SQL> @d:\test.sql
68 /
a(i) := b(i);
*
ERROR at line 30:
ORA-06550: line 30, column 1:
PLS-00103: Encountered the symbol "A" when expecting one of the following:
. ( * @ % & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute multiset save
merge
The symbol "." was substituted for "A" to continue.
ORA-06550: line 30, column 6:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
. ( * % & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute multiset save
merge
ORA-06550: line 30, column 13:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( , * % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ alter between || mult
ORA-06550: line 36, column 1:
PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map



Let me know if I have still missed something

Re: PL/block not working [message #324219 is a reply to message #324217] Sat, 31 May 2008 12:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You are definitely doing things the hard way.

Why not just select from the alphabets table?

Or, if you want to output from pl/sql, then loop through the alphabets table to display the values?

Or, if for some reason you need to use the object, then select bulk collect directly from the alphabets table into the object?

Why use a cursor or an intermediary type?

Also, either fetch one record at a time within a loop or bulk collect without the loop.
Re: PL/block not working [message #324221 is a reply to message #324219] Sat, 31 May 2008 13:04 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks
Re: PL/block not working [message #324256 is a reply to message #324217] Sun, 01 June 2008 03:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
forall is NOT a general loop construction. It can only be used in conjunction with a dml statement.
Re: PL/block not working [message #324313 is a reply to message #324256] Sun, 01 June 2008 23:39 Go to previous message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks. Now it is clearer.

Previous Topic: create XML file from an Oracle Table
Next Topic: problem writing in correlated subquery
Goto Forum:
  


Current Time: Sat Dec 10 11:11:58 CST 2016

Total time taken to generate the page: 0.15076 seconds