Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    62

    Unanswered: sqlplus commands in plsql

    Hi,

    I would like to know is there any way I can use SQL+ command in SQL.

    My code is below which is failing:

    Set Head off
    Set pau off
    Set feedback off

    DECLARE

    FILE_NAME varchar2(200) := null;
    cursor c1 is
    select lower(object_name) procname from dba_objects
    where object_type = 'PROCEDURE'
    and status = 'VALID'
    and object_name not in(
    'SUBPTXT',
    'SUBPTXT2',
    'ODCICOLINFODUMP',
    'ODCIINDEXALTEROPTIONDUMP',
    'ODCIINDEXINFODUMP',
    'ODCIPREDINFODUMP',
    'ODCIQUERYINFODUMP',
    'ODCISTATSOPTIONSDUMP',
    'ORA$_SYS_REP_AUTH',
    'PSTUB',
    'PSTUBT'
    )
    order by 1 ;

    BEGIN
    for v1 in c1
    loop
    begin
    FILE_NAME := v1.procname;
    spool FILE_NAME
    SELECT text
    FROM user_source
    WHERE name = v1.procname
    ORDER BY line;
    spool off
    end;
    end loop;
    END;

    It is failing on the lines in red color above.

    Thanx for any help.

    -Bheemsen

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    NO
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    In PL/SQL to spool the results from a PL/SQL procedure/code you can use the DBMS_OUTPUT package and its functions, instead of trying to spool the output as in SQL*Plus.


    HTH

    clio_usa - OCP 8/8i/9i DBA

  4. #4
    Join Date
    Apr 2004
    Posts
    8

    Exclamation PL/SQL source

    You want the sources for all the procs. in the DB i understand.

    Why not try this ?
    export feature in [PLEdit] from www.benthicsoftware.com
    there is a 30-day trial version for the software.

  5. #5
    Join Date
    Apr 2004
    Posts
    8

    Thumbs up this is possible too !

    DECLARE

    l_text user_source.text%type;
    FILE_NAME varchar2(200) := null;

    cursor c1 is
    select lower(object_name) procname from dba_objects
    where object_type = 'PROCEDURE'
    and status = 'VALID'
    and object_name not in(
    'SUBPTXT',
    'SUBPTXT2',
    'ODCICOLINFODUMP',
    'ODCIINDEXALTEROPTIONDUMP',
    'ODCIINDEXINFODUMP',
    'ODCIPREDINFODUMP',
    'ODCIQUERYINFODUMP',
    'ODCISTATSOPTIONSDUMP',
    'ORA$_SYS_REP_AUTH',
    'PSTUB',
    'PSTUBT'
    )
    order by 1 ;

    l_output utl_file.file_type;

    BEGIN

    for v1 in c1
    loop
    begin
    FILE_NAME := v1.procname || '.txt' ;
    l_output := utl_file.fopen( <your_directory_name>, FILE_NAME, 'w' );
    SELECT text
    INTO l_text
    FROM user_source
    WHERE name = v1.procname
    ORDER BY line;
    utl_file.put( l_output, l_text );
    utl_file.fclose( l_output );
    end loop;
    END;

  6. #6
    Join Date
    May 2002
    Posts
    62
    Thanx to all who had replied to my post.

    I am using the UTL_FILE method and it works.

    -Bheemsen

Posting Permissions

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