Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLSQL problem creating procedure inconsistent data types

BeefStuJul 26 2021 — edited Jul 26 2021

I am trying to create a procedure so the dates can be easily passed instead of modifying working code.
<html>
INSERT INTO timeoff
(employee_id, timeoff_date)
SELECT e.employee_id,
c.date_val
FROM employees e
INNER JOIN table(generate_dates_pipelined(date '2021-08-01', DATE '2021-08-10')) c
PARTITION BY ( e.employee_id )
ON (SUBSTR(e.work_days, TRUNC(c.date_val) - TRUNC(c.date_val, 'IW') + 1, 1) = 'Y')
WHERE NOT EXISTS (
SELECT 1
FROM holidays h
WHERE c.date_val = h.holiday_date
)
ORDER BY
e.employee_id,
c.date_val
;
</html>
Below are two different versions of the procedure date_test(), which are partially coded to remove holidays, which is the first step I'm working on.

In the first procedure everything seems to work fine. In the second procedure, which emulates my SQL, the one I want to replace the SQL with, I get errors when I try to create the procedure.

Can someone familiar with PLSQL tell me what's wrong with the second procedure as I can't seem to figure out the problem. I want the second procedure as close as possible to my working SQL and would like to understand what my problem is and how to fix it.

The error when I try to create the second procedure is as follows:

Errors: PROCEDURE DATE_TEST
Line/Col: 7/7 PL/SQL: SQL Statement ignored
Line/Col: 7/14 PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got DATE

Below is my test CASE. I'm testing in live sql so we can both have the same Oracle version. Thanks in advance to all that answer.
<html>
CREATE OR REPLACE TYPE obj_date IS OBJECT (
date_val DATE
);
CREATE OR REPLACE TYPE nt_date IS TABLE OF obj_date;
create or replace function generate_dates_pipelined(
p_from in date,
p_to in date
)
return nt_date
pipelined
is
begin
for c1 in (
with calendar (start_date, end_date ) as (
select trunc(p_from), trunc(p_to) from dual
union all
select start_date + 1, end_date
from calendar
where start_date + 1 <= end_date
)
select start_date as day
from calendar
) loop
pipe row (obj_date(c1.day));
end loop;

    return;  

end generate_dates_pipelined;

create table holidays(
holiday_date DATE not null,
holiday_name VARCHAR2(20),
constraint holidays_pk primary key (holiday_date),
constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )
);

INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME) WITH dts as (
select to_date('01-AUG-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'August 1st 2021' from dual union all
select to_date('05-AUG-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'August 5th 2021' from dual
)
SELECT * from dts;

    Create table employees(  
     employee\_id NUMBER(6),   
     first\_name VARCHAR2(20),  
     last\_name VARCHAR2(20),  
     card\_num VARCHAR2(10),  
work\_days VARCHAR2(7)  
    );  


     ALTER TABLE employees  
             ADD ( CONSTRAINT employees\_pk  
           PRIMARY KEY (employee\_id));  

INSERT INTO employees
(
EMPLOYEE_ID,
first_name,
last_name,
card_num,
work_days
)
WITH names AS (
SELECT 1, 'Jane', 'Doe', 'F123456', 'NYYYYYN' FROM dual UNION ALL
SELECT 2, 'Madison', 'Smith', 'R33432','NYYYYYN'
FROM dual UNION ALL
SELECT 3, 'Justin', 'Case', 'C765341','NYYYYYN'
FROM dual UNION ALL
SELECT 4, 'Mike', 'Jones', 'D564311','NYYYYYN' FROM dual
) SELECT * FROM names;

create table timeoff(
seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee_id NUMBER(6),
timeoff_date DATE,
timeoff_type VARCHAR2(1) DEFAULT 'V',
constraint timeoff_chk check (timeoff_date=trunc(timeoff_date, 'dd')),
constraint timeoff_pk primary key (employee_id, timeoff_date)
);

###### Works fine ########

CREATE OR REPLACE PROCEDURE date_test (start_date DATE, end_date DATE)
IS
l_res nt_date;
i NUMBER;
l_cnt NUMBER;
BEGIN

SELECT generate_dates_pipelined (start_date, end_date)
INTO l_res
FROM DUAL;

  DBMS\_OUTPUT.put\_line ('contents of L\_RES (all dates) ------------');  

  FOR i IN l\_res.FIRST .. l\_res.LAST  
  LOOP  
     DBMS\_OUTPUT.put\_line (l\_res (i).date\_val);  
  END LOOP;  

  DBMS\_OUTPUT.put\_line ('removing holidays -------------------------');  

  FOR i IN l\_res.FIRST .. l\_res.LAST  
  LOOP  
     SELECT MAX (1)  
       INTO l\_cnt  
       FROM holidays  
      WHERE holiday\_date = l\_res (i).date\_val;  

     DBMS\_OUTPUT.put\_line (  
        l\_res (i).date\_val || ': cnt = ' || l\_cnt || ' - delete it!');  

     IF l\_cnt = 1  
     THEN  
        l\_res.delete (i);  
     END IF;  
  END LOOP;  

  DBMS\_OUTPUT.put\_line ('contents of L\_RES (holidays excluded) ----');  

  i := l\_res.FIRST;  

  WHILE i IS NOT NULL  
  LOOP  
     DBMS\_OUTPUT.put\_line (l\_res (i).date\_val);  
     i := l\_res.NEXT (i);  
  END LOOP;  

END;

    EXEC date\_test(DATE '2021-08-01', DATE '2021-08-10');  

#### doesn't create ###

CREATE OR REPLACE PROCEDURE date_test (start_date DATE, end_date DATE)
IS
l_res nt_date;
BEGIN

  SELECT date\_val INTO  l\_res  
     FROM TABLE (  
       generate\_dates\_pipelined (start\_date, end\_date))  
 WHERE NOT EXISTS  
         (SELECT 1  
             FROM holidays h  
            WHERE date\_val = h.holiday\_date);  

  END;  


    EXEC date\_test(DATE '2021-08-01', DATE '2021-08-10');  

</html>

This post has been answered by James Su on Jul 26 2021
Jump to Answer

Comments

RichF
Hmm, is the following telling me that setBackground(), although implemented for List.AccessibleAWTListChild, doesn't actually work?
247:            public final void testSetBackground() {
248:                Color color = Color.DARK_GRAY;
249:                assertFalse(list.isBackgroundSet());
250:                aComp3.setBackground(color);
251:                assertEquals(color, aComp1.getBackground());
252:                assertEquals(color, aComp2.getBackground());
253:                assertEquals(color, aComp3.getBackground());
254:                assertTrue("setBackground() is delegated to List", list
255:                        .isBackgroundSet());
256:                assertEquals(color, list.getBackground());
257:            }
above found at:

* http://www.java2s.com/Open-Source/Java-Document/Apache-Harmony-Java-SE/java-package/java/awt/AccessibleAWTListChildTest.java.htm

I have no knowledge of Java accessibility, but I was excited when I found something that gave access to "list children", such as aComp3 above. They seem to be the individual list items themselves. However, line 254 above seems to suggest something .. unfortunate. One may go through the motions of setting the background color of a child, but the setBackground() is actually delegated to the List as a whole. Am I interpreting this correctly?

The above is part of a test suite implemented using:

* import junit.framework.TestCase;

I'm trying to avoid the learning curve to use JUnit when I already suspect the answer is "no". :(

-- Rich
pietblok
Answer
What you want to do can be easyly implemented in a swing JList, with the help of a customized renderer. So I would advise to rewrite your applet in swing and then use a JList. That way you can skip studying JUnit just for forcing your awt applet to do something awt was nor designed for and concentrate on swing GUI stuff. Below an example of how you might implement your JList:
import java.awt.*;
import java.math.BigInteger;
import java.util.Arrays;
import java.util.Comparator;

import javax.swing.*;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;

public class ColorList {

    public static void main(String[] args) {
	SwingUtilities.invokeLater(new Runnable() {

	    @Override
	    public void run() {
		new ColorList().createGUI();
	    }
	});
    }

    private void createGUI() {
	String[][] array = getArray();
	// sort the array as you wish
	Arrays.sort(array, new Comparator<String[]>() {

	    @Override
	    public int compare(String[] o1, String[] o2) {
		return o1[1].compareTo(o2[1]);
	    }

	});
	// create the JList
	final JList list = new JList(array);
	// set some adequate renderer
	list.setCellRenderer(new DefaultListCellRenderer() {
	    private static final long serialVersionUID = 1L;

	    @Override
	    public Component getListCellRendererComponent(JList list,
		    Object value, int index, boolean isSelected,
		    boolean cellHasFocus) {
		DefaultListCellRenderer renderer = (DefaultListCellRenderer) super
			.getListCellRendererComponent(list, value, index,
				isSelected, cellHasFocus);
		renderer.setText(((String[]) value)[1]);
		int color = new BigInteger(((String[]) value)[0], 16)
			.intValue();
		renderer.setBackground(new Color(color));
		renderer.setForeground(new Color(0xFFFFFFFF ^ color));
		return renderer;
	    }
	});
	final JLabel demoLabel = new JLabel(
		"Display this text in the selected color", JLabel.CENTER);
	list.addListSelectionListener(new ListSelectionListener() {

	    @Override
	    public void valueChanged(ListSelectionEvent e) {
		String[] selected = (String[]) list.getSelectedValue();
		demoLabel.setForeground(new Color(new BigInteger(selected[0],
			16).intValue()));
	    }
	});

	JFrame frame = new JFrame("ColorList");
	frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	frame.add(new JScrollPane(list), BorderLayout.LINE_START);
	frame.add(demoLabel, BorderLayout.CENTER);
	frame.pack();
	frame.setLocationRelativeTo(null);
	frame.setVisible(true);
    }

    private String[][] getArray() {
	return new String[][] {

	{ "000000", "Black" },

	{ "000080", "Navy Blue" },

	{ "0000C8", "Dark Blue" },

	{ "0000FF", "Blue" },

	{ "000741", "Stratos" },

	{ "00FF00", "Green" },

	{ "FF0000", "Red" },

	{ "FFFFF0", "Ivory" },

	{ "FFFFFF", "White" }

	};

    }

}
Piet
Marked as Answer by RichF · Sep 27 2020
RichF
Piet, wow, that's beautiful!! :)

What is amazing as the example you took time to write is that you pulled together info from two different threads in two different forums to do so. Thank you very much!

Time to learn Swing, enter the 21st century ...

-- Rich
User_64CKJ
RichF wrote:
Piet, wow, that's beautiful!! :)
Agreed.
..Time to learn Swing, enter the 21st century ...
++ ;)
pietblok
Andrew Thompson wrote:
RichF wrote:
Piet, wow, that's beautiful!! :)
Agreed.
..Time to learn Swing, enter the 21st century ...
++ ;)
So you see, in some cases, rare I do agree, just "giving the codes" may have some educational value ;-)
Piet
RichF
Piet, converting to Swing will not only be educational, it will likely be easier than my other option. What I was considering was either extending awt.List, or perhaps rewriting it if necessary. I'm quite glad I don't have to reinvent the list...

