| v7.3.4 execute immediate [message #18357] |
Tue, 29 January 2002 08:18  |
Kieron Smythe
Messages: 11
Registered: March 2000
|
Junior Member |
|
|
Hi,
How to achieve the following in 7.3.4. The following works in 8i only.
declare
n varchar2(30):='10+20*30';
l_no number;
begin
execute immediate 'select '||n||' from dual 'into l_no;
dbms_output.put_line(l_no);
end;
SQL> /
610
PL/SQL procedure successfully completed.
Thanks,
Kieron
|
|
|
|
| Re: v7.3.4 execute immediate [message #18360 is a reply to message #18357] |
Tue, 29 January 2002 08:48  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
dmbs_sql is required. Create this proc to simplify things and call it instead of "execute immediate". Name the proc execute_immediate if you like...
CREATE OR REPLACE PROCEDURE DYNSQL (i_sql in VARCHAR2) iS
c_id PLS_INTEGER default dbms_sql.open_cursor;
o_count PLS_INTEGER;
BEGIN
dbms_sql.parse (c_id, i_sql, dbms_sql.native);
o_count := dbms_sql.execute(c_id);
dbms_sql.close_cursor(c_id);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(c_id);
DBMS_OUTPUT.PUT_LINE ( 'dynsql failed' );
END;
/
|
|
|
|
| Re: v7.3.4 execute immediate [message #18362 is a reply to message #18360] |
Tue, 29 January 2002 09:00 |
Kieron Smythe
Messages: 11
Registered: March 2000
|
Junior Member |
|
|
Thanks for the help guys. I worked out a solution incorporating your suggestions on dynamic sql.
FUNCTION eval_expr(v_calc IN VARCHAR2) RETURN NUMBER IS
v_cid1 NUMBER;
v_out NUMBER;
v_formula VARCHAR2(400);
v_stmt VARCHAR2(500);
rid1 NUMBER;
BEGIN
v_cid1:=DBMS_SQL.open_CURSOR;
v_stmt := 'select '||v_calc||' from dual';
DBMS_SQL.PARSE(v_cid1,v_stmt,DBMS_SQL.NATIVE);
dbms_sql.define_column(v_cid1,1,v_formula,20);
rid1:=dbms_sql.execute(v_cid1);
v_out:=dbms_sql.fetch_rows(v_cid1);
dbms_sql.column_value(v_cid1,1,v_formula);
DBMS_SQL.CLOSE_CURSOR (v_cid1);
RETURN TO_NUMBER(v_formula);
END;
Thanks again,
Kieron
|
|
|
|
| Re: v7.3.4 execute immediate [message #20348 is a reply to message #18357] |
Wed, 15 May 2002 14:12 |
Ravi
Messages: 251
Registered: June 1998
|
Senior Member |
|
|
Hi Satish,
My name is Ravi. When I am browsing for a help "EXECUTE IMMEDIATE" i found your name in the disscussion forum,
I have a query in a procedure embeded in a Package. The query finally says
"where name = 'T&M Labor';"
When i try to compile the package It is asking for a value for "M" since '&' is attached to M Labor. Actually There are more number of records which match the name "T&M Labor".
So how should i use "EXECUTE IMMEDIATE" in my package to overcome this problem
Your kind help will be highly appreciated.
Thanks
Ravi.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
