ORA-01008 not all variables bound error when trying to connect to Oracle 19c

DOKMANC2 · June 8, 2021 6:09PM

Some of our databases were recently updated to Oracle 19c.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Our production is still on

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

I had the Oracle 12.3 Client installed on my PC and tried to use it to compare. While I was able to verify my connection against any database, 12c or 19c, I could not perform a comparison that included a to the 19c database. I got the error:

ORA-01008 not all variables bound error

So, I upgraded my Oracle Client to 19c hoping t hat that would fix it. The same exact behavior resulted: 12c worked, 19c didn't

I cannot send file attachments outside of the company. I was hoping this forum allowed attachments, as sometimes I am able to submit files this way but this forum does not appear to support it.

My support policy just expired....

Eddie D · June 14, 2021 10:13AM

Hi, thank you for your forum post.

What is the full version number of Schema Compare for Oracle you are using?

Can you please generate a verbose log file as per the instructions in this help article and review the log file generated? Does it contain any information or identify the query that maybe causing the error?

Many Thanks
Eddie

DOKMANC2 · June 14, 2021 2:32PM

My Red Gate Oracle Schema compare version is 14.07.13517.
Re: Does it contain info that may identify the cause of the error?
The file that I think that you are referring to has an extension "saencryptedreport." when I opened it up, I was not surprised to see that it was encrypted. Is this the log file that you wanted me to check?

Many times when the error has occurred in the UI, I select the option to send it to Red Gate. I have sent it about 8 times, approx, most recently today. I referenced this post in the Description. My email address was included in all.

Eddie D · June 14, 2021 2:52PM

Hi,
I think you have misunderstood me.

The help article contains the information to increase the logging level to verbose and the how to locate the log file generated.

The log file is not associated to the error report with the "saencryptedreport."extension.

Many Thanks
Eddie

DOKMANC2 · June 14, 2021 3:20PM

Eddie,

Yes, I did misread your reply. Sorry about that. I do see these errors in the verbose log. I know that we've seen other permissions issues related to the upgrade but to my knowledge, they were "all" fixed. I will forward these to the DBA in hopes that he does not tell me that I should not have access to these views. This is the expected default reply. Let's see what he says...

SELECT s.sowner as owner, s.vname as mview_name, decode(bitand(s.flag, 262144), 262144, 'Y', 'N') as reduced_precisions

FROM sys.snap$ s WHERE ((s.sowner = 'FDS_MAINT' OR s.sowner = 'FDS_APPS' OR s.sowner = 'FDS_BASE') );

--1 ORA-00942: table or view does not exist SQL2.sql 2 34

SELECT

u.username as OWNER,

o.NAME as TABLE_NAME,

p.periodname as PERIOD_NAME,

p.flags as FLAGS,

p.periodstart as PERIOD_START,

p.periodend as PERIOD_END

FROM sys.sys_fba_period p,sys.tab$ t,sys.obj$ o, all_users u, m_owners

WHERE p.obj# = t.obj#

AND t.obj# = o.obj#

AND o.owner# = u.user_id

AND u.username = m_owners.owner;

--ORA-00942: table or view does not exist

SELECT

u.username as OWNER,

o.NAME as TABLE_NAME,

c.NAME as COLUMN_NAME,

c.property,

decode(bitand(c.property, 137438953472 + 274877906944), 137438953472, 'YES', 274877906944, 'YES', 'NO') AS IDENTITY_COLUMN,

decode(bitand(c.property, 68719476736), 68719476736, 'YES','NO') AS IDENTITY_ONNULL,

decode(bitand(c.property, 137438953472), 137438953472, 'YES','NO') AS IDENTITY_ALWAYS,

decode(bitand(c.property, 274877906944), 274877906944, 'YES', 'NO') AS IDENTITY_DEFAULT,

'NO' AS IDENTITY_FAIL

FROM

sys.col$ c,

sys.tab$ t,

sys.obj$ o,

all_users u,

m_owners

WHERE c.obj# = t.obj#

AND t.obj# = o.obj#

AND o.owner# = u.user_id

AND u.username = m_owners.owner

AND decode(bitand(c.property, 137438953472 + 274877906944), 137438953472, 'YES', 274877906944, 'YES', 'NO') = 'YES';

-- ORA-00942: table or view does not exist SQL2.sql 38 3

