Hi,
We have a requirement where we need to pass value as parameter for the Before/After SQL in Oracle Connector. This value can be a DML statement or a Procedure/Package call and these statements are not limited to one table/procedure. The statement in general contains multiple lines and so i have to make into a single line so that it can be passed as parameter.
Is there a way that we can pass multiple lines/block as parameter.
Thanks in Adavance
Pass multiple lines or rows as parameter
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Any form of delimited string.
If you think about it, that's all a file is, with line terminator as the delimiter.
Of course, the Before/After subroutine will need to unpack the delimited string. Even if you persevere with using line terminator as the delimiter.
If you think about it, that's all a file is, with line terminator as the delimiter.
Of course, the Before/After subroutine will need to unpack the delimited string. Even if you persevere with using line terminator as the delimiter.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks for the reply Ray and appreciate the idea of reading from file \n as delimiter.
First of all, let me be more clear on the purpose of this job. Sorry for not being so earlier.
This job will be run on an Adhoc request in Production environment. Most of the times I did not face a problem just had to make the parameter value into a single line easily. But yesterday I had to run 4000 insert statements and these statements were prepared manually. The data is not in any of the table and when I made all those insert statements into a line it has length of 2 million.
First problem was :
When I put that entire insert statements as a single string in the parameter field, a warning message popped up saying that the maximum allowed is 65,519 bytes.
is there an environment variable setting or something that this limit can be increased.
Second thing is
To read from file with \n as delimiter, the file has to be again migrated from DEV to PROD. We had already more than a million records to be processed for that day and the migrations involves Admin team, request forms and approvals.
So I was looking for an option if we can pass multiple lines as a parameter at a time. I am still wondering why IBM has not provided this.
Once again thanks for the reply
First of all, let me be more clear on the purpose of this job. Sorry for not being so earlier.
This job will be run on an Adhoc request in Production environment. Most of the times I did not face a problem just had to make the parameter value into a single line easily. But yesterday I had to run 4000 insert statements and these statements were prepared manually. The data is not in any of the table and when I made all those insert statements into a line it has length of 2 million.
First problem was :
When I put that entire insert statements as a single string in the parameter field, a warning message popped up saying that the maximum allowed is 65,519 bytes.
is there an environment variable setting or something that this limit can be increased.
Second thing is
To read from file with \n as delimiter, the file has to be again migrated from DEV to PROD. We had already more than a million records to be processed for that day and the migrations involves Admin team, request forms and approvals.
So I was looking for an option if we can pass multiple lines as a parameter at a time. I am still wondering why IBM has not provided this.
Once again thanks for the reply
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can, but only up to a limit of about 64KiB, as you have discovered.
There is no environment variable for changing this limit, at least not one of which I'm aware. You might find one from your official support provider.
You may need to contemplate a different approach - perhaps an External Source or Row Generator stage to generate rows which a preliminary job can insert for you.
There is no environment variable for changing this limit, at least not one of which I'm aware. You might find one from your official support provider.
You may need to contemplate a different approach - perhaps an External Source or Row Generator stage to generate rows which a preliminary job can insert for you.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.