Home » SQL & PL/SQL » SQL & PL/SQL » Oracle DB 11g "LEVEL" issue (Oracle DB 11g, Win XP)
Oracle DB 11g "LEVEL" issue [message #588091] Thu, 20 June 2013 16:54 Go to next message
infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA
Member

I am getting 2 different results for the below queries in oracle 11g Database.
Any idea on this.

1. With LEVEL pseudo column, This is returning all the records AND it is not considering where condition
SELECT -1,
LEVEL,
item_id,
item_name
FROM items
WHERE item_id = '0129343';

1. Without LEVEL pseudo column, This is returning only one records AND it is considering where condition.

SELECT -1,
-- LEVEL,
item_id,
item_name
FROM items
WHERE item_id = '0129343';
Re: Oracle DB 11g "LEVEL" issue [message #588092 is a reply to message #588091] Thu, 20 June 2013 17:01 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you think, what is LEVEL used for? What do you plan to do with it? Are you sure you posted the correct 1st query? I'm pretty sure it wouldn't work at all (i.e. it would end up with an error).
Re: Oracle DB 11g "LEVEL" issue [message #588093 is a reply to message #588091] Thu, 20 June 2013 17:12 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
I don't believe you

  1* select first_name, last_name, level from employees where employee_id = 06
SQL> /
select first_name, last_name, level from employees where employee_id = 06
                                         *
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block



Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


post COPY & PASTE proof of whole session
Re: Oracle DB 11g "LEVEL" issue [message #588094 is a reply to message #588091] Thu, 20 June 2013 17:34 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
In addition, you may want to read Hierarchical Queries where you have description, syntax and examples.

Regards,
Dariyoosh
Re: Oracle DB 11g "LEVEL" issue [message #588095 is a reply to message #588092] Thu, 20 June 2013 18:54 Go to previous messageGo to next message
infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA
Member

I know without connect by LEVEL will through error.

But i am able to get the result, see the attachment. I dont know there may be some setting issue....

Please note one thing, don't think that you guys knows every thing. We are seeking the help on this post not just posting and playing in in this FAQ's. If you know then help us and reply otherwise just $$$$$$hut up.
./fa/10909/0/


[mod-edit: image inserted into message body by bb]

[Updated on: Wed, 07 August 2013 00:21] by Moderator

Report message to a moderator

Re: Oracle DB 11g "LEVEL" issue [message #588096 is a reply to message #588095] Thu, 20 June 2013 18:58 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

If/when you choose to ignore Posting Guidelines, then we can choose to ignore your posts.
Re: Oracle DB 11g "LEVEL" issue [message #588097 is a reply to message #588096] Thu, 20 June 2013 19:34 Go to previous messageGo to next message
infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA
Member

Please find the below result with level only., thanks
SQL> SELECT   -1,
  2           LEVEL ,
  3           folder_name,
  4           'folder',
  5           folder
  6    FROM   nav_folder
  7  WHERE   user_id = 'RETEK';

        -1      LEVEL FOLDER_NAME                              'FOLDE FOLDER
---------- ---------- ---------------------------------------- ------ ---------------------------
        -1          0 user                                     folder user

SQL> 

[Updated on: Thu, 20 June 2013 19:40]

Report message to a moderator

Re: Oracle DB 11g "LEVEL" issue [message #588098 is a reply to message #588097] Thu, 20 June 2013 19:43 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
>With LEVEL pseudo column, This is returning all the records AND it is not considering where condition
the single record returned above appears to be correctly handling WHERE clause.

how can we reproduce what you claim?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

which include posting here a Test Case.

Re: Oracle DB 11g "LEVEL" issue [message #588099 is a reply to message #588098] Thu, 20 June 2013 19:50 Go to previous messageGo to next message
infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA
Member

-------------- Database 10g result ----------

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

5 rows selected.

SQL> SELECT   -1,
  2           LEVEL ,
  3           folder_name,
  4           'folder',
  5           folder,
  6           user_id
  7    FROM   nav_folder
  8  WHERE   user_id = 'RETEK'
  9  /

        -1      LEVEL FOLDER_NAME                              'FOLDE FOLDER                                   USER_ID
---------- ---------- ---------------------------------------- ------ ------------------------------
        -1          0 user                                     folder user                                     RETEK

1 row selected.




********** Database 11g Result *****

Please check the user_id , it have null, A420237, A840518 etc.,,,, but in the where condition i have only RETEK


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.

SQL> SELECT   -1,
  2           LEVEL ,
  3           folder_name,
  4           'folder',
  5           folder,
  6           user_id
  7    FROM   nav_folder
  8  WHERE   user_id = 'RETEK'
  9  /

        -1      LEVEL FOLDER_NAME                              'FOLDE FOLDER                                   USER_ID
---------- ---------- ---------------------------------------- ------ ------------------------------
        -1          0 SKU Store Mass Maintenance               folder SKU_STORE
        -1          0 retek                                    folder retek                                    A420237
        -1          0 rtprod                                   folder rtprod                                   A840518
        -1          0 Audit Carton Receiving                   folder Audit Carton Receiving
        -1          0 Platform                                 folder bbyplatform
        -1          0 RESENDS TO INTERFACE                     folder RESENDS TO INTERFACE                     A444069
        -1          0 GPC Defaults                             folder GPC_DEFAULTS
        -1          0 China Business Trust                     folder CBT
        -1          0 resends to interfaces                    folder resends to interfaces                    A444069
        -1          0 SERVICE CONTRACT PLANS                   folder PSPPRP
        -1          0 BAND PRICING SETUP                       folder PSPPRP_BAND_SETUP

        -1      LEVEL FOLDER_NAME                              'FOLDE FOLDER                                   USER_ID
---------- ---------- ---------------------------------------- ------ ------------------------------
        -1          0 Strategy Maintenance                     folder STRATEGY_MAINTENANCE
        -1          0 CPFR Attributes                          folder CPFR_ATTRIBUTES
        -1          0 AIP Orders                               folder AIP_ORDERS
        -1          0 Merchandise Hierarchy Exception          folder MERCH_HIER_EXCP
        -1          0 Mass Approve                             folder AIP_ORD_MASS_APPRVE
        -1          0 Strategy Path Definition                 folder AIP_STRATEGY_PATH
        -1          0 AIP Sku Store Mass Maintenance           folder AIP_SKU_STORE_MASS_MAINT
        -1          0 Ordering Schedule                        folder ORDERING_SCHEDULE
        -1          0 Items                                    folder ITEM_RELATED
        -1          0 Pricing                                  folder PRICING
        -1          0 Finance                                  folder DEPT_DETAILS

Re: Oracle DB 11g "LEVEL" issue [message #588100 is a reply to message #588099] Thu, 20 June 2013 19:56 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
We don't have your table.
We don't have your data.
We can't run posted SQL since we don't have table or data.
Re: Oracle DB 11g "LEVEL" issue [message #588113 is a reply to message #588095] Fri, 21 June 2013 00:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
infosuresh2k wrote on Fri, 21 June 2013 01:54
I know without connect by LEVEL will through error.

But i am able to get the result, see the attachment. I dont know there may be some setting issue....

Please note one thing, don't think that you guys knows every thing. We are seeking the help on this post not just posting and playing in in this FAQ's. If you know then help us and reply otherwise just $$$$$$hut up.


One more insult and you are banned.
Please read OraFAQ Forum Guide.

Regards
Michel

[Updated on: Fri, 21 June 2013 00:26]

Report message to a moderator

Re: Oracle DB 11g "LEVEL" issue [message #588133 is a reply to message #588113] Fri, 21 June 2013 04:28 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The only option I can think of is that NAV_FOLDER table contains the "LEVEL" column.

Let's start with this (as you didn't provide your own test case, shame on you!):
SQL> create table nav_folder
  2    (folder_name varchar2(20),
  3     folder      varchar2(20),
  4     user_id     varchar2(20)
  5    );

Table created.

SQL> insert into nav_folder (folder_name, folder, user_id)
  2    values ('user', 'user', 'RETEK');

1 row created.

SQL> select
  2    -1,
  3    level,
  4    folder_name,
  5    'folder',
  6    folder
  7  from nav_folder
  8  where user_id = 'RETEK';
from nav_folder
     *
ERROR at line 7:
ORA-01788: CONNECT BY clause required in this query block

Obviously, it won't work. Usage of the LEVEL pseudocolumn requires CONNECT BY clause.

Now, let's add the LEVEL column into a table:
SQL> alter table nav_folder add level number;
alter table nav_folder add level number
                           *
ERROR at line 1:
ORA-00904: : invalid identifier
Ooops! LEVEL can't be used as a column name! Reserved word, eh? But this will work:
SQL> alter table nav_folder add "LEVEL" number;

Table altered.

SQL> update nav_folder set "LEVEL" = 0;

1 row updated.

SQL> select
  2    -1,
  3    "LEVEL",
  4    folder_name,
  5    'folder',
  6    folder
  7  from nav_folder
  8  where user_id = 'RETEK';

        -1      LEVEL FOLDER_NAME          'FOLDE FOLDER
---------- ---------- -------------------- ------ --------------------
        -1          0 user                 folder user

SQL>

I got the result, just like you did, but note the difference: my level is enclosed into double quotes "LEVEL", while yours is not. How can it be? I don't know, honestly. I have no idea how you might use LEVEL without CONNECT BY in queries you posted. Though, all of them (including a JPG) look like this:
SQL> SELECT   -1,                 --> no space
  2           LEVEL ,
                   ^
                   |
           space here. Why? None of other columns you used in all of these SELECTs has it. 
           Did you, by any chance, remove it and fake everything? 

  3           folder_name,        --> no space between folder_name and comma
  4           'folder',           --> no space
  5           folder,             --> no space
  6           user_id
  7    FROM   nav_folder
Re: Oracle DB 11g "LEVEL" issue [message #589798 is a reply to message #588133] Wed, 10 July 2013 15:59 Go to previous messageGo to next message
infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA
Member

Hi Guys,

Just for your information oracle 11.2.0.1/2 has an issue with level(See Bug ID : 10202228), please read the below OTN documents.
We can use the Level pseudo-column without connect by in oracle 11.2.0.1/2

Mr. LittleFooot,

No one will post the wrong or fake data in public forums. Whatever i posted in this forum is correct one , may be those are not formatted correctly sorry for that. You just check my queries in oracle 11.2.0.1/2 not in other oracle versions.

Bug 10202228 wrong result when _allow_level_without_connect_by set to true

[Edit MC: Metalink note content removed]

Bug:10202228 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article



[Edit MC: Metalink note content removed, Metalink materials are protected by copyright do NOT post them]

[Updated on: Thu, 11 July 2013 00:07] by Moderator

Report message to a moderator

Re: Oracle DB 11g "LEVEL" issue [message #589802 is a reply to message #589798] Thu, 11 July 2013 00:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
No one will post the wrong or fake data in public forums.


So you did not read many forums, yes this happens and we see it every week here.
Note: do NOT post Metalink materials, this is illegal.

In addition, one that uses a hidden parameter without Oracle support is silly.
Especially, one that uses this parameter is stupid. There is real no reason to use LEVEL out of its usage.

Regards
Michel
Re: Oracle DB 11g "LEVEL" issue [message #589804 is a reply to message #589802] Thu, 11 July 2013 00:19 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I apologize, my wording was abosolutely wrong. Moreover, my "example" was meaningless in this context because I did it on 10g, while you're on 11g - not that I didn't have 11g available, I just didn't care enough to switch to version you use.

As it turns out that you hit a bug, it once again emphasizes the importance of people specifying exact versions they use, as well as other posters (in this case, it was me) to do their test on the same version, if possible. Because - something that works OK in one version might fail in another.
Re: Oracle DB 11g "LEVEL" issue [message #589806 is a reply to message #589804] Thu, 11 July 2013 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
something that works OK in one version might fail in another.


Especially with hidden parameters.

Regards
Michel
Re: Oracle DB 11g "LEVEL" issue [message #589873 is a reply to message #589802] Thu, 11 July 2013 09:55 Go to previous messageGo to next message
infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA
Member

This is the exiting code and it was developed long back, we got this issue while upgrading database 10 to 11. Sorry for posting the metalink content.

[Updated on: Thu, 11 July 2013 09:57]

Report message to a moderator

Re: Oracle DB 11g "LEVEL" issue [message #589875 is a reply to message #589804] Thu, 11 July 2013 10:27 Go to previous messageGo to next message
infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA
Member

Thanks for posting so many examples/queries for my issue.
Re: Oracle DB 11g "LEVEL" issue [message #589879 is a reply to message #589875] Thu, 11 July 2013 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Long back? Before Oracle V2? As far I remember this was in this version LEVEL appears and was a reserved word.

Regards
Michel
Re: Oracle DB 11g "LEVEL" issue [message #589881 is a reply to message #589879] Thu, 11 July 2013 10:55 Go to previous messageGo to next message
infosuresh2k
Messages: 77
Registered: September 2009
Location: CHENNAI, INDIA
Member

As far I remember this was in this version LEVEL appears and was a reserved word?

this version --> can you tell me which version?

The code i posted here is developed by some one at least 5 or 6 years back in oracle forms 6i and database 10g.

As per my knowledge Oracle 10g and above version has a hidden parameter _allow_level_without_connect_by we can use the level without connect by.

We got this issue on the database upgrade from 10g to 11g.

Re: Oracle DB 11g "LEVEL" issue [message #589889 is a reply to message #589881] Thu, 11 July 2013 12:32 Go to previous message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
this version --> can you tell me which version?


Quote:
Before Oracle V2?


Quote:
As per my knowledge Oracle 10g and above version has a hidden parameter _allow_level_without_connect_by we can use the level without connect by.


Right, for poor applications that do not check the reserved words like yours, NOT to be used.
The client asks, Oracle makes just a patch in the syntax analyser to jump this test but does not guarantee there is no bug, of course.

Regards
Michel
Previous Topic: RECORDs --> %ROWTYPE
Next Topic: do not accept punctuation character while inserting
Goto Forum:
  


Current Time: Sat Nov 22 12:05:45 CST 2014

Total time taken to generate the page: 0.10998 seconds