This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Oct 4, 2013 1:04 PM by Frank Kulash RSS

code question

natpidgeon Newbie
Currently Being Moderated

code

 

 

  • 1. Re: code question
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    natpidgeon wrote:

     

    code

     

     

    1. TYPE SegRec IS RECORD 
    2.     DDValue VARCHAR2(50), 
    3.      DDDesc VARCHAR2(50) 
    4. ); 
    5. TYPE SegTabType IS TABLE OF SegRec; 
    6. vSegData SegTabType; 
    7. vIndex      INTEGER
    8. vRecs       INTEGER
    9. vRetval     VARCHAR2(30000); 
    10. vUnallCount INTEGER
    11. vUnallOpt   VARCHAR(100) := ''
    12. BEGIN 
    13.     vUnallCount := AVAILABLE.GET_UNALLOCATED_COUNT(pvUnitTypeID,pvUsageDate); 
    14.     IF vUnallCount > 0 THEN 
    15.         vUnallOpt := '<option value="---" SELECTED>Unallocated / '||TO_CHAR(vUnallCount)||'</option>'||CHR(10); 
    16.     END IF; 
    17.     SELECT Main.SEGMENT_CODE,Main.DDDesc 
    18.     BULK COLLECT INTO vSegData 
    19.     FROM 
    20.     ( 
    21.         SELECT cc.SEGMENT_CODE,cc.SEGMENT_CODE || ' - ' ||s.SEGMENT_DESC||' / ' || TO_CHAR(ALLOCATION-UNITS_RESERVED) || ' Avail' DDDesc 
    22.         FROM P_PM_CONTROL_COUNT cc, 
    23.              P_PM_SEGMENT s, 
    24.              S_PM_PROP_SEG ps 
    25.         WHERE cc.SEGMENT_CODE = s.SEGMENT_CODE 
    26.         AND s.SEGMENT_CODE = ps.SEGMENT_CODE 
    27.         AND ps.PROPERTY_ID = PvProperty 
    28.         AND s.SEGMENT_ACTIVE = 'Y' 
    29.         AND cc.PM_UNIT_TYPE_ID = pvUnitTypeID 
    30.         AND cc.Usage_Date = pvUsageDate 
    31.         AND cc.ALLOCATION-cc.UNITS_RESERVED > 0 
    32.         AND cc.SEGMENT_CODE IN 
    33.         ( 

    i have DDDec varchar2(50) , but what exactly is it doing in the select in line 26

    DDDec isn't doing anything on line 26.  In fact, I don't see where DDDec is used at all.

     

    Did the forum eliminate blank lines, or otherwise confuse the line numbering?  As posted, line 26 is

     

    WHERE cc.SEGMENT_CODE = s.SEGMENT_CODE

    That's a Join Condition.  It means that the result set will contain combinations of rows from table cc paired with rows from table s, but only rows where segment_code in cc has the same value as segment_code in s.

  • 2. Re: code question
    natpidgeon Newbie
    Currently Being Moderated

    oh my bad i meant line 22 and line 18

  • 3. Re: code question
    SomeoneElse Guru
    Currently Being Moderated

    > oh my bad i meant line 22 and line 18

     

    You mean the SELECT statements?

     

    What's your question?

  • 4. Re: code question
    natpidgeon Newbie
    Currently Being Moderated

    i dont understand what dddesc is doing in line 22, it looks misplace

  • 5. Re: code question
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Did you mean DDDesc, rather than DDDec?

     

    On line 22, DDDesc is being defined as a column alias.  The big string formed by concatenating 6 smaller strings can be refered to as DDDesc when dealing with the results of the sub-query that starts on line 22.

    Line 18 is an example of that.  Line 18 is part of a query that is using the result set of that sub-query as if it were a table, and DDDesc is one of the columns in that "table".

  • 6. Re: code question
    rp0428 Guru
    Currently Being Moderated

    DDDESC is the column alias for the value being constructed

    SELECT cc.SEGMENT_CODE,cc.SEGMENT_CODE || ' - ' ||s.SEGMENT_DESC||' / ' || TO_CHAR(ALLOCATION-UNITS_RESERVED) || ' Avail' DDDesc

    The code is constructing a value from SEGMENT_CODE, SEGMENT_DESC and two other columns and then giving the alias DDDesc to that value so the outer query can refer to it by name.

     

    It is no different than doing this:

    SELECT empno myEmpnoAlias, ename myEnameAlias FROM EMP;

    That 'myEnameAlias' is just a made-up name that is now being used instead of 'ename' So now I can use that 'alias' in a query:

    SELECT myEmpnoAlias, myEnameAlias from (

        SELECT empno myEmpnoAlias, ename myEnameAlias FROM EMP

    )

  • 7. Re: code question
    natpidgeon Newbie
    Currently Being Moderated

    i know this doesnt have to do with this question but , what is the difference between putting desc varchar2(50) and desc varchar2(50 byte)?

  • 8. Re: code question
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

    natpidgeon wrote:

     

    i know this doesnt have to do with this question but , what is the difference between putting desc varchar2(50) and desc varchar2(50 byte)?

    If you have a new, separate question, why not start a new, separate thread?

     

    VARCHAR2 (50 BYTE) means that the column or variable can hold up to 50 bytes.  If you're using a multi-byte character set, that could be fewer than 50 characters

    VARCHAR2 (50 CHAR) means it can hold up to 50 characters.  If you're using a multi-byte character set, that could take more than 50 bytes.

    VARCHAR2 (50) means one or the other of the above, depending on NLS_LENGTH_SEMANTICS.

     

    By the way, DESC is not a good variable name; it's an Oracle keyword, used to specify DESCending order.  Choose variable- and column names that don't have any special meaning in Oracle.

    Check the view V$RESERVED_WORDS.  Anything on a row with RESERVED='N' is a poor name for your own objects; anything with RESERVED='Y' (this includes DESC) is even worse.

  • 9. Re: code question
    natpidgeon Newbie
    Currently Being Moderated

    im trying to make the code fail. so if i have segment_desc(35) byte +segment_code(3  byte)+ dont know what this is for 3 +segment_active(1 byte) +property_id(6 byte) +2 left for allocation-units_reserved for , i will have to max everything in the table , so i can get a specific error. will i have to max the lengths in the tables or will changing dddesc to (45 or something bytes)will it work?

  • 10. Re: code question
    rp0428 Guru
    Currently Being Moderated

    Don't hijack your own thread to ask a different question.

     

    You've already been told to create a new thread and ask your new question there.


  • 11. Re: code question
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Sorry, it's unclear exactly what you want to do.

    If you want to see exactly what happens when you try to put a long string into a variable that's too short, then, as I think you suggested, making the variable so short that it's bound to fail is one way.  Another is to conctenate a string that you know will be to big to the string.  An easy way to generate a string that's N characters long is

     

    LPAD ('X', N, 'X')

    This will return a string of N 'X's.

  • 12. Re: code question
    natpidgeon Newbie
    Currently Being Moderated

    im trying to force the code to fail

    so everything that is

     

  • 13. Re: code question
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

     

    natpidgeon wrote:

     

    im trying to force the code to fail

    so everything that is

    SELECT cc.SEGMENT_CODE,cc.SEGMENT_CODE || ' - ' ||s.SEGMENT_DESC||' / ' || TO_CHAR(ALLOCATION-UNITS_RESERVED) || ' Avail' DDDesc

            FROM P_PM_CONTROL_COUNT cc,

                 P_PM_SEGMENT s,

                 S_PM_PROP_SEG ps

            WHERE cc.SEGMENT_CODE = s.SEGMENT_CODE

            AND s.SEGMENT_CODE = ps.SEGMENT_CODE

            AND ps.PROPERTY_ID = PvProperty

            AND s.SEGMENT_ACTIVE = 'Y'

            AND cc.PM_UNIT_TYPE_ID = pvUnitTypeID

            AND cc.Usage_Date = pvUsageDate

            AND cc.ALLOCATION-cc.UNITS_RESERVED > 0

            AND cc.SEGMENT_CODE IN

    im trying to maximize the bytes which i did in the table p_pm_segment

    for example i max length of segment_desc to (35 byte) and segment_code(3 byte)

    3+3+35+1+6, that leaves 2 characters left for allocation-units_reserved(equals 50 for dddesc)...  which could be more than 2 easily.. then i should get a error,

    the thing is i did max set them all to the max and still no error, which should max it out,

    i have try changing dddesc(50) to dddesc(40) to make it smaller but nothing

    That query will work in SQL, no matter how long any of the strings are.  SQL doesn't care how long the DDDesc column is.

     

    Are you trying to get it to fail in PL/SQL, where the DDDesc column has to be stored in, say, a VARCHAR2 (40) variable?  If so, post your complete PL/SQL code, and some sample data (CREATE TABLE and INSERT statements) for any tables that the code needs.

    Whenever you have any question, post a complete test script that the people who want to help you can run to re-create the problem and test their ideas.

     

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 14. Re: code question
    natpidgeon Newbie
    Currently Being Moderated

    well here is the code

     

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points