Solved

How to call cursor procedure and dbms output results?

Posted on 2009-03-30
6
1,227 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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 72
Migrate Oracle Database from ASM to Non-ASM on a Windows server. 1 45
Converting a row into a column 2 53
selective queries 7 29
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

773 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