Solved

How to call cursor procedure and dbms output results?

Posted on 2009-03-30
6
1,223 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
figured it out thanks it was based on I was not putting in the right columns
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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.

Join & Write a Comment

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now