How can I get a large SQL script running in Toad?

by on April 1, 2013

Q: Hi There,

I have wrote a huge SQL script in Toad. 50 000 Char.

I found out that our system accepts only 4000 Char script MAX. How would I run this ?

Note: They dont want this as crystal report (output), they want it as .csv file only . So I cannot use packages and procedures. but is there a way to use maybe a function ? if Yes how ?


9 Responses to “How can I get a large SQL script running in Toad?”
  1.  

    Your options are entirely dependent on what kind of database system you’re connecting to.

    I’ll need to know the database software and version you’re connecting to. Ask your DBAs if you’re unable to get this information.

     
  2.  

    Hi,

    My DB system is Oracle 10g and the application I am using called Acumen (Banking web application)from Fiserv company for banking system. That app only accept a script of 4000 char max byt the script run normal outside that app of course

     
  3.  

    okay, so if you hit Oracle directly from Toad you’re able to run your script just fine, but if you run it through Acumen, it won’t work?

     
  4.  

    It would if it is less than 4000 char but since it s more than 4000 I cannot paste my whole script. So that wont work. I read something about using Table Functions but i don’t understand them well yet.

    BTW, we do have packages, procedures and functions on Oracle for crystal reports.

     
  5.  

    I’d suggest either hitting the database directly for your information, or possibly talking to Fiserv.

     
  6.  

    Sounds good. Hitting the database directly would not be a good solution as I need that report to run on shedule. I will talk to Fiserv or try to use a Table Functions.

    Thank you for the input.

     
  7.  

    Hi Mark, Could you explain this to me: I have the following in a Package:

    TYPE t_fraud_ext IS TABLE OF FI_RPT.FI_Fraud_OBJ;

    FUNCTION fraud_ext_Sql
    ( schema_name IN VARCHAR2
    , select_beginning_business_date IN DATE — Start Date
    , select_thru_business_date IN DATE — End Date
    , select_beginning_business_time IN VARCHAR2 — Start Time
    , select_thru_business_time IN VARCHAR2 — End Time

    ) RETURN VARCHAR2;

    FUNCTION fraud_ext
    ( schema_name IN VARCHAR2
    , select_beginning_business_date IN DATE — Start Date
    , select_thru_business_date IN DATE — End Date
    , select_beginning_business_time IN VARCHAR2 — Start Time
    , select_thru_business_time IN VARCHAR2 — End Time
    ) RETURN t_fraud_ext PIPELINED;

    How these are related ? never worked with Pipelined function

     
  8.  

    and where is that FI_Fraud_OBJ coming from ?

     
  9.  

    Unfortunately, I haven’t spent a lot of time with those either.

    I’d suggest working with your Oracle DBAs or procedural DBAs if you’re lucky enough to have some on staff.

    Otherwise if you can’t find any good documentation on-line you might want to ask at http://stackoverflow.com/