0

I'm trying to make a modification to an existing PL/SQL package I coded some time ago. The original block in question looked like this (which worked fine)

FUNCTION GEN_COLUMN_TYPE ( DTYPE IN VARCHAR2,  --DATATYPE OF THE COLUMN
                            PRCISION IN NUMBER, --PRECISION OF THE COLUMN
                            SCLE IN NUMBER,    --PRECISION OF THE COLUMN
                            DATA_LENGTH IN NUMBER, --DATA LENGTH OF THE COLUMN
                            CHAR_LENGTH IN NUMBER, --CHARACTER LENGTH OF THE COLUMN
                            APP_CODE IN VARCHAR2     -- DATABASE TYPE
                            )RETURN STRING IS

  V_COLUMNTYPE  STRING(40) := NULL;
  BEGIN
             V_COLUMNTYPE := DTYPE;
             BEGIN
              SELECT TARGET_DATATYPE  INTO V_COLUMNTYPE  FROM DDL_DATATYPE_MAP  DDM
              INNER JOIN DDL_SOURCE_APPLICATION DSA ON DDM.TARGET_DBMS=DSA.TARGET_DBMS AND DDM.SOURCE_DBMS=DSA.SOURCE_DBMS
              AND  DSA.APPLICATION_CODE=APP_CODE
              WHERE DDM.SOURCE_DATATYPE=DTYPE;
             END;

I have modified it now to look like this. The only change I made was adding the 2 new variables into the definition and the "SELECT INTO" statement so I could use them based on the same join criteria later in the code.

FUNCTION GEN_COLUMN_TYPE ( DTYPE IN VARCHAR2,  --DATATYPE OF THE COLUMN
                            PRCISION IN NUMBER, --PRECISION OF THE COLUMN
                            SCLE IN NUMBER,    --PRECISION OF THE COLUMN
                            DATA_LENGTH IN NUMBER, --DATA LENGTH OF THE COLUMN
                            CHAR_LENGTH IN NUMBER, --CHARACTER LENGTH OF THE COLUMN
                            APP_CODE IN VARCHAR2,     --APPLICATION CODE
                            DBMS_TYPE VARCHAR2,    --DATABASE TYPE
                            VAR_LENGTH_IND VARCHAR2  --WHETHER THE FIELD NEEDS PARENS OR NOT
                            )RETURN STRING IS

  V_COLUMNTYPE  VARCHAR2(40) := NULL;
  BEGIN
             V_COLUMNTYPE := DTYPE;

              BEGIN
              SELECT TARGET_DATATYPE, SOURCE_DBMS, TARGET_VARIABLE_LEN_IND  INTO V_COLUMNTYPE, DBMS_TYPE, VAR_LENGTH_IND FROM DDL_DATATYPE_MAP  DDM
              INNER JOIN DDL_SOURCE_APPLICATION DSA ON DDM.TARGET_DBMS=DSA.TARGET_DBMS AND DDM.SOURCE_DBMS=DSA.SOURCE_DBMS
              AND  DSA.APPLICATION_CODE=APP_CODE
              WHERE DDM.SOURCE_DATATYPE=DTYPE;
             END;

And I'm getting the all-too-common "PL-00382: expression is of wrong type" on the "SELECT ... INTO" statement at the end. All three variables and columns datatypes are varchar2. I've poured over this and can't see anything wrong. What am I missing?

Edit: Sharing this attempt to use local variables and rewrote the naming for more readability. However I am getting an error on "'V_DBMS_TYPE' must be declared"

FUNCTION GEN_COLUMN_TYPE ( P_DTYPE IN VARCHAR2,  --DATATYPE OF THE COLUMN
                            P_PRCISION IN NUMBER, --PRECISION OF THE COLUMN
                            P_SCLE IN NUMBER,    --PRECISION OF THE COLUMN
                            P_DATA_LENGTH IN NUMBER, --DATA LENGTH OF THE COLUMN
                            P_CHAR_LENGTH IN NUMBER, --CHARACTER LENGTH OF THE COLUMN
                            P_APP_CODE IN VARCHAR2
                            )RETURN STRING IS

  V_COLUMNTYPE  VARCHAR2(40) := NULL;
  BEGIN
             V_COLUMNTYPE := P_DTYPE;
             DECLARE
             V_DBMS_TYPE VARCHAR2(30) := NULL;    --DATABASE TYPE
             V_VAR_LENGTH_IND VARCHAR2(30) := NULL;  --WHETHER THE FIELD NEEDS PARENS OR NOT
              BEGIN
              SELECT TARGET_DATATYPE, SOURCE_DBMS, TARGET_VARIABLE_LEN_IND  INTO V_COLUMNTYPE, V_DBMS_TYPE, V_VAR_LENGTH_IND FROM DDL_DATATYPE_MAP  DDM
              INNER JOIN DDL_SOURCE_APPLICATION DSA ON DDM.TARGET_DBMS=DSA.TARGET_DBMS AND DDM.SOURCE_DBMS=DSA.SOURCE_DBMS
              AND  DSA.APPLICATION_CODE=P_APP_CODE
              WHERE DDM.SOURCE_DATATYPE=P_DTYPE;
Improve this question

1 Answer 1

Reset to default
3

You added two parameters to your function. You don't specify a parameter mode (IN, OUT, or IN OUT) so the default is IN. An IN parameter cannot be the target of a SELECT INTO because the parameter is read-only inside the function.

It is not clear to me whether you really wanted to declare two new local variables, rather than two additional parameters, or whether you really want to declare the two new parameters to be OUT or IN OUT parameters. If you declare the parameters as OUT or IN OUT, they could be the target of an assignment. But your function would no longer be callable from SQL. If you declare two new local variables, the function would still be callable from SQL but you would not be able to pass in (or be passed back) the values that you gather from your SELECT statement.

As a general suggestion, I would much prefer to see a common naming convention used to differentiate parameters from local variables and from column names-- that makes reading the code much easier. If your parameters, for example, used a standard P_ prefix, it would be immediately clear in your SELECT statement that some of your targets were local variables and some were parameters.

Based on the comments, you want to declare additional local variables rather than adding parameters. That would look something like

CREATE OR REPLACE FUNCTION GEN_COLUMN_TYPE ( 
                           DTYPE IN VARCHAR2,  --DATATYPE OF THE COLUMN
                           PRCISION IN NUMBER, --PRECISION OF THE COLUMN
                           SCLE IN NUMBER,    --PRECISION OF THE COLUMN
                           DATA_LENGTH IN NUMBER, --DATA LENGTH OF THE COLUMN
                           CHAR_LENGTH IN NUMBER, --CHARACTER LENGTH OF THE COLUMN
                           APP_CODE IN VARCHAR2     --APPLICATION CODE
                         )
  RETURN VARCHAR2
IS
  V_DBMS_TYPE      VARCHAR2(100);    --DATABASE TYPE
  V_VAR_LENGTH_IND VARCHAR2(100);  --WHETHER THE FIELD NEEDS PARENS OR NOT
  V_COLUMNTYPE     VARCHAR2(40) := NULL;
BEGIN
  V_COLUMNTYPE := DTYPE;

  SELECT TARGET_DATATYPE, SOURCE_DBMS, TARGET_VARIABLE_LEN_IND  
    INTO V_COLUMNTYPE, V_DBMS_TYPE, V_VAR_LENGTH_IND 
    FROM DDL_DATATYPE_MAP  DDM
         INNER JOIN DDL_SOURCE_APPLICATION DSA ON DDM.TARGET_DBMS=DSA.TARGET_DBMS AND  
                                                  DDM.SOURCE_DBMS=DSA.SOURCE_DBMS AND
                                                  DSA.APPLICATION_CODE=APP_CODE
   WHERE DDM.SOURCE_DATATYPE=DTYPE;

  <<more code>>
END;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for the response Justin. You're 100% correct what I really wanted was local variables not additional parameters, however when I tried to use local variables I was having some trouble with the variables "Must be declared" error. I had assumed this was due to where the BEGINs were nested but no matter where I moved the declarations it didn't seem to make the error go away. I will post again in a moment with a re-write attempting this again just to make sure I wasn't doing something obviously wrong.
@MRand - Guessing at the lengths of the local variables that you'd need to declare, I updated my answer with an example of what you might be looking for. I'd still want to add some sort of prefix to the parameters, I'd want to use the aliases in the SELECT list, I'd want the three local variables, at least, to be anchored definitions (i.e. v_dbms_type ddl_source_application.source_dbms%type), and I'd strongly reconsider typing everything in upper case. But hopefully this points you in the right direction.
Thanks for the help here Justin. Updated again following your example, However now I'm getting the "expression of wrong type" on a different chunk of code slightly further down: ELSIF (INSTR(P_DTYPE,'VARCHAR') AND P_CHAR_LENGTH = -1) THEN I had originally guessed that Number was having an issue with -1, but I tried that out in an anonymous block and that compiled fine. and I can't see what's wrong with the instr search...
Nevermind, I fixed it by doing a straight equivalency instead of a string search as I didn't need it in this case. Still was odd. Thanks much for all the help here, and also the tips on naming conventions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.