Home » SQL & PL/SQL » SQL & PL/SQL » Unable to display contents of CHARARR; (SQL Developer)
Unable to display contents of CHARARR; [message #612607] Tue, 22 April 2014 12:39 Go to next message
plsqlnewbie
Messages: 6
Registered: April 2014
Location: Oklahoma City
Junior Member
Have the following code:
DECLARE
    v_Lines DBMS_OUTPUT.CHARARR;
    v_NumLines NUMBER := 0;
BEGIN
    DBMS_OUTPUT.ENABLE(100000);
    UPDATE jackie.jo_test_table jotest 
        SET su_second_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20032004'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SUMMER'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.su_second_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing second summer in same institution.' );
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET fa_second_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20032004'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'FALL'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.fa_second_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing second fall in same institution.');
    v_NumLines := v_NumLines + 1;   
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET sp_second_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20032004'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SPRING'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.sp_second_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing second spring in same institution.');
    v_NumLines := v_NumLines + 1;      

    UPDATE jackie.jo_test_table jotest 
        SET su_third_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20042005'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SUMMER'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.su_third_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing third summer in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET fa_third_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20042005'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'FALL'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.fa_third_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing third fall in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET sp_third_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20042005'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SPRING'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.sp_third_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing third spring in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;

    UPDATE jackie.jo_test_table jotest 
        SET su_fourth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20052006'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SUMMER'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.su_fourth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing fourth summer in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    
    UPDATE jackie.jo_test_table jotest 
        SET fa_fourth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20052006'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'FALL'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.fa_fourth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing fourth fall in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET sp_fourth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20052006'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SPRING'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.sp_fourth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing fourth spring in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;

    UPDATE jackie.jo_test_table jotest 
        SET su_fifth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20062007'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SUMMER'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.su_fifth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing fifth summer in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    
    UPDATE jackie.jo_test_table jotest 
        SET fa_fifth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20062007'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'FALL'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.fa_fifth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing fifth fall in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET sp_fifth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20062007'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SPRING'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.sp_fifth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing fifth spring in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;

    UPDATE jackie.jo_test_table jotest 
        SET su_sixth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20072008'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SUMMER'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.su_sixth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing sixth summer in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET fa_sixth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20072008'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'FALL'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.fa_sixth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing sixth fall in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET sp_sixth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '200762008'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SPRING'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.sp_sixth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing sixth spring in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;

    UPDATE jackie.jo_test_table jotest 
        SET su_seventh_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20082009'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SUMMER'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.su_seventh_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing seventh summer in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET fa_seventh_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20082009'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'FALL'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.fa_seventh_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing seventh fall in same institution.'); 
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET sp_seventh_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '200862009'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SPRING'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.sp_seventh_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing seventh spring in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;

    UPDATE jackie.jo_test_table jotest 
        SET su_eighth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20092010'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SUMMER'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.su_eighth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing eighth summer in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET fa_eighth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20092010'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'FALL'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.fa_eighth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing eighth fall in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET sp_eighth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20092010'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SPRING'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.sp_eighth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing eighth spring in same institution.'); 
    v_NumLines := v_NumLines + 1;      
    COMMIT;

    UPDATE jackie.jo_test_table jotest 
        SET su_ninth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20102011'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SUMMER'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.su_ninth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing ninth summer in same institution.'); 
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET fa_ninth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20102011'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'FALL'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.fa_ninth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing ninth fall in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET sp_ninth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20102011'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SPRING'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.sp_ninth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing ninth spring in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;

    UPDATE jackie.jo_test_table jotest 
        SET su_tenth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20112012'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SUMMER'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.su_tenth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing tenth summer in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET fa_tenth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20112012'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'FALL'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.fa_tenth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing tenth fall in same institution.');
    v_NumLines := v_NumLines + 1;      
    COMMIT;
    UPDATE jackie.jo_test_table jotest 
        SET sp_tenth_sem_crhrs_same_inst =
        (
            SELECT total_credit_hours
            FROM oeis.student_record stdrec
            WHERE stdrec.academic_year = '20112012'
              AND jotest.institution_code = stdrec.institution_code              
              AND stdrec.semester = 'SPRING'
              AND jotest.social_security_number = stdrec.social_security_number
        )
    WHERE jotest.academic_year = '20022003'
      AND jotest.sp_tenth_same_inst = 1
      AND jotest.incl_flag = 1;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing tenth spring in same institution.');
    v_NumLines := v_NumLines + 1;        
    COMMIT;

    DBMS_OUTPUT.GET_LINES(v_Lines,v_NumLines);
    FOR v_Cntr IN 1..v_NumLines 
    LOOP
        DBMS_OUTPUT.PUT_LINES(v_Lines(v_Cntr));
    END LOOP;

END;
/

This is the error generated:

Error report -
ORA-06550: line 451, column 21:
PLS-00302: component 'PUT_LINES' must be declared
ORA-06550: line 451, column 9:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Trying to display messages from a table showing rows updated for every update statement. Please tell me what I have coded incorrectly.

Thank you!
*BlackSwan added {code} tags.
Please do so yourself in the future.
see URL below
http://www.orafaq.com/forum/t/174502/102589/

[Updated on: Tue, 22 April 2014 12:53] by Moderator

Report message to a moderator

Re: Unable to display contents of CHARARR; [message #612613 is a reply to message #612607] Tue, 22 April 2014 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59182
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Quote:
PLS-00302: component 'PUT_LINES' must be declared


There is no PUT_LINES procedure in the package.

Re: Unable to display contents of CHARARR; [message #612620 is a reply to message #612613] Tue, 22 April 2014 13:48 Go to previous messageGo to next message
plsqlnewbie
Messages: 6
Registered: April 2014
Location: Oklahoma City
Junior Member
Thanks so much! I looked and looked, but never saw the 's' on put_line. However...once that fix was made, I still only get two messages listed after execution. My goal is to get 30. Really do not understand the DBMS_OUTPUT package. Looks straight-forward, but NOT. Could you please take another look at my code?

plsqlnewbie
Re: Unable to display contents of CHARARR; [message #612621 is a reply to message #612620] Tue, 22 April 2014 13:56 Go to previous messageGo to next message
BlackSwan
Messages: 22802
Registered: January 2009
Senior Member
>I still only get two messages listed after execution. My goal is to get 30.
Realize that since we do not have your tables or data, we can not run, test, debug or improve posted code.
I do not understand what you expect the results to actually be.

"v_Lines" only appears 3 times in posted code & does not get populated inside any loop; so why do you expect it to contain 30 values?
Re: Unable to display contents of CHARARR; [message #612622 is a reply to message #612621] Tue, 22 April 2014 14:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
It is populated by executing:

DBMS_OUTPUT.GET_LINES(v_Lines,v_NumLines);

SY.
Re: Unable to display contents of CHARARR; [message #612623 is a reply to message #612621] Tue, 22 April 2014 14:18 Go to previous messageGo to next message
plsqlnewbie
Messages: 6
Registered: April 2014
Location: Oklahoma City
Junior Member
Sorry to be so green. I need to understand how to put messages into dbms_output.chararr and output them via a loop. I want to put one message per semester per year into chararr. The desire is to print out 30 messages (3 semesters X 10 year). I've tried examples on the internet, but still not working correctly. I get two messages. Not the first. Not the last. Hit or miss in between. Like fall third year and summer ninth year.
Re: Unable to display contents of CHARARR; [message #612624 is a reply to message #612607] Tue, 22 April 2014 14:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
And where did you find DBMS_OUTPUT.PUT_LINES? And your code doesn't make much sense. You use DBMS_OUTPUT.PUT_LINE to write text to DBMS_OUTPUT buffer. Then you read it from that buffer and then put it back again. Tell us in words what are you trying to do with DBMS_OUTPUT?

SY.
Re: Unable to display contents of CHARARR; [message #612625 is a reply to message #612624] Tue, 22 April 2014 14:38 Go to previous messageGo to next message
plsqlnewbie
Messages: 6
Registered: April 2014
Location: Oklahoma City
Junior Member
So you are saying, SY, to use DBMS_OUTPUT.GET_LINES within my final FOR LOOP?
Example:

FOR cntr 1..NumLines
LOOP
DBMS_OUTPUT.GET_LINES(lines,NumLines);
END LOOP;
Re: Unable to display contents of CHARARR; [message #612626 is a reply to message #612625] Tue, 22 April 2014 14:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
What I am saying is get rid of

    DBMS_OUTPUT.GET_LINES(v_Lines,v_NumLines);
    FOR v_Cntr IN 1..v_NumLines 
    LOOP
        DBMS_OUTPUT.PUT_LINES(v_Lines(v_Cntr));
    END LOOP;



SY.
Re: Unable to display contents of CHARARR; [message #612627 is a reply to message #612626] Tue, 22 April 2014 15:47 Go to previous messageGo to next message
plsqlnewbie
Messages: 6
Registered: April 2014
Location: Oklahoma City
Junior Member
I removed the code per SY's instruction. Reran. Got following:

2847 rows after processing first summer in same institution
32429 rows after processing first spring in same institution
24130 rows after processing second fall in same institution
6923 rows after processing third summer in same institution
14519 rows after processing third spring in same institution

Missing first fall, second summer and second spring, third fall, etc...
Re: Unable to display contents of CHARARR; [message #612629 is a reply to message #612627] Tue, 22 April 2014 16:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
Post SQL*Plus snippet showing your code execution along with output and errors, if any.

SY.
Re: Unable to display contents of CHARARR; [message #612707 is a reply to message #612629] Wed, 23 April 2014 08:52 Go to previous messageGo to next message
plsqlnewbie
Messages: 6
Registered: April 2014
Location: Oklahoma City
Junior Member
Here is my code snippet:

SET SERVEROUTPUT ON SIZE UNLIMITED

DROP TABLE jo_test_table;

CREATE TABLE jo_test_table
AS SELECT * FROM ops$marion.cca_cohort_2002;

DECLARE
lines dbms_output.chararr;
num_lines int := 0;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);
BEGIN


/* UPDATING jackie.jo_test_table USING oeis.student_record academic year 20022003 */

UPDATE jackie.jo_test_table jotest
SET su_first_sem_crhrs_same_inst =
(
SELECT total_credit_hours
FROM oeis.student_record stdrec
WHERE stdrec.academic_year = '20022003'
AND jotest.institution_code = stdrec.institution_code
AND stdrec.semester = 'SUMMER'
AND jotest.social_security_number = stdrec.social_security_number
)
WHERE jotest.academic_year = '20022003'
AND jotest.su_first_same_inst = 1
AND jotest.incl_flag = 1;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing first summer in same institution');
num_lines := num_lines + 1;
COMMIT;

UPDATE jackie.jo_test_table jotest
SET fa_first_sem_crhrs_same_inst =
(
SELECT total_credit_hours
FROM oeis.student_record stdrec
WHERE stdrec.academic_year = '20022003'
AND jotest.institution_code = stdrec.institution_code
AND stdrec.semester = 'FALL'
AND jotest.social_security_number = stdrec.social_security_number
)
WHERE jotest.academic_year = '20022003'
AND jotest.fa_first_same_inst = 1
AND jotest.incl_flag = 1;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing first fall in same institution');
num_lines := num_lines + 1;
COMMIT;

UPDATE jackie.jo_test_table jotest
SET sp_first_sem_crhrs_same_inst =
(
SELECT total_credit_hours
FROM oeis.student_record stdrec
WHERE stdrec.academic_year = '20022003'
AND jotest.institution_code = stdrec.institution_code
AND stdrec.semester = 'SPRING'
AND jotest.social_security_number = stdrec.social_security_number
)
WHERE jotest.academic_year = '20022003'
AND jotest.sp_first_same_inst = 1
AND jotest.incl_flag = 1;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows after processing first spring in same institution');
num_lines := num_lines + 1;
COMMIT;

END;
END;
/

Here are the results:

table JO_TEST_TABLE dropped.
table JO_TEST_TABLE created.
anonymous block completed
2847 rows after processing first summer in same institution
32429 rows after processing first spring in same institution

Why does the first fall message not get displayed?
Re: Unable to display contents of CHARARR; [message #612708 is a reply to message #612707] Wed, 23 April 2014 09:06 Go to previous message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
This is code snippet. I asked for SQL*Plus snippet showing your code execution along with output and errors, if any.

SY.
Previous Topic: oracle pl/sql procedure
Next Topic: New to sql and need some help please (merged)
Goto Forum:
  


Current Time: Tue Sep 23 05:41:07 CDT 2014

Total time taken to generate the page: 0.09870 seconds