Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Question Unanswered: SQL command within PL*SQL block

    1) Is there a way to call a SQL command from PL*SQL block ?

    2) I have defined LINESIZE as 80 using the SET command. I created a file using the SPOOL command. From the PL*SQL block I am doing a DBMS_OUTPUT.put_line (20 char value) to write it to the spool file. But the file contains the 20 char + 60 blanks.

    Is there a way to print only 20 char without changing the LINESIZE ?
    Regards,

    Ajay Patel

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL command within PL*SQL block

    1) You mean a SQL Plus command? No.

    2) SET TRIMSPOOL ON

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    1)
    Begin
    EXECUTE IMMEDIATE 'SQL Statement';
    End;

    2)
    SET WRAP ON // avoid string truncation
    COLUMN name FORMAT Ax, where x = # of characters to display per line.

    Example:

    COLUMN C_COLUMN A20;

    Select c.column C_COLUMN from c;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Feb 2004
    Posts
    11

    Talking Re: SQL command within PL*SQL block

    The SET TRIMSPOOL ON worked.

    But if I cannot execute SQL Plus command from PL*SQL, is there a way to achieve the below:

    Some of the DBMS_OUTPUT.put_line result in the PL*SQL block must be written to the output file and some of them on the screen. I want to print some statistics in the end on the screen. Before that I want to call SPOOL OFF.


    Originally posted by andrewst
    1) You mean a SQL Plus command? No.

    2) SET TRIMSPOOL ON
    Regards,

    Ajay Patel

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL command within PL*SQL block

    There's no easy way. You could split the process into 2 parts in SQL Plus like this:

    spool myfile
    exec part1
    spool off
    exec part2

    The statistics from part1 could be saved in a temporary table or package state and then retrieved in part2.

    Or you could re-write in Pro*C or some other programming environment.

  6. #6
    Join Date
    Jan 2004
    Posts
    492

    Re: SQL command within PL*SQL block

    Originally posted by dbforums_ajayp
    The SET TRIMSPOOL ON worked.

    But if I cannot execute SQL Plus command from PL*SQL, is there a way to achieve the below:

    Some of the DBMS_OUTPUT.put_line result in the PL*SQL block must be written to the output file and some of them on the screen. I want to print some statistics in the end on the screen. Before that I want to call SPOOL OFF.

    As previously mentioned, you cannot use SQL*PLUS in PL/SQL blocks. If you want to be able to use output files and write to the screen, I would suggest using UTL_FILE. It is an Oracle supplied package that writes output files to the Oracle server.

    Keep in mind it will not spool directly to your PC, and you may have to configure the server. But if you need to use PL/SQL, this is your best bet. You can write to the screen using DBMS_OUTPUT at any point, and can write to the file at any point.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •