ORACLE PL/SQL
ORACLE PL/SQL Interview Questions
Interview Questions, Answers, and Explanations
By Equity Press
ORACLE PL/SQL: Interview Questions, Answers, and Explanations
ISBN 978-1-60332-282-9
Smashwords Edition
© 2006 Equity Press all rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, without either the prior written permission of the publisher or a license permitting restricted copying in the United States or abroad.
The programs in this book have been included for
instructional value only. They have been tested with
care but are not guaranteed for any particular purpose.
The publisher does not offer any warranties or
Representations and does not accept any liabilities with
respect to the programs.
Trademark notices: Oracle, Oracle PL/SQL, and Oracle Inc are registered trademarks of Oracle Incorporated. This publisher gratefully acknowledges SAP permission to use its trademark in this publication. SAP AG is not the publisher of this book and is not responsible for it under any aspect of the law.
Please visit our website at www.oracookbook.com
Question 01: On DBMS METADATA.get_ddl
Question 02: Pipe select query
Question 03: ORA-29278: SMTP transient error: 421 Service not available
Question 04: Multiple Row Updates
Question 07: Copy data from .dat files to oracle?
Question 08: Error while using dbms_lob.fileopen
Question 09: Procedure execution hierarchy
Question 10: Genius trigger thoughts needed
Question 11: Reading a string from a file until end of line
Question 12: Pass the parameter to the cursor
Question 13: TRIGGER 9i – CLEAR or REVERT
Question 14: What kind of mistake is this?
Question 15: Put 30+ select statements in 1 proc with 1 refcursor
Question 16: Function to return database name
Question 17: Default permission for files written by UTL_FILE
Question 18: Separating values from string
Question 19: Procedures under package
Question 20: Executing shell scripts and other UNIX commands
Question 23: AUTHID CURRENT_USER
Question 24: Comparing numeric arrays
Question 27: Creating or Locating Efficient Function
Question 28: Regarding output generation
Question 29: Execute windows copy cmd within PL/SQL block
Question 30: Suppress additional message lines in raise application error
Question 31: Regarding information about files
Question 32: Stored procedures returning column
Question 33: Ref Cursor Return Parameter
Question 34: Difference between Execute and DBMS_SQL
Question 35: Can’t see any data
Question 36: Replacing Null Values from existing values in table
Question 37: Fetch value from record type using dyn sql
Question 38: pl/sql packages source
Question 39: Writing debug messages into an external (.txt) file using utl_file package
Question 41: See data from a table dynamically
Question 42: SQL*Plus not responding to UTL_REF package
Question 43: Generating a number for a name
Question 45: To load few columns from Excel sheet to Oracle table
Question 46: Execution Time Problem of a stored procedure
Question 48: Use of one or two triggers
Question 49: ORA 12839 cannot modify an object in parallel after modifying it
Question 50: Trigger is used, data output not coming as desired
Question 51: Initialize populated row type variable
Question 52: Cursor taking a lot of time
Question 54: Eliminating duplicate rows while using SQL loader
Question 55: Date format for a column in External Tables
Question 56: Repeating Group Removal
Question 57: Looping through lines
Question 58: Remove a Specific Character
Question 59: No need for cursors
Question 60: Exception which violates primary key constraint
Question 61: Package dependency - recompile
Question 62: Input variable character
Question 63: Trigger creating two rows rather than just one
Question 64: Can't call PL/SQL Package in VB
Question 65: Single Column Multiple Row Data
Question 66: Pass a ref cursor value from Oracle to MS access Database
Question 67: Execution of scripts
Question 68: Erroneous trigger
Question 70: Use the package DBMS_TRANSACTION
Question 71: Numeric or value error
Question 72: Overcoming Mutating Table Error
Question 73: Procedures for calling function
Question 74: PL/SQL initialize ()
Question 75: PLS-00049 - Bad bind variable
Question 76: Uploading of data from Oracle database table to an excel file
Question 77: Dynamic SQL for an IF statement
Question 78: PARTITION CAN NOT BE SPLIT
Question 79: UTL_FILE handling ISO Latin 1
Question 80: To achieve Parallel Processing using procedure
Question 81: Capture SQL in trigger
Question 82: FORALL with EXECUTE IMMEDIATE
Question 83: Oracle date function
Question 84: Bulk select with Dynamic SQL
Question 85: Inserting and displaying image from database
Question 86: Error procedure + package variables
Question 88: Trigger code error
Question 89: Get the latest records
Question 91: Returning a Ref Cursor
Question 92: Use of returned cursor in another PL/SQL proc
Question 93: Dynamic selection of columns
Question 94: Dynamic insert procedure
Question 95: Procedure/Function Execution Time
Question 96: Fetch bulk records subjected to very frequent updation
Question 97: Delete Old Records
Question 99: “TO_DATE” behaving differently for 2049 and 2050
Question 100: Error while refreshing materialized view through DBMS_MVIEW
Question 01: On DBMS METADATA.get_ddl
How do I copy constraints from remote database 'TEST' into local copy of these tables?
ERROR Description:
ERROR at line 1:
ORA-31603: object "ORDER_ITEMS" of type TABLE not found in schema "HAHMED2"
ORA-06512: at "SYS.DBMS_METADATA", line 1511
ORA-06512: at "SYS.DBMS_METADATA", line 1548
ORA-06512: at "SYS.DBMS_METADATA", line 1864
ORA-06512: at "SYS.DBMS_METADATA", line 2684
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
ORA-06512: at line 29
Using script as below:
DECLARE
V_TABLENAME all_tables.table_name%TYPE;
l_ddl varchar2(32000);
V_COPYTABLENAME all_tables.table_name%TYPE;
v_tableExists boolean := false;
CURSOR c1 is
SELECT table_name FROM all_tables WHERE dropped = 'NO' and owner = 'TEST';
CURSOR c2 Is
Select table_name FROM user_tables where dropped = 'NO';
sql_stmt varchar2(200);
BEGIN
FOR rec1 IN c1 LOOP
v_tablename := rec1.table_name;
for rec2 in c2 loop
if rec2.table_name = (rec1.table_name || '_COPY') then
v_tableExists := true;
end if;
END LOOP;
if v_tableExists = false then
execute immediate 'create table ' || v_tablename || '_COPY as select * from brendan.' || v_tablename;
---CONSTRAINT COPY -----
SELECT DBMS_METADATA.GET_DDL ('TABLE', v_tablename)
INTO v_copytablename
FROM all_tables
where owner='TEST';
----TRIGGERS COPY --------
For trg in (select trigger_name from all_triggers where owner= 'BRENDAN' and table_name = v_tablename ) loop
l_ddl:= cast(replace(replace(dbms_metadata.get_ddl( 'TRIGGER', trg.trigger_name),v_tablename,v_copytablename),
trg.trigger_name,substr(v_copytablename||trg.trigger_name, 1 , 30)) as varchar2);
execute immediate substr(l_ddl, 1, instr(l_ddl,'ALTER TRIGGER')-1);
END LOOP;
END IF;
END LOOP;
END;
A. If the schema listed have the table as below:
ORA-31603: object "ORDER_ITEMS" of type TABLE not found in schema "HAHMED2"
Do not select multiple calls/copies of get_ddl from all_tables especially since you are passing the same table name each time.
If it is not the same table name, you have not closed out of your outer loop. Please format your posts with appropriate code tags. Multiple results can't be used with a select into construct.
Try to avoid nested cursor for loops looping line by line in PLSQL, do things as single queries.
Better yet, go through the PLSQL user guide to become more familiar with the language. Try to write statements that generate SQL statements through the get_ddl calls, and have those written out to a file or the screen as a first step.
Question 02: Pipe select query
How can I perform a select operation on few tables in a DB, specifically placing a pipe (l) in between the column names and their corresponding data? The following is the kind of output I need:
JOB_ID|JOB_TITLE|MIN_SALARY|MAX_SALARY <--Column Names
AD_PRES|President|20000|40000
AD_VP|Administration Vice President|15000|30000
AD_ASST|Administration Assistant|3000|6000
FI_MGR|Finance Manager|8200|16000
FI_ACCOUNT|Accountant|4200|9000
...
...
...
A. Spool it to a file and do a batch conversion. Another option would be string concatenation:
SQL> select the_value
2 from ( SELECT concat_all(concat_expr(column_name,'|')) the_value
3 , 1 rnk
4 FROM user_tab_columns
5 WHERE table_name = 'JOBS'
6 UNION ALL
7 SELECT job_id||'|'||job_title||'|'||min_salary||'|'||max_salary the_value
8 , 2 rnk
9 FROM jobs
10 ORDER BY rnk
11 )
12 /
THE_VALUE
----------------------------------------------------------------------------------------------------
JOB_ID|JOB_TITLE|MIN_SALARY|MAX_SALARY
AD_PRES|President|20000|40000
AD_VP|Administration Vice President|15000|30000
AD_ASST|Administration Assistant|3000|6000
FI_MGR|Finance Manager|8200|16000
FI_ACCOUNT|Accountant|4200|9000
AC_MGR|Accounting Manager|8200|16000
AC_ACCOUNT|Public Accountant|4200|9000
SA_MAN|Sales Manager|10000|20000
SA_REP|Sales Representative|6000|12000
PU_MAN|Purchasing Manager|8000|15000
PU_CLERK|Purchasing Clerk|2500|5500
ST_MAN|Stock Manager|5500|8500
ST_CLERK|Stock Clerk|2000|5000
SH_CLERK|Shipping Clerk|2500|5500
IT_PROG|Programmer|4000|10000
MK_MAN|Marketing Manager|9000|15000
MK_REP|Marketing Representative|4000|9000
HR_REP|Human Resources Representative|4000|9000
PR_REP|Public Relations Representative|4500|10500
In order to get the concatenation string for the second part, use a slightly modified version of the first part:
SQL> SELECT concat_all(concat_expr(column_name,'||''|''||')) the_value
2 FROM user_tab_columns
3 WHERE table_name = 'JOBS'
4 /
THE_VALUE
----------------------------------------------------------------------------------------------------
JOB_ID||'|'||JOB_TITLE||'|'||MIN_SALARY||'|'||MAX_SALARY
Another variant of concatenation is called STRAGG. The numeric and date columns should have a TO_CHAR wrapped around them. A decode could solve that:
SQL> SELECT concat_all(concat_expr(decode( data_type
2 ,'VARCHAR2', column_name
3 ,'TO_CHAR('||column_name||')'
4 )
5 , '||''|''||')) the_value
6 FROM user_tab_columns
7 WHERE table_name = 'JOBS'
8 /
THE_VALUE
--------------------------------------------------------------------------------
JOB_ID||'|'||JOB_TITLE||'|'||TO_CHAR(MIN_SALARY)||'|'||TO_CHAR(MAX_SALARY)
Question 03: ORA-29278: SMTP transient error: 421 Service not available
I need to know why I get an error after inputting the following:
SQL> l
1 BEGIN
2 demo_mail.mail(
3 sender => 'Me <qalandar.hotmail.com>',
4 recipients => 'Someone <qalandar.gmail.com>, ' ||
5 '"Another one" <qalandark@hotmail.com>',
6 subject => 'Test',
7 message => 'Hi! This is a test.');
8* END;
SQL> /
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
A. Your server requires log-in information using UTL_SMTP.COMMAND (V_COON, 'AUTH LOGIN') where V_COON is connection variable. Give another two command lines for log-in and password.
Question 04: Multiple Row Updates
The table I am using has indexes but not on columns which are getting updated.
How do I make the process of making multiple row updates faster?
A. Indexes on the columns being updated would render it even slower since oracle additionally needs to update the indexes (not only the table data).
Using indexes make data retrieval faster and since you have no WHERE clause in the UPDATE statement, it will always update every record in your table. Dividing your tables by dates, and having appropriate indexes and WHERE clause will help the process faster.
How do I take the contents of my database into an Excel sheet/spread sheet?
A. You can create an ODBC connection directing towards your oracle database. Open Excel worksheet and click to get external data (import data). Use the ODBC connection to get database query and get all the data that you want into your excel worksheet.
I got a form where three long running queries are started successively by pressing a button. The queries are independent from each other.
Is there a way to start the three queries parallel on a single processor machine?
A. Yes, but not exactly at the same time and surely not one after another. Use a scheduler which would run all three of them practically but you need time to do this.
Question 07: Copy data from .dat files to oracle?
How can I copy data from .dat files to oracle?
A. You can choose either by using EXTERNAL TABLES or a “DTS” (Microsoft Data Transfer Service). Another possibility to do this is an SQL*Loader.
Question 08: Error while using dbms_lob.fileopen
Please help me to store an avi file on my database. I did the following but got an error response.
1- I've created a director name as 'sounds_dir'
2- I made a procedure to load the bfile:
--------------------------------------
create or replace procedure p_load (ploc varchar2)
is
vfile bfile;
begin
vfile := bfilename(ploc,'002.avi');
dbms_lob.fileopen(vfile);
dbms_lob.fileclose(vfile);
end;
-------------------------------------------
sql> execute p_load('sound_dir');
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "SYS.P_LOAD", line 6
ORA-06512: at line 1
A. SOUNDS_DIR must be in upper case, when you pass it to the procedure as a parameter. Like all other objects, by default, it is created in upper case.
scott@ORA92> create or replace directory sounds_dir as 'c:\oracle'
2 /
Directory created.
scott@ORA92> create or replace procedure p_load (ploc varchar2)
2 is
3 vfile bfile;
4 begin
5 vfile := bfilename(ploc,'002.avi');
6 dbms_lob.fileopen(vfile);
7 -- additional code to do something with it here
8 dbms_lob.fileclose(vfile);
9 exception
10 when others then dbms_lob.fileclose (vfile);
11 RAISE;
12 end;
13 /
Procedure created.
scott@ORA92> execute p_load('SOUNDS_DIR')
PL/SQL procedure successfully completed.
Question 09: Procedure execution hierarchy
I have an issue, wherein I need to find out procedure execution hierarchy.
Let’s say Proc A call Proc B
Proc B call Proc C
Proc C call Proc D
In Proc D I encounter an exception, and I need to insert into an error table the hierarchy of the procedure calls, in the error message.
I.e. I want to say "Encountered error in D: execution flow Is A.B.C.D"
How can I generate "A.B.C.D", without having to write extra code? Is there some internal table which I can use to figure the procedure execution hierarchy?
A. You can use the Oracle supplied dbms_utility.foramt_call_stack, and/or Tom Kyte's who_am_i and who_called_me, available at:
http://asktom.oracle.com/~tkyte/who_called_me/index.html
Incorporate all three in the demonstration below.
scott@ORA92> CREATE TABLE error_tab
2 (who_am_i VARCHAR2(61),
3 who_called_me VARCHAR2(61),
4 call_stack CLOB)
5 /
Table created.
scott@ORA92>
scott@ORA92> CREATE OR REPLACE PROCEDURE d
2 AS
3 v_num NUMBER;
4 v_owner VARCHAR2(30);
5 v_name VARCHAR2(30);
6 v_line NUMBER;
7 v_caller_t VARCHAR2(100);
8 BEGIN
9 select to_number('a') into v_num from dual; -- cause error for testing
10 EXCEPTION
11 WHEN OTHERS THEN
12 who_called_me (v_owner, v_name, v_line, v_caller_t);
13 INSERT INTO error_tab
14 VALUES (who_am_i,
15 v_owner || '.' || v_name,
16 dbms_utility.format_call_stack);
17 END d;
18 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PROCEDURE c
2 AS
3 BEGIN
4 d;
5 END c;
6 /
Procedure created.
scott@ORA92> CREATE OR REPLACE PROCEDURE b
2 AS
3 BEGIN
4 c;
5 END b;
6 /
Procedure created.
scott@ORA92> CREATE OR REPLACE PROCEDURE a
2 AS
3 BEGIN
4 b;
5 END a;
6 /
Procedure created.
scott@ORA92> execute a PL/SQL procedure successfully completed.
scott@ORA92> COLUMN who_am_i FORMAT A13
scott@ORA92> COLUMN who_called_me FORMAT A13
scott@ORA92> COLUMN call_stack FORMAT A45
scott@ORA92> SELECT * FROM error_tab
2 /
To make a few quick modifications to Tom Kyte's who_called_me procedure to create a flow function that is close to what you asked for, include revised demonstration below.
scott@ORA92> CREATE TABLE error_tab
2 (who_am_i VARCHAR2(61),
3 who_called_me VARCHAR2(61),
4 flow VARCHAR2(2000),
5 call_stack CLOB)
6 /
Table created.
scott@ORA92> create or replace function flow
2 return varchar2
3 as
4 call_stack varchar2(4096) default dbms_utility.format_call_stack;
5 n number;
6 found_stack BOOLEAN default FALSE;
7 line varchar2(255);
8 cnt number := 0;
9 v_flow varchar2(2000);
10 caller_t varchar2(30);
11 owner varchar2(30);
12 name varchar2(30);
13 lineno number;
14 begin
15 --
16 loop
17 n := instr( call_stack, chr(10) );
18 exit when ( n is NULL or n = 0 );
19 --
20 line := substr( call_stack, 1, n-1 );
21 call_stack := substr( call_stack, n+1 );
22 --
23 if ( NOT found_stack ) then
24 if ( line like '%handle%number%name%' ) then
25 found_stack := TRUE;
26 end if;
27 else
28 cnt := cnt + 1;
29 -- cnt = 1 is ME
30 -- cnt = 2 is MY Caller
31 -- cnt = 3 is Their Caller
32 if ( cnt >= 2 ) then
33 lineno := to_number(substr( line, 13, 6 ));
34 line := substr( line, 21 );
35 if ( line like 'pr%' ) then
36 n := length( 'procedure ' );
37 elsif ( line like 'fun%' ) then
38 n := length( 'function ' );
39 elsif ( line like 'package body%' ) then
40 n := length( 'package body ' );
41 elsif ( line like 'pack%' ) then
42 n := length( 'package ' );
43 elsif ( line like 'anonymous%' ) then
44 n := length( 'anonymous block ' );
45 else
46 n := null;
47 end if;
48 if ( n is not null ) then
49 caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
50 else
51 caller_t := 'TRIGGER';
52 end if;
53
54 line := substr( line, nvl(n,1) );
55 n := instr( line, '.' );
56 owner := ltrim(rtrim(substr( line, 1, n-1 )));
57 name := ltrim(rtrim(substr( line, n+1 )));
58 v_flow := v_flow || '->' || caller_t || ' ' || owner || '.' || name;
59 end if;
60 end if;
61 end loop;
62 return ltrim (v_flow, '->');
63 end;
64 /
Function created.
scott@ORA92> show errors
No errors.
scott@ORA92>
scott@ORA92> CREATE OR REPLACE PROCEDURE d
2 AS
3 v_num NUMBER;
4 v_owner VARCHAR2(30);
5 v_name VARCHAR2(30);
6 v_line NUMBER;
7 v_caller_t VARCHAR2(100);
8 BEGIN
9 select to_number('a') into v_num from dual; -- cause error for testing
10 EXCEPTION
11 WHEN OTHERS THEN
12 who_called_me (v_owner, v_name, v_line, v_caller_t);
13 INSERT INTO error_tab
14 VALUES (who_am_i,
15 v_owner || '.' || v_name,
16 flow,
17 dbms_utility.format_call_stack);
18 END d;
19 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PROCEDURE c
2 AS
3 BEGIN
4 d;
5 END c;
6 /
Procedure created.
scott@ORA92> CREATE OR REPLACE PROCEDURE b
2 AS
3 BEGIN
4 c;
5 END b;
6 /
Procedure created.
scott@ORA92> CREATE OR REPLACE PROCEDURE a
2 AS
3 BEGIN
4 b;
5 END a;
6 /
Procedure created.
scott@ORA92> execute a
PL/SQL procedure successfully completed.
scott@ORA92> COLUMN who_am_i FORMAT A13
scott@ORA92> COLUMN who_called_me FORMAT A13
scott@ORA92> COLUMN call_stack FORMAT A45
scott@ORA92> SELECT * FROM error_tab
2 /
Question 10: Genius trigger thoughts needed
I want to save data to a table (TBLSDSHIPMENTS) after a new record is saved in table (SHIPPER_LINE). Because of other triggers and restrictions on this table, I am not able to save all the data I need to a table with an after insert trigger.
Some of the critical fields that are needed can be saved but not all. I have used a vb app with a timer to monitor a table and perform the updates. Is there a work-around or timer that can be used to go in, check and populate the TBLSDSHIPMENTS table?
A. The design is flawed if this is a real world business requirement. Always implement a private JOB_QUEUE table that is updated by the INSERT trigger and processed later by a real DBMS_JOB entry.
Question 11: Reading a string from a file until end of line
There is a flat file "param.txt" which has got some parameters set in it. (no repetitions of parameter-name)
...<param1>......................
...<param2>.... ..................
............... ..................
Recipient a@b.com, c@d.com
............... ..................
...<paramn>......................
I have to open param.txt and search for "Recipient" word, and then copy the words thereafter until end-of-line (i.e., the email addresses).
How do I call (and pass arguments to) a java method from a PL/SQL procedure?
A. Using UTL_FILE built-in package can perform file I/O. Check the documentation or search the web, there are plenty of examples.
If possible you could use the file as an external table.
You can't just call a method of an externally running java application. You can write a stored procedure in java (which is stored in the database as pl/sql and is running in the oracle's internal JVM). You can't "communicate" with a JVM running on the oracle's host system.
Question 12: Pass the parameter to the cursor
Kindly refer to procedure below and help find any mistakes that I have done. How do I pass the parameter to the cursor? Creating the cursor only will take the value and create, or while opening the cursor, the value will be taken and data will be fetched. Help me find the solution to this.
create or replace PROCEDURE S_vid AS
SUP_CODE VARCHAR2(8);
SUP_NAME VARCHAR2(28);
ITM_CODE VARCHAR2(10);
ITM_DESC1 VARCHAR2(24);
ITM_DESC2 VARCHAR2(24);
FAC_CODE VARCHAR2(9);
FAC_NAME VARCHAR2(28);
DISP_INDENT NUMBER(20,3);
sin_st_date date;
sin_end_date date;
day1 varchar2(9);
CURSOR SCOMP(s_st_date date,s_end_date date) IS
SELECT SIN_SUP_CODE,SUP_NAME,SIN_ITM_CODE,ITM_DESCRIPTION1,
ITM_DESCRIPTION2,SIN_FAC_CODE,FAC_NAME,SUM(SIN_DISP_INDENT)
FROM
SUP_INDENT,SUP_MSTR, FSI_REF, ITM_MSTR, FAC_MSTR
WHERE
SIN_IND_DATE >= TO_DATE(TO_CHAR(s_st_date,'DD-MON-YYYY')) AND
SIN_IND_DATE <= TO_DATE(TO_CHAR(s_end_date,'DD-MON-YYYY')) AND
SIN_FAC_CODE = FSI_FAC_CODE AND
SIN_ITM_CODE = FSI_ITM_CODE AND
SIN_SUP_CODE = FSI_SUP_CODE AND
SIN_DISP_INDENT > 0 AND
SIN_FAC_CODE = FAC_CODE AND
SIN_ITM_CODE = ITM_CODE AND
SIN_SUP_CODE = SUP_CODE AND
SUP_PC_CODE = 'PP' AND
ITM_PC_CODE = 'PP'
GROUP BY
SIN_SUP_CODE, SUP_NAME, SIN_ITM_CODE,ITM_DESCRIPTION1, ITM_DESCRIPTION2, SIN_FAC_CODE,FAC_NAME;
BEGIN
select to_char(sysdate,'DAY') into day1 from dual;
if day1 = 'WEDNESDAY' then
select sysdate - 5 into sin_st_date from dual;
select sysdate+1 into sin_end_date from dual;
elsif day1 = 'THURSDAY' then
select sysdate - 6 into sin_st_date from dual;
select sysdate into sin_end_date from dual;
end if;
OPEN SCOMP(sin_st_date,sin_end_date);
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('"SUP_CODE","SUP_NAME","ITM_CODE","ITM_DESCRIPTION","FAC_CODE","FAC_NAME","DISPATCH_INDENT"');
LOOP
FETCH SCOMP INTO SUP_CODE, SUP_NAME, ITM_CODE,
ITM_DESC1, ITM_DESC2, FAC_CODE, FAC_NAME, DISP_INDENT;
EXIT WHEN SCOMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('"'||SUP_CODE||'","'||SUP_NAME||'","'||ITM_CODE||'","'||ITM_DESC1||' '||ITM_DESC2||'","'|| FAC_CODE||'","'||FAC_NAME||'","'||DISP_INDENT||'",'||FAC_CODE||SUP_CODE||ITM_CODE);
END LOOP;
END;
A. Although unformatted, it appears that procedure is correctly written (i.e. doesn't have syntax errors).
Why doesn't it return any records? As you enabled SERVEROUTPUT, perhaps cursor isn't fetching anything. Besides, as it is Thursday here (where I live), this code will work for several more hours. What then and what values will 'sin_st_date' and 'sin_end_date' have tomorrow? Is the procedure designed to be run on Wednesdays and/or Thursdays?
Try it with.....
select to_char(sysdate,'fmDAY') into day1 from dual;
OR
select trim(to_char(sysdate,'DAY')) into day1 from dual;
instead of
select to_char(sysdate,'DAY') into day1 from dual;
because..
>SELECT TO_CHAR(SYSDATE,'DAY')||'!!!' FROM dual;
TO_CHAR(SYSD
------------
THURSDAY !!!
>SELECT TO_CHAR(SYSDATE,'fmDAY')||'!!!' FROM dual;
TO_CHAR(SYSD
------------
THURSDAY!!!
Another possible solution will be to truncate a date to remove the time portion. Furthermore, TO_DATE without an explicit DATE format is not very robust coding.
It seems you are querying from past Friday to next Thursday. You could rewrite the query using sysdate or TRUNC(sysdate) in your case, and the NEXT_DAY built-in to fetch the next Thursday. Start_date would be NEXT_DAY(TRUNC(SYSDATE-7),'FRI') and stop_date would be NEXT_DAY(TRUNC(SYSDATE-1),'THU'). No need for IF constructions.
Question 13: TRIGGER 9i – CLEAR or REVERT
I have a 9i AFTER INSERT trigger below which populates another table only if a condition is met. I need to CLEAR or REVERT the prior trigger commands if that condition (IF mCUSTOMER_ID='97' AND mUSER_SHIPPED_QTY >0) is not met. Other triggers on the table are not saving a record in this table, due to my trigger.
DECLARE
mSHIPPED_DATE SYSADM.SHIPPER.SHIPPED_DATE%type;
mPACKLIST_ID SYSADM.SHIPPER.PACKLIST_ID%type;
mLINE_NO SYSADM.SHIPPER_LINE.LINE_NO%type;
mCUST_ORDER_ID SYSADM.SHIPPER_LINE.CUST_ORDER_ID%type;
mCUST_ORDER_LINE_NO SYSADM.SHIPPER_LINE.CUST_ORDER_LINE_NO%type;
mORDER_QTY SYSADM.CUST_ORDER_LINE.ORDER_QTY%type;
mUSER_SHIPPED_QTY SYSADM.SHIPPER_LINE.USER_SHIPPED_QTY%type;
mQTY_BO varchar2(10);
mDESCRIPTION SYSADM.PART.DESCRIPTION%type;
mCUSTOMER_ID SYSADM.CUSTOMER_ORDER.CUSTOMER_ID%type;
mCUSTOMER_PO_REF SYSADM.CUSTOMER_ORDER.CUSTOMER_PO_REF%type;
mSHIPPED_TIME varchar2(30);
mseqNo NUMBER;
mPART_ID SYSADM.CUST_ORDER_LINE.PART_ID%type;
mTOTALSHIPPEDQTY SYSADM.CUST_ORDER_LINE.TOTAL_SHIPPED_QTY%type;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT
SYSADM.SHIPPER.SHIPPED_DATE,
SYSADM.SHIPPER.PACKLIST_ID,
:OLDDATA.LINE_NO,
:OLDDATA.CUST_ORDER_ID,
:OLDDATA.CUST_ORDER_LINE_NO,
SYSADM.CUST_ORDER_LINE.ORDER_QTY,
:OLDDATA.USER_SHIPPED_QTY,
SYSADM.PART.DESCRIPTION,
SYSADM.CUSTOMER_ORDER.CUSTOMER_ID,
SYSADM.CUSTOMER_ORDER.CUSTOMER_PO_REF,
SYSADM.SHIPPER.SHIPPED_TIME,
SYSADM.CUST_ORDER_LINE.PART_ID,
SYSADM.CUST_ORDER_LINE.TOTAL_SHIPPED_QTY
INTO mSHIPPED_DATE, mPACKLIST_ID, mLINE_NO, mCUST_ORDER_ID, mCUST_ORDER_LINE_NO, mORDER_QTY, mUSER_SHIPPED_QTY, mDESCRIPTION, mCUSTOMER_ID, mCUSTOMER_PO_REF, mSHIPPED_TIME, mPART_ID, mTOTALSHIPPEDQTY
FROM SYSADM.SHIPPER, SYSADM.CUST_ORDER_LINE, SYSADM.CUSTOMER_ORDER, SYSADM.CUSTOMER, SYSADM.PART
WHERE SYSADM.SHIPPER.PACKLIST_ID= :OLDDATA.PACKLIST_ID
AND :OLDDATA.CUST_ORDER_ID = SYSADM.CUST_ORDER_LINE.CUST_ORDER_ID
AND :OLDDATA.CUST_ORDER_LINE_NO = SYSADM.CUST_ORDER_LINE.LINE_NO
AND SYSADM.CUSTOMER_ORDER.CUSTOMER_ID = SYSADM.CUSTOMER.ID
AND SYSADM.CUST_ORDER_LINE.CUST_ORDER_ID = SYSADM.CUSTOMER_ORDER.ID
AND SYSADM.CUST_ORDER_LINE.PART_ID = SYSADM.PART.ID;
IF mCUSTOMER_ID='97' AND mUSER_SHIPPED_QTY >0 THEN
SELECT TBLSDSHIPMENTS_SEQUENCE.nextval INTO mseqNo FROM dual;
INSERT INTO TBLSDSHIPMENTS (SDID, SHIPPED_DATE, PACKLIST_ID, LINE_NO, CUST_ORDER_ID, CUST_ORDER_LINE_NO, ORDER_QTY, USER_SHIPPED_QTY, QTY_BO, DESCRIPTION, CUSTOMER_ID, CUSTOMER_PO_REF, SHIPPED_TIME, SDSHIPMENTSTATUS, PART_ID)
VALUES (mseqNo, mSHIPPED_DATE, mPACKLIST_ID, mLINE_NO, mCUST_ORDER_ID, mCUST_ORDER_LINE_NO, mORDER_QTY, mUSER_SHIPPED_QTY, mORDER_QTY - (mTOTALSHIPPEDQTY + mUSER_SHIPPED_QTY), mDESCRIPTION, mCUSTOMER_ID, mCUSTOMER_PO_REF, SUBSTR(mSHIPPED_TIME,12,, 10, mPART_ID);
COMMIT;
END IF;
END;
I want none of the commands (lines) to affect the table if that condition is not met. For some reason now, even though the code above seems straight forward, another trigger on the table has a problem with my code and will not save a record in the table, even though this is an after insert trigger. I was hoping for some REVERT, or CLEAR command that could be executed if the insert condition is not met.
The logic is to save a record to another table, (TBLSDSHIPMENTS) if the value of the inserted record and related tables in this table is equal to
(IF mCUSTOMER_ID='97' AND mUSER_SHIPPED_QTY >0).
This seems easy, but there are other triggers on the table (my table is based on) that my trigger is affecting. In some rare cases a record is not saved. I want to prevent my trigger logic affecting anything unless the above condition is met.
What is the best way to do this?
A. If something in another trigger that is affected by the transaction fails, then the insert from your trigger will be rolled back. In most cases, that is the desired effect. If you want the insert to be committed, even if the rest of it fails, then you will need to use a commit with pragma autonomous_transaction. However, if the failure occurs before your trigger is fired, then your insert will never occur. Triggers fire in the order of: before statement, before row, after row, and after statement. Triggers at the same level may fire in any order. If you need to guarantee the order, then you need to put the logic in one trigger, or procedures that are called from one trigger.
Question 14: What kind of mistake is this?
What is causing an error if I have this procedure?
PROCEDURE wstawmysql1
(tabela IN VARCHAR2)
AS
script CLOB;
script1 clob;
nazwa varchar2(50);
typ varchar2(50);
Cursor_name INTEGER := DBMS_SQL.OPEN_CURSOR;
ret INTEGER;
col1 VARCHAR2(2000);
v_col_count INTEGER := 0;
Cursor cur(c_tname varchar2) is
select column_name,data_type from user_tab_columns where lower(table_name)=lower(c_tname);
BEGIN
-- ddl
script := 'Create table ' || tabela || '(' || CHR(13) || CHR(10);
open cur(tabela);
loop
fetch cur into nazwa,typ;
exit when cur%notfound;
if typ='VARCHAR2' then typ:='varchar(255)';
end if;
if typ='NUMBER' then typ:='INT';
end if;
script:=script||nazwa||' '||typ||','||CHR(13)||CHR(10);
end loop;
close cur;
script:=script||');';
script1:=substr(script,1,length(script)-5);
script:=script1||');' || CHR(13) || CHR(10);
DBMS_SQL.PARSE (cursor_name, 'SELECT * FROM ' || tabela, DBMS_SQL.NATIVE);
FOR i IN 1 .. 255 LOOP
BEGIN
DBMS_SQL.DEFINE_COLUMN (cursor_name, i, col1, 2000);
v_col_count := i;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1007 THEN EXIT;
ELSE RAISE;
END IF;
END;
END LOOP;
DBMS_SQL.DEFINE_COLUMN (cursor_name, 1, col1, 2000);
ret := DBMS_SQL.EXECUTE (cursor_name);
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS (cursor_name) <= 0;
script := script || 'INSERT INTO ' || tabela || ' VALUES (';
FOR i IN 1 .. v_col_count LOOP
DBMS_SQL.COLUMN_VALUE (cursor_name, i, col1);
script := script || '''' || REPLACE (col1, '''', '''''') || '''' || ',';
END LOOP;
script := SUBSTR (script, 1, LENGTH (script) - 1) || ');' || CHR(13) || CHR(10);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cursor_name);
-- delete from xmltab and insert scripts into xmltab:
DELETE from xmltab;
INSERT INTO xmltab VALUES (19, script);
commit;
END wstawmysql1;
When I run it on a smaller table, all is good. But when I run the procedure for a bigger table, for example 600 rows and 17 columns, procedure works longer and returns with this mistake:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Another problem I would encounter is when the table has more columns (about 12 or more). My procedure creates me a ddl script with the same columns, but in different order. for example; table has these columns 1,2,3,4,5,6, but procedure create this ddl 4,1,2,3,5,6 etc.
A. You are out of space in TEMP tablespace. Either add more space or make your query do less sorting. Check if somebody was doing a large index build at that time. Without using an ORDER BY clause, do not expect Oracle to know what order to return your select statement, hence the different order each time you run it. This is a classic mistake by people who do not fully grasp what a relational database is.
Question 15: Put 30+ select statements in 1 proc with 1 refcursor
I have about 30+ queries in Oracle and I need to write a stored procedure that will return a refcursor.
Procedure will have User ID as input parameter and will be passed in a string of numbers that are comma delimited and refcursor as output.
All 30 + queries return different data.
I have created a temporary table Temp_Table.
First procedure (phase 1) will be executed to empty the temp table.
In the next step (phase II), information from the 30+ individual queries would be stored in a single row for each user id within that Temporary table.
I want to use 30+ variables to store the results of each individual query for each individual User ID. My understanding is I should not have to use a cursor at this point.
After all the queries have been performed, the variables would be used to insert the results into the temp table for the individual users being processed.
The last phase (phase III), the Stored Procedure would be creating a "single" Ref Cursor. The structure of the cursor would be the results of the 30+ individual queries contained in the temporary table.
What I am doing right now is:
PROCEDURE USERID_PROC(p_user_id IN NUMBER,
c_get_data OUT c_get_typ);
At first, I was using
r_get_data c_get_typ;
LOOP
OPEN c_get_date for
SELECT.........
FETCH INTO
EXIT WHEN c_get_data%NOTFOUND;
CLOSE c_get_data;