Home » SQL & PL/SQL » SQL & PL/SQL » Insert with Select Inner Join (Oracle versions 8 and 9)
Insert with Select Inner Join [message #402096] Thu, 07 May 2009 10:18 Go to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
Can anyone tell me what's wrong with this SQL statement?

INSERT INTO slblnup
2 ( coilid, fcenum, lnupnum, location, timstp
3 )
4 SELECT ProdData.CoilId,
5 CAST(ProdData.FceNum AS NUMBER),
6 HSMFMC.SLBLNUPSEQ.NEXTVAL,
7 'L',
8 CURRENT_TIMESTAMP
9 FROM System.ProdData
10 INNER JOIN System.tmpslb
11 ON ProdData.CoilId = tmpslb.CoilId;
INSERT INTO slblnup
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

The Select statement, outside of the Insert, generates these values:
SELECT ProdData.CoilId,
2 CAST(ProdData.FceNum AS NUMBER),
3 HSMFMC.SLBLNUPSEQ.NEXTVAL,
4 'L',
5 CURRENT_TIMESTAMP
6 FROM System.ProdData
7 INNER JOIN System.tmpslb
8 ON ProdData.CoilId = tmpslb.CoilId;

COILID CAST(PRODDATA.FCENUMASNUMBER) NEXTVAL '
------- ----------------------------- ---------- -
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
D36806 4 207 L
07-MAY-09 09.58.29.938630 AM -04:00

D36805 4 208 L
07-MAY-09 09.58.29.938630 AM -04:00

D36804 4 209 L
07-MAY-09 09.58.29.938630 AM -04:00

And I can enter all of these values individually, when I'm not counting to ten backwards and threatening this FRED with a 32oz cup of salt-water.
Re: Insert with Select Inner Join [message #402097 is a reply to message #402096] Thu, 07 May 2009 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Insert with Select Inner Join [message #402098 is a reply to message #402096] Thu, 07 May 2009 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Insert with Select Inner Join (Oracle versions 8 and 9)
What was the 1st version when Oracle supported ANSI INNER JOIN?

>Can anyone tell me what's wrong with this SQL statement?
>FROM System.ProdData
>INNER JOIN System.tmpslb
VERY, Very, very bad behavior to utilize SYSTEM schema!
Re: Insert with Select Inner Join [message #402099 is a reply to message #402096] Thu, 07 May 2009 10:29 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
I can't even get it to work with a single table:

INSERT INTO slblnup
(coilid, lnupnum, location, timstp)
(SELECT tmpslb.coilid, slblnupseq.nextval, 'L', CURRENT_TIMESTAMP);

Re: Insert with Select Inner Join [message #402100 is a reply to message #402096] Thu, 07 May 2009 10:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I can't even get it to work with a single table:
Not a known or valid Oracle error message/code.

Post results from
SELECT * FROM V$VERSION


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Insert with Select Inner Join [message #402101 is a reply to message #402099] Thu, 07 May 2009 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rboggess

Whatever you can say the answer is: ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

And it is very doubtful you get it for ALL 8i and 9i versions.

Regards
Michel
Re: Insert with Select Inner Join [message #402102 is a reply to message #402098] Thu, 07 May 2009 11:00 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
Ai! I'm goin ta hell fer sure now! Shocked

Seriously, though, I'm in development/discovery mode right now. In the end, what schema our customer uses on their box is beyond my control. (In fact, what schema the lead uses on our box is beyond my control.) I'm just the guy trying to write stored procedures to wire up the HMI in vb.net. Confused

Got it to work on the single case. It was dieing because timstp is a Date field, and I was trying to insert CURRENT_TIMESTAMP, instead of SYSDATE. Mad

If I can get the more general case to work, great, if not, I can do a followup Update. Thanks for taking the time to look. I really do appreciate it.
Re: Insert with Select Inner Join [message #402103 is a reply to message #402096] Thu, 07 May 2009 11:06 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
While you probably don't have much control over the schemas using system remains a very bad idea.
Oracle won't be impressed if you need support off of them and you've been using it.
Re: Insert with Select Inner Join [message #402120 is a reply to message #402103] Thu, 07 May 2009 12:06 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
cookiemonster wrote on Thu, 07 May 2009 12:06
While you probably don't have much control over the schemas using system remains a very bad idea.
Oracle won't be impressed if you need support off of them and you've been using it.


I know. Embarassed It's just that I can't fix that. And neither I nor anyone else is likely to contact oracle for support on this (our internal) server. It's only even powered up to test code against. And it would be next to nothing to wipe out the whole oracle installation and reinstall. (Except that VMS requires a bit more attention during the install than we have short of a dire emergency.)

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production

That's the version I'm currently testing against. But our customer is running 8. Or maybe 7. Confused You get the idea. And the problem is, I'm expected to make it work. So, patching our system, doesn't mean I can patch theirs.

And even if I could patch theirs, it doesn't mean I can patch the next customer's. From our perspective, it's better for us to build against the lowest common denominator we can to ensure functionality. (A real buzzkill.)

So, again, thank you one and all for even taking the time to read the post and bother to reply. I do appreciate it, but I think I have my answer -- use SYSDATE or CURRENT_DATE for a Date field.
Re: Insert with Select Inner Join [message #402122 is a reply to message #402120] Thu, 07 May 2009 12:16 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production

That's the version I'm currently testing against.

Which is the buggiest version Oracle ever released.

Quote:
But our customer is running 8. Or maybe 7.

So whatever you do you will never validate the sql for your customer. Especially that ansi syntax is not supported in these version.

Quote:
And even if I could patch theirs, it doesn't mean I can patch the next customer's. From our perspective, it's better for us to build against the lowest common denominator we can to ensure functionality. (A real buzzkill.)

Go to a supported version and tell your customer to also do it.

Regards
Michel

[Updated on: Thu, 07 May 2009 12:23]

Report message to a moderator

Previous Topic: Random Selections
Next Topic: Different CharcterSet are not recongnisable
Goto Forum:
  


Current Time: Fri Dec 09 23:30:01 CST 2016

Total time taken to generate the page: 0.08163 seconds