ORCHESTRATE exception in main program: APT_ParseError

kapil008 · Post by **kapil008** » Mon Feb 06, 2006 3:10 pm

hi guys,
when i run a px job the job abborts with the following message : -

main_program: Fatal Error: Caught ORCHESTRATE exception in main program: APT_ParseError: Parsing parameters "max=0" for schema type "ustring": Max length must be positive, got: "0"
Could not find type: ustring[max=0]

can anyone help me on this.....???

Kapil

ArndW · Post by **ArndW** » Mon Feb 06, 2006 5:35 pm

The error message is very clear, you are using an incorrect schema for your input or output. Did you import the schema file from a text file?

kumar_s · Post by **kumar_s** » Tue Feb 07, 2006 3:27 am

Hi,

What is the stage you use in the job for input. Are you doing any type conversion. Using any transformer?
Is there any field length been unhaldled or empty?
Have you given any varchar or char type of field as feild length as 0?
What type of data is it?

-Kumar

aramachandra · Post by **aramachandra** » Tue Feb 07, 2006 9:23 am

I have seen this error when working with oracle source tables.

If your metadata is as follows
COL1 decimal (38,10)
TEST varchar 20

And For example if you have a query which is as follows

SELECT
COL1
,NULL TEST
FROM TABLE;

where you want to set the test column to null,( though many will question why someone would like to hard code a null value to begin with)

If you do a view data on this you should get the error you have listed or similar to that.

Fundamentally in my case i was trying to hard code a null value in the query. I changed my job design to not have situations where i have to hard code nulls, and instead starting giving default values which are not significant.

Hope this helps...

Arvind

kapil008 · Post by **kapil008** » Tue Feb 07, 2006 1:14 pm

kumar_s wrote:Hi,

What is the stage you use in the job for input. Are you doing any type conversion. Using any transformer?
Is there any field length been unhaldled or empty?
Have you given any varchar or char type of field as feild length as 0?
What type of data is it?

-Kumar

Hi Kumar,

I am doing a simple moving of data from oracle tables using a transformer. I am attaching a screenshot of my design.
http://www.geocities.com/kapil_ladha/meta.bmp
( If the image on the URL is blurry, save the BMP file on your desktop and open it using Paint Brush )

The Two Table Metadata are : Point_A ---> Transformer ----> Point_B

