How to execute "Select .NEXTVAL from dual" query of Database in SAP PI ?

[Former Member]

HI,

I have a requirement for RFC to JDBC interface where for a database field : Sequence_Number,

PI needs to execute the query : Select Sequence_Number.NEXTVAL from dual. This query is currently being used by Tibco to get the next value of Sequence number and insert data into the database. Tibco will be replaced by PI. How can PI use the NEXTVAL function to get the sequence number ?

We are trying to execute a JDBCLookup udf which will get the result for the query. Below is the udf that we have used.

Have anyone used NEXTVAL function of database in SAP PI lookup ?Can this query retrieve the Next Value of Sequence Number from UDF or we need to use Stored procedure ? Kindly help.

Thanks in advance.

[Former Member]

I was able to execute the udf but now am getting the below error:

SQLSyntaxErrorException :ORA-02289: sequence doesnot exist.

Has anyone come across this type of Oracle database error? Please help.

Thanks!

[anupam_ghosh2]

Hi Trisha,

                 could you post the SQL statement?

Please check in database whether excat SQL statement is getting executed or not?

Maybe you are proividing wrong sequence name or the sequence is not present.

Regards

Anupam

[baskar_gopalakrishnan2]

>Have anyone used NEXTVAL function of database in SAP PI lookup ?Can this query retrieve the Next Value of Sequence Number from UDF or we need to use Stored procedure ? Kindly help.

If your target system is jdbc then you can also use the statement structure as below and call the query directly too...

Please refer statement6 structure. You can construct the target structure as this

http://help.sap.com/SAPHELP_NW04s/helpdata/EN/2e/96fd3f2d14e869e10000000a155106/content.htm

You can even avoid using placeholders if you dont have to pass for the query.

[anupam_ghosh2]

Hi Trisha,

                 the exact sql statement should be like this

                             "SELECT sequence_number.NEXTVAL FROM DUAL".

From the screen shot you have uploaded I can see an extra space before the dot or period.

Secondly is there a space between the words "sequence" and "number" in the line  

"Query=........."    then that space has to be removed. Please check this link http://www.java2s.com/Code/Oracle/Sequence/Sequencecurrentvalueandnextvalue.htm

to have greater details on the sql statement. I have not used this statement myself but this should work after corrections I have pointed out. you can check this link too http://www.coderanch.com/t/301501/JDBC/databases/next-sequence-number

Regards

Anupam