Thanks for the question, antoine.

Asked: August 30, 2002 - 9:00 pm UTC

Last updated: August 31, 2005 - 1:06 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

o/s sun solaris 5.8
oracle 8.1.7

Hi Tom,

Thank you for all your great help and suggestions.

I have a problem with this PL/SQL code. I created a native dynamic sql statement, but when I tried to pass many arguments in a single string (str_BIND) it didn't work. If you have some hints or solutions about this or about my code, feel free to give me some advice.

This procedure is going to be called from java program and it is for a dynamic search.

Yours sincerely,

Antoine

here's the code:

I look at your code from your book
expert one-on-one on page 711-712

create or replace package my_pkg as

type refcursor_Type is ref cursor;

PROCEDURE spGetPROPword(
v_HOTELPROPERTYCITY IN varchar2 default NULL,
v_HOTELPROPERTYADDRESS1 IN varchar2 default NULL,
v_HOTELPROPERTYADDRESS2 IN varchar2 default NULL,
v_HOTELPROPERTYADDRESS3 IN varchar2 default NULL,
v_HOTELPROPERTYADDRESS4 IN varchar2 default NULL,
v_HOTELPROPERTYSTATE IN varchar2 default NULL,
v_HOTELPROPERTYCOUNTRY IN varchar2 default NULL,
v_HOTELPROPERTYPOSTAL IN varchar2 default NULL,
v_HOTELPROPERTYPHONE1 IN varchar2 default NULL,
v_HOTELPROPERTYPHONE2 IN varchar2 default NULL,
v_HOTELPROPERTYNAME IN varchar2 default NULL,
p_cursor out refcursor_Type) ;

end;

CREATE OR REPLACE package body my_pkg as

procedure spGetPROPword
(
v_HOTELPROPERTYCITY IN varchar2 default NULL,
v_HOTELPROPERTYADDRESS1 IN varchar2 default NULL,
v_HOTELPROPERTYADDRESS2 IN varchar2 default NULL,
v_HOTELPROPERTYADDRESS3 IN varchar2 default NULL,
v_HOTELPROPERTYADDRESS4 IN varchar2 default NULL,
v_HOTELPROPERTYSTATE IN varchar2 default NULL,
v_HOTELPROPERTYCOUNTRY IN varchar2 default NULL,
v_HOTELPROPERTYPOSTAL IN varchar2 default NULL,
v_HOTELPROPERTYPHONE1 IN varchar2 default NULL,
v_HOTELPROPERTYPHONE2 IN varchar2 default NULL,
v_HOTELPROPERTYNAME IN varchar2 default NULL,
p_cursor out refcursor_Type ) IS

str_QUERY varchar2(500);
str_BIND varchar2(500);
tag varchar2(1);
begin

str_QUERY:= 'SELECT * from ODD_PROPERTY_MAINTENANCE';

if (V_HOTELPROPERTYCITY IS NOT NULL) OR (V_HOTELPROPERTYADDRESS1 IS NOT NULL) OR

(V_HOTELPROPERTYADDRESS2 IS NOT NULL) OR (V_HOTELPROPERTYADDRESS3 IS NOT NULL) OR

(V_HOTELPROPERTYADDRESS4 IS NOT NULL) OR (V_HOTELPROPERTYSTATE IS NOT NULL) OR

(V_HOTELPROPERTYCOUNTRY IS NOT NULL) OR (V_HOTELPROPERTYPOSTAL IS NOT NULL) OR

(V_HOTELPROPERTYPHONE1 IS NOT NULL) OR (V_HOTELPROPERTYPHONE2 IS NOT NULL) OR

(V_HOTELPROPERTYNAME IS NOT NULL) then

str_QUERY:=str_QUERY || ' WHERE ';
end if;

if (V_HOTELPROPERTYCITY IS NOT NULL)
then
str_QUERY := str_QUERY || 'HOTELPROPERTYCITY = :a ';
tag:= 'Y';
str_BIND := v_HOTELPROPERTYCITY;

end if;