CREATE TABLE POINT_A
(
DLV_DT VARCHAR2(8 BYTE),
FRQ VARCHAR2(3 BYTE),
PCS_DT VARCHAR2(8 BYTE),
PRO_JB_RUN_KEY NUMBER(19),
PRO_STP_RUN_KEY NUMBER(19),
DELETED_FLAG NUMBER(19)

Hope this helps to decode the problem.

ArndW · Post by **ArndW** » Tue Feb 07, 2006 2:07 pm

Can you change your output to a dataset, run the job and then look at the schema generated for the DataSet? This might help a bit, I think it might be your null handling in the output.

ArndW · Post by **ArndW** » Tue Feb 07, 2006 2:35 pm

That is very legible, try doing a UNIX "orchadmin describe {AbsoluteDataSetFileName}, that is easier to cut-and-paste as text here.

kapil008 · Post by **kapil008** » Tue Feb 07, 2006 2:45 pm

ArndW wrote:That is very legible, try doing a UNIX "orchadmin describe {AbsoluteDataSetFileName}, that is easier to cut-and-paste as text here.

what the Unix command? I tried...

$ orchadmin describe ladha.txt

and get the message

ksh: orchadmin: not found.

Vi editor to view the file is also not that helpful...it give a very legible gibrish... any more ideas

Besides...the text data I put in here was just for FYI ( true .. not too legible though) . I am able to view the actual data using the "View Data" tab in the DataSet stage.

So the actual question I am still asking is how do i handle nulls and have data redirected to an Oracle table and not get the following error..

main_program: Fatal Error: Caught ORCHESTRATE exception in main program: APT_ParseError: Parsing parameters "max=0" for schema type "ustring": Max length must be positive, got: "0"
Could not find type: ustring[max=0]

Kapil

ArndW · Post by **ArndW** » Tue Feb 07, 2006 2:54 pm

I'm not at a DataStage connection now, so I can't give you the exact path. It seems that "orchadmin" is not in your UNIX session's path. You will need to add it or execute the program from it's location. It should be in a bin directory underneath your $ORCHHOME directory; but you can always do a "cd /" and then a "find . -name orchadmin -print >2/dev/null" to find the program. The DataSet descriptor is a binary file and can't be viewed using vi (and if you do a ctrl-x-s you will break the file)

kapil008 · Post by **kapil008** » Tue Feb 07, 2006 3:02 pm

ArndW wrote:I'm not at a DataStage connection now, so I can't give you the exact path. It seems that "orchadmin" is not in your UNIX session's path. You will need to add it or execute the program from it's location. It should be in a bin directory underneath your $ORCHHOME directory; but you can always do a "cd /" and then a "find . -name orchadmin -print >2/dev/null" to find the program. The DataSet descriptor is a binary file and can't be viewed using vi (and if you do a ctrl-x-s you will break the file)

Ok... I will try that... however... as I mentioned before I am able to view the actual data using the "View Data" tab in the DataSet stage.

So the question I am still asking is how do i handle nulls and have data redirected to an Oracle table using Oracle Stage and not get the following error..

main_program: Fatal Error: Caught ORCHESTRATE exception in main program: APT_ParseError: Parsing parameters "max=0" for schema type "ustring": Max length must be positive, got: "0"
Could not find type: ustring[max=0]

Kapil

ArndW · Post by **ArndW** » Tue Feb 07, 2006 3:07 pm

Kapil - I give up. We are not trying to hide a solution from you and you don't need to restate your query. If someone could answer your question with the data listed so far they would have. You are getting a schema error. You need to list the schema the way DataStage sees it. Orchadmin will do that. You need to go into the output columns, right-click and edit row to set you attributes. I can't tell you which ones to set until we see the schema.

kapil008 · Post by **kapil008** » Tue Feb 07, 2006 3:24 pm

ArndW wrote:Kapil - I give up. We are not trying to hide a solution from you and you don't need to restate your query. If someone could answer your question with the data listed so far they would have. You are getting a schema error. You need to list the schema the way DataStage sees it. Orchadmin will do that. You need to go into the output columns, right-click and edit row to set you attributes. I can't tell you which ones to set until we see the schema.

Aarand,

Dude..thanks for your help man. You are very prompt. However I am struggling to view the file using Orchadmin as I think the program is not installed or hidden or I dont have access to it.

However I have uploaded the screen shot of the Data that is viewed via datastage.

here is the link to it

http://www.geocities.com/kapil_ladha/datset.bmp

let me know if this helps...

-K

aramachandra · Post by **aramachandra** » Tue Feb 07, 2006 3:32 pm

Kapil

Here are a few more thoughts

I have done queries against oracle all the time where the column could have a null value.

The data stage jobs will work just fine if the meta data matches. In that for example say your query is

select col1 from table1

If col1 can have null make sure the meta data in datastage set to be the field to be NULLABLE .IE Nullable = YES.

If you set it to NO AND YOU HAVE the query return null thanyou have issues.

I am sure purist of datastage world donot like this, but you can always handle the null in your source query itself using the NVL function.

Fundamentally debug your job using the oracle nvl function so that you give a default value for all the columns and eliminiate it one by one to figure which one is causing grief

Anyways can you post the META as you have defined for the column

arvind

aramachandra · Post by **aramachandra** » Tue Feb 07, 2006 3:34 pm

Can you paste the snapshot of the column definition as defined in your oracle stage

ArndW · Post by **ArndW** » Tue Feb 07, 2006 3:35 pm

Kapil,

since the orchadmin program is run by PX jobs it needs to be installed. The screenshot is nice, but doesn't help in locating your problem, please refer to your original post where you showed that DS thinks a ustring length in the schema is 0...
I think that the path to solution of your problem was posted in the previous note. It seems you haven't done either the "find" command or the right-mouse-click to edit row data. I think that you ought to take either a look at the documentation, particularly as it relates to column definitions and schemas, or read the posts. PX is quite picky about data types and default values and when you have nullable columns it make matters even more complicated for you.