Can I use a WITH() closure in a procedure?

Edisson Gabriel López

I have a script that I run in Oracle Database 19c 19.0.0.0.0.0.0, and I use two date parameters from - to in a WITH() closure then depending on what it returns it does the INSERT to a table, then I wonder if it is possible to use my SQL statement in a procedure for this case, it is for an orchestrator that does not allow the SQL statement because of the number of lines, since it exceeds the maximum and I have to use a procedure.

It is the first time that I am going to use a procedure and I did not find information if I can use a with() closure and the parameters in a procedure, thank you.

I attach the SQL statement that I execute, thank you for your attention

SQL Insert F59INVEN.txt

Frank Kulash

Hi, @Edisson Gabriel López

 I use two date parameters from - to in a WITH() closure then depending on what it returns it does the INSERT to a table

I think you're asking about a WITH clause, not "closure".

You can use WITH clauses in PL/SQL SQL statements the same way you use them in pure SQL, but there might be better ways to do whatever you want. Defining parameters is one example: in PL/SQL, a SQL statement can reference local variables, and that could be a better way than a WITH clause to pass parameters to a SQL statement.

I attach the SQL statement that I execute

Post everything right in this space. Don't use attachments. Not everyone who wants to help you can or will open attachments. Post the entire procedure (or a simplified version), not just the SQL statement.

Frank Kulash

Hi, @Edisson Gabriel López

 I use two date parameters from - to in a WITH() closure then depending on what it returns it does the INSERT to a table

I think you're asking about a WITH clause, not "closure".

You can use WITH clauses in PL/SQL SQL statements the same way you use them in pure SQL, but there might be better ways to do whatever you want. Defining parameters is one example: in PL/SQL, a SQL statement can reference local variables, and that could be a better way than a WITH clause to pass parameters to a SQL statement.

I attach the SQL statement that I execute

Post everything right in this space. Don't use attachments. Not everyone who wants to help you can or will open attachments. Post the entire procedure (or a simplified version), not just the SQL statement.

Edisson Gabriel López

Hi @Frank Kulash, if it worked for me using a with() closure in a procedure, what gave me more problems was the variables, because if I put a NCHAR format it automatically put me (200) so I do not know if it is possible to change that size, since the field where you must insert is only 10 spaces (NCHAR(10)).

Hi Frank Kulash, yes me funciono using a clause with()

CREATE OR REPLACE PROCEDURE CRPDTA.insertF59INVEN(
	   FDESDE NCHAR,
	   FHASTA NCHAR,
     USER_ID NCHAR)
IS
BEGIN

INSERT INTO F59INVEN (

The problem is with the user USER_ID, since I do it to insert the name of the user who made the INSERT:

So I don't know if it is possible to change the field size.

Frank Kulash

Hi, @Edisson Gabriel López

Sorry, I don't understand. You said this problem was all about using WITH to pass two DATE parameters, but the code fragment you passed has nothing about WITH and nothing about DATEs, If this is a new problem, then start a ew thread for it. If the question is about using some front-end tool, then post the question in a space that deals with that front end, not in the SQL and PL/SQL space.