Skip to content

ODP and RefCursors.... #3

Open
Open
ODP and RefCursors....#3

Description

@wdalrymple
wdalrymple
opened on Apr 23, 2013

Do you support sys_refcurosrs? I can't seem to get this to work ok. The regular select statement works fine.

Here is my exception:

ORA-06550: line 1, column 123:
PLS-00103: Encountered the symbol "," when expecting one of the following:

. ( * @ % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec as between || indicator multiset member
submultiset

Here is my proc and code that is failing:

PROCEDURE MyProc
(
inDate DATE,
RC1 IN OUT SYS_REFCURSOR,
RC2 IN OUT SYS_REFCURSOR,
RC3 IN OUT SYS_REFCURSOR,
RC4 IN OUT SYS_REFCURSOR,
RC5 IN OUT SYS_REFCURSOR,
RC6 IN OUT SYS_REFCURSOR
)

...
var parameters = [
{
name: "inDate",
type: datatypes.DATE,
value: null,
direction: parameterDirection.INPUT
},
{
name: "RC1",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC2",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC3",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC4",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC5",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
},
{
name: "RC6",
type: datatypes.REFCURSOR,
direction: parameterDirection.OUTPUT
}
];

var cmdProc = new odpnode.OracleCommand("BEGIN MyProc( :inDate, :RC1, :RC2, :RC3, :RC4, :RC5, :RC6 ); END;", cmdType.STOREDPROCEDURE, parameters, con);

    cmdProc.executeNonQuery(function(err, rowsAffected, params){
        if(err){
            console.log(err);
        }else{
            console.log(params);
        }    
    });

I have also tried:

var cmdProc = new odpnode.OracleCommand("MyProc( :inDate )", cmdType.STOREDPROCEDURE, parameters, con);

and

var cmdProc = new odpnode.OracleCommand("MyProc( :inDate, :RC1, :RC2, :RC3, :RC4, :RC5, :RC6 )", cmdType.STOREDPROCEDURE, parameters, con);

In both of these cases, I get that RC1 is undefined or not bound. Any help would be appreciated.

Activity

  1. bomsy commented on Apr 23, 2013

    @bomsy
    bomsy
    on Apr 23, 2013
    Owner

    Hi,
    Try
    var cmdProc = new odpnode.OracleCommand("BEGIN MyProc( :inDate, :RC1, :RC2, :RC3, :RC4, :RC5, :RC6 ); END;", cmdType.TEXT, parameters, con);

    instead of

    var cmdProc = new odpnode.OracleCommand("BEGIN MyProc( :inDate, :RC1, :RC2, :RC3, :RC4, :RC5, :RC6 ); END;", cmdType.STOREDPROCEDURE, parameters, con);

    and let me know.
    Cheers

  2. wdalrymple commented on Apr 23, 2013

    @wdalrymple
    wdalrymple
    on Apr 23, 2013
    Author

    Thanks! Thats all it was. I would suggest adding this a sample to the descriptor page.

  3. wdalrymple commented on Apr 23, 2013

    @wdalrymple
    wdalrymple
    on Apr 23, 2013
    Author

    spoke to soon, how do I actually open up the cursor to read it afterwards?

    I would expect, from your sample: parameters[x].value

    Just trying to figure out what I am missing.

    Thanks!

  4. wdalrymple commented on Apr 23, 2013

    @wdalrymple
    wdalrymple
    on Apr 23, 2013
    Author

    Ok.. actually looked at your code. I should be using the executereader method (from your lib). Unfortunately, from what I have seen in code is that its limited to a single cursor (or reader).

    i.e in your method:

    Handle OdpCommand::ExecuteReader(const Arguments& args){
    HandleScope scope;
    Oracle::DataAccess::Client::OracleDataReader^ reader;
    Local callback = Local::Cast(args[0]);
    Handlev8::Value error = Undefined();
    Handlev8::Value rows;
    const unsigned argc = 2;
    //unwrap to access the object
    OdpCommand* c = ObjectWrap::Unwrap(args.This());
    try{
    //c->command->Connection->Open();
    reader = c->command->ExecuteReader();
    rows = ToJSON(reader);
    }catch(OracleException ^e){
    error = Helpers::String::ToV8String(e->Message);
    rows = Undefined();
    }

    //set up the variables for the callback function
Local<Value> argv[argc] = { Local<v8::Value>::New(error), Local<v8::Value>::New(rows) };
callback->Call(Context::GetCurrent()->Global(), argc, argv);

reader->Close();
//c->command->Connection->Close();
return scope.Close(Undefined());

    }

    I suggest that you create a executeDataset method possibly, or provide a call back with a multiple cursors.

  5. wdalrymple commented on Apr 23, 2013

    @wdalrymple
    wdalrymple
    on Apr 23, 2013
    Author

    Maybe a new method like this?

    Handle OdpCommand::ExecuteDataSet(const Arguments& args){
    HandleScope scope;
    Oracle::DataAccess::Client::OracleDataAdapter* adapter = new Oracle::DataAccess::Client::OracleDataAdapter();
    Oracle::DataAccess::Client::OracleDataSet* dataset = new Oracle::DataAccess::Client::OracleDataSet();

    Local<Function> callback = Local<Function>::Cast(args[0]);
Handle<v8::Value> error = Undefined();
Handle<v8::Value> result;
const unsigned argc = 2;
//unwrap to access the objectOdpCommand* c = ObjectWrap::Unwrap<OdpCommand>(args.This());   
adapter->SelectCommand = ObjectWrap::Unwrap<OdpCommand>(args.This());

try{
    //c->command->Connection->Open();
    adapter->Fill(dataset);
    result = ToJSON(dataset);       
}catch(OracleException ^e){
    error = Helpers::String::ToV8String(e->Message);
    result = Undefined();
}

//set up the variables for the callback function
Local<Value> argv[argc] = { Local<v8::Value>::New(error), Local<v8::Value>::New(result) };
callback->Call(Context::GetCurrent()->Global(), argc, argv);

reader->Close();
//c->command->Connection->Close();
return scope.Close(Undefined());

    }

  6. bomsy commented on Apr 24, 2013

    @bomsy
    bomsy
    on Apr 24, 2013
    Owner

    hi,
    Thanks. will look into the dataset.
    Did you try this to access your ref cursors
    cmdProc.executeNonQuery(function(err, rowsAffected, params){
    if(err){
    console.log(err);
    }else{
    console.log(params.RC1); //
    console.log(params.RC2);
    ...
    }
    });

  7. wdalrymple commented on Apr 24, 2013

    @wdalrymple
    wdalrymple
    on Apr 24, 2013
    Author

    Yes. When I do that all i get in response is the original parameter, without any of the data.

  8. wdalrymple commented on Apr 24, 2013

    @wdalrymple
    wdalrymple
    on Apr 24, 2013
    Author

    Thanks for all the quick support on this!

  9. bomsy commented on Apr 24, 2013

    @bomsy
    bomsy
    on Apr 24, 2013
    Owner

    No probs,
    i'm currently working on support for REFCursors based on your request.

    The way i'm thinking of going is making the parameters more intelligent
    so that it knows if it (a parameter) is a ref cursor and returns its value as json object
    representing the cursor.
    What do you think?

    An update should be out soon.
    Cheers

  10. wdalrymple commented on Apr 24, 2013

    @wdalrymple
    wdalrymple
    on Apr 24, 2013
    Author

    That would sound appropriate i would think.

    Generally in the .NET world you want to be using a dataset or an individual reader on each cursor.

    I think that this would be a nice comprimise and would be in line how you have structured things.

    Great idea for a project!
    Sent from my BlackBerry device on the Rogers Wireless Network

    -----Original Message-----
    From: Hubert Boma Manilla notifications@github.com
    Date: Wed, 24 Apr 2013 03:29:42
    To: bomsy/node-odpnode-odp@noreply.github.com
    Reply-To: bomsy/node-odp reply@reply.github.com
    Cc: wdalrymplemwdalrymple@Yahoo.com
    Subject: Re: [node-odp] ODP and RefCursors.... (#3)

    No probs,
    i'm currently working on support for REFCursors based on your request.

    The way i'm thinking of going is making the parameters more intelligent
    so that it knows if it (a parameter) is a ref cursor and returns its value as json object
    representing the cursor.
    What do you think?

    An update should be out soon.
    Cheers

    Reply to this email directly or view it on GitHub:
    #3 (comment)

  11. wdalrymple commented on Apr 25, 2013

    @wdalrymple
    wdalrymple
    on Apr 25, 2013
    Author

    Thanks for looking into this :)

  12. wdalrymple commented on May 6, 2013

    @wdalrymple
    wdalrymple
    on May 6, 2013
    Author

    Hey,was wondering how things were going :)

  13. bomsy commented on May 6, 2013

    @bomsy
    bomsy
    on May 6, 2013
    Owner

    Hi, bin busy. i've made changes to the code on github. on testing there were small issues. will release an update in few hours.
    Though you could pull the current code update from github, test it and let me know.

    For REFCursors,

    • Specify your parameters a RefCursors as usual

    • Call the executeReader and read the rows parameter of the callback
      eg

      .executeReader(error, rows){
      rows[0][0]["ZONE_ZONECODE"]; // zone_zonecode column of the first row of the first refcursor
      }

  14. wdalrymple commented on May 8, 2013

    @wdalrymple
    wdalrymple
    on May 8, 2013
    Author

    Still seems to be erroring, unless I am not pulling the right version of the code via npm.

    My code looks something like this:

  15. wdalrymple commented on May 8, 2013

    @wdalrymple
    wdalrymple
    on May 8, 2013
    Author

    var cmdProc = new odpnode.OracleCommand("BEGIN MyProx( :inDate1, :RC1, :RC2, :RC3, :RC4, :RC5, :RC6 );END;", cmdType.TEXT, parameters, con);
    cmdProc.executeReader(function(err, rows){
    if(err){
    console.log(err);
    }else{
    console.log(rows[0]);
    console.log(rows[1]);
    console.log(rows[2]);
    console.log(rows[3]);
    console.log(rows[4]);
    console.log(rows[5]);

                res.send(       
       '{ "RC1":' + JSON.stringify(rows[0]) + ',"RC2":' 
        + JSON.stringify(rows[1]) + ',"RC3":'
        + JSON.stringify(rows[2]) +',"RC4":' 
        + JSON.stringify(rows[3]) + ',"RC5":' 
        + JSON.stringify(rows[4]) + ',"RC6":' 
        + JSON.stringify(rows[5]) + "}" 
        );  

        }   
    });

    At the execute reader statement, it just dies, throwing an exception that it can't even seem to catch.

  16. 17 remaining items

  17. wdalrymple commented on Jul 3, 2013

    @wdalrymple
    wdalrymple
    on Jul 3, 2013
    Author

    I am not sure if I am missing something here, but it keeps failing silently for me (i.e. on the execute it crashes node w/o any exceptions that can be caught). Here is my code:

        var refParams = [
    {
        name: 'inDate1',
        type: datatypes.DATE,
        value: null,
        size: 2000,
        direction: parameterDirection.INPUT         
    },{
        name: 'RC1',
        type: datatypes.REFCURSOR,
        value: "",
        size: 2000,
        direction: parameterDirection.OUTPUT
    },{
        name: 'RC2',
        type: datatypes.REFCURSOR,
        size: 2000,
        value: "",
        direction: parameterDirection.OUTPUT
    },
    {
        name: 'RC3',
        type: datatypes.REFCURSOR,
        size: 2000,
        value: "",
        direction: parameterDirection.OUTPUT
    },
    {
        name: 'RC4',
        type: datatypes.REFCURSOR,
        size: 2000,
        value: "",
        direction: parameterDirection.OUTPUT
    },
    {
        name: 'RC5',
        type: datatypes.REFCURSOR,
        size: 2000,
        value: "",
        direction: parameterDirection.OUTPUT
    },{
        name: 'RC6',
        type: datatypes.REFCURSOR,
        size: 2000,
        value: "",
        direction: parameterDirection.OUTPUT
    }       
    ];  


    var refCmd = new odpnode.OracleCommand("<some schema>.<some package>.<some method with a date parameter>", cmdType.STOREDPROCEDURE, refParams, con);
    console.log('Create Command.');
    refCmd.executeReader(function(err, rowset){
        console.log('Command executed.');
        if(err){
            console.log(err);
        }else{
            console.log(rowset);
            res.send(JSON.stringify(rowset));
        }
    });

  18. wdalrymple commented on Jul 3, 2013

    @wdalrymple
    wdalrymple
    on Jul 3, 2013
    Author

    It would be handy to have the underlying module to create some sort of error log? To try and capture what is actually failing/dying.

    I can execute "select * from .

    " statements, so I know that the database connection is good.

    I am using node-odp version 2.3.

    I also forked my own branch off the master and tried that.

  19. wdalrymple
    closed this as completedon Jul 3, 2013
  20. wdalrymple
    reopened this on Jul 3, 2013

  21. bomsy commented on Jul 3, 2013

    @bomsy
    bomsy
    on Jul 3, 2013
    Owner

    pull the updates i made from the master on github
    Can you try making your function return only one cursor and test it?
    Also does any of the resultsets you are expecting back have characters that are not compatible with json?

  22. wdalrymple commented on Jul 3, 2013

    @wdalrymple
    wdalrymple
    on Jul 3, 2013
    Author

    Ok. I think that I have narrowed it down.

    I don't think that your solution can handle 'number' data types properly.

    I created a stored proc that generates a large negative and positive #. It seems to be the case that is causing it to crash (doesn't matter if the number is negative or positive). It works if I am just returning strings though so i guess that is some progress.

    Here is an sample of two numbers:

    -9827569743.16939
    7827569743.16939

  23. wdalrymple commented on Jul 5, 2013

    @wdalrymple
    wdalrymple
    on Jul 5, 2013
    Author

    Did this help? Are you able to reproduce the issue with it?

  24. bomsy commented on Jul 5, 2013

    @bomsy
    bomsy
    on Jul 5, 2013
    Owner

    not really. With these numbers it works fine for me.
    what are the column names for the data?

  25. wdalrymple commented on Jul 5, 2013

    @wdalrymple
    wdalrymple
    on Jul 5, 2013
    Author

    The column name is "VALUATION1"

  26. bomsy commented on Jul 5, 2013

    @bomsy
    bomsy
    on Jul 5, 2013
    Owner

    pls confirm, this samples
    [[{ "VALUATION1": -9827569743.16939 } ]] //this does not work?
    [[ { "VALUATION1": "-9827569743.16939" } ]] //this works?

  27. wdalrymple commented on Jul 5, 2013

    @wdalrymple
    wdalrymple
    on Jul 5, 2013
    Author

    Ok. This is very strange

    This method works:
    PROCEDURE Sample
    (
    RC1 IN OUT SYS_REFCURSOR
    )
    IS
    inId varchar(2000);
    BEGIN

    inId :='1234';

    OPEN RC1 FOR
    SELECT-1827569743.16939  FROM DUAL;

    END Sample;

    But this one then crashes. I can execute this method fine via other means (i.e. pl/sql developer etc).

    PROCEDURE Sample
    (
    RC1 IN OUT SYS_REFCURSOR
    )
    IS
    inId varchar(2000);
    BEGIN

    inId :='1234';

    OPEN RC1 FOR
    SELECT somenumber FROM schema1.table1 WHERE id = inId AND somefiler='FilterByThis';

    END Sample;

    Where somenumber is column defined as a NUMBER. I believe that your code does not handle this database datatype correctly.

    I am really baffled by this.

    I can run the following method fine that returns a list of varchars:

    PROCEDURE Sample
    (
    RC1 IN OUT SYS_REFCURSOR
    )
    IS
    inId varchar(2000);
    BEGIN

    inId :='1234';

    OPEN RC1 FOR
      SELECT DISTINCT       
       P.Department       
     FROM 
       someschema.sometableaboutdepartments P
      WHERE companyId = inId;

    END Sample;

    I think that it has something to do with the handling of NUMBER. That or I haven't built the node package correctly? I don't know. I don't think that it would half work with varchars if it hadn't build properly (i.e. the wrong version of code).

  28. wdalrymple commented on Jul 5, 2013

    @wdalrymple
    wdalrymple
    on Jul 5, 2013
    Author

    (your sample did work.. if I just did a selection -19219219219219 as valudation1 from dual.)

  29. bomsy commented on Jul 5, 2013

    @bomsy
    bomsy
    on Jul 5, 2013
    Owner

    i think ive figured it out, you are right about the numbers. The issue is valid json does not accept numbers as keys
    e.g
    {
    1000: "bla" //this is invalid json
    }

    while
    {
    "1000": "bla" //this is valid json
    }

  30. wdalrymple commented on Jul 6, 2013

    @wdalrymple
    wdalrymple
    on Jul 6, 2013
    Author

    I tried this as well: SELECT somenumber as "COLUMN1" FROM schema1.table1 WHERE id = inId AND somefiler='FilterByThis'; to the same effect.

  31. wdalrymple commented on Jul 15, 2013

    @wdalrymple
    wdalrymple
    on Jul 15, 2013
    Author

    any luck?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions