CREATE OR REPLACE FUNCTION GET_FUNCTION(ENG_DIS IN VARCHAR2)
RETURN VARCHAR2 IS
col VARCHAR2(50);
BEGIN
SELECT XYZ_DESCRIPTION INTO col
FROM TBL_NAME
WHERE ROWNUM < 2;
RETURN col;
END GET_FUNCTION;
/
show errors;
I understand that a FUNCTION returns just "a value".
If I execute the function in toad for testing it returns XYZ_DESCRIPTION and about 6000 rows (same as the no of rows in table "TBL_NAME").
Not really a play function. I will add more functionalities to it.
I plan to use Substr(Instr(XYZ_DESCRIPTION).
If I add a where clause
WHERE XYZ_DESCRIPTION = ' aaaa bbbb cccc'
I get this
ORA-12801: error signaled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "STAGE.GET_ENGINE_DISPLACEMENT", line 5
ORA-06512: at "SYS.ORACLE_LOADER", line 52
Where did you add that 'where'? You'd need to talk to your DBA about the nature of those errors.
I only ask if it was real as it doesn't really make any sense as a function to me right now. A function is some sort of derived field that you select like any other field from any table - yours will always return 1 particular value from 1 particular table, hence my confusion.
You also seem to be duplicating functionality already inside DataStage.
-craig
"You can never have too many knives" -- Logan Nine Fingers
I m trying to incorporate a PL/SQL user-defined function in Datastage as I have been asked to have C-routine as the last option for a task (that I tried with a tfr one-liner; reasons best known to the management).
Anyways I resolved the issue with the test function I posted.
CREATE OR REPLACE FUNCTION GET_FUNCTION(ENG_DIS IN VARCHAR2)
RETURN VARCHAR2 IS
col VARCHAR2(50);
BEGIN
SELECT SUBSTR(XYZ_DESCRIPTION, INSTR(XYZ_DESCRIPTION, ' L ', -7), 9) INTO col
FROM TBL_NAME
WHERE ROWNUM < 2;
RETURN col;
END GET_FUNCTION;
/
show errors;
I was passing the argument within 'quotes' while calling the function.
Ray's advise to run on dual was for testing purposes. You will reference the table which has the column 'XYZ_DESCRIPTION'. Use this function inside the database stage. Use generated sql. Put the function in the derivation of 'XYZ_DESCRIPTION' in the column tab. Examine the sql to be like how you want it to be.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
1) vick, I don't see you using the input parameter to the function, ENG_DIS, anywhere. Is that what you want?
2)You get the ORA-01422 error because your where clause:
WHERE XYZ_DESCRIPTION = ' aaaa bbbb cccc'
is returning more than 1 single value rows and with a SELECT INTO, it should return one 1 value.
3)
SELECT GET_FUNCTION(XYZ_DESCRIPTION)
FROM DUAL
WHERE ROWNUM <2
You get an error in this one because Dual doesn't know the value of XYZ_DESCRIPTION. You have to either declare it as a variable and initialize its value or put it in single quotes when passing to the function.
