Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00903:Invalid table Name
ORA-00903:Invalid table Name [message #219227] Tue, 13 February 2007 11:05 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Can some one explain to me why this snippet of code works and when I uncommented the lines below or added more lines to complete my qry: the same procedure failed?
Thanks in advance.


declare
sql_text long;
maxval number := 15;
startval number := 1 ;
currentval NUMBER := startval;
type rc is ref cursor;
v_rc rc;

begin
for v_rc in 1..15 loop
sql_text := 'select ';
sql_text := sql_text ||' lr_num,' ;
sql_text := sql_text ||' p_num,';
--sql_text := sql_text ||' t_date r_date,';
--sql_text := sql_text ||' t_type rec_type,';
sql_text := substr(sql_text,1,length(sql_text)-1) || ' FROM ttable tl' ; -- I added 2 more tables for above commented lines
sql_text := sql_text || ' WHERE t_num = ''0900-141.00-02.00-037.000'' ';
--sql_text := sql_text || ' order by trunc(t_date) DESC ' ;
sql_text := sql_text ||' AND rownum < 16 ' ;
end loop;
p(sql_text); <-- I think that is where the problem is
open :rc for sql_text; -- this is line 23
end;
/

PL/SQL procedure successfully completed.

SQL> print rc

L_NUM P_NUM
--------- ------
M00019006 006
M00019006 007
M00021170 096
M00021170 097
M00020883 109
M00019356 168
M00021066 237
M00019100 327
M00021335 349
D00011752 355
M00021195 393
D00012412 398
D00012419 520
M00021371 739
M00019265 781

15 rows selected.

failed qry
-----------
with the following msg:
SQL> /
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 23

Re: ORA-00903:Invalid table Name [message #219230 is a reply to message #219227] Tue, 13 February 2007 11:11 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
No Message Body

[Updated on: Tue, 13 February 2007 11:13]

Report message to a moderator

Re: ORA-00903:Invalid table Name [message #219236 is a reply to message #219227] Tue, 13 February 2007 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
MarcL,

Well said & I concur.

HAND!
Re: ORA-00903:Invalid table Name [message #219255 is a reply to message #219227] Tue, 13 February 2007 12:42 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Without seeing the code you added or the error you got, I'll take a wild guess.
The table access has been granted to a role you have been assigned to and not directly to you. How about that?
Re: ORA-00903:Invalid table Name [message #219272 is a reply to message #219255] Tue, 13 February 2007 14:26 Go to previous message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks a lot for your reply.
But I figured out what the problem was, PL/SQL does not like the alias that I used after the 2 added tables i.e record date and record_type; though it worked in Sql+. I have to get used to the differences.
sql_text := sql_text ||' t_date record_date,';
sql_text := sql_text ||' t_type record_type,';
Previous Topic: Bond Yield / Price Calculation
Next Topic: How to insert data in Procedure????
Goto Forum:
  


Current Time: Thu Dec 08 08:45:35 CST 2016

Total time taken to generate the page: 0.15484 seconds