|
-
Alias?
In this case, does this simply not just give the table an alias of NOLOGGING?
Code:
SQL> SET TRIMSPOOL ON
SQL> SET LINES 129
SQL> DROP TABLE ERRORLOGGING;
DROP TABLE ERRORLOGGING
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE ERRORLOGGING
2 (primarykey NUMBER
3 , SOURCE VARCHAR2(30)
4 , result VARCHAR2(300)
5 , timestamp DATE
6 , wherewasi VARCHAR2(30)
7 , processed_count NUMBER
8 , process_id NUMBER
9 );
Table created.
SQL> CREATE SEQUENCE p_primarykey;
Sequence created.
SQL> CREATE SEQUENCE p_source;
Sequence created.
SQL> CREATE SEQUENCE p_result;
Sequence created.
SQL> INSERT /*+ APPEND */ INTO errorlogging NOLOGGING
2 ( primarykey
3 , source
4 , result
5 , timestamp
6 , wherewasi
7 , processed_count
8 , process_id )
9 SELECT
10 p_primarykey.NEXTVAL
11 , p_source.NEXTVAL
12 , SUBSTR(p_result.NEXTVAL, 1, 1000)
13 , SYSDATE
14 , NULL
15 , NULL
16 , 1
17 FROM dual;
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(1) FROM ERRORLOGGING;
COUNT(1)
----------
1
Ok, the above works as expected. I just simply used sequences for the parameters.
The following is going to fail... because errorlogging is now NOLOGGING.
Code:
SQL> INSERT /*+ APPEND */ INTO errorlogging NOLOGGING
2 ( errorlogging.primarykey
3 , errorlogging.source
4 , errorlogging.result
5 , errorlogging.timestamp
6 , errorlogging.wherewasi
7 , errorlogging.processed_count
8 , errorlogging.process_id )
9 SELECT
10 p_primarykey.NEXTVAL
11 , p_source.NEXTVAL
12 , SUBSTR(p_result.NEXTVAL, 1, 1000)
13 , SYSDATE
14 , NULL
15 , NULL
16 , 1
17 FROM dual;
, errorlogging.process_id )
*
ERROR at line 8:
ORA-00904: "ERRORLOGGING"."PROCESS_ID": invalid identifier
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(1) FROM ERRORLOGGING;
COUNT(1)
----------
1
Now, this will work, because I am calling the proper alias of NOLOGGING.
Code:
SQL> INSERT /*+ APPEND */ INTO errorlogging NOLOGGING
2 ( NOLOGGING.primarykey
3 , NOLOGGING.source
4 , NOLOGGING.result
5 , NOLOGGING.timestamp
6 , NOLOGGING.wherewasi
7 , NOLOGGING.processed_count
8 , NOLOGGING.process_id )
9 SELECT
10 p_primarykey.NEXTVAL
11 , p_source.NEXTVAL
12 , SUBSTR(p_result.NEXTVAL, 1, 1000)
13 , SYSDATE
14 , NULL
15 , NULL
16 , 1
17 FROM dual;
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(1) FROM ERRORLOGGING;
COUNT(1)
----------
2
SQL> SPOOL OFF
Or am I completely missing a post already saying this?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|