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
    Posts
    33
    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'

    CHEERS

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    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
    Posts
    153
    Hi

    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

    ''''||variable||''''
    Vijay.s

  4. #4
    Join Date
    Jun 2001
    Posts
    1

    Talking

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

    /

  5. #5

    dbms_sql

    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