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'
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
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
if ur running on 8i or later u can try this:
execute immediate variabel_name;
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
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... !
Click Here to Expand Forum to Full Width