1 2 Previous Next 15 Replies Latest reply on Oct 4, 2013 8:04 PM by Frank Kulash Go to original post
      • 15. Re: code question
        Frank Kulash

        Hi,

         

        It looks like you didn't read my last message, where I said you need to post a complete script that people can run to re-create the problem and test their ideas.

        If I run what you posted, I get dozens of errors, starting with:

         

        SP2-0734: unknown command beginning "FUNCTION R..." - rest of line ignored.

        SP2-0734: unknown command beginning "PvProperty..." - rest of line ignored.

        SP2-0734: unknown command beginning "pvUnitType..." - rest of line ignored.

        That's because no SQL or SQL*Plus statements start with FUNCTION, or PvProperty, or pvUnitType..  So it looks like your problem is that you forgot the keywords CREATE OR REPLACE before FUNCTION.

        Is that really what you're asking?  I'll bet it's not.  Post a complete test script that people can run to re-create the problem and test their ideas.  If your code refers to any tables, post CREATE TABLE and INSERT statements for those tables (unless you can show what the problem is using commonly available tables, like thse in the scott schema).

        Simplify the problem.  Post only to show the part that you're having trouble with.

         

        In your earlier messages, it sounded like you were trying to see what would happen if you tried to read a long string into a short variable.  Here's a complete scipt that you could post for that problem:

         

        CREATE OR REPLACE FUNCTION  fun_x
        RETURN varchar2
        IS
            TYPE SegRec IS RECORD
            (
                DDValue VARCHAR2(50),
                 DDDesc VARCHAR2(50)  -- either   VARCHAR2 (50)   or   VARCHAR2 (5)
            );

            TYPE SegTabType IS TABLE OF SegRec;

            vSegData SegTabType;
        BEGIN
            SELECT  dname
            ,       dname || ' - ' || loc     -- or   LPAD (loc, 50, 'X')
            BULK COLLECT INTO  vSegData
            FROM    scott.dept;

            RETURN 'OK';
        END;
        /
        SHOW ERRORS

         

        --  Sample execution
        SELECT  fun_x
        FROM dual
        ;

        If I run this, I get the following output:

         

        Function created.

        No errors.

        FUN_X
        ----------
        OK

        If I wanted to deliberately raise an error, I could reduce the size of the variable, by changing the first commented line to:

         

                 DDDesc VARCHAR2(5) -- either   VARCHAR2 (50)   or   VARCHAR2 (5)

        or leave that line alone and increase the size of the data, by changing the other commented line to

         

            ,     dname || ' - ' || LPAD (loc, 50, 'X')

        Either way, the function compiles, but it gives run-time errors if I try to use it:

         

        Function created.

        No errors.
        SELECT  fun_x
                *
        ERROR at line 1:
        ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
        ORA-06512: at "FUBAR.FUN_X", line 14

        1 2 Previous Next