Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Unexpected ORA-06533: Subscript beyond count

Miro MasJul 8 2021 — edited Jul 8 2021

If I run this script, I get ORA-06553 :

CREATE TABLE TBL
(
  COL  NUMBER
);


declare
ln_numbers   apex_t_number := apex_t_number(1,2);
ln_numbers_count NUMBER := ln_numbers.count;

begin
for i in 1..ln_numbers_count + 1 loop
  dbms_output.put_line('i: '||i||' ln_numbers_count: '||ln_numbers_count||
  ' inserting '||case when i <= ln_numbers_count then ln_numbers(i) else 3 end);
  insert into TBL(COL) values(case when i <= ln_numbers_count then ln_numbers(i) else 3 end);
  dbms_output.put_line('insert completed');
end loop;
EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
  raise;
end;

DBMS OUTPUT:

i: 1 ln_numbers_count: 2 inserting 1
insert completed
i: 2 ln_numbers_count: 2 inserting 2
insert completed
i: 3 ln_numbers_count: 2 inserting 3
ORA-06533: Subscript beyond count

It seems like Oracle tries to claculate ln_numbers(i) for i=3 but I can not figure out why? Case sentence is

case when i <= ln_numbers_count then ln_numbers(i) else 3 end

and ln_number_count is 2. It should work without error. Can please someone describe what happens here?
If I extract case sentence outside insert it works as expected:

declare
ln_numbers   apex_t_number := apex_t_number(1,2);
ln_numbers_count NUMBER := ln_numbers.count;
ln_number NUMBER;

begin
for i in 1..ln_numbers_count + 1 loop
  ln_number := case when i <= ln_numbers_count then ln_numbers(i) else 3 end;
  dbms_output.put_line('i: '||i||' ln_numbers_count: '||ln_numbers_count||
  ' inserting '||ln_number);   
  insert into TBL(COL) values(ln_number);
  dbms_output.put_line('insert completed');
end loop;
EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
  raise;
end;

DBMS OUTPUT:

i: 1 ln_numbers_count: 2 inserting 1
insert completed
i: 2 ln_numbers_count: 2 inserting 2
insert completed
i: 3 ln_numbers_count: 2 inserting 3
insert completed
This post has been answered by mathguy on Jul 8 2021
Jump to Answer

Comments

Christian.Shay -Oracle

You can only use the Basic connection type for certain types of database connections (specifically, ones where only host/ip, port and service name are sufficient. Connection types that need to use info in tnsnames.ora, sqlnet.ora, or ldap.ora, for example those using directory servers or wallets will not be able to connect using that method.
I am not sure of your database configuration, but perhaps a directory server is involved or perhaps you are using a wallet.
Can you please check the Tns Admin Location for connections that work for you, and please provide:

  1. tnsnames.ora file
  2. tnsalias you are choosing from that file
  3. sqlnet.ora if it exists
  4. ldap.ora if it exists
    (You should redact info of course)
  5. And please provide the info you are entering into the Basic connection, particularly the service name.

Finally, please note that users can paste the connection info from a tnsnames.ora into the Advanced connection type. So if you want you could provide them everything that comes after the "=" (equals sign) from one tnsnames.ora entry to use as a connection string in the Advanced type.

Tim Plößer

Hi,
thank you for the reply!
Just to clarify: when using the TNS Alias connection type every connection saved in the tnsnames.ora files works without any issues/problems, the problems only occur when the basic connection type is used (did also test the advanced connection type - which did also work without any problems for entries copied from the tnsnames.ora files)
Since I do not feel comfortable posting the complete tnsnames.ora file here, here is the (redacted) structure of the alias entry I am trying to reach via the basic connection type (again connection via advanced or tns alias method works flawlessly):
POWERDATENT.ORACLE.XXX.YYY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = POWERDATENT.ORACLE.XXX.YYY)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = POWERDATENT)))

the sqlnet.ora file contains the following data:
NAMES.DEFAULT_DOMAIN = oracle.XXX.YYY
# Local Naming
NAMES.DIRECTORY_PATH = (TNSNAMES)
# Client
LOG_DIRECTORY_CLIENT = C:\temp
LOG_FILE_CLIENT = client
TRACE_DIRECTORY_CLIENT = C:\temp
TRACE_FILE_CLIENT = client
TRACE_LEVEL_CLIENT = off
TRACE_UNIQUE_CLIENT = on
# tnsping
TNSPING.TRACE_DIRECTORY = C:\temp
TNSPING.TRACE_LEVEL = off
# authentication
SQLNET.AUTHENTICATION_SERVICES= (none)

ldap.ora does not exist in the file location

I have attached the screenshot of the information I am entering into the basic connection type (instead of xxx.yyy I am of course using the real values). When now trying to create the connection I get the ORA-12154 error mentioned in my original post.
basic_connection.pngAs mentioned before for us it is not that big of a deal, since we can fall back to TNS Alias (or now that I know advanced connection) to connect to the DBs which exist in the tnsnames.ora file. However from time to time there is a new server which is not yet in the tnsnames.ora so it would be cool to be able to use the extension instead of having to fall back to sql developer.
And as I said before the identical data used in sql developer or used within python for sqlalchemy does work.

Christian.Shay -Oracle

Hi,
With connection type Basic, can you please try connecting with the same connection info you used before except set Service name to: POWERDATENT.oracle.XXX.YYY
If that does not work, please make a backup copy of sqlnet.ora, then edit sqlnet.ora like so:
Change:
NAMES.DIRECTORY_PATH = (TNSNAMES)
to:
NAMES.DIRECTORY_PATH = (EZCONNECT, TNSNAMES)
Then close VS Code, reopen and try to connect, this time using the original service name: POWERDATENT

Tim Plößer

Hi,
the first solution using POWERDATENT.oracle.XXX.YYY as service name does lead to the same behaviour as before (ORA-12154) error.
The sqlnet.ora file is located on a network drive, where I have no editing rights - so I can't try the second proposed solution.

Christian.Shay -Oracle

You can copy all the files in that shared directory to a non-share directory and then in the Oracle Developer Tools for VS Code extension settings, change the "Config File Folder" and the "Wallet File Folder" settings to the local folder.
Then make the changes I described earlier, restart VS Code, and retry.

Tim Plößer

Hi Christian,
when using local files the change from:
NAMES.DIRECTORY_PATH = (TNSNAMES)
to:
NAMES.DIRECTORY_PATH = (EZCONNECT, TNSNAMES)
Does solve the issue for me.
When doing this I did also actually discover that within the Extension Settings the path was/is set to:
C:\Users\USERNAME\Oracle\network\admin
which was empty (I copied the two .ora files to that location and did the changes there).
I guess to solve this issue more permenantly while still having access to the latest tnsnames.ora files I should talk with one of our DBA to see if the EZCONNECT part can be added to the sqlnet.ora file and point the extension to the network drive, correct?

Christian.Shay -Oracle

Hi,
Glad you got it working.,
Yes, the dba can add EZCONNECT to NAMES.DIRECTORY_PATH on the share. However, he or she could also just delete the entry entirely as the default includes both of them.
Database Net Services Reference (0 Bytes)Could you please update your review on the VS Marketplace to indicate that you identified the issue?

Tim Plößer

Hi,
I did update my review on the VS Marketplace :)
Thank you for the quick and successfull support!

1 - 8

Post Details

Added on Jul 8 2021
3 comments
2,287 views