Home » SQL & PL/SQL » SQL & PL/SQL » Identifier too long error
Identifier too long error [message #217050] Wed, 31 January 2007 08:53 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
I am using tryng to create this cursor and I am getting the following error msg:


SQL> var c1 refcursor

SQL> r
1 declare
2 sql_text varchar2(32767) := 'select ';
3 p_num varchar2(4) ;
4 begin
5 for i in 1..15 loop -- need 15 rec or less
6 sql_text := sql_text || 'T_num';
7 sql_text := sql_text || 'l_num' ;
8 sql_text := sql_text || 'p_num';
9 end loop;
10 sql_text := sql_text || ' FROM T_table';
11 -- sql_text := sql_text || ' WHERE l_num LIKE 'M%'';
12 open :c1 for sql_text;
13* end;
declare
*
ERROR at line 1:
ORA-00972: identifier is too long
ORA-06512: at line 12

Please help!
Thks!
Re: Identifier too long error [message #217062 is a reply to message #217050] Wed, 31 January 2007 09:26 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
If I rewrite your block to:

declare
 sql_text varchar2(32767) := 'select ';
 p_num varchar2(4) ;
begin
 for i in 1..15 loop -- need 15 rec or less
 sql_text := sql_text || 'T_num';
 sql_text := sql_text || 'l_num' ;
 sql_text := sql_text || 'p_num';
end loop;
 sql_text := sql_text || ' FROM T_table';
 dbms_output.put_line (sql_text);
 -- sql_text := sql_text || ' WHERE l_num LIKE 'M%'';
-- open :c1 for sql_text;
end;


You will see that the column you are selecting is VERY LONG:
select T_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_numT_numl_nump_num FROM T_table


Try this:

declare
 sql_text varchar2(32767) := 'select ';
 p_num varchar2(4) ;
begin
 for i in 1..15 loop -- need 15 rec or less
 sql_text := sql_text || 'T_num,';
 sql_text := sql_text || 'l_num,' ;
 sql_text := sql_text || 'p_num,';
end loop;
 sql_text := substr(sql_text,1,length(sql_text)-1) || ' FROM T_table';
 
 dbms_output.put_line (sql_text);
 -- sql_text := sql_text || ' WHERE l_num LIKE 'M%'';
-- open :c1 for sql_text;
end;

[Updated on: Wed, 31 January 2007 09:28]

Report message to a moderator

Re: Identifier too long error [message #217077 is a reply to message #217062] Wed, 31 January 2007 10:42 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks it compiled but I wanted to print only 15 rows and I am getting 1000's of rows here is what I added:

declare
sql_text varchar2(32767) := 'select ';
p_num varchar2(4) ;
n number := 15;
i number := 1;
begin
for i in 1..15 loop
sql_text := sql_text || 't_num,';
sql_text := sql_text || 'l_num,' ;
sql_text := sql_text || 'p_num,';
end loop;
sql_text := substr(sql_text,1,length(sql_text)-1) || ' FROM T_table';
dbms_output.put_line(i);
dbms_output.put_line (sql_text);
-- sql_text := sql_text || ' WHERE l_num LIKE 'M%'';
open :c1 for sql_text;
end;


PS I 've ommitted the output sorry:

PL/SQL procedure successfully completed.

SQL> print c1

T_NUM L_NUM P_N
-------------------------- --------- ------ ----------
0900-141.00-02.00-037.000 M00019006 006 0900-141.00-02.00-037.000 M00019006 006 0900-141.00-
0900-141.00-02.00-037.000 M00019006 007 0900-141.00-02.00-037.000 M00019006 007 0900-141.00-
0900-141.00-02.00-037.000 M00021170 096 0900-141.00-02.00-037.000 M00021170 096 0900-141.00-
0900-141.00-02.00-037.000 M00021170 097 0900-141.00-02.00-037.000 M00021170 097 0900-141.00-

The records are accurate but the same lines are repeated across Not sure why?
Thanks again!

[Updated on: Wed, 31 January 2007 11:49]

Report message to a moderator

Re: Identifier too long error [message #217186 is a reply to message #217077] Thu, 01 February 2007 01:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Take a good look at the generated sql string. Does this look like a query that gets 15 rows?
Re: Identifier too long error [message #217358 is a reply to message #217186] Thu, 01 February 2007 16:34 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
That is why I am a newby I am sure how to loop when using ref cursor I am reading about as we speak. Therefore, I will ask off the wall Qs. I made some changes to the code w/o much success:

declare
sql_text varchar2(32767);
maxval number := 15;
startval number := 1 ;
i number;
begin
for i in startval..maxval loop
sql_text := 'select ';
sql_text := sql_text ||' t_number,';
sql_text := sql_text ||' l_num,' ;
sql_text := sql_text ||' p_num,';
sql_text := substr(sql_text,1,length(sql_text)-1) || ' FROM ttable';
sql_text := sql_text || ' WHERE t_num = ''0900-141.00-02.00-037.000'' ';
--sql_text := sql_text || ' order by trunc(t_date) DESC ' ;
end loop;
dbms_output.put_line (i + 1);
dbms_output.put_line (sql_text);
open :c1 for sql_text;
end;
/
PL/SQL procedure successfully completed.

SQL> print c1

T_NUM L_NUM P_N
--------------------------- --------- ------
0900-141.00-02.00-037.000 M00019006 006
0900-141.00-02.00-037.000 M00019006 007
0900-141.00-02.00-037.000 M00021170 096
0900-141.00-02.00-037.000 M00021170 097
0900-141.00-02.00-037.000 M00020883 109
0900-141.00-02.00-037.000 M00019356 168
0900-141.00-02.00-037.000 M00021066 237
0900-141.00-02.00-037.000 M00019100 327
0900-141.00-02.00-037.000 M00021335 349
0900-141.00-02.00-037.000 D00011752 355
0900-141.00-02.00-037.000 M00021195 393

T_NUM L_NUM P_N
--------------------------- --------- ------
0900-141.00-02.00-037.000 D00012412 398
0900-141.00-02.00-037.000 D00012419 520
0900-141.00-02.00-037.000 M00021371 739
0900-141.00-02.00-037.000 M00019265 781
0900-141.00-02.00-037.000 M00020901 835
0900-141.00-02.00-037.000 M00020901 836
0900-141.00-02.00-037.000 D00012458 864
0900-141.00-02.00-037.000 M00021356 878

19 rows selected.

My goal to get 15 or less of the most recent records.

Thanks .
Re: Identifier too long error [message #217507 is a reply to message #217050] Fri, 02 February 2007 10:15 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
This will only work if there is ONLY one row in the table. So the following


declare
sql_text varchar2(32767);
begin
sql_text := 'select ';
sql_text := sql_text ||' t_number,';
sql_text := sql_text ||' l_num,' ;
sql_text := sql_text ||' p_num,';
sql_text := substr(sql_text,1,length(sql_text)-1) || ' FROM ttable';
sql_text := sql_text || ' WHERE rownum <= 15';
--sql_text := sql_text || ' order by trunc(t_date) DESC ' ;
dbms_output.put_line (i + 1);
dbms_output.put_line (sql_text);
open :c1 for sql_text;
end;
/
Unable to Loop thru a refcursor successfully [message #217513 is a reply to message #217507] Fri, 02 February 2007 11:02 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks Bill,

It does work alright. However...
PL/SQL procedure successfully completed.

SQL> print c1

T_NUM L_NUM P_N DATE
--------------------------- --------- ------ ---------
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00
0900-141.00-02.00-037.000 M00019006 006 12-DEC-00

15 rows selected.

If you look at my previous output w/ 19 rows , each record is unique and most recent, it may( record) may have the same l_num, but the P_num will, most certainly be different.

In your example which, I appreciate the help on, gave me like you said the same rec 15 times.
I know I am new at this but the above data is not my desired goal. The 19 row data is the correct data except I only need to display the most recent 15 of them.

Re: Identifier too long error [message #217514 is a reply to message #217050] Fri, 02 February 2007 11:04 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
It does NOT give you the same record 15 times, it gives the first 15 rows from the table. If you want 15 unique rows then use the following


declare
sql_text varchar2(32767);
begin
sql_text := 'select distinct ';
sql_text := sql_text ||' t_number,';
sql_text := sql_text ||' l_num,' ;
sql_text := sql_text ||' p_num,';
sql_text := substr(sql_text,1,length(sql_text)-1) || ' FROM ttable';
sql_text := sql_text || ' WHERE rownum <= 15';
--sql_text := sql_text || ' order by trunc(t_date) DESC ' ;
dbms_output.put_line (i + 1);
dbms_output.put_line (sql_text);
open :c1 for sql_text;
end;
/

[Updated on: Fri, 02 February 2007 11:06]

Report message to a moderator

Re: Identifier too long error [message #217521 is a reply to message #217514] Fri, 02 February 2007 11:22 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks a million Bill.
But Nop! same thing. what will work and I don't know how to accomplish that yet would be loop around the cursor's select stmt until the maxval of 15 is reached.
Hopefully my problem will be solved,

Again your help was/is greatly appreciated.

Regards,
Re: Identifier too long error [message #217532 is a reply to message #217521] Fri, 02 February 2007 12:21 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Can you write the sql statement you're trying to generate? Write this first, figure out how to get the 15 records you want, test it, etc. And only then, once you know what your goal is, start writing PL/SQL to generate the statement. This approach will save you a lot of confusion...

(although I can't think of a reason why anyone would write PL/SQL to do this, but possibly I'm missing something).
Re: Identifier too long error [message #217533 is a reply to message #217050] Fri, 02 February 2007 12:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>(although I can't think of a reason why anyone would write PL/SQL to do this, but possibly I'm missing something).
Homework assignment, perhaps?
Re: Identifier too long error [message #217543 is a reply to message #217532] Fri, 02 February 2007 12:53 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks. But I did that. I know for that particular t_num there are 4 records there and it worked. But I know there will be times when more 15 will be available thereofore I wnat to ensure I get just that or less. That is where my quandry really is?

1 select d.l_num,
2 d.p_num,
3 trunc(t_date) r_date,
4 desc r_type,
5 nvl(amt1,0) amount
6 from ddtable d,
7 ttable t,
8 dltable dl,
9 dtable dt,
10 rtable r
11 where t_num = '0900-141.00-02.00-037.000'
12 and d.l_num like 'D%'
13 and d.l_num = t.l_num
14 and d.p_num = t.p_num
15 and d.r_num = r.r_num
16 and d._id = dl.d_id
17 and d.r_num = dt.r_num(+)
18 and d.tt_num = dt.tt_num(+)

L_NUM P_N R_DAte R_TYPE AMOUNT
--------- ------ --------- -------------------------------
M0001 739 28-AUG-06 MY TYPE 0
M00026 878 08-AUG-06 More TYPE 0
D00012 864 12-JUL-06 DType 322000
M00021 349 12-JUL-06 Mtype 351150

You wanted to know the reason for PL/SQL? Speed and to prevention of extensive dbase hits all day long. when I am done I would like to create a view that users can access in real time, Hence minimize direct dbase hits. There will be extensive users' traffic. User will only connect to our server and access already compiled code. One can do that in SQL. ANd that is the only tool I am given.

Many thanks & regards

Re: Identifier too long error [message #217546 is a reply to message #217050] Fri, 02 February 2007 13:24 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Speed and to prevention of extensive dbase hits all day long.
Exactly how does the use of PL/SQL prevent "extensive dbase hits all day long."?

>when I am done I would like to create a view
What does this view have to do with PL/SQL being used?

>Hence minimize direct dbase hits.
Exactly how does the use of a view "minimize direct dbase hits."?

>User will only connect to our server and access already compiled code
The compiled PL/SQL seems to generated SQL statements that will require hard parse for EVERY execution which does not scale.
EXACTLY what s/w will the user use to "connect to the serever"?

This is not close to an optimal & scalable application.

For maximum database performance the rule is:
If it can be done in SQL, then do it in SQL.
When PL/SQL is used a context switch occurs every time when
going from PL/SQL to SQL & back again.
SQL is the ONLY way to directly interact with the database.

[Updated on: Fri, 02 February 2007 19:48] by Moderator

Report message to a moderator

Previous Topic: very urgent
Next Topic: Parallel Query Died
Goto Forum:
  


Current Time: Sun Dec 04 20:27:26 CST 2016

Total time taken to generate the page: 0.07462 seconds