"RQSYS.RQEVALIMPL"Line 104->output data.frame doesnot match output specification

gitaish-Oracle

At First the issues seems like script error but apparently some other issue. Below is the snapshot of the architecture.

OFSADV->Working Env

OFSUAT->Non working env.

1. we have a exadata half rack. Which is running on 4 nodes like 4 physical node ip's and 1 scan ip.

2. OFSADV database runs on Node-1 and Node-3 which is the working environment and is in PDB/CDB architecture.

3. OFSUAT is running on Node-3 but is a stand alone database.

4. Oracle R distribution which were RPM's has been installed on all the 4 nodes and (usr/lib64/R is the R home for all, since bank wanted all node IP to have the same image.

5. Now ORE is installed on OFSADV(PDB/CDB->working env, operational for almost 6 months).

6. Again ORE is installed on OFSUAT(Non-Working Env, standalone architecture)

Now when we execute the below scrip in the working environement

begin
sys.rqScriptDrop( 'SID' );
end;
/


begin
sys.rqScriptCreate( 'SID',
'
function(){

return( data.frame(ORACLE_SID = Sys.getenv( "ORACLE_SID" ) ) );
}
'
);
end;
/

select * from table ( rqEval( NULL, 'select ''1234567890123456789123456789'' as ORACLE_SID from dual', 'SID'));

ORACLE_SID

Consildal

But when we run the same query in OFSUAT Environment we get the following error

select * from table ( rqEval( NULL, 'select ''1234567890123456789123456789'' as ORACLE_SID from dual', 'SID'));

select * from table ( rqEval( NULL, 'select ''1234567890123456789123456789'' as ORACLE_SID from dual', 'SID'))

*

ERROR at line 1:

ORA-20000: RQuery error

output data.frame does not match output specification

ORA-06512: at "RQSYS.RQEVALIMPL", line 104

ORA-06512: at "RQSYS.RQEVALIMPL", line 101

gitaish-Oracle

Hi raphel

The issue was resolved with below change in db parameter. it was hitting a database bug and do eval was giving invalid identifier issue.

R> ore.exec('alter session set cursor_sharing=EXACT') or SQL> alter session set cursor_sharing=EXACT;

We had a session with Sherry and she helped in debuggin the issues and gave the resoliution.

Thanks All for the help.

regards

Gitaish

gitaish-Oracle

OFSADV(working env)

NAME OPEN_MODE            CDB CON_ID VERSION         

--------- -------------------- --- ---------- -----------------

CONSILDA  READ WRITE YES          0 12.1.0.2.0      

1 row selected.

OFSAUAT(Non working env)

NAME OPEN_MODE CDB     CON_ID VERSION         

--------- -------------------- --- ---------- -----------------

OFSAUATS  READ WRITE NO           0 12.1.0.2.0      

1 row selected.

Sherry Lamonica-Oracle

Please confirm that the database for OFSUAT running on node 3 is a separate, standalone database not shared across any other nodes.  Oracle R Enterprise is not supported (and will not work) when installed upon a single node of a shared database, it must be installed across all nodes of the shared database.

The command:

select * from table ( rqEval( NULL, 'select ''1234567890123456789123456789'' as ORACLE_SID from dual', 'SID'));

will not validate the Oracle R Enterprise installation or functionality.

If node 3 was installed as a true standalone database (non-RAC), please return the output from validation commands listed in the Oracle R Enterprise 1.4.1 Installation Guide

run from the R interface on the database server.

Thank you.

Sherry

rtiran

Hi,

Error message seems to indicate that the dataframe returned by the function contains several columns whereas the query that it is mapped to only contains a single field.

It looks strange as the way your R code constructs the output, it should be a single column dataframe.

Can you try to change the query to :

select * from table ( rqEval( NULL, 'select ''1234567890123456789123456789'' as ORACLE_SID, ''1234567890123456789123456789'' as FIELD2, from dual', 'SID'))

If it does not fail, the value that you'll retrieve for FIELD2 will probably shed some light to why the function is sending back several columns.

If you get once again the ORA-20000 exception, you can try to add more fields to the query up to the point that it will match the number of columns of the dataframe returned by the function.

The idea here would be to understand what unexpected value is returned by the function in addition to the ORACLE_SID.

Regards,

Raphaël

gitaish-Oracle

Hi raphel

i tried with almost 20 columns still no luck. Will it give the same error if there is no rows selected, any other trick we can use.

regards

Gitaish

rtiran

Hi Gitaish,

OK then, what I would do next would be to invoque the stored script directly from R (with ore.doEval).

As there is no CAST to a predefined structure involved, you should see exactly the content of the returned dataset.

For instance, here I intentionnaly modify your script to reproduce the error you get:

SQL> begin

  2  sys.rqScriptCreate( 'SID',

  3  'function(){

  4  ORACLE_SID = Sys.getenv( "ORACLE_SID" );

  5  return( data.frame(ORACLE_SID, ORACLE_SID) );

  6  }');

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL>

Note that there are two occurences of ORACLE_SID in the returned dataframe (therefore I get a 2 columns dataframe).

If I try to use the SQL call, I get the exact same message than you get:

SQL> select * from table ( rqEval( NULL, 'select ''1234567890123456789123456789'' as ORACLE_SID from dual', 'SID'));

select * from table ( rqEval( NULL, 'select ''1234567890123456789123456789'' as ORACLE_SID from dual', 'SID'))

*

ERROR at line 1:

ORA-20000: RQuery error

output data.frame does not match output specification

ORA-06512: at "RQSYS.RQEVALIMPL", line 104

ORA-06512: at "RQSYS.RQEVALIMPL", line 101

SQL>

Now, in order to understand what's happening, I can call the stored script directly from R:

> library(ORE)

> ore.connect(user="*******", password="*******", conn_string="*******")

> ore.doEval(FUN.NAME="SID")

   ORACLE_SID ORACLE_SID.1

1 HODBA04D1_1  HODBA04D1_1

>

Here I can see that the returned dataframe contains indeed 2 columns.

See if you can find out with that method what extra information is returned in your case.

Hope this helps.

Regards,

Raphaël

gitaish-Oracle

Hi raphel

The issue was resolved with below change in db parameter. it was hitting a database bug and do eval was giving invalid identifier issue.

R> ore.exec('alter session set cursor_sharing=EXACT') or SQL> alter session set cursor_sharing=EXACT;

We had a session with Sherry and she helped in debuggin the issues and gave the resoliution.

Thanks All for the help.

regards

Gitaish