How to spool dynamic filenames from SQL script

25 pts.
Tags:
SQL
SQL scripts
I need to get different file names each time I run the script. How can I do that?
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Post your code and let us take a look. It sounds like a homework question, and should not be to tough. It most likely will involve setting up variables.
    134,585 pointsBadges:
    report
  • TheRealRaven
    What do you mean by "get different file names"? Get them from where? What do you want to do with them? Why do you want to get them "each time [you] run the script"? (Will the script somehow create a different outfile each time you run it?)
    36,025 pointsBadges:
    report
  • 011292
    Dynamic filenames means each time i run the script i should get dynamic name of the spool file, it should not override the previous file.

    I am calling a script file as:

    set termout off;
    @outputdashboard.sql circle
    set termout on;

    here circle is the variable in which i am taking user input as 1 or 2.On the basis of user input, the script should spool file from the below sql file.

    And in the outputdashboard.sql file, i have written:

    set serveroutput on
    declare
    circle_id number:= &circle;
    BEGIN      
    if(circle_id = 1)
    then
    EXECUTE IMMEDIATE 'spool 1_DASHBOARD.csv';
    for dash in (select * from DASHBOARD_HP_V)
    Loop
    DBMS_OUTPUT.PUT_LINE(dash.package_id||','||dash.package_value||','||dash.tot_rev||','||

    dash.tot_count);
    end loop;
    EXECUTE IMMEDIATE 'spool off';

    elsif(circle_id =2)
    then
    EXECUTE IMMEDIATE 'spool 2_DASHBOARD.csv';
    for dash in (select * from DASHBOARD_DEL_V)
    Loop
    DBMS_OUTPUT.PUT_LINE(dash.package_id||','||dash.package_value||','||dash.tot_rev||','||

    dash.tot_count);
    end loop;
    EXECUTE IMMEDIATE 'spool off';
    end if;         
    End;
    /
       
    Can this be possible


    25 pointsBadges:
    report
  • TheRealRaven
    What platform are you running this on? It seems to be PL/SQL, but running where/on what?
    36,025 pointsBadges:
    report
  • 011292
    I have done PL/SQL coding in Toad for Oracle software, but then created an Executable file to avoid running the code from inside the database.
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: