Home » SQL & PL/SQL » Client Tools » print text
print text [message #605578] Fri, 10 January 2014 15:58 Go to next message
anncao
Messages: 82
Registered: August 2013
Member
I am using oracle sql developer.
I have a script that needs to run in Oracle SQL developer. They are multiple update commands.

I would like print out a line of text before each update so that they show in the result window:

for example

my script is:

print 'high school'

update students
set ....
where schooltype ='high school'

print 'middle school'

update students
set ...
where schooltype ='middle school'

apparently the print will not work, so what is the equivalent in oracle. I am using it in oracle sql developer.

Also if I needs roll back the above update, whatever the print command is , will it affect rollback or commit?

Thanks
Re: print text [message #605579 is a reply to message #605578] Fri, 10 January 2014 17:42 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
If the purpose is real time tracing of the updates then the proper way, IMHO, is not printing information on the output screen, because they are often cached in the buffer until the end of the current transaction (at least this is how it works with dbms_output.put_line).

You can define a log table (the following is just a basic example, obviously you define/add columns based on your needs)
drop table logtab;
create table logtab
(
    username    varchar2(30)    ,
    log_msg     varchar2(300)   ,
    stacktrace  varchar2(2000)  ,
    logtime     timestamp
);
--
--
create or replace procedure writelog
(
    p_logmsg        in  varchar2,
    p_stacktrace    in  varchar2
)
authid definer
is
    pragma autonomous_transaction;
begin
    <<bk>>
    declare
        tstamp  logtab.logtime%type;
    begin
        bk.tstamp := systimestamp;
        --
        insert into 
            logtab
            (
                username    , 
                log_msg     , 
                stacktrace  , 
                logtime
            ) 
            values 
            (
                user        , 
                p_logmsg    , 
                p_stacktrace, 
                bk.tstamp
            );
        commit;
    end;
end writelog;
/
show errors;

And the updates are done while you update also your log table
begin
    writelog(. . .);
    update students
    set . . . 
    where schooltype ='high school';

    writelog(. . .);
    update students
    set . . .
    where schooltype ='middle school';
exception:
    writelog(. . .);
    . . .
end;
/
Re: print text [message #605580 is a reply to message #605579] Fri, 10 January 2014 18:08 Go to previous messageGo to next message
BlackSwan
Messages: 23159
Registered: January 2009
Senior Member
other sessions can NOT see uncommitted data!
if COMMIT is issued to "see" data in log table then ROLLBACK can NOT occur!
(Please do not suggest that AUTONOMOUS transaction be used.)
Re: print text [message #605581 is a reply to message #605580] Fri, 10 January 2014 19:34 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
I'm not sure to understand what do you mean

"BlackSwan Sat, 11 January 2014 01:08"

if COMMIT is issued to "see" data in log table then ROLLBACK can NOT occur!


Autonomous transaction is independent from the update transaction so I don't see why the ROLLBACK according to you cannot occur
create table tmptab(numval number);
--
--
insert into tmptab(numval) 
    (select rownum from all_objects where rownum <= 10);
commit;
--
select t1.numval from tmptab t1;
--
begin
    writelog(p_logmsg=>'update 4 to 40', p_stacktrace=>null);
    update tmptab t1
    set t1.numval = 40
    where t1.numval = 4;
--     
    writelog(p_logmsg=>'update 9 to 90', p_stacktrace=>null);
    update tmptab t1
    set t1.numval = 90
    where t1.numval = 9;
end;
/
--
rollback;
--
select t1.numval from tmptab t1;

And the output
Table created.


10 rows created.


Commit complete.


    NUMVAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.


PL/SQL procedure successfully completed.


Rollback complete.


    NUMVAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.


So the rollback is done and the tmptab has the same data as before update statements.

So I think either I misunderstood totally what the OP is looking or I don't understand what you're telling me.

What I understand from the OP is that he wants to run several UPDATE statements but before starting each of them he want to use some message as a tracing tool and for the reasons I explained in my previous comment I believe he might not obtain what he expects.

Now you may want to tell me what I misunderstand?
Re: print text [message #605582 is a reply to message #605578] Sat, 11 January 2014 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
apparently the print will not work, so what is the equivalent in oracle.


Try "prompt".

Re: print text [message #605584 is a reply to message #605578] Sat, 11 January 2014 03:16 Go to previous messageGo to next message
John Watson
Messages: 4865
Registered: January 2010
Location: Global Village
Senior Member
Possibly your fundamental problem is that you are doing this in SQL Developer, which is designed to be an interactive tool. Yes, you can run scripts - but that really isn't what it is meant for. Is it not possible for you to do this operation in SQL*Plus? That is a tool with facilities designed for running scripts.
Re: print text [message #605606 is a reply to message #605578] Sat, 11 January 2014 23:13 Go to previous messageGo to next message
anncao
Messages: 82
Registered: August 2013
Member
Thank you all for all the suggestions, I ended up to use prompt text to see that in the result window for example: I see in the result window:

High school :

2300 updated

Middle school:

3009 updated

Elementary:
5800 updated


I see Michel 's post is that too, thanks.
But other answers help too, at first I thought I just need to manually run it a couple of times in sql developer, but now we decide to schedule it to run, so i think I will definitely need to use sql plus to run the script and schedule it. I am new to Oracle, I attached my script in the attachment , it works in SQL developer, do I need to add any other special command in the script in order for sql plus to call it? I do add another two lines at the bottom, one / and exit then another /
Since I don't have any variable to declare, so I do not need a begin ...end to wrap the code in a block, correct?
Also I see some scripts they include following code at beginning of the script:
whenever sqlerror exit sql.sqlcode
set heading off
set feedback off
set newpage none
set echo off
set termout off
set verify off
set trimspool on
set pause off
rem ***************** linesize (logical record) MUST be big enough
set linesize 1000
set pagesize 0

Do I need to include this in my script too?

Also thanks dariyoosh about the tip to write a log file to trace updates. I will definitely takes the notes and learn from it using in my future script, for this one, it is a simple update I will ignore log for now.

Thanks again.

[Updated on: Sat, 11 January 2014 23:18]

Report message to a moderator

Re: print text [message #605616 is a reply to message #605606] Sun, 12 January 2014 04:04 Go to previous message
Littlefoot
Messages: 19893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Do I need to include this in my script too?

Spend some time and learn what these SET commands represent. Then you'd know whether you need them or not. Also, you might find out that you need some of them (listed in your message), and - possibly - add some other you'll find while reading the documentation.

As of scheduling it as a job: unless you create some kind of a "log file" (doesn't really matter how - whether a true filesystem file or set of table records), you won't see much of the execution. Job starts, does its job, ends, closes the CMD window. Or, will you leave the window open? How often do you plan to check what's being done? What actions will you perform, then? From my point of view, for scheduling purposes, Dariyoosh's suggestion seems to be the most appropriate because you can easily query the log table, analyze it and act if necessary.
Previous Topic: Script not available in Session Browser
Next Topic: Select query returns different characters on different desktops, same pl/sql developer versions
Goto Forum:
  


Current Time: Mon Dec 22 07:13:59 CST 2014

Total time taken to generate the page: 0.21067 seconds