1 2 Previous Next 15 Replies Latest reply: Oct 4, 2013 3:04 PM by Frank Kulash RSS

    code question

    natpidgeon

      code

       

       

      
      
        • 1. Re: code question
          Frank Kulash

          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

            oh my bad i meant line 22 and line 18

            • 3. Re: code question
              SomeoneElse

              > oh my bad i meant line 22 and line 18

               

              You mean the SELECT statements?

               

              What's your question?

              • 4. Re: code question
                natpidgeon

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

                • 5. Re: code question
                  Frank Kulash

                  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

                    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

                      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

                        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

                          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

                            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

                              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

                                im trying to force the code to fail

                                so everything that is

                                 

                                • 13. Re: code question
                                  Frank Kulash

                                  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

                                    well here is the code

                                     

                                    1 2 Previous Next