Hi Guys,
I am new to oracle coding and I wrote this store procedure

PROCEDURE Get_Faq_Proc
       ( header_name in varchar2,
         out_faq_list OUT curTableList
       )
    IS
    BEGIN
         OPEN out_faq_list for
         select question , answer
         from faq_data
         where header_id = (select header_id from faq_header where title LIKE (''%' || header_name || '%'');

         end Get_Faq_proc;
And it comes up with an oracle error at like statement.
Any help would be appreciated.
Nitin

LIKE ('% || header_name || %')

Try that.

I tried doing this but still my store procedure does not return any data. The code I wrote in asp follows below:

strFAQRequest = LCase(request("section"))
dim faqs
dim dbCmd
set faqs = server.CreateObject("ADODB.RECORDSET")
set dbCmd = Server.CreateObject("ADODB.COMMAND")
Const getfaqs = "{call HMIS_FAQ_Pkg.Get_Faq_Proc(?)}"
dbCmd.ActiveConnection = con
dbCmd.CommandText = getfaqs
dbCmd.CommandType = adCmdText
dbCmd.Parameters(0).direction = adParamInput
dbCmd.Parameters(0).Value = strFAQRequest
set faqs = dbCmd.Execute
dbCmd.Cancel
set dbCmd = nothing

It always comes up with the message that no data is returned from the recordset.I reckon the way i am passing the parameters to the Store Procedure is right??

Cheers,
Nitin

What does your data look like?

The data which I fetch from table are just 2 varchar values.
Its a table of Frequently Asked question with following enteries

ID Number
Header_id Number
Question Varchar2
Answer Varchar2

The header_id is a foriegn key with maps from the table with following attributes

Header_id Number
Title Varchar2

Now from my asp page I am sending the name of this title and then trying to fetch its header_id and get my question and answer recordset.There is no data returned at all on any options I am giving .
Cheers
Nitin

Will (select header_id from faq_header where title LIKE ('% || header_name || %') return one record or multiple records?

Also try select header_id from faq_header where upper(title) LIKE upper('% || header_name || %')

The Select statement only returns one record. and its header_id which is the primary key for the table.

select question, answer
  from faq_data d, faq_header h
 where d.header_id = h.header_id
   and UPPER(h.title) LIKE UPPER('% || header_name || %');

You can also try this.