I hope someone can help me with this silly problem which is driving me (derogatory word removed):
I have a Text Item on my form and let us assume it has the text 'bob','fred','harry'
I do a query as follows:
select name from names where firstname in ( :block1.text1 );
this does not work but its baffling me as to why......
thanks,
p.s. I am using oracle forms 4 and oracle 8i
Full Version: this query is killing me :)
What does "does not work" mean?
A select statement by itself is not going to work because you are not selecting anything into a variable.
declare
v_name <define the datatype>;
begin
select name into v_name
from names
where firstname = :block.text;
end;
What if you get more than one value or none? Both will return errors. If you get one value returned, then :block.text := v_name;
You will need an exception handling for the possible errors.
A select statement by itself is not going to work because you are not selecting anything into a variable.
declare
v_name <define the datatype>;
begin
select name into v_name
from names
where firstname = :block.text;
end;
What if you get more than one value or none? Both will return errors. If you get one value returned, then :block.text := v_name;
You will need an exception handling for the possible errors.
Thanks for your reply SteveC but I guess I was not being very clear what I am trying to achieve. I was trying to ger the text item to represent multiple valuse so that I can use the IN to check against. This is clearly not working...
I want to end up with where firstname in ('bob','fred','harry');
but this example gives me where firstname in (''bob','fred','harry''):
in theory
the reason i am trying this convoluted stuff is that I want to end up with the above but I am working from a recordgroup so I loop through all the items in the recodrgroup and put them in the textitem...
I desperately need to end up being able to create a query using IN as this is a very basic example of something much more complicated going on
thanks again
I want to end up with where firstname in ('bob','fred','harry');
but this example gives me where firstname in (''bob','fred','harry''):
in theory
the reason i am trying this convoluted stuff is that I want to end up with the above but I am working from a recordgroup so I loop through all the items in the recodrgroup and put them in the textitem...
I desperately need to end up being able to create a query using IN as this is a very basic example of something much more complicated going on
thanks again
QUOTE (SteveC @ Jun 30 2008, 01:48 PM) 
What does "does not work" mean?
A select statement by itself is not going to work because you are not selecting anything into a variable.
declare
v_name <define the datatype>;
begin
select name into v_name
from names
where firstname = :block.text;
end;
What if you get more than one value or none? Both will return errors. If you get one value returned, then :block.text := v_name;
You will need an exception handling for the possible errors.
A select statement by itself is not going to work because you are not selecting anything into a variable.
declare
v_name <define the datatype>;
begin
select name into v_name
from names
where firstname = :block.text;
end;
What if you get more than one value or none? Both will return errors. If you get one value returned, then :block.text := v_name;
You will need an exception handling for the possible errors.
You're trying to pass dynamic text into a non-dynamic query. The real syntax of a query using IN is:
select * from table
where column IN ('val1', 'val2', 'val3');
However, your :block1.text1 field is a string unto itself. Meaning if you entered
bob, fred, harry
the value of :block1.text1 would be:
'bob, fred, harry'
Had you entered
'bob', 'fred', 'harry'
The value of :block1.text1 would be:
''bob', 'fred', 'harry''
Which is your actual problem.
You have two choices. You can either allow multiple text fields in your form for values of :block1.text1, then bring them together in one IN (3 text fields, they enter bob in one, fred in the next, harry in the next, you use IN with the value of each field), or you can use a dynamic parsing method for your query such as EXECUTE IMMEDIATE in PL/SQL, which allows you to create a dynamic query built using various strings.
select * from table
where column IN ('val1', 'val2', 'val3');
However, your :block1.text1 field is a string unto itself. Meaning if you entered
bob, fred, harry
the value of :block1.text1 would be:
'bob, fred, harry'
Had you entered
'bob', 'fred', 'harry'
The value of :block1.text1 would be:
''bob', 'fred', 'harry''
Which is your actual problem.
You have two choices. You can either allow multiple text fields in your form for values of :block1.text1, then bring them together in one IN (3 text fields, they enter bob in one, fred in the next, harry in the next, you use IN with the value of each field), or you can use a dynamic parsing method for your query such as EXECUTE IMMEDIATE in PL/SQL, which allows you to create a dynamic query built using various strings.
Thanks steve,
thanks for understanding my rambling
as you may realise, that as I am working from a recordgroup, there will not be 3 items... there will be anywhere from 1 to 60.....
so the first option is out.... could you maybe elaborate on the second ?
thanks
thanks for understanding my rambling
as you may realise, that as I am working from a recordgroup, there will not be 3 items... there will be anywhere from 1 to 60.....
so the first option is out.... could you maybe elaborate on the second ?
thanks
I am thinking the best way to achieve this would be to loop through my recordgroup and make a table of the contents, then I could simply use:-
where firstname in ( select * from temptable);
only problem is that I dont know how to make a table within the code.. here`s what I have
procedure recordgroup_was_updated is
the_Rowcount NUMBER;
rg_id RecordGroup;
gc_id GroupColumn;
col_val VARCHAR2(80);
Exit_Function Exception;
BEGIN
rg_id := Find_Group( 'names' );
IF Id_Null(rg_id) THEN
Message('Record Group names does not exist.');
RAISE Exit_Function;
END IF;
gc_id := Find_Column( 'names.firstname' );
IF Id_Null(gc_id) THEN
Message('firstname does not exist.');
RAISE Exit_Function; END IF;
the_Rowcount := Get_Group_Row_Count( rg_id );
/* create the temp table*/
FOR j IN 1..the_Rowcount
LOOP
/* insert into temptable from current position in recordgroup */
blah ..... Get_Group_Char_Cell( gc_id,j)';
END LOOP;
END;
where firstname in ( select * from temptable);
only problem is that I dont know how to make a table within the code.. here`s what I have
procedure recordgroup_was_updated is
the_Rowcount NUMBER;
rg_id RecordGroup;
gc_id GroupColumn;
col_val VARCHAR2(80);
Exit_Function Exception;
BEGIN
rg_id := Find_Group( 'names' );
IF Id_Null(rg_id) THEN
Message('Record Group names does not exist.');
RAISE Exit_Function;
END IF;
gc_id := Find_Column( 'names.firstname' );
IF Id_Null(gc_id) THEN
Message('firstname does not exist.');
RAISE Exit_Function; END IF;
the_Rowcount := Get_Group_Row_Count( rg_id );
/* create the temp table*/
FOR j IN 1..the_Rowcount
LOOP
/* insert into temptable from current position in recordgroup */
blah ..... Get_Group_Char_Cell( gc_id,j)';
END LOOP;
END;
Wow, I can *almost* see how the original question is the same as the revised problem statement. How did I not see that?
QUOTE (SteveC @ Jun 30 2008, 03:18 PM)
Wow, I can *almost* see how the original question is the same as the revised problem statement. How did I not see that?
Sorry Steve. I can only surmise that through the great help here, my problem evolved. so now I have a new problem. would appreciate some help, but failing that maybe i can work it out somehow.... thanks again
I wouldn't recommend a temp table. Those may be all well and good in SQL Server and MySQL, but in Oracle...*shudders*
How about doing it with REFCURSOR or entirely in PL/SQL?
See my blog article here for some help with this: http://www.oraclealchemist.com/oracle/easy...ocedure-output/
Here's an example:
Note that the parameters I pass in have two single quotes (not one double quote) around each name. This is the way you escape single quotes when inside of a string literal in Oracle. I'm not sure if Forms will automatically do this transformation for you when you call the function. However, as you can see the "bob, fred, harry" part IS one single string, not a parsed string, with the single quotes contained therein, as you tried in your example.
How about doing it with REFCURSOR or entirely in PL/SQL?
See my blog article here for some help with this: http://www.oraclealchemist.com/oracle/easy...ocedure-output/
Here's an example:
CODE
SQL> create table names (first_name varchar2(100), last_name varchar2(100));
Table created.
SQL> insert into names values ('bob', 'jones');
1 row created.
SQL> insert into names values ('bob', 'barker');
1 row created.
SQL> insert into names values ('fred', 'thompson');
1 row created.
SQL> insert into names values ('fred', 'karam');
1 row created.
SQL> insert into names values ('steve', 'karam');
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace function get_names (first_name in varchar2) return sys_refcursor
2 as
3 c_test sys_refcursor;
4 begin
5 open c_test for 'select first_name, last_name from names where first_name in (
6 '||first_name||')';
7
8 return c_test;
9 end;
10 /
Function created.
SQL> column first_name format a30
SQL> column last_name format a30
SQL> variable rc refcursor
SQL> exec :rc := get_names(' ''bob'', ''fred'', ''harry'' ')
PL/SQL procedure successfully completed.
SQL> print :rc
FIRST_NAME LAST_NAME
------------------------------ ------------------------------
bob jones
bob barker
fred thompson
fred karam
Table created.
SQL> insert into names values ('bob', 'jones');
1 row created.
SQL> insert into names values ('bob', 'barker');
1 row created.
SQL> insert into names values ('fred', 'thompson');
1 row created.
SQL> insert into names values ('fred', 'karam');
1 row created.
SQL> insert into names values ('steve', 'karam');
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace function get_names (first_name in varchar2) return sys_refcursor
2 as
3 c_test sys_refcursor;
4 begin
5 open c_test for 'select first_name, last_name from names where first_name in (
6 '||first_name||')';
7
8 return c_test;
9 end;
10 /
Function created.
SQL> column first_name format a30
SQL> column last_name format a30
SQL> variable rc refcursor
SQL> exec :rc := get_names(' ''bob'', ''fred'', ''harry'' ')
PL/SQL procedure successfully completed.
SQL> print :rc
FIRST_NAME LAST_NAME
------------------------------ ------------------------------
bob jones
bob barker
fred thompson
fred karam
Note that the parameters I pass in have two single quotes (not one double quote) around each name. This is the way you escape single quotes when inside of a string literal in Oracle. I'm not sure if Forms will automatically do this transformation for you when you call the function. However, as you can see the "bob, fred, harry" part IS one single string, not a parsed string, with the single quotes contained therein, as you tried in your example.
thanks so much for your replies, I now have exactly what I need.
Have implemented with temp table but will also try a solution as above.
thanks again
Have implemented with temp table but will also try a solution as above.
thanks again
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.