I posted an update to the other, "simpler way to sort 2-d string array?", thread:

* 1557000

Understated person which you are, you snuck in an answer to that question as well. Did you already know how to do that, or was the Javascript code I posted there an inspiration?

One thing puzzles me in your example. Why the need for BigInteger? The hex color values are only 24-bit, which should not cause problems with 32-bit integers, right? Oh, I see. the Integer constructors do not include a form with radix, but the BigInteger constructors do. You've programmed with Java before. ;)

-- Rich
pietblok
RichF wrote:
Understated person which you are, you snuck in an answer to that question as well. Did you already know how to do that, or was the Javascript code I posted there an inspiration?
No. I usually skip all text that I don't understand. Javascript falls into that category. It's just the way I would do it.
One thing puzzles me in your example. Why the need for BigInteger? The hex color values are only 24-bit, which should not cause problems with 32-bit integers, right? Oh, I see. the Integer constructors do not include a form with radix, but the BigInteger constructors do. You've programmed with Java before. ;)
Here I was educated by kajbj, reply number 5 in this thread http://forums.sun.com/thread.jspa?threadID=5450453&tstart=90 on the old forums. I internalized that reply for the rest of my life.

Piet
RichF
I've got the original awt code pretty-much ported to Swing now. The only new feature so far is that I've added toolTips to the GUI buttons. Neat!

