Solved

How to call cursor procedure and dbms output results?

Posted on 2009-03-30
6
1,229 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 500 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
Independent Software Vendors: 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!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Include a logo in email body using Oracle utl_mail 11 57
oracle collections 2 29
oracle query 3 26
error in oracle form 11 28
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

730 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