if (V_HOTELPROPERTYADDRESS1 IS NOT NULL) and (tag ='Y')
then
str_QUERY := str_QUERY || ' and HOTELPROPERTYADDRESS1 =:b ';
str_BIND := str_BIND || ',' || V_HOTELPROPERTYADDRESS1;
elsif (V_HOTELPROPERTYADDRESS1 IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYADDRESS1 =:b ';
str_BIND := str_BIND || V_HOTELPROPERTYADDRESS1;
tag:= 'Y';
end if;

if (V_HOTELPROPERTYADDRESS2 IS NOT NULL) and (tag ='Y') then

str_QUERY := str_QUERY || ' and HOTELPROPERTYADDRESS2 = :c ';
str_BIND := str_BIND || ',' || v_HOTELPROPERTYADDRESS2;
elsif (V_HOTELPROPERTYADDRESS2 IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYADDRESS2 = :c ';
str_BIND := str_BIND || v_HOTELPROPERTYADDRESS2;
tag:= 'Y';
end if;

if (V_HOTELPROPERTYADDRESS3 IS NOT NULL) and (tag ='Y') then

str_QUERY := str_QUERY || ' and HOTELPROPERTYADDRESS3 =:d ';
str_BIND := str_BIND || ',' || v_HOTELPROPERTYADDRESS3;
elsif (V_HOTELPROPERTYADDRESS3 IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYADDRESS3 =:d ';
str_BIND := str_BIND || v_HOTELPROPERTYADDRESS3;
tag:= 'Y';
end if;

if (V_HOTELPROPERTYADDRESS4 IS NOT NULL) and (tag ='Y') then
str_QUERY := str_QUERY || ' and HOTELPROPERTYADDRESS4 =:e ';
str_BIND := str_BIND || ',' || v_HOTELPROPERTYADDRESS4;
elsif (V_HOTELPROPERTYADDRESS4 IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYADDRESS4 =:e ';
str_BIND := str_BIND || v_HOTELPROPERTYADDRESS4;
tag:= 'Y';
end if;

if (V_HOTELPROPERTYSTATE IS NOT NULL) and (tag ='Y') then
str_QUERY := str_QUERY || ' and HOTELPROPERTYSTATE = :f ';
str_BIND := str_BIND || ',' || V_HOTELPROPERTYSTATE;
elsif (V_HOTELPROPERTYSTATE IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYSTATE = :f ';
str_BIND := str_BIND || V_HOTELPROPERTYSTATE;
tag:= 'Y';
end if;

if (V_HOTELPROPERTYCOUNTRY IS NOT NULL) and (tag ='Y') then
str_QUERY := str_QUERY || ' and HOTELPROPERTYCOUNTRY = :g';
str_BIND := str_BIND || ',' || V_HOTELPROPERTYCOUNTRY;

elsif (V_HOTELPROPERTYCOUNTRY IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYCOUNTRY = :g ';
str_BIND := str_BIND || V_HOTELPROPERTYCOUNTRY;
tag:= 'Y';
end if;

if (V_HOTELPROPERTYPOSTAL IS NOT NULL) and (tag ='Y') then
str_QUERY := str_QUERY || ' and HOTELPROPERTYPOSTAL = :h ';
str_BIND := str_BIND || ',' || V_HOTELPROPERTYPOSTAL;
elsif (V_HOTELPROPERTYPOSTAL IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYPOSTAL = :h ';
str_BIND := str_BIND || V_HOTELPROPERTYPOSTAL;
tag:= 'Y';
end if;

if (V_HOTELPROPERTYPHONE1 IS NOT NULL) and (tag ='Y') then
str_QUERY := str_QUERY || ' and HOTELPROPERTYPHONE1 = :i ';
str_BIND := str_BIND || ',' || V_HOTELPROPERTYPHONE1;
elsif (V_HOTELPROPERTYPHONE1 IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYPHONE1 = :i ';
str_BIND := str_BIND || V_HOTELPROPERTYPHONE1;
tag:= 'Y';
end if;

if (V_HOTELPROPERTYPHONE2 IS NOT NULL) and (tag ='Y') then
str_QUERY := str_QUERY || ' and HOTELPROPERTYPHONE2 =:j ';
str_BIND := str_BIND || ',' || V_HOTELPROPERTYPHONE2;
elsif (V_HOTELPROPERTYPHONE2 IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYPHONE2 = :j ';
str_BIND := str_BIND || V_HOTELPROPERTYPHONE2;
tag:= 'Y';
end if;

if (V_HOTELPROPERTYNAME IS NOT NULL) and (tag ='Y') then
str_QUERY := str_QUERY || ' and HOTELPROPERTYNAME = :k ';
str_BIND := str_BIND || ',' || V_HOTELPROPERTYNAME;
elsif (V_HOTELPROPERTYNAME IS NOT NULL) and (tag <>'Y') then
str_QUERY := str_QUERY || 'HOTELPROPERTYNAME = :k ';
str_BIND := str_BIND || V_HOTELPROPERTYNAME;

end if;

open p_cursor for str_QUERY using str_BIND;

end;

end ;

Here the error I got:

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Aug 30 14:24:14 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> variable t refcursor
SQL> DECLARE
2 V_HOTELPROPERTYCITY VARCHAR2(200);
3 V_HOTELPROPERTYADDRESS1 VARCHAR2(200);
4 V_HOTELPROPERTYADDRESS2 VARCHAR2(200);
5 V_HOTELPROPERTYADDRESS3 VARCHAR2(200);
6 V_HOTELPROPERTYADDRESS4 VARCHAR2(200);
7 V_HOTELPROPERTYSTATE VARCHAR2(200);
8 V_HOTELPROPERTYCOUNTRY VARCHAR2(200);
9 V_HOTELPROPERTYPOSTAL VARCHAR2(200);
10 V_HOTELPROPERTYPHONE1 VARCHAR2(200);
11 V_HOTELPROPERTYPHONE2 VARCHAR2(200);
12 V_HOTELPROPERTYNAME VARCHAR2(200);
13 VV_HOTELPROPERTYCITY VARCHAR2(200);
14
15 BEGIN
16 V_HOTELPROPERTYCITY := 'Hong Kong';
17 V_HOTELPROPERTYADDRESS1 := NULL;
18 V_HOTELPROPERTYADDRESS2 := NULL;
19 V_HOTELPROPERTYADDRESS3 := NULL;
20 V_HOTELPROPERTYADDRESS4 := NULL;
21 V_HOTELPROPERTYSTATE := NULL;
22 V_HOTELPROPERTYCOUNTRY := 'HK';
23 V_HOTELPROPERTYPOSTAL := NULL;
24 V_HOTELPROPERTYPHONE1 := NULL;
25 V_HOTELPROPERTYPHONE2 := NULL;
26 V_HOTELPROPERTYNAME := NULL;
27 --VV_HOTELPROPERTYCITY := NULL;
28
29 my_pkg.spGetPROPword ( V_HOTELPROPERTYCITY, V_HOTELPROPERTYADDRESS1, V_HOTELPROPERTYADDRESS2,
V_HOTELPROPERTYADDRESS3, V_HOTELPROPERTYADDRESS4, V_HOTELPROPERTYSTATE, V_HOTELPROPERTYCOUNTRY, V_HO
TELPROPERTYPOSTAL, V_HOTELPROPERTYPHONE1, V_HOTELPROPERTYPHONE2, V_HOTELPROPERTYNAME, :t );
30 END;
31 /
DECLARE
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "ABOCTI.MY_PKG", line 169
ORA-06512: at line 29

SQL>

and Tom said...

You only ever bind the first variable

open x for y using z;

will only bind a single variable with the value of Z. If Z = 'a,b,c,d' that is NOT the same as coding:

open x for y using a,b,c,d;

is it opening X using the string Y with the single value in Z which just happens to be a comma delimited list.

Here is how to do this:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

Rating

(13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thank again Tom

Antoine Bocti, September 02, 2002 - 11:05 pm UTC

Interesting, I never knew about sys_context()!
Sorry for not searching properly in the archive.

REF cursor out and intermedia

antoine Bocti, September 03, 2002 - 8:48 pm UTC

Hi Tom,

is it possible with your exemple that you gave me to declare a ref cursor out.

And in the applacation context to put a search with intermedia like for exemple:

if ( p_hotelpropertycity is NOT NULL ) then
dbms_session.set_context( 'MY_CTX', 'city',
'contains hotelpropertycity,'''p_hotelpropertycity%''' ');
l_query := l_query ||
' and ename like
sys_context( ''MY_CTX'', ''city'' ) ';
end if;

thanks.

September 03, 2002 - 8:54 pm UTC

yes, you can use a ref cursor IN OUT parameter instead of a local variable.

that example you gave is not "intermedia"? there is no contains....

REF cursor out and intermedia

antoine Bocti, September 03, 2002 - 9:17 pm UTC

I put this code in expemple instead of your code.

This is the ink you gave me.

CURSOR
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

My question was, is it possible in your exemple (that you gave me above (CURSOR)) to put some intermedia operator like contains to build a dynamic search procedure?

If it's possible can you give me an exemple please.

Thank Tom.

September 04, 2002 - 7:16 am UTC

hmm, looks like contains doesn't get along with sys_context in 8i...

You'll have to use this technique instead:

if ( p_ename is NOT NULL ) then
l_query := l_query ||
' and contains( field, :p_ename ) > 0 '
else
l_query := l_query ||
' and 1 = DECODE(:p_ename,NULL,1,1)';
end if;

and then when you open the query:

open refcursor for l_query using p_ename;

thanksTom!

Antoine Bocti, September 04, 2002 - 12:37 pm UTC

I just have a question for intermedia and the use of wildcard(%), what's your opinion about it?

September 04, 2002 - 3:31 pm UTC

wildcards slow everything down. I only do them on structured data (where we have a CHANCE at maintaining performance)

thanksTom!

Antoine Bocti, September 04, 2002 - 8:35 pm UTC

Hi tom,

with intermedia can I search over multiple table with a single query.

If not, can you create a view or a materialize view to do the query?

What is your point of view?

thanks.

September 04, 2002 - 8:50 pm UTC

Yes, in fact -- that is EXACTLY what I do here on asktom!

I indexed this procedure:

create or replace procedure index_ask_tom( p_id in rowid, p_lob IN OUT clob )
as
begin
for x in ( select * from ask_tom.WWC_ASK_INDEXED_QUESTIONS$ where rowid = p_id )
loop
dbms_lob.copy( p_lob, x.text, dbms_lob.getLength(x.text) );
for y in ( select ''), ' -->' );
end loop;
end;
/

that glues the questions with the answers to the comments and my followups (so when you search, you are searching across all of them).

See:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:440419921146 <code>
for a full example.

contains operator

mo, September 04, 2002 - 9:54 pm UTC

Tom:

1. What do you mean by "structured data". Is it char or varchar2 column types.

"wildcards slow everything down. I only do them on structured data (where we
have a CHANCE at maintaining performance) "

2. Does 8i support the contains operator. How is that different than using the like % .

Thank you,

September 05, 2002 - 7:28 am UTC

1) yes.

2) yes.

read the intermedia documentation that is freely available and you'll learn alot about its capabilities:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/inter.817/a77063/toc.htm <code>

me again...

Antoine Bocti, September 04, 2002 - 11:51 pm UTC

Hi Tom,

So if I want to do a search on two table by exemple:

table brand
id_brand
name
code

table hotel
id_hotel
id_brand
name
number_room

Can I do a select and join brand and hotel and use one index for a field in brand and and use one index for a field in hotel?

select * from brand A,hotel where contains(a.name,'Hilton') > 0 OR
contains(b.name,'hilton by the sea') > 0;

Is it better to create a PLSQL function that returns the two fields glued together?

September 05, 2002 - 7:31 am UTC

It would be more efficient if you indexed the function that glued the data together if you frequently search on those two items as if they were a single item.

REF cursor out and intermedia

Antoine Bocti, September 05, 2002 - 10:23 am UTC

Hi Tom,

Is it possible to add a wildcard add the end of :p_hotelpropertycity in my code?

I try these :
l_query := l_query || ' and contains( hotelpropertycity, :p_hotelpropertycity' ||' ||% ) > 0';

it compile but at the run time it failed!

Original code that work!

if ( p_hotelpropertycity is NOT NULL ) then
l_query := l_query || ' and contains(hotelpropertycity, :p_hotelpropertycity ) > 0 ';
else
l_query := l_query || ' and 1 = DECODE(:p_hotelpropertycity,NULL,1,1)';
end if;

September 05, 2002 - 9:24 pm UTC

If that is not working just add the
' and contains( hotel.., :p_hotelpropertycity ) > 0'

or

' and 1 = decode( :p_hotel..., null, 1, 1 ) '

and when you open the query use

USING p_hotelcityproperty || '%';

Tom thank you so much again!

A reader, September 06, 2002 - 1:09 pm UTC

intermedia

mo, September 07, 2002 - 12:20 pm UTC

Tom:

AM I Correct:

1. If you are doing queries on structured data you can get away with regular SQL statements and using like % operator.
even using varchar2(2000).

2. When you do text searches for unstructured data like blob, or long you need intermedia and use contains operator?

September 07, 2002 - 6:48 pm UTC

1) yes

2) "need" is too strong. WANT is the correct term.

Help

Srinivas, March 17, 2004 - 11:40 pm UTC

SQL> create table t(x number default 10);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> insert into t values(2);

1 row created.

SQL> insert into t values(default);

1 row created.

SQL>  insert into t values(default);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

         X                                                                      
----------                                                                      
         1                                                                      
         2                                                                      
        10                                                                      
        10                                                                      

SQL> delete from t where x = default;
delete from t where x = default
                        *
ERROR at line 1:
ORA-00936: missing expression 


SQL> delete from t where x is default;
delete from t where x is default
                         *
ERROR at line 1:
ORA-00908: missing NULL keyword 


SQL> delete from t where x in(default);
delete from t where x in(default)
                         *
ERROR at line 1:
ORA-00936: missing expression 

How to correct this?Will I have to specify as
sql>delete from t where x = 10;
Could you please help?
Bye!

March 18, 2004 - 7:18 am UTC

there is no way to "correct this" as default is allowed in the values clause of an insert or the set clause of an update and that is "about it"

this might help U

A reader, March 18, 2004 - 9:07 am UTC

Srinivas,

U really need this "feature" ?

the following might work in some cases

sqlplus> create or replace function data_default(table_name in varchar2, column_name in varchar2) return varchar2
2 is
3 ret varchar2(32767);
4 begin
5 select data_default
6 into ret
7 from user_tab_columns
8 where table_name=table_name
9 and column_name=column_name;
10 return ret;
11 end;
12 /

Funktion wurde erstellt.

sqlplus> create table t(x number default 10);

Tabelle wurde angelegt.

sqlplus> insert into t values(1);

1 Zeile wurde erstellt.

sqlplus> insert into t values(default);

1 Zeile wurde erstellt.

sqlplus> select * from t;

X
----------
1
10

sqlplus> delete t where x in (select data_default('T','X') from dual);

1 Zeile wurde gel÷scht.

sqlplus> select * from t;

X
----------
1

Oracle Text for unrelated tables

Mohini, August 30, 2005 - 8:21 pm UTC

Database : 9i (Release 2)

In explaining, the way you use Oracle text..you said you index a function.
In that function, I see you query from different tables that you have and tie them with display id..which I guess is in all your tables (Primary and Foreign Key)...
What if the search needs to be done on totally unrelated tables...
User enters a key word and it needs to search through columns that belong to totally unrelated tables (an or search)...

Thanks.

August 31, 2005 - 1:06 pm UTC

there has to be "something" to relate them, even if you just "invent" that thing.

give me a "for example"

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

thank again Tom

REF cursor out and intermedia

REF cursor out and intermedia

thanksTom!

thanksTom!

contains operator

me again...

REF cursor out and intermedia

Tom thank you so much again!

intermedia

Help

this might help U

Oracle Text for unrelated tables

More to Explore

VLDB

VLDB