PL/SQL: SQL Statement ignored and PL/SQL: ORA-00911: invalid character

Question

The following pl/sql function is used to fetch default values from a table 'TESTER' and return only one NOT NULL value from all 3 different default data types.

CREATE OR REPLACE TYPE mdvs AS OBJECT
(
l_default_number NUMBER,
l_default_text VARCHAR2(200),
l_default_date DATE
);

CREATE OR REPLACE FUNCTION DEF_VALUE (f_table_name IN VARCHAR2,f_column_name IN VARCHAR2)
RETURN v_mdvs 
IS 
obj_mdvs v_mdvs:=mdvs(0,NULL,NULL);
BEGIN
SELECT Def_NUMBER_Value,Def_TEXT_Value,Def_DATE_Value
INTO obj_mdvs.*,obj_mdvs.*,obj_mdvs.*
FROM    TESTER 
WHERE   Table_Name=f_table_Name
AND     Column_Name=f_column_Name;

IF (l_default_number != 0 ) THEN
obj_mdvs := mdvs(l_default_number);
ELSIF (l_default_text IS NOT NULL) THEN
obj_mdvs := mdvs(l_default_text);
ELSIF (l_default_date IS NOT NULL) THEN
obj_mdvs := mdvs(l_default_date);
ELSE
obj_mdvs :=mdvs('NULL');
END IF;
RETURN obj_mdvs;
END;

o/p: Type MDVS compiled

LINE/COL ERROR 6/1 PL/SQL: SQL Statement ignored 7/5 PL/SQL: ORA-00911: invalid character Errors: check compiler log

not sure how to handle this error.

EDIT:i am facing error in the below code as well . error is mentioned below :

    create or replace function defaulttest
        ( f_table_name  in varchar2
        , f_column_name in varchar2 )
        return mdva
    is
        obj_mdva mdva;
    begin
        begin
            select mdva(defaultnumber), mdva(defaulttext), mdva(defaultdate)
            into   obj_mdva
            from   tester
            where  tablename = f_table_name
            and    columnname = f_column_name;
        exception
            when no_data_found then null;
        end;

        return
            case
                when obj_mdva.default_number is not null then
                    mdva(obj_mdva.default_number)
                when obj_mdva.default_text is not null then
                    mdva(obj_mdva.default_text)
                when obj_mdva.default_date is not null then
                    mdva(obj_mdva.default_date)
                else
                    mdva(null)
            end;
    end defaulttest;

error: PL/SQL: SQL Statement ignored PL/SQL: ORA-00947: not enough values PL/SQL: Statement ignored PLS-00307: too many declarations of 'MDVA' match this call

code : create or replace type mdva FORCE as object ( default_number number , default_text varchar2(200) , default_date date , constructor function mdva (num number) return self as result , constructor function mdva (txt varchar2) return self as result , constructor function mdva (dt date) return self as result ); /

        create or replace type body mdva as
            constructor function mdva (num number) return self as result 
            is
            begin
                self.default_number := num;
                return;
            end;

            constructor function mdva (txt varchar2) return self as result 
                is
                begin
                    self.default_text:= txt;
                    return;
                end;

                constructor function mdva (dt date) return self as result 
                is
                begin
                    self.default_date := dt;
                    return;
                    end;
            end;

            create or replace function defaulttest
                ( f_table_name  in varchar2
                , f_column_name in varchar2 )
                return mdva
            is
                obj_mdva_number mdva;
                obj_mdva_text   mdva;
                obj_mdva_date   mdva;
            begin
                begin
                    select mdva(t.defaultnumber), mdva(t.defaulttext), mdva(t.defaultdate)
                    into   obj_mdva_number, obj_mdva_text, obj_mdva_date
                    from   tester t
                    where  tablename = f_table_name
                    and    columnname = f_column_name;
                exception
                    when no_data_found then null;
                end;

                return coalesce(mdva_number, mdva.text, obj_mdva_date);
                end defaulttest;
                
                select defaulttest('table','field') from dual;

o/p: Defaulttest('table','field') SDPSGT.MDVA(4, NULL, NULL)

SQLQUERY: SELECT CASE WHEN DEFAULTNUMBER IS NOT NULL THEN TO_CHAR(DEFAULTNUMBER) WHEN DEFAULTDATE IS NOT NULL THEN TO_CHAR(DEFAULTDATE, 'YYYY-MM-DD') ELSE DEFAULTTEXT END AS COLUMNVALUE FROM TESTER WHERE tablename= 'table and columnname ='field'

Answer 1

Object definition: (I removed the l_ prefixes because we normally use l_ for local variables, not type attributes):

create or replace type mdvs as object
( default_number    number
, default_text      varchar2(200)
, default_date      date
);

create table tester
( table_name        varchar2(128)
, column_name       varchar2(128)
, def_number_value  number
, def_text_value    varchar2(4000)
, def_date_value    date
, constraint tester_pk primary key (table_name, column_name) );

Test data:

insert all
    into tester(table_name, column_name, def_number_value)
    values ('T1', 'C1', 123)
    into tester(table_name, column_name, def_text_value)
    values ('T1', 'C2', 'Hello')
    into tester(table_name, column_name, def_date_value)
    values ('T1', 'C3', sysdate)
select * from dual;

Function:

create or replace function def_value
    ( f_table_name  in varchar2
    , f_column_name in varchar2 )
    return mdvs
is
    obj_mdvs mdvs;
