Dimension table load - PLSQL question

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Dimension table load - PLSQL question

Dimension table load - PLSQL question

2005-09-29 - By Ranko Mosic

Reply: 1 2 3 4 5 6 7 8 9 10 >>

Thanks Thomas. This is good idea to work around single table problem and
MERGE command, but it will not work in this case.
I am not updating anything, only returning key if record exists. Inserting
otherwise.
Regards, Ranko.

On 9/29/05, Mercadante, Thomas F (LABOR) <
Thomas.Mercadante@(protected)> wrote:
>
> Ronko,
>
> Could you union all of the selects into one query and then use merge?
>
> Merge (table)
>
> Using
>
> select descr1
>
> from lkp_table1 where cd = p_cd1
>
> union
>
> select descr2
>
> from lkp_table2 where cd = p_cd2 etc
>
>
>
> etc???
>
>
>
> Would this work?
>
> Tom
>
> -- ---- ---- ---- ---- ---- --
>
> *From:* oracle-l-bounce@(protected) [mailto:
> oracle-l-bounce@(protected)] *On Behalf Of *Ranko Mosic
> *Sent:* Thursday, September 29, 2005 8:45 AM
> *To:* Paul Drake
> *Cc:* ORACLE-L
> *Subject:* Re: Dimension table load - PLSQL question
>
> Thanks for very helpful, no patronizing answer. If you've read more
> carefully what the problem is
>
> you'd see that MERGE can't work because it works on one table upserting
> another.
>
> I have one table being inserted from 6 tables.
>
> Thanks genius.
>
> On 9/28/05, *Paul Drake* <bdbafh@(protected)> wrote:
>
> On 9/28/05, *Ranko Mosic* <ranko.mosic@(protected) > wrote:
>
> Hi,
>
> requirement:
>
> - input parameters are codes p_cd1, p_cd2, ...
>
> - for these codes I get descriptions ( select descr1 into v_descr1 from
>
>
>
> lkp_table1 where cd = p_cd1; select descr2 into v_descr2 from lkp_table2 where
>
>
>
> cd = p_cd2 etc )
>
> - check if table t has records where t.descr1 = v_descr1
>
> and t.descr2 = v_descr2 and on and on ....;
>
> - if row exists return primary key;
>
> - if not then insert.
>
>
> What is the best way of doing it ( simplest ) ?
>
>
>
>
> Regards, Ranko.
>
>
>
> Ranko,
>
> "Simplest way" is to solicit opinions without using a search engine or
> checking the documentation.
> Its also usually "simplest" to leverage the existing provided
> functionality, rather than writing your own routines, error handling, etc.
>
> A search of "oracle 10.1 upsert" in google.com <http://google.com/> + "I'm
> feeling lucky" produced this for me.
> Perhaps you might get lucky too.
>
> Paul
>
> http://www.psoug.org/reference/merge.html
>
> MERGE <hint> INTO <table_name>
> USING <table_view_or_query>
> ON (<condition>)
> WHEN MATCHED THEN <update_clause>
> WHEN NOT MATCHED THEN <insert_clause>;
>
>

<div>Thanks Thomas. This is good idea to work around single table problem and
MERGE command, but it will not work in this case. </div>
<div>I am not updating anything, only returning key if record exists. Inserting
otherwise.</div>
<div> </div>
<div>Regards, Ranko.  </div>
<div>On 9/29/05, Mercadante, Thomas F (LABOR) <<a href="mailto:Thomas.Mercadante@(protected)
.state.ny.us">Thomas.Mercadante@(protected)</a>> wrote:
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">
<div>
Ronko,
 
Could you union all of the selects into one query
and then use merge?
 
Merge (table)
  Using<pre>
      select descr1 </pre><pre> 
   from lkp_table1 where cd = p_cd1</pre>
<pre>
   union</pre><pre>   select
descr2 </pre><pre>
    from lkp_table2 where cd = p_cd2 etc 
 
</pre><pre> </pre><pre>etc???</pre><pre>
 </pre><pre>Would this work?
</pre>
 
Tom
 
