CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-function
Денис Шарыпов
Денис Шарыпов

Reputation: 11

How to write a SQL function that executes SELECT * FROM TABLE(name_function(date '01/01/2022'));?

I need to write a function to which I pass a date, and in response I receive records with the same date.

Table:

CREATE TABLE USERS(NAME VARCHAR, DATE_REG DATE);
INSERT INTO USERS(NAME, DATE_REG) VALUES ('TEST1', '01/01/2022'),('TEST2', '01/02/2022');

I am running a function:

  SELECT * FROM TABLE(name_function(date '01/01/2022'));
Expecting to receive: 'TEST1', '01/01/2022'.

How to write a function?

What matters to me is that the function call was "SELECT * FROM TABLE(name_function(date '01/01/2022'));" Script need for Oracle

Upvotes: 0

Views: 83

Answers (1)

Pawel W
Pawel W

Reputation: 147

  1. To correct your SQL:


INSERT INTO USERS(NAME, DATE_REG) VALUES ('TEST1', TO_DATE('2003/07/09', 'yyyy/mm/dd'));

SELECT NAME, DATE_REG FROM USERS 
WHERE 
DATE_REG = TO_DATE('2003/07/09', 'yyyy/mm/dd');
  1. Normal function return you one value, you can consider create procedure that create some results table or function (Return a «table» from a function)

You can try: https://renenyffenegger.ch/notes/development/databases/Oracle/PL-SQL/collection-types/return-table-from-function/index

So you should take your date as parameter then, get results from select and transform results to result table and return from function

Upvotes: 1

Related Questions