I say "pretty-much" because I haven't found an equivalent way to do the awt update() method. Under awt, I had overridden update() to handle the situation of a partial canvas update, such as only needing to update the wheel but not everything else. Since Swing never calls update() I'm patching it in by hand:
    // was named paint() in awt version
    public void paintComponent(Graphics g)
    {
        Rectangle ulc;

	if (font == null)  defineFont(g);

	// handle partial repaints of specific items
	if (partialOnly(g))  return;

        if (r != null)
	    r = null;	/*  re-init to handle resizing of frame  */
	r = getBounds();

	// for clean double-buffering
	super.paintComponent(g);

        maxI = calcMaxI(intensity);

        g.setColor(background);
        g.fillRect(0, 0, r.width, r.height);
        ...
    }

    /**
    ***   There is no automatic update() with Swing.  Therefor we need
    *** to patch our own into paint to avoid complete redraws.
    **/
    public boolean partialOnly(Graphics g)
    {
	boolean	imDone = true;

        if (wedgeOnly)
        {
	    putDotOnWheel(g);
            paintWedge(g);
	    drawSnake(g);
	    drawSatSnake(g);
	    updateLumaBars(g);
            wedgeOnly = false;
        }
          else if (wheelOnly)
          { // update the wedge's intensity dot and label
            putDotNearWedge(g);
	    labelWedge(g);
	    miscLabels(g);
	    drawSnake(g);
	    drawSatSnake(g);
            // update the GUI items having to do with intensity
            updateGUIintensity(g);
	    updateLumaBars(g);

            paintWheel(g);
	    putDotOnWheel(g);
            wheelOnly = false;
          }
          ... // etc
          else
            imDone = false;	// allow full paint()

	return(imDone);
    }
There are two problems; perhaps both relate to double-buffering. The first is that the canvas goes wonky-white when the user clicks on the wheel. What is supposed to happen is simply the user sees another dot on the wheel for his new selected color. Forcing a complete redraw via any of the GUI buttons at the bottom sets things right. The canvas behaves itself from then on, at least until minimized or resized, at which point one needs to click a GUI button again. I'll be disabling resizing, but minimizing will still be allowed.

The second problem is that for some strange reason, the last selected GUI button and possibly it's tooltip will be replicated at the top of the painting canvas. Why this happens, I don't know -- they are two separate JPanels. Since a full paintComponent() refills the entire canvas, this effect was not visible until I started doing the partialOnly() stuff.

You can see what the heck I'm talking about by downloading:

* http://r0k.us/rock/Junk/SIHwheel.jar

Type "java -jar SIHwheel.jar" to run it.

What am I doing wrong?

Heh, I hadn't run it as an applet until I checked on the html page in the Junk folder:

* http://r0k.us/rock/Junk/SIHwheel.html

It appears to function great as an applet, without either problem mentioned above.

For anyone really interested, the full source is there as well:

* http://r0k.us/rock/Junk/SIHwheel.java
* http://r0k.us/rock/Junk/ntc.java

(File ntc.java is only necessary if you plan on running it from your own build.)

-- Rich
RichF
I have more or less fixed the first and most significant problem ("wonky-white"). I kludged-in two full paints:
    public boolean partialOnly(Graphics g)
    {
	boolean	imDone = true;
	if (resized > 0)
	{   // should enter on 1 or 2
	    imDone = false;
	    resized += 1;	// clock thru two forced-full paints
	    if (resized > 2)  resized = 0;
	}
        ...
    }
It is not elegant, but forcing the two full paints appears to do whatever magic the double-buffers need. I'm still looking for a non-kludgy solution, which I suspect will fix the second problem as well.
RichF
I decided to continue this thread in the Swing forum. My issues are Swing now (yay!), and the other forum is a lot more popular. See "conversion from awt to Swing, colored list widget, and awt update() method" at:

* 1685138

Please respond there. Thank you.
1 - 10

Post Details

Added on Jul 26 2021
19 comments
552 views