<div>
<div style="TEXT-ALIGN: center" align="center">
<hr align="center" width="100%" size="2">
</div>
From:
 <a onclick="return top.js
.OpenExtLink(window,event,this)" href="mailto:oracle-l-bounce@(protected)"
target="_blank">
oracle-l-bounce@(protected)</a> [mailto:<a onclick="return top.js.OpenExtLink
(window,event,this)" href="mailto:oracle-l-bounce@(protected)" target="_blank"
>oracle-l-bounce@(protected)</a>] 
On Behalf Of Ranko Mosic Sent:
 Thursday, September 29, 2005 8:45 AM To: Paul Drake 
Cc: ORACLE-L Subject: Re: Dimension table load - PLSQL question</div>
<div>
 
<div>
Thanks
for very helpful, no patronizing answer. If you've read more carefully what the
problem is</div>
<div>
you'd
see that MERGE can't work because it works on one table upserting another. </div>
<div>
I have
one table being inserted from 6 tables. </div>
<div>
Thanks
genius.  </div>
<div>
On
9/28/05, Paul Drake <<a
onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:bdbafh
@(protected)" target="_blank">
bdbafh@(protected)</a>> wrote: 
<div>
On
9/28/05, Ranko Mosic <<a
onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:ranko.mosic
@(protected)" target="_blank">
ranko.mosic@(protected) </a>> wrote: 
<div>
<blockquote style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP:
medium none; PADDING-LEFT: 6pt; PADDING-BOTTOM: 0in; MARGIN-LEFT: 4.8pt; BORDER
-LEFT: #cccccc 1pt solid; MARGIN-RIGHT: 0in; PADDING-TOP: 0in; BORDER-BOTTOM:
medium none">
<pre>Hi, 
requirement: 
- input parameters are codes p_cd1, p_cd2, ... 
- for these codes I get descriptions ( select descr1 into v_descr1 from 
</pre><pre> 
lkp_table1 where cd = p_cd1; select descr2 into v_descr2 from lkp_table2 where
</pre><pre> 
cd = p_cd2 etc ) 
- check if table t has records  where t.descr1 = v_descr1 
   and t.descr2 = v_descr2 and on and on ....; 
- if row exists return primary key; 
- if not then insert. 
 
What is the best way of doing it ( simplest ) ? </pre><pre> 
 
Regards, Ranko.  </pre>
 </blockquote></div>
 
</div>
Ranko, "Simplest way" is to solicit
opinions without using a search engine or checking the documentation.
 Its also usually "simplest" to leverage the existing provided
functionality, rather than writing your own routines, error handling, etc. 
 A search of "oracle 10.1 upsert" in <a onclick="return top.js
.OpenExtLink(window,event,this)" href="http://google.com/" target="_blank">
google.com</a> + "I'm feeling lucky" produced this for me. Perhaps you might get lucky too. Paul <a onclick="return top.js
.OpenExtLink(window,event,this)" href="http://www.psoug.org/reference/merge.html
" target="_blank">
http://www.psoug.org/reference/merge.html</a> 
<table style="BACKGROUND: #d8d8c4; WIDTH: 90%" cellpadding="0" width="90%"
bgcolor="#d8d8c4" border="1">
<tbody>
<tr>
<td style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt;
WIDTH: 25%; PADDING-TOP: 0.75pt" width="25%" rowspan="2">
 </td>
<td style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt;
WIDTH: 75%; PADDING-TOP: 0.75pt" width="75%">
MERGE <hint> INTO <table_name> USING <table_view_or
_query> ON (<condition>) WHEN MATCHED THEN <update_clause>
 WHEN NOT MATCHED THEN <insert_clause>; </td>
<td style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT:
medium none; BORDER-BOTTOM: medium none" width="0">
 </td></tr>
<tr height="19">
<td style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP: medium
none; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; PADDING
-TOP: 0in; BORDER-BOTTOM: medium none" width="386">
 </td>
<td style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT:
medium none; BORDER-BOTTOM: medium none" width="0" height="19"></td></tr><
/tbody></table>
 </div>
 </div></div></blockquote></div>