Unsupported Column

Jaymie

Hi, I hope someone can help me. Last weekend, our team made some changes to SQL. They added a column and removed 1 column; those added are not part of any view I used in Oracle. But because of the changes, oracle is now returning an error. When I go to connection on Oracle BI, it says failed to retrieve data, and it says unsupported column. Even my other connection is doing the same thing. Do I have to do anything on the RPD?

Federico Venturin - Oracle

Hi @Jaymie ,

I would focus on the error that you are getting when you try to view a report first.

It says that the OAC_LOGIN_INITBLOCK initialization block (defined in the RPD) must complete successfully. It has probably been marked as "Required for authentication" and gives you this kind of issues when its query returns no result.

You need to open the RPD, check the query that must be executed by that initialization block, fix the SQL code if necessary, and eventually disable the "Required for authentication" option if it's no longer required.

Federico Venturin - Oracle

Hi @Jaymie ,

It would be great if you can give us additional information:

• Which tool and version are you using?
• Where have changes been applied? Directly to the physical database? To the dataset definition in DV?
• What do you mean exactly with "when I go to connection on Oracle BI"? Are you in the dataset editor? semantic modeler editor?

Jaymie

Hi @Federico Venturin

Thank you for always willing to help. I'm doing it on the Data > Connection > click on one of the Connections. It will give me the Schema; under Schema, it gives me an error saying Failed to retrieve, and when I hover over the error, it says, unsupported column, and when I try to view a report. It returned this error:  

Dashboard Display Error

Error during query processing (SQLDriverConnectW).

  Error DetailsError Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TACState: 08004. Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused. (08004)State: HY000. Code: 43275. [nQSError: 43275] Message returned from OBIS [ecid:a4e4a23f-651c-4558-b808-e3a160438555-0014fddf,0:1:2:6 ts:2024-04-15T15:09:53.708+00:00]. (HY000)State: HY000. Code: 13024. [nQSError: 13024] Successful completion of init block 'OAC_LOGIN_INITBLOCK' is required. (HY000)

I'm using the Oracle BI Administrator Tool for the RPD; those columns added last weekend in SQL Server in one of the tables are unnecessary on my RPD.

Also, I tried to edit or inspect one of the connections and add a password, and when I clicked on save, this error came out: Failed to save the connection. There was an unexpected error while processing this request. Please try again.

Jaymie

Hi @Federico Venturin ,

I appreciate your help. I tried to disable the required authentication, and it seems to work, and if I enable it again. It will give me the same error.

Federico Venturin - Oracle

Hi @Jaymie ,

This is expected. I was able to see the screenshot of the intialization block before you removed it, and noticed that you are using an INSERT statement instead of a SELECT one: this is not supposed to work and will always generate issues. You have to use only SELECT statements in initialization blocks.

Jaymie

Hi @Federico Venturin

I was working before, no problem. We always used insert. It only happens after we add four columns or fields in one table.

I don't know what I will do to this. :(

GayathriAnand-Oracle

You should not be using INSERT statements for Init Block SQL.

The sql you had for insert was to insert into OAC_LOGIN table and not the INIT_BLOCK_INFO table you have highlighted above, so not sure what exactly you are trying to show above.

You should follow best practice for RPD, keep the usage tracking schema definition as what OAC has defined it as - do not modify. If you have custom tables in this connection pool, suggest you create a new connection pool for this purpose and use that connection pool for any custom defined schemas.

Seems like you are tweaking the application to log every user login info into UT by using insert in init block - this is not the intended use case for this feature.

[Deleted User]

+1 to Gayathri and Federico. You don't INSERT with init blocks. Init blocks read. Usage Tracking writes but it does so behind the scenes.

What is that OAC_LOGIN table and why are you writing into it? Do you have Usage Tracking enabled? What's the business use case for this? Are you tracking unique logins?

And most importanly: Are you doing other such things??

Jaymie

Hi All,

I don't know but I restore the backup, and it's working fine now. Idk what happened, but I'm still clueless. But seems like it's working fine now. Thank you for all the help.

Gianni Ceresa

 it's working fine now

While it is working, and it was working before, it's technically not supported (there are many things that works in the product while not being officially supported).

Therefore it's a risk still to use that. There are other ways, official ways, to track users, sessions etc.

Consider planning to change that init block to remove the INSERT statement and move to something else. It could stop working at any time as soon as you patch or upgrade your environment.