LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: saravan

Hi,

My requirements is to calling a oracle storedprocedure by passing a INPUT from the .CSV file. which lavastorm node is used for executing the storedprocedure? An example will help me out.

Thanks in Advance.

Regards,
Saravan

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

You have a couple of choices available. If you want to call a packaged function you can use the JDBC Query, DB Execute or DB Query nodes. If you want to execute a procedure you can use either the JDBC Execute or the DB Execute node.

The attached example does this a couple ways. In one node it uses the DB Execute node to call the procedure to seed Oracle's random number generator using input from a CSV file and then retrieves a random number. And using the JDBC Query node it demonstrates calling a function to retrieve a random number passing in data from the CSV input file. Lastly it demonstrates calling a procedure using the JDBC Execute node.

You will have to populate the Run parameters in the attached graph to supply a directory name for the CSV file that gets created and then read and to log into the Oracle database.

Ernest
Attachments:
OracleRandom.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: saravan

Hi,

Thanks for your reply,

Actually i want to execute the function having java class below.

create or replace
FUNCTION "FN_WRITE_BLOB_TO_FILE" (ID IN VARCHAR2) RETURN VARCHAR2
As
Language Java Name 'BLOBFileExample.readBLOBToFileGet(java.lang.Strin g) return java.lang.String';

When i try to execute the function using DB Execute node from the graph, i'm getting the following error

>> errlog:
>> errlog:Installing log (05/21/2012 19:48:52) ...
>> errlog:2012-05-21 19:48:53; Level: 2; Type: 0; Desc: "index out of bounds: 0 / 0"; File: "./../../jigsaw/Code/Include/JtArray.h"; Line: 323; Context: "JtArray<>::GetAt()"
>> errlog:2012-05-21 19:48:53; Level: 2; Type: 0; Desc: "...exception seen"; File: "./../code/source/expert/SqlNonSelect_ExprStmt.cpp"; Line: 90; Context: "Salmon::NED::SqlNonSelect_ExprStmt::BindValue s"
>> errlog:2012-05-21 19:48:53; Level: 2; Type: 0; Desc: "...exception seen in statement 'sql-non-select', line 2"; File: "./../code/source/expert/ExprStatement.cpp"; Line: 56; Context: "Salmon::NED::Statement_Expr::evaluate()"
>> errlog:2012-05-21 19:48:53; Level: 2; Type: 0; Desc: "...exception seen in context: { 0(in1):2 }"; File: "./../code/source/brain/ExprTableIo.cpp"; Line: 255; Context: "Salmon::ExprTableIo::processInputRows()"
>> errlog:Uninstalling log (05/21/2012 19:48:53) ...

I've attached the java class and sample graph for your reference.

Regards,
Saravan
Attachments:
Attachment Sample.brg
BLOBFileExample.java.txt

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

Thanks. So have you been able to call this Java code from SQL*Plus or any SQL tool? My first thought is that you might make sure it works inside SQLPlus because if it doesn't work there you can more easily get help from Oracle experts. I am unfamiliar with calling Java from Oracle and based on what I see you doing here there are only a couple steps to getting it done.

One possible answer is that Oracle functions have to be called in such a way that they can put the value they return somewhere. So in a block of PL/SQL code you need to assign the results to a variable or pass it as a parameter in another function call. Or, and I think this might be the answer you need, you can call the function in a select statement. Oracle has a system table that is very helpful in situations like this named DUAL which always has exactly one record. So replace this code that is in the Attachment node:

sql1 = "begin FN_WRITE_BLOB_TO_FILE(attachmentId); end;"
sqlNonSelect(sql1,'ATTACHMENT_ID')

with this:

sql1 = "select FN_WRITE_BLOB_TO_FILE(:1) as FUNCTION_RETURNED_THIS from dual"
sqlSelect(1,sql1,'ATTACHMENT_ID')

Notice the change to a different function, sqlSelect() which will take the results of the SQL statement and send it to the output specified as its first parameter. And when using the DB Execute node you need to use bind parameters to indicate where to put the values that come from the input stream. In this case there is only one value indicated by :1 and this is matched to the 'ATTACHMENT_ID' field, the third parameter in the call to the sqlSelect() function. (Disclaimer: I didn't test the code so I hope I have the syntax right)

One more thing, in case you want to understand the error message above better, the "index out of bounds: 0 / 0" is because the 'ATTACHMENT_ID' parameter didn't have a matching ":1" in the SQL statement. So it seems that somewhere in the internal processing it must have created an array variable to hold the parameter values and since there are no parameters, its size is set to zero. Then when it attempts to place the value currently in 'ATTACHMENT_ID' field into that array it throws the "index out of bounds" error.

I hope this helps.

Ernest

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: saravan

Hi,
Thanks a lot, Its working fine

How can we define the delete query in the DB execute node (Syntax). for example I need to delete the table value based on the previous node input (ATTACHMENT_ID).

Thanks again..

Thanks,
Saravan.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

If I understand what you need, and assuming you have a table named THAT_TABLE_NAME with a column named ATTACHMENT_ID_KEY it would be something like this:

sql = "delete from THAT_TABLE_NAME where ATTACHMENT_ID_KEY = :1"
sqlNonSelect(sql,'ATTACHMENT_ID')

ernest