Eddie D · June 14, 2021 4:02PM

Hi, thank you for your reply.

Just in case it is required by yourself and your DBA colleague, this help document explains the permissions required, the further information lists all the Oracle Data Dictionaries and system tables the comparison engine will require access to.

Many Thanks
Eddie

DOKMANC2 · June 14, 2021 9:04PM

Question: In theory, Is this product compatible with Oracle 19c (patch 19.10)?

The thing that confused me is that I got the the error message ' table or view does not exist" above in an upgraded env where the compare failed but I also could not query these table in a db where the Schema Compare program worked correctly.

I confirmed that my user id belonged to the SELECT_CATALOG_ROLE in all envs.

When I compared 3 schemas simultaneously, the comparison failed. However, I was able to successfully compare them individually.

i can send the entire verbose log to you if you like, or I can post it here, but it's long. Would you like me to mail it to you?

DOKMANC2 · June 15, 2021 7:54PM

The discovery that run into an issue when I select a 19c db AND more than one schema at a time to compare should help identify the issue, which sounds now more like an app issue than a permissions issue.

What do you think?

DOKMANC2 · June 22, 2021 5:03PM

What do you think?

DOKMANC2 · June 23, 2021 1:51PM

What do you think?

DOKMANC2 · June 23, 2021 1:53PM

Since I can compare two 19c database as long as I only select one schema at a time but it fails when I select multiple schema, do you accept that this is a product bug instead of a permissions issue?

DOKMANC2 · June 24, 2021 4:56PM

if I have any further questions, I'll be sure to contact "Red Gate Support"

Eddie D · July 1, 2021 11:40AM

Hi DOKMANC2,
Sorry for the delayed response, I have been out of the office so to speak and not available to update this forum post.

To my knowledge I am not aware of any problems until now in regards to Oracle 19c and being unable to compare multiple schemas.

The "saencryptedreport." reports you send, by pass the Product Support Team and update the Bug Tracking application directly. The Bug Tracking system does not reply to reports submitted.

However, I have located the reports you have supported. It appears that you are still using V5.7.4.3160 of Schema Compare for Oracle.

Can you please upgrade to V5.7.15.1555 of Schema compare for Oracle using this link? There have been various updates and bug fixes between versions 5.7.4 and 5.7.15. Then please confirm if the problem still occurs or no longer occurs.

Many Thanks
Eddie

DOKMANC2 · July 1, 2021 12:28PM

I upgraded to 5..15.1555 and got the same error, which I submitted the same way, through the application capturing and sending an email.

By the wat, before I upgraded, I selected the "Check for Updates" menu option and got a message that I was on the latest version. Due to past experience with upgrades resulting in my app turning into a demo version I figured this was the most likely way to upgrade to a version that would still be considered registered.

Eddie D · August 9, 2021 2:29PM

Hi,
Sorry for the delayed response.

I am unable to replicate the reported problem of not being able to select multiple schemas for a comparison using Oracle 19c databases.

My search for articles to get an explanation for the error, indicates the following:

This ORA-01008 errors are related with the SQL statement containing substitution variables was executed without all variables bound. All substitution variables must have a substituted value before the SQL statement is executed.

I am unsure how this relates to the code in the GUI where a user adds additional schemas to be compared.

In the Schema Compare for Oracle GUI, if you increase the logging level as per the instructions in this help article. Repeat the comparison attempt or project setup. When the problem occurs, locate the current log file. Is there any additional information in the log file on what maybe the cause of the problem outside of the ORA-01008 error?

Many Thanks

Eddie

DOKMANC2 · August 9, 2021 4:03PM

I attached the verbose log

DOKMANC2 · August 9, 2021 4:09PM

I attached the verbose log file here and saved but I don't see the log. I got no error. I also submitted it as a case.

What is puzzling is that when I selected only one schema, even though the UI appears to work fine the log shows SQLs followed by error messages like "view does not exist". Furthermore, I can execute the SQLs that the log reports as having raised errors.

When I select multiple schemas, I see one different error that may be important. This SQL I actually cannot execute myself as I get the same 'table or view does not exist" error reported in the log.

Is access to the two $ views really required? Why are they only required when comparing multiple schemas?

WITH m_owners AS

(

SELECT 'FDS_APPS' as owner from dual

)

SELECT

u.username as OWNER,

