Executing sql text from a variable
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Executing sql text from a variable

  1. #1
    Join Date
    Sep 2000
    I have these sql statements stored in the database. I retrieve them and store the in a variables. eg. a variable sqlquery varchar2(1000) will hava a value of 'SELECT sum(dr_total) INTO balance FROM gl_balances'. How can I retrieve the value from the variable i.e 'SELECT sum(dr_total) INTO balance FROM gl_balances' and run it in a pl/sql program unit. SRW.DO_SQL does not work because it does not recognize the INTO key word within the sql query. I tried SRW.DO_SQL(sqlquery) but it failed. Any Ideas.

    How can I insert quoted text into the database using SQL insert command . Eg How would I insert this string Select name from employee where name = 'NEWTON'


  2. #2
    Join Date
    Aug 2000
    have you looked at native dynamic sql? Search the forums here for dynamic sql and you'll get some good info.
    Oracle DBA and Developer

  3. #3
    Join Date
    Jan 2001

    since u ve mentione srw.do_sql i have assumed u r using the Report Builder !! just declare a variable of ref cusor..say open for ..

    for implementing the single quotes..u have to use the PIPEs


  4. #4
    Join Date
    Jun 2001


    if ur running on 8i or later u can try this:
    execute immediate variabel_name;


  5. #5


    If you are not using reposrt3+, or you are on Oracle 7 then you cannot use the Native Dynamoc SQL as it came in with Oracle 8. In this case you should be able to use the supplied package DBMS_SQL.

    Look in the docs for a full example but basically you:
    1. open cursor
    2. Parse the sql
    3. define_column to set the outout variables
    4. execute
    5. COLUMN_VALUE to get the selected value from the cursor into a local variable
    6. close cursor.

    Dbms_sql is a whole lot more fiddly than Native Dynamic SQL (which is fine for selects) but personally I think gives more control over whats going on... saying that, I could just be being an old git who likes whay he knows... !


Posting Permissions

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

Click Here to Expand Forum to Full Width