begin
    begin
        select mdvs(def_number_value, def_text_value, def_date_value)
        into   obj_mdvs
        from   tester
        where  table_name = f_table_name
        and    column_name = f_column_name;
    exception
        when no_data_found then null;
    end;

    return
        case
            when obj_mdvs.default_number is not null then
                mdvs(obj_mdvs.default_number, null, null)
            when obj_mdvs.default_text is not null then
                mdvs(null, obj_mdvs.default_text, null)
            when obj_mdvs.default_date is not null then
                mdvs(null, null, obj_mdvs.default_date)
            else
                mdvs(0, null, null)
        end;
end def_value;

Note that we construct an mdvs object named obj_mdvs in SQL as part of the select into statement. (We could also have fetched the three values into separate variables and then constructed the object in a second step.) After that it will have values in its attributes obj_mdvs.default_number etc.

I wasn't sure what you wanted to use as the default to return when there is no matching row in the table, as mdvs('NULL') is not valid (unless you define a constructor function) because an mdvs object requires three values. You can adjust the else condition to suit your requirement.

PL/SQL syntax has no brackets around if conditions, because they are terminated by the then keyword.

Putting random bits of code in uppercase is quite popular in the industry and even used in the documentation examples, but PL/SQL isn't Cobol and it's not 1974 any more so you don't have to.

Test:

declare
    result mdvs;
begin
    result := def_value('T1', 'C2');

    dbms_output.put_line('result.default_number: '|| result.default_number);
    dbms_output.put_line('result.default_text:   '|| result.default_text);
    dbms_output.put_line('result.default_date:   '|| result.default_date);
end;

result.default_number:
result.default_text:   Hello
result.default_date:

Edit: thinking about it some more, it might be simpler to fetch the values into separate scalar variables, as we aren't really getting much value from the one created in the select into. Refactored version:

create or replace function def_value
    ( f_table_name  in varchar2
    , f_column_name in varchar2 )
    return mdvs
is
    default_mdvs     mdvs := mdvs(0, null, null);
    l_default_number default_mdvs.default_number%type;
    l_default_text   default_mdvs.default_text%type;
    l_default_date   default_mdvs.default_date%type;
begin
    begin
        select def_number_value, def_text_value, def_date_value
        into   l_default_number, l_default_text, l_default_date
        from   tester
        where  table_name = f_table_name
        and    column_name = f_column_name;
    exception
        when no_data_found then null;
    end;

    return
        case
            when l_default_number is not null then
                mdvs(l_default_number, null, null)
            when l_default_text is not null then
                mdvs(null, l_default_text, null)
            when l_default_date is not null then
                mdvs(null, null, l_default_date)
            else
                default_mdvs
        end;
end def_value;

Another Edit: If you want custom constructors so that you can define an object with just mdvs(42) for example, they might look something like this:

create or replace type mdvs as object
( default_number number
, default_text varchar2(200)
, default_date date
, constructor function mdvs (num number) return self as result 
, constructor function mdvs (txt varchar2) return self as result 
, constructor function mdvs (dt date) return self as result 
);
/

create or replace type body mdvs as
    constructor function mdvs (num number) return self as result 
    is
    begin
        self.default_number := num;
        return;
    end;

    constructor function mdvs (txt varchar2) return self as result 
    is
    begin
        self.default_text:= txt;
        return;
    end;

    constructor function mdvs (dt date) return self as result 
    is
    begin
        self.default_date := dt;
        return;
    end;
end;
/

Another version of the function following comments:

I have kept the original name for mdvc and the table columns def_number_value, def_text_value and def_date_value. Feel free to rename them in your environment.

Here is an example of creating three objects in the select into clause. It complicates the select but it simplifies the return clause. This version relies on the object constructors added above.

create or replace function defaulttest
    ( f_table_name  in varchar2
    , f_column_name in varchar2 )
    return mdvs
is
    obj_mdvs_number mdvs;
    obj_mdvs_text   mdvs;
    obj_mdvs_date   mdvs;
begin
    begin
        select mdvs(t.def_number_value), mdvs(t.def_text_value), mdvs(t.def_date_value)
        into   obj_mdvs_number, obj_mdvs_text, obj_mdvs_date
        from   tester t
        where  table_name = f_table_name
        and    column_name = f_column_name;
    exception
        when no_data_found then null;
    end;

    return coalesce(obj_mdvs_number, obj_mdvs_text, obj_mdvs_date);
end defaulttest;

OK, last version I promise. I don't think you need any object type, because you just want to return a plain scalar value (number, text or date). Forget about the object.

create or replace function defaulttest
    ( f_table_name  in varchar2
    , f_column_name in varchar2 )
    return varchar2
is
    l_numval tester.def_number_value%type;
    l_textval tester.def_text_value%type;
    t_dateval tester.def_text_value%type;
begin
    begin
        select t.def_number_value, t.def_text_value, t.def_date_value
        into   l_numval, l_textval, t_dateval
        from   tester t
        where  table_name = f_table_name
        and    column_name = f_column_name;
    exception
        when no_data_found then null;
    end;

    return coalesce(to_char(l_numval), l_textval, to_char(t_dateval));
end defaulttest;

Answer 2

You can't use * in 'into' clause. Just specify variables there:

SELECT Def_NUMBER_Value,Def_TEXT_Value,Def_DATE_Value
INTO obj_mdvs.l_default_number ,obj_mdvs.l_default_text ,obj_mdvs.l_default_date
FROM    TESTER 
WHERE   Table_Name=f_table_Name
AND     Column_Name=f_column_Name;