o.NAME as TABLE_NAME,

p.periodname as PERIOD_NAME,

p.flags as FLAGS,

p.periodstart as PERIOD_START,

p.periodend as PERIOD_END

FROM sys.sys_fba_period p,sys.tab$ t,sys.obj$ o, all_users u, m_owners

WHERE p.obj# = t.obj#

AND t.obj# = o.obj#

AND o.owner# = u.user_id

AND u.username = m_owners.owner;

Eddie D · August 12, 2021 9:57AM

Hi,
Thank you for your replies.

It appears that you only sent in another copy of the Error Report and not the log file I was seeking. If you right click the Schema Compare for Oracle icon in the very top left hand corner of the GUI, immediately above the File menu. -> Select the Minimum Log Levels option (also note the Locate log Files and Open Current log File options). ->Set the logging level to be Verbose.

Now repeat the comparison attempt with the multiple schemas. When you reproduce the problem, locate the Current Log file and reply with a copy for me to review.

In regards to this comment you posted:

When I select multiple schemas, I see one different error that may be important. This SQL I actually cannot execute myself as I get the same 'table or view does not exist" error reported in the log.

Is access to the two $ views really required? Why are they only required when comparing multiple schemas?

In the background the comparison engine will query a number of Oracle Data Dictionaries and system tables to obtain information about the objects held in the schema or schemas that the user is comparing. Along with the dependencies between the objects.

The query that is failing and generating the "view does not exist" message, is executed if you are comparing a single pair of schemas or multiple pairs of schemas.

The query in question to obtain PERIODFOR information in regards to any Materialized Views. I suspect the user that connects to the Oracle database does not have security permissions to the system tables sys.sys_fba_period p,sys.tab$ t and sys.obj$ o. Also the Oracle Data Dictionaries all_users u and m_owners.

This help document explains the permissions required and the Further Information section lists all the Oracle data dictionaries and system tables the tool requires security permissions upon.

Many Thanks
Eddie

DOKMANC2 · August 13, 2021 7:08PM

It looks like our company tightened up access when they upgraded out dbs to 19c. From past experience, I don't expect anyone is going to grant me special access to use Red Gate tools.

i clearly don't have access to sys.tab$ , sys.obj$ and the Red Gate docs say that this is needed. See the email from my DBA.

The product is only 13 mos old. Can I get a credit to buy something else? Otherwise, maybe I can put it on eBay. Interested parties, reply to this post.

Hi Chad,

On looking at the logs and your previous access.

You had “SELECT ANY TABLE” privilege to your LAN id which might have masked all the individual access needed to run these queries when the database was on 12c version.

As part of CIP standard, granting “SELECT ANY TABLE” is not allowed on individual lan id’s.

19c upgrade cleaned up most of the vulnerabilities as directed from CIP.

For the below query, you need select access on sys.sys_fba_period , sys.tab$ , sys.obj$ and all_users objects.

Again, these objects are internal db objects and access to them are restricted.

I have copied our dba security team who can assist you further on this.

Thanks,

Eddie D · August 17, 2021 4:25PM

Hi, thank you for your reply.

Sadly, the comparison engine requires permissions to the Oracle Data Dictionaries and System tables highlighted in the help document.

To reply to this comment:

The product is only 13 mos old. Can I get a credit to buy something else? Otherwise, maybe I can put it on eBay. Interested parties, reply to this post.

I cannot provide advice to you in regards to the purchasing and refunding of Redgate's products. Therefore I have been in contact with the Sales AE for your organisation's account.

If you contact the Redgate Sales team, I am sure that they can explore potential options for replacing the product, but these might be limited given you are working on Oracle and all of our Oracle tools we require the permissions highlighted in the help document. Alternatively, if you are able get the required security permissions, the Sales Team may investigate extending the support on your existing license.

Many Thanks
Eddie

DOKMANC2 · August 17, 2021 4:43PM

I still don't understand why the product works when one schema is selected. The product must execute different unnecessary code when multiple schemas are selected and that the solution to make your product more accessible to security conscious customers to is to avoid the 2nd path and two do the 1st path once per schema.

Internal Security just told me to use TOAD compare and contacted Software purchasing to take Red Gate software off the list of approved software.

Goodbye.

ORA-01008 not all variables bound error when trying to connect to Oracle 19c

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk