CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Viktor
Viktor

Reputation: 3

Oracle PL/SQL stored procedure with user type object in plain query

I've some stored procedure in Oracle database - GetBalance. Single input parameter is custom user type

create or replace TYPE XXX_INPUT_PARAM as object (
  CUSTOMER_CODE VARCHAR2(2000),
  DATA_START VARCHAR2(10),
  DATA_END VARCHAR2(10),
)

I want to use this in plain query. Something like this:

select * from table(bigOraclePackage.GetBalance('12345', '2023-04-01', '2023-04-06'))

Is it possible? How can i do it? I've try:

SELECT bigOraclePackage.GetBalance(CAST(MULTISET(SELECT '12345','2023-04-01', '2023-04-06' from DUAL) as XXX_INP_PARAM)) FROM DUAL

but catched ORA-22907 "invalid CAST to a type that is not a nested table or VARRAY"

I can't use stored procedure or function. I can't edit or change or create new database objects.

Upvotes: 0

Views: 116

Answers (1)

Alex Poole
Alex Poole

Reputation: 191520

Assuming your function returns a scalar number - based on its name - then you can call it as:

select bigOraclePackage.GetBalance(xxx_input_param('12345', '2023-04-01', '2023-04-06'))
from dual

That creates an object using the literal values, and passes that object to the function; and as a select-list item.

fiddle

You can select from any table - with fixed values dual makes sense, but if you're getting the values from some other table then you can query against that and use the column names in the object call.

Upvotes: 0

Related Questions