| whats wrong in this [message #58626] |
Mon, 22 September 2003 12:23  |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
please whats wrong in this block
declare
a varchar2(30) := '&tabname';
i number := 0;
q varchar2(300);
begin
select count(1) into i from a ;
if i = 6 then
dbms_output.put_line('This is true');
else
dbms_output.put_line('This is false');
end if;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
|
|
|
|
| Re: whats wrong in this [message #58628 is a reply to message #58626] |
Mon, 22 September 2003 13:13  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Although I am not going into the efficiency & functionality of your code, I didnt see a problem running it.
SQL> declare
2 a varchar2(30) := '&tabname';
3 i number := 0;
4 q varchar2(300);
5 begin
6 select count(1) into i from a ;
7 if i = 6 then
8 dbms_output.put_line('This is true');
9 else
10 dbms_output.put_line('This is false');
11 end if;
12 exception
13 when others then
14 dbms_output.put_line(sqlerrm);
15 end;
16 /
Enter value for tabname: A
old 2: a varchar2(30) := '&tabname';
new 2: a varchar2(30) := 'A';
PL/SQL procedure successfully completed.
SQL> set serverout on
SQL> /
Enter value for tabname: A
old 2: a varchar2(30) := '&tabname';
new 2: a varchar2(30) := 'A';
This is false
PL/SQL procedure successfully completed.
SQL>
-Thiru
|
|
|
|
| Re: whats wrong in this [message #58629 is a reply to message #58628] |
Mon, 22 September 2003 14:12 |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
I dont have a table name called 'A' the 'a' am using in "select count(1) into i from a;" is a variable of varchar2.
give some table name don't give a as the table name.
|
|
|
|
| Re: whats wrong in this [message #58630 is a reply to message #58629] |
Mon, 22 September 2003 14:57 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Oh,you may not use such a variable name in place of table/view. Oracle will look for a table by that name .However you could do the following :
SQL> declare
2 i number := 0;
3 q varchar2(300);
4 begin
5 select count(1) into i from &tabname ;
6 if i = 6 then
7 dbms_output.put_line('This is true');
8 else
9 dbms_output.put_line('This is false');
10 end if;
11 exception
12 when others then
13 dbms_output.put_line(sqlerrm);
14 end;
15 /
Enter value for tabname: B
old 5: select count(1) into i from &tabname ;
new 5: select count(1) into i from B ;
This is true
PL/SQL procedure successfully completed.
|
|
|
|
|
|
| Re: whats wrong in this [message #58632 is a reply to message #58631] |
Mon, 22 September 2003 15:47 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
SQL> create or replace function check_count(table_name IN Varchar2) return integer as
2 i number;
3 sql_stmt varchar2(300) := 'select count(1) from '||table_name;
4 begin
5 execute immediate sql_stmt into i ;
6 return i;
7 end;
8 /
Function created.
SQL> select check_count('A') from dual;
CHECK_COUNT('A')
----------------
1000
SQL> declare
2 begin
3 if check_count('A')=6 then
4 dbms_output.put_line('This is true');
5 else
6 dbms_output.put_line('This is false');
7 end if;
8 exception
9 when others then
10 dbms_output.put_line(sqlerrm);
11 end;
12 /
This is false
PL/SQL procedure successfully completed.
SQL> create table B as select * from A where rownum < 7;
Table created.
-- now use the substituition variable as argument to the function.
SQL> declare
2 begin
3 if check_count('&table_name')=6 then
4 dbms_output.put_line('This is true');
5 else
6 dbms_output.put_line('This is false');
7 end if;
8 exception
9 when others then
10 dbms_output.put_line(sqlerrm);
11 end;
12 /
Enter value for table_name: B
old 3: if check_count('&table_name')=6 then
new 3: if check_count('B')=6 then
This is true
PL/SQL procedure successfully completed.
HTH
Thiru
|
|
|
|
|
|
|
|
| Re: whats wrong in this [message #58635 is a reply to message #58634] |
Mon, 22 September 2003 17:18 |
Kapil
Messages: 145
Registered: May 2002
|
Senior Member |
|
|
I thought its not possible for a sec, and i have been trying execute immediate, but i dint know that i could have done execute immediate q into i;
Thanks for the Help again.
|
|
|
|
| Re: whats wrong in this [message #58637 is a reply to message #58626] |
Tue, 23 September 2003 05:11 |
vimal
Messages: 46
Registered: February 2000
|
Member |
|
|
declare
a varchar2(30) := '&tabname';
i number := 0;
q varchar2(300);
begin
select count(1) into i from &3ek ;
if i = 6 then
dbms_output.put_line('This is true');
else
dbms_output.put_line('This is false');
end if;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
