Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to call cursor procedure and dbms output results?

Posted on 2009-03-30
6
Medium Priority
?
1,236 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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

810 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