OBIEE 12C Direct Database Request with variable

BASSAM CHEHABEDDINE

Hi,

I am trying to make a DDR from OBIEE 12C i.e

select * from STTM_CUSTOMER where branch_code=:br

where br should be a variable or command prompt as it is used to work in toad and sql developer

and i am getting the following error:

View Display Error

Error generating view. Error getting cursor in GenerateHead

  Error Details

Error Codes: OAMP2OPY:OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

Odbc driver returned an error (SQLExecDirectW).

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

State: HY000. Code: 43093. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. (HY000)

State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 904, message: ORA-00904: "BRANCH_CODE": invalid identifier at OCI call OCIStmtExecute. (HY000)

State: HY000. Code: 17010. [nQSError: 17010] SQL statement preparation failed. (HY000)

SQL Issued: {call NQSGetQueryColumnInfo('EXECUTE PHYSICAL CONNECTION POOL "Connection Pool" select * from STTM_CUSTOMER where branch_code=:br')}

Refresh

your support and advice are highly appreciated...

Best Regards,

Gianni Ceresa

Well,

OBIEE isn't Toad and OBIEE isn't SQL Developer.

So if you want to use a variable you need to use the OBIEE syntax for it.

For a presentation variable you could use the standard syntax like @{variable} etc.

It will depends on the kind of variable you want to use, the syntax could be different. There are many blog posts, articles and even the official doc covering all this.

Just to be more explicit: don't expect the DDR to prompt you for the value!

You will need to set the variable somewhere else using something else (like a prompt) and the DDR will be able to use that value.

DDR is something which would need to be avoided as it bypass all the logic and all the features OBIEE gives you, so don't expect it to be smarter than needed prompting you to enter a value automatically when executing the DDR.

BASSAM CHEHABEDDINE

Thank you, where i should set the variable then can you please advise i tried to search but didn't a clear steps .

for the other part what other the DDR you prefer the drag and drop or the analysis simple sql statement?

Regards,

[Deleted User]

1.) Everything that Gianni said.

2.) ORA-00904: "BRANCH_CODE": invalid identifier at OCI call

That points to a rather more basic issue in the sense that BRANCH_CODE seems to not be known in the database.

BASSAM CHEHABEDDINE

My Issue was solved by creating Dashboard Prompt and put BRANCH_CODE='@{BRANCH_CODE_V}{}' .

where BRANCH_CODE_V is added inside DASHBOARD PROMPT.

hope it will help ..

Gianni Ceresa

As posted 10 days ago ...