Simple question..I am running a stored procedure from SQL PLUS 
Author Message
 Simple question..I am running a stored procedure from SQL PLUS
so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
have also set serverout on ..but it doesn't print the messages what am
i missing?


Sun, 25 Jan 2004 07:47:25 GMT
 Simple question..I am running a stored procedure from SQL PLUS

PRINTER?

Quote:

> so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
> have also set serverout on ..but it doesn't print the messages what am
> i missing?

--
Posted via CNET Help.com
http://www.help.com/


Sun, 25 Jan 2004 19:30:11 GMT
 Simple question..I am running a stored procedure from SQL PLUS
I hope you mean you did:

   set serveroutput on

right?

Quote:

> so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
> have also set serverout on ..but it doesn't print the messages what am
> i missing?



Sun, 25 Jan 2004 20:50:52 GMT
 Simple question..I am running a stored procedure from SQL PLUS
HI

in your sqlplus session type

SQL> set serveroutput on size 1000000
SQL> exec my_sp....

then you will see output.

cheers

Pete Finnigan
Pentest Limited



Quote:
>PRINTER?


>> so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
>> have also set serverout on ..but it doesn't print the messages what am
>> i missing?

>--
>Posted via CNET Help.com
>http://www.help.com/

--
Pete Finnigan


Sun, 25 Jan 2004 20:49:56 GMT
 Simple question..I am running a stored procedure from SQL PLUS

Quote:
>so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
>have also set serverout on ..but it doesn't print the messages what am
>i missing?

I assume by printing you mean display on screen?  If so, make sure
that you use dms_output.put_line and that the set serveroutput is done
immediately before the exec.  With 8.1.6 in NT, it turns itself off
with monotonous regularity...

Cheers
Nuno Souto



Sun, 25 Jan 2004 21:08:39 GMT
 Simple question..I am running a stored procedure from SQL PLUS
Hi

No i meant what i typed, its worth setting the default buffer size to
1000000, the maximum, otherwise it remains at the default size of 20000
bytes.

You can do either syntax, but if your output exceeeds the minimum it
will just stop.

cheers

Pete
Pentest Limited



Quote:
>I hope you mean you did:

>   set serveroutput on

>right?



>> so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
>> have also set serverout on ..but it doesn't print the messages what am
>> i missing?

--
Pete Finnigan


Sun, 25 Jan 2004 21:15:47 GMT
 Simple question..I am running a stored procedure from SQL PLUS
This will happen if whatever you are printing has a value of null.  

Gene Hubert

Quote:

> so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
> have also set serverout on ..but it doesn't print the messages what am
> i missing?



Sun, 25 Jan 2004 23:05:39 GMT
 Simple question..I am running a stored procedure from SQL PLUS
YES GUYS I MEANT I USED SET SERVEROUTPUT ON and also SET SERVEROUTPUT
ON SIZE 1000000. BUT IT STILL DOESN'T WORK....I TRIED TO SET ALL OTHER
PARAMETERS IN SET TO ON BUT NO LUCK....YESTERDAY IT WORKED JUST ONCE
WHEN I DID A SET SERVEROUTPUT ON . Like Nuno mentions it looks like
serveroutput is turned off arbitrarily...I am using NT 4.0 ....

code is here

create or replace procedure GPMS_Interface
as

/****
* Process 5
*****/
lNewGPMSTitle VARCHAR2(255);
lOldTitleRelease VARCHAR2(255);

/***
* PROCESS 6
****/
lGPMSTitle VARCHAR2(255);
lNewGPMSNo VARCHAR2(6);
lOldGPMSNo VARCHAR2(6);

/************
* PROCESS 8
*********/
lNewGPMSTitleCT VARCHAR2(255);
lOldTitleCT VARCHAR2(255);

/***
* PROCESS 9
****/
lGPMSTitleCT VARCHAR2(255);
lNewGPMSNoCT VARCHAR2(6);
lOldGPMSNoCT VARCHAR2(6);

CURSOR NEW_OLD_TITLE_cursor
IS select distinct b.GPMS_TITLE, a.TITLE from title_release a,
gpms_title b where b.GPMS_TITLE <> a.TITLE
                and a.GPMS_NO = b.GPMS_NO;

CURSOR NEW_OLD_GPMSNo_cursor
IS select distinct b.GPMS_TITLE, b.GPMS_NO, a.GPMS_NO from
title_release a, gpms_title b where b.GPMS_NO <> a.GPMS_NO
                and a.TITLE = b.GPMS_TITLE;

CURSOR NEW_OLD_TITLE_CT_cursor
IS select distinct b.GPMS_TITLE, a.CONSTITUENT_TITLE from
constituent_titles a, gpms_title b where b.GPMS_TITLE <>
a.CONSTITUENT_TITLE
                and a.GPMS_NO = b.GPMS_NO;

CURSOR NEW_OLD_GPMSNo_CT_cursor
IS select distinct b.GPMS_TITLE, b.GPMS_NO, a.GPMS_NO from
constituent_titles a, gpms_title b where b.GPMS_NO <> a.GPMS_NO
                and a.CONSTITUENT_TITLE = b.GPMS_TITLE;

