Solved

Ref cursor or PLSQL table in the INTO clause of EXECUTE IMMEDIATE

Posted on 2008-10-16
3
6,949 Views
Last Modified: 2013-12-07
Hi,
I have created a procedure that searches tables in the database.
Table name is decided at run time and i want the output of the query to be in REF CURSOR or PLSQL table to increase the performance.

Please find below the proc
Procedure pr_search_acronym (p_i_acronym varchar2, p_i_category integer DEFAULT NULL, p_i_user varchar2, p_i_result OUT acronym_record,p_i_error OUT varchar2,p_i_exit_status OUT integer )
IS
acronym_start_alphabet char(1);
acronym_table_name char(15);
acronym_result acronym_record;
sql_str varchar2(1000);
error_id number;
error_message varchar2(300);
p_i_error varchar2(500);
BEGIN
       /* Finding the table name, in which data is to be inserted*/
         acronym_start_alphabet := SUBSTR(p_i_acronym,1,1);
         acronym_table_name := 'TBLA_ACRONYM_' || acronym_start_alphabet;
      
       sql_str := 'SELECT * FROM ' || acronym_table_name
                          || ' WHERE ACRONYM LIKE :1 ' ;
                  
       EXECUTE IMMEDIATE sql_str INTO p_i_result USING p_i_acronym ;       
             
       EXCEPTION
                      WHEN OTHERS THEN
        p_i_error := SQLERRM;
         p_i_exit_status := 1;
           SELECT SEQ_EXCEPTION.NEXTVAL INTO error_id FROM DUAL;
           INSERT INTO TBLA_EXCEPTION (ERROR_ID, ERROR_DESC, ERROR_LOCATION,         USER_ID, ERROR_DATE)                                 VALUES ( error_id,p_i_error ,'SearchAcronym' ,p_i_user,sysdate );       
END pr_search_acronym;

Also note : TYPE acronym_record IS REF CURSOR;
But when i am trying to run the proc.
It gives following error
ORA-00932: inconsistent datatypes: expected CURSER got NUMBER

Please help me.
Thanks.
0
Comment
Question by:bhavanisharansingh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 22728871
Change the below line.

You cannot execute immediate into a ref cur. you have to open it using the sql stmt.
.
.
 
       --EXECUTE IMMEDIATE sql_str INTO p_i_result USING p_i_acronym ;        
       -- you should open the ref cur here
       open p_i_result for sql_str;
.
.

Open in new window

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22728882
missed out the bind variable. the changed code will look like this.
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 500 total points
ID: 22728890

Procedure pr_search_acronym (p_i_acronym varchar2, 
			p_i_category integer DEFAULT NULL, 
			p_i_user varchar2, 
			p_i_result OUT acronym_record,
			p_i_error OUT varchar2,
			p_i_exit_status OUT integer )
IS
acronym_start_alphabet char(1);
acronym_table_name char(15);
acronym_result acronym_record;
sql_str varchar2(1000);
error_id number;
error_message varchar2(300);
p_i_error varchar2(500);
BEGIN
       /* Finding the table name, in which data is to be inserted*/
         acronym_start_alphabet := SUBSTR(p_i_acronym,1,1);
         acronym_table_name := 'TBLA_ACRONYM_' || acronym_start_alphabet;
       
       sql_str := 'SELECT * FROM ' || acronym_table_name|| ' WHERE ACRONYM LIKE :1 ' ;
                  
       --EXECUTE IMMEDIATE sql_str INTO p_i_result USING p_i_acronym ;        
       -- you should open the ref cur here
       open p_i_result for sql_str USING p_i_acronym;
              
       EXCEPTION
                      WHEN OTHERS THEN
        p_i_error := SQLERRM;
         p_i_exit_status := 1;
           SELECT SEQ_EXCEPTION.NEXTVAL INTO error_id FROM DUAL;
           INSERT INTO TBLA_EXCEPTION (ERROR_ID, ERROR_DESC, ERROR_LOCATION,         USER_ID, ERROR_DATE)                                 VALUES ( error_id,p_i_error ,'SearchAcronym' ,p_i_user,sysdate );       
END pr_search_acronym;

Open in new window

0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question