How to show sequence table in sequence folder-CodePudding

I'm working on a SOA BPEL process where it's reading data from an XML file and load it into database tables. However, the table sequence I need to call in my transformation file is not showing in the sequence table, but it can be queried (see below query). Because of this, it returns an error where transformation file is failing as it treats these sequences non-existent. I need to call these sequences for me to be able to generate sequencing values for the table IDs.

SELECT XXTG_SAMPLE_HEADER_S.nextval FROM DUAL;
SELECT XXTG_SAMPLE_LINES_S.nextval FROM DUAL;

Here are the only sequence showing in the folder:

Here I'm calling them in my transformation file:

<tns:repairId>
    <xsl:value-of select="oraext:sequence-next-val ('XXTG_SAMPLE_HEADER_S', 'jdbc/timexappsDataSource')"/>
</tns:repairId>

<tns:repairLineId>
    <xsl:value-of select="oraext:sequence-next-val ('XXTG_SAMPLE_LINES_S', 'jdbc/timexappsDataSource')"/>
</tns:repairLineId>

CodePudding user response：

There are only simple explanations for the error ORA-02289: sequence does not exist when you can ad Hoc query it.

Let's list them

the sequence exists, but in a different database than your process is connected to.
the sequence exists, but in a different schema than your process is using to connect.

Solution of the former case is obvious.

In the latter case

check the schema of the sequence

select SEQUENCE_OWNER from all_sequences where sequence_name = 'XXTG_SAMPLE_HEADER_S';

check the user your process connects
grant SELECT on the sequence to the connect user

connect with the user that created the sequence and

grant select on seq_user.XXTG_SAMPLE_HEADER_S to connect_user;