BEGIN
  begin
        dbms_output.put_line('start process 5' );
  open NEW_OLD_TITLE_cursor;
  EXCEPTION
  WHEN OTHERS THEN
            dbms_output.put_line('NEW_OLD_TITLE cursor in exception
sto_selcnt set to 1' );
  END;

  dbms_output.put_line('after opeing cursor and exception');
  fetch NEW_OLD_TITLE_cursor INTO GPMS_Interface.lNewGPMSTitle,
GPMS_Interface.lOldTitleRelease ;

  IF NEW_OLD_TITLE_cursor%NOTFOUND THEN
        dbms_output.put_line('NEW_OLD_TITLE cursor NOT FOUND ');
  END IF;

  WHILE  (NEW_OLD_TITLE_cursor%FOUND) LOOP      
  BEGIN
         BEGIN
           insert into title_changes(DATE_OF_CHANGE,TITLE,OLD_VALUE,TYPE_OF_CHANGE)
                          values(sysdate,GPMS_Interface.lNewGPMSTitle,GPMS_Interface.lOldTitleRelease,
                                                        'Title Changed');                                                                                                                                                                                                                                                                                          
        END;
  END;                                                                                                                                                                                                                                                                                                                                                  
  END LOOP;
  commit;
  close NEW_OLD_TITLE_cursor;

  begin
  update        title_release a set a.title = ( select distinct b.gpms_title
from gpms_title b where b.GPMS_TITLE <> a.TITLE
                and a.GPMS_NO = b.GPMS_NO ) where a.GPMS_NO  in (select b.GPMS_NO
from gpms_title b where a.GPMS_NO = b.GPMS_NO and
                                                                                                                b.GPMS_TITLE <> a.TITLE);
  end;
  commit;
 END GPMS_Interface;

Quote:

> Hi

> No i meant what i typed, its worth setting the default buffer size to
> 1000000, the maximum, otherwise it remains at the default size of 20000
> bytes.

> You can do either syntax, but if your output exceeeds the minimum it
> will just stop.

> cheers

> Pete
> Pentest Limited



> >I hope you mean you did:

> >   set serveroutput on

> >right?



> >> so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
> >> have also set serverout on ..but it doesn't print the messages what am
> >> i missing?



Mon, 26 Jan 2004 01:33:06 GMT
 Simple question..I am running a stored procedure from SQL PLUS
for what it is worth, I usually put 'dmbs_output.enable(1000000);' as
one of the first line in my procedures as well as 'set serveroutput
on'.
Quote:

> Hi

> No i meant what i typed, its worth setting the default buffer size to
> 1000000, the maximum, otherwise it remains at the default size of 20000
> bytes.

> You can do either syntax, but if your output exceeeds the minimum it
> will just stop.

> cheers

> Pete
> Pentest Limited



> >I hope you mean you did:

> >   set serveroutput on

> >right?



> >> so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
> >> have also set serverout on ..but it doesn't print the messages what am
> >> i missing?



Mon, 26 Jan 2004 03:41:52 GMT
 Simple question..I am running a stored procedure from SQL PLUS
Hi

I cannot see anything obviously wrong with this... you could try
dbms_output.enable(1000000); as Joe suggests.

If dbms_output is tempramental on NT try changing to use UTL_FILE
instead. you need to do a bit extra than when using dbms_output, but not
much

create or replace procedure....
...
        fprt utl_file.file_type;
..
begin
....
        fptr:=utl_file.fopen('directory','filename','A');

....

-- output  like dbms_output
        utl_file.put_line(fptr,'string as per dbms output');
....
        utl_File.fclose(fptr);
....
exception

end;

you also need to add the line

utl_file_dir="directory name" to your init.ora and bounce the database

cheers

Pete Finnigan
Pentest Limited



Quote:
>YES GUYS I MEANT I USED SET SERVEROUTPUT ON and also SET SERVEROUTPUT
>ON SIZE 1000000. BUT IT STILL DOESN'T WORK....I TRIED TO SET ALL OTHER
>PARAMETERS IN SET TO ON BUT NO LUCK....YESTERDAY IT WORKED JUST ONCE
>WHEN I DID A SET SERVEROUTPUT ON . Like Nuno mentions it looks like
>serveroutput is turned off arbitrarily...I am using NT 4.0 ....

>code is here

>create or replace procedure GPMS_Interface
>as

>/****
>* Process 5
>*****/
>lNewGPMSTitle VARCHAR2(255);
>lOldTitleRelease VARCHAR2(255);

>/***
>* PROCESS 6
>****/
>lGPMSTitle VARCHAR2(255);
>lNewGPMSNo VARCHAR2(6);
>lOldGPMSNo VARCHAR2(6);

>/************
>* PROCESS 8
>*********/
>lNewGPMSTitleCT VARCHAR2(255);
>lOldTitleCT VARCHAR2(255);

>/***
>* PROCESS 9
>****/
>lGPMSTitleCT VARCHAR2(255);
>lNewGPMSNoCT VARCHAR2(6);
>lOldGPMSNoCT VARCHAR2(6);

>CURSOR NEW_OLD_TITLE_cursor
>IS select distinct b.GPMS_TITLE, a.TITLE from title_release a,
>gpms_title b where b.GPMS_TITLE <> a.TITLE
>               and a.GPMS_NO = b.GPMS_NO;

>CURSOR NEW_OLD_GPMSNo_cursor
>IS select distinct b.GPMS_TITLE, b.GPMS_NO, a.GPMS_NO from
>title_release a, gpms_title b where b.GPMS_NO <> a.GPMS_NO
>               and a.TITLE = b.GPMS_TITLE;

>CURSOR NEW_OLD_TITLE_CT_cursor
>IS select distinct b.GPMS_TITLE, a.CONSTITUENT_TITLE from
>constituent_titles a, gpms_title b where b.GPMS_TITLE <>
>a.CONSTITUENT_TITLE
>               and a.GPMS_NO = b.GPMS_NO;

>CURSOR NEW_OLD_GPMSNo_CT_cursor
>IS select distinct b.GPMS_TITLE, b.GPMS_NO, a.GPMS_NO from
>constituent_titles a, gpms_title b where b.GPMS_NO <> a.GPMS_NO
>               and a.CONSTITUENT_TITLE = b.GPMS_TITLE;

>BEGIN
>  begin
>       dbms_output.put_line('start process 5' );
>  open NEW_OLD_TITLE_cursor;
>  EXCEPTION
>  WHEN OTHERS THEN
>           dbms_output.put_line('NEW_OLD_TITLE cursor in exception
>sto_selcnt set to 1' );
>  END;

>  dbms_output.put_line('after opeing cursor and exception');
>  fetch NEW_OLD_TITLE_cursor INTO GPMS_Interface.lNewGPMSTitle,
>GPMS_Interface.lOldTitleRelease ;

>  IF NEW_OLD_TITLE_cursor%NOTFOUND THEN
>       dbms_output.put_line('NEW_OLD_TITLE cursor NOT FOUND ');
>  END IF;

>  WHILE  (NEW_OLD_TITLE_cursor%FOUND) LOOP    
>  BEGIN
>        BEGIN
>          insert into title_changes(DATE_OF_CHANGE,TITLE,OLD_VALUE,TYPE_OF_CHAN
>GE)
>                         values(sysdate,GPMS_Interface.lNewGPMSTitle,GPMS_Inter
>face.lOldTitleRelease,
>                                                       'Title Changed');                                          
>       END;
>  END;                                                                                            
>  END LOOP;
>  commit;
>  close NEW_OLD_TITLE_cursor;

>  begin
>  update       title_release a set a.title = ( select distinct b.gpms_title
>from gpms_title b where b.GPMS_TITLE <> a.TITLE
>               and a.GPMS_NO = b.GPMS_NO ) where a.GPMS_NO  in (select
>b.GPMS_NO
>from gpms_title b where a.GPMS_NO = b.GPMS_NO and

>b.GPMS_TITLE <> a.TITLE);
>  end;
>  commit;
> END GPMS_Interface;


>> Hi

>> No i meant what i typed, its worth setting the default buffer size to
>> 1000000, the maximum, otherwise it remains at the default size of 20000
>> bytes.

>> You can do either syntax, but if your output exceeeds the minimum it
>> will just stop.

>> cheers

>> Pete
>> Pentest Limited



>> >I hope you mean you did:

>> >   set serveroutput on

>> >right?




>> >> so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
>> >> have also set serverout on ..but it doesn't print the messages what am
>> >> i missing?

--
Pete Finnigan


Tue, 27 Jan 2004 16:56:29 GMT
 Simple question..I am running a stored procedure from SQL PLUS

Quote:
> YES GUYS I MEANT I USED SET SERVEROUTPUT ON and also SET
> SERVEROUTPUT ON SIZE 1000000. BUT IT STILL DOESN'T WORK....I
> TRIED TO SET ALL OTHER PARAMETERS IN SET TO ON BUT NO
> LUCK....YESTERDAY IT WORKED JUST ONCE WHEN I DID A SET
> SERVEROUTPUT ON . Like Nuno mentions it looks like serveroutput

WHY ARE YOU SHOUTING?
--
Galen Boyer
It seems to me, I remember every single thing I know.


Sat, 31 Jan 2004 11:46:12 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. Simple question..I am running a stored procedure from SQL PLUS

2. SQL*Plus question (Where am I)

3. question RE. running two SQL statements in one Stored Procedure

4. Many Simple Stored Procedures VS. Few Complex Stored Procedures

5. Embarassingly simple SQL question - what am I missing?!

6. A simple sql*plus question

7. simple question about sql*plus

8. stored procedure question, plus recommendation on books

9. can call stored procedure from sql worksheet but not sql plus

10. Problem executing a SQL Server 2000 Stored Procedure from SQL*PLUS

11. How to test run a procedure at SQL plus mode

12. Oracle running Stored Procs through SQL Plus


 
Powered by phpBB® Forum Software