?
Solved

How to call cursor procedure and dbms output results?

Posted on 2009-03-30
6
Medium Priority
?
1,233 Views
Last Modified: 2012-05-06
How do I call this procedure and output one of the fields called BillRun within TOAD in my SQL Editor?  I tried a cursor thing but it seems to not work.  Any help?

Also just one other thing is if there is no values returned my application that calls this throws an error.  If I pass a blank value to the value list it throws this error here but if I pass in a valid value it works fine

"ORA-00936: missing expression\nORA-06512: at \"INVOICES.EDI_PLATINUM\", line 16\nORA-06512: at line 1"

But this works fine if I pass a valid value to it.
DECLARE TYPE cur_type IS REF CURSOR;
  my_cursor cur_type;
      billrun VARCHAR2(50);
	  my_list_of_values VARCHAR2(100);
begin
     my_list_of_values := '''C15'',''C16''';
     EDI_PLATINUM.GET_EDIPLATINUM_ACCOUNTS(my_list_of_values,my_cursor);
        fetch my_cursor into billrun;
     while my_cursor%found loop
     dbms_output.put_line(billrun);
     fetch my_cursor into billrun;
     end loop;
     close my_cursor;
  end;
/
 
Trying above throws a inconsistent datatype error.  I am trying to test above to see why I get that missing expression error.
 
THIS IS THE PROC:
  procedure GET_EDIPLATINUM_ACCOUNTS(in_list in varchar2, p_rc out sys_refcursor) IS
  v_select varchar2(8000);
  begin
       v_select := 'Select Cus_Parent, Parent_Name, BillRun, Folder, Map_Version From BillRun.RPT_CUSTOM_XLS Where (Delivery_Method like ''%EDI%'' Or Delivery_Method like ''%GSI%'') AND BillRun IN (' || in_list || ') Order By BillRun, Cus_Parent';
       open p_rc for v_select;
  end GET_EDIPLATINUM_ACCOUNTS;

Open in new window

0
Comment
Question by:sbornstein2
[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
  • 4
  • 2
6 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 24019114
What selects your cursor?
Is this (billrun VARCHAR2(50);) enough?
Is there only one column or more then one column?

Next remark:

See this:

EDI_PLATINUM.GET_EDIPLATINUM_ACCOUNTS(my_list_of_values,my_cursor);
        fetch my_cursor into billrun;

It is outside the loop.

So if you do not pass parameters the cursor is empty and this is not initialized at all.
Possibly it fails and this generates the error.
0
 

Author Comment

by:sbornstein2
ID: 24019142
actually there is more than one column it selects about 10 columns, I am just wanting to see the output to make sure it is getting the records correct overall.  Is there a way to do that?  The proc is posted above in the code area to see the column names.
0
 

Author Comment

by:sbornstein2
ID: 24019163
i wanted to get the results into a cursor fom the proc and then loop through and output the records so I can see the data coming out in the DBMS window.  If there is a way to see the records in grid format than that would be a plus.  So the proc outside the loop I thought was correct I dont want to call it each time just load the mycursor and then wanted to loop through it and output rows.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:sbornstein2
ID: 24019520
so it looks like there is something wrong with the proc.  It returns data if I pass in a valid code such as 'C12' returns data but if I pass in a value that does not return data for some reason I get the missing expression error.
0
 

Author Closing Comment

by:sbornstein2
ID: 31564306
figured it out thanks it was based on I was not putting in the right columns
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24019830
If you have more then one column in the SELECT you should pass the data in a ROW type variable like

 DECLARE
  TYPE emp_curtype IS
    REF CURSOR RETURN emp%ROWTYPE;
    emp_curvar emp_curtype;
BEGIN
   OPEN emp_curvar FOR
        SELECT * FROM emp;
END;
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 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