- TYPE SegRec IS RECORD
- DDValue VARCHAR2(50),
- DDDesc VARCHAR2(50)
- TYPE SegTabType IS TABLE OF SegRec;
- vSegData SegTabType;
- vIndex INTEGER;
- vRecs INTEGER;
- vRetval VARCHAR2(30000);
- vUnallCount INTEGER;
- vUnallOpt VARCHAR(100) := '';
- vUnallCount := AVAILABLE.GET_UNALLOCATED_COUNT(pvUnitTypeID,pvUsageDate);
- IF vUnallCount > 0 THEN
- vUnallOpt := '<option value="---" SELECTED>Unallocated / '||TO_CHAR(vUnallCount)||'</option>'||CHR(10);
- END IF;
- SELECT Main.SEGMENT_CODE,Main.DDDesc
- BULK COLLECT INTO vSegData
- 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
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.
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".
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
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.
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?
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.
im trying to force the code to fail
so everything that is
FROM P_PM_CONTROL_COUNT cc,
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