| How to affect a result into a var from a dbms_sql execution ? [message #8959] |
Tue, 07 October 2003 02:54  |
Gerald
Messages: 54
Registered: January 2001
|
Member |
|
|
Dear,
Here's something puzzling for me ...
i want to affect a result in a var inside a PL/SQL block and use the value with some if/then.
here is the script:
declare
nb number;
str VARCHAR2(100);
cur integer;
status integer:=0;
begin
str:='select count(*) into ||nb|| from TOTO';
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, str, dbms_sql.native);
status:=dbms_sql.execute(cur);
DBMS_OUTPUT.PUT_LINE(nb);
dbms_sql.close_cursor (cur);
end;
/
it doesn't know 'NB' !!
i've tryed to Fetch it with :
'fetch cur into nb;' instead using the clause 'Select...into nb' I recieved the PLS-00306 error number or wrong type of argument so I change the type of nb with a varchar2 cuz some1 says that cur returns char ...
someone see how to handle those variables ??
Take Care
gerald
|
|
|
|
| A solution but not with the dbms.sql FETCH statement [message #8960 is a reply to message #8959] |
Tue, 07 October 2003 04:23  |
Gerald
Messages: 54
Registered: January 2001
|
Member |
|
|
This script works fine, but it doesn't explain me the problem with this damned dbms_sql package !
declare
nb integer;
str VARCHAR2(1000);
cur integer;
status integer:=0;
cursor c1 IS select count(*) from TOTO;
begin
OPEN c1;
LOOP
FETCH c1 into nb;
EXIT WHEN c1%NOTFOUND;
End LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE(nb);
end;
|
|
|
|
| Re: How to affect a result into a var from a dbms_sql execution ? [message #8984 is a reply to message #8959] |
Thu, 09 October 2003 00:36 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
You didn't end your quotes my friend:
First of all, if you concatenate you should end the strings:
str:='select count(*) into '||nb||' from TOTO';
And even that won't work. You parse this select statement straight to the SQL engine, which doesn't know SELECT...INTO. That's PL/SQL. You have a particular method for DBMS_SQL:SQL> declare
2 nb number;
3 str VARCHAR2(100);
4 cur integer;
5 status integer:=0;
6 begin
7 str:='select count(*) x from EMP';
8 cur := dbms_sql.open_cursor;
9 dbms_sql.parse(cur, str, dbms_sql.native);
10 dbms_sql.define_column(cur, 1, nb);
11 status:=dbms_sql.execute(cur);
12 IF DBMS_SQL.FETCH_ROWS(cur)>0 THEN
13 -- get column values of the row
14 DBMS_SQL.COLUMN_VALUE(cur, 1, nb);
15 DBMS_OUTPUT.PUT_LINE('Result: '||nvl(nb,-5));
16 END IF;
17 dbms_sql.close_cursor (cur);
18 end;
19 /
Result: 14
One last remark: EXECUTE IMMEDIATE and ref cursors are more easy and flexible than DBMS_SQL.
Good luck!
MHE
|
|
|
|
| Re: How to affect a result into a var from a dbms_sql execution ? [message #8987 is a reply to message #8984] |
Thu, 09 October 2003 03:02 |
Gerald
Messages: 54
Registered: January 2001
|
Member |
|
|
Dear Maaher,
yes ! it was one of the missing piece of the puzzle.
I'm scouting the docs right now ...
By the way i find an alternative solution for the fecth by inserting values in a temp table.
It's quite heavy for the script .
I know the execute immediate fonction but it's for 8i and not for 8.0 so i have to adapt my scripts for the worst machine ... and , yes life is beautiful thank you :)
cheerz
G
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
