Week 2 - Assignment 2 - Is my answer correct? - PL/SQL Program Units

Is my answer correct? SET SERVEROUTPUT ONDECLARE    v_department_id NUMBER := 40;    e_childrecord_exists EXCEPTION;    PRAGMA EXCEPTION_INIT(e_childrecord_exists,-02292);    --v_error_code NUMBER;    --v_error_msg VARCHAR2(255);BEGIN     dbms_output.put_line('Deleting department '||v_department_id||'....');    DELETE FROM ad_departments WHERE department_id = v_department_id;     EXCEPTION    WHEN e_childrecord_exists THEN        dbms_output.put_line('Can not delete this department. There are active courses in this department (child records exist).');        --v_error_code := SQLCODE;        --v_error_msg := SQLERRM;        --dbms_output.put_line('Erro code: '||v_error_code);        --dbms_output.put_line('Erro message: '||v_error_msg);END;/

Hi Clayton Pereira As I mentioned in the other post, please check if your output matches with the sample output. Otherwise, there is an issue with the code and you can reach out to us with the specific question or issue with your code.We can then look into the outstanding issue and suggest a solution. Thanks,Anjani.

Hello,Is the output expected?I did, pretty much the same as your code. My output gives the expected. I was afraid to be "forcing" way output. But I do not think so. I think both are right. -------------------------------SET SERVEROUTPUT ON; DECLARE    e_childrecord_exists EXCEPTION;    PRAGMA EXCEPTION_INIT(e_childrecord_exists, -02292); BEGIN    DBMS_OUTPUT.PUT_LINE('Deleting department ID from AD_DEPARTMENTS table.');    DELETE FROM ad_departments WHERE department_id = 40;        EXCEPTION        WHEN e_childrecord_exists THEN            DBMS_OUTPUT.PUT_LINE('Cannot delete this department. There are active courses in this department (child record exist.)');END;/---------------------------------- 

Related

Query/Procedure to generate a weekly report showing week number as a column header.

Hi, Can anyone help me in getting the output as follows?  Start Date and End Date should be dynamic. I am going to pass these dates as a parameter to the SQL function. Please help me in getting this output.The table is having fields Employee Name. Start Date, End Date, Engagement percent. This Engagementpercent column is used to calculate the weekly engagement of that resource on the basis of start and end date. StartDate 01/06/2017   EndDate 30/06/2017Get Data buttonEmployeeWeek1Week2Week3Week4Week5Rich Robb1010101010Sam2010133456 Thanks in advance.
Can Anybody help me on this? 
You need to provide a test case, including create table statements and insert statements with sample data that matches your desired output provided.  You also need to provide what you have tried yourself so far and show where you got stuck, including any error messages received.  This forum is not intended to do your work or homework for you, but to help you learn to do it. The following link contains some examples of how to pivot data.  If you scroll down, there is one using parameterized date ranges.  This may help to get you started.  If you search the web, you can find other examples. how to rotate rows into columns in MySQL | only bluefeet
I have written following query but when I am calling this through apex it is not working.For executing this I am using classic report based on function returning a sql query. I don't get where I am doing wrong.declaress_interval number;dayInterval number; ss_qry varchar2(20000); ss_qry_main varchar2(1000);ss_qry_from varchar2(1000); ss_qry_where varchar2(1000);ss_qry_groupBy varchar2(1000); ss_qry_build_alloc varchar2(20000); ss_dt date;end_dt date;v_startdate date;total_days number;TYPE cursor_ref IS REF CURSOR; c1 cursor_ref; cntr number;BEGIN ss_qry_main := 'select ae.supervisorid,(select m.fname||'' ''||m.lname from ace_employees m where m.srid = ae.supervisorid) "Manager",level,ae.srid,ae.fname||'' ''||ae.lname "Resource"'; ss_qry_from := ' from allocationdetails ad, ACE_EMPLOYEES ae '; ss_qry_where := ' Where ae.ENDDATE IS NULL and ae.srid = ad.employeeid(+) start with ae.srid = (select aceemp.srid from ace_employees aceemp where aceemp.fname||'' ''||aceemp.lname = ''Sam William'') connect by prior ae.srid = ae.supervisorid'; ss_qry_groupBy := ' group by ae.supervisorid,level,ae.srid,ae.fname,ae.lname order by level';    cntr := 1; ss_dt := p23_start_date; end_dt := p23_end_date; total_days := trunc((:p23_end_date - :p23_start_date)/7); for cntr in 1..total_days loop         ss_dt := ss_dt + 6;        ss_qry_build_alloc := ss_qry_build_alloc ||', COALESCE(sum(case when ad.startdate >= '''|| to_date(ss_dt, 'MM/DD/YYYY') ||''' and ad.enddate <= '''|| to_date(end_dt, 'MM/DD/YYYY') ||''' then round(ad.weeklyallocpercent*100,0) end),0) '||' "Week-'|| to_char(to_date(ss_dt, 'MM/DD/YYYY'),'IW') ||'"';   end loop; ss_qry := ss_qry_main||ss_qry_build_alloc||ss_qry_from||ss_qry_where||ss_qry_groupBy;retrun ss_qry; --EXECUTE IMMEDIATE ss_qry USING  OUT AllocDetails;   exception       when others then       dbms_output.put_line(sqlerrm);           null;END  This is my workspace url : https://apex.oracle.com/pls/apex/f?p=21550:23:56726658419::NO::: Username:oracommPassword: Test#123Please check this once and let me know where I am doing mistake. I am doing this on page 23.
Sorry Wrong Post. I don't how to update it but you can ignore the above post which is for oracle apex.

Compare two identical tables and to report only column values that has changed

Basically I am trying to compare two identically structured oracle tables in same schema and to result only with column names which has changed for whatsoever row number. So the output would be like as below. This has been already discussed in another thread but the function mentioned is not working and is returning error. Any immediate help would be really helpful.    RNUM         CNAME               OLD_VAL                      NEW_VAL------------    -------------------- ------------------------------ -----------------------------     1               ENAME                Name11                         Name1     1               HIREDATE          13-may-2011                  13-may-2001     3               ENAME                Name32                         Name3     3               HIREDATE          13-jul-2011                     13-jul-2001     4               DESIGNATION    SENIOR ANALYST        ANALYST     5               SALARY              10000                              5000  variable q1 varchar2(40);variable q2 varchar2(40);exec :q1 := 'select * from table_a order by IDexec :q2 := 'select * from table_b order by IDcolumn rnum format 99999;column cname format a20;column old_val format a30;column new_val format a30;SET DEFINE OFF;select t1.rnum ,t1.cname, t1.val as old_val,t2.val as new_valfrom table (cols_as_rows(:q1)) t1,table (cols_as_rows(:q2)) t2where t1.rnum = t2.rnumand t1.cname = t2.cnameand t1.val <> t2.val; Function implementation is as follows - create or replace type mytabtype FORCE as object(rnum number,cname varchar2(30),val varchar2(4000));/create or replace type mytab is table of mytabtype;/create or replace -----when I try to create this function resulting in error (shown after this function code) function cols_as_rows(p_query in VARCHAR2) return mytab pipelined  as l_cursor   number default dbms_sql.open_cursor; l_column_count  number := 0; l_desctab          dbms_sql.desc_tab;l_column_value  varchar2(4000);l_status        number; l_rnum             number default 1; begin   dbms_sql.parse(l_cursor,p_query,dbms_sql.native); dbms_sql.describe_columns (l_cursor,l_column_count,l_desctab);  for i in 1..l_column_count    loop  dbms_sql.define_column (l_cursor,i,l_column_value,4000); end loop; l_status := dbms_sql.execute(l_cursor); while ( dbms_sql.fetch_rows(l_cursor) > 0 )loopfor i in 1..l_column_countloop  dbms_sql.column_value (l_cursor,i,l_column_value); dbms_output.put_line ( rpad(' ',5,' ')||rpad(l_desctab(i).col_name,20,'-') ||' '|| l_column_value ); pipe row(mytabtype(l_rnum,l_desctab(i).col_name,l_column_value));   end loop;  l_rnum := l_rnum + 1;end loop;dbms_sql.close_cursor(l_cursor); return;end cols_as_rows;/ 
'select * from table_a order by ID';closing quote and semicolumn is missing.  ----when I try to create this function resulting in error (shown after this function code)This is not a correct comment, use -- and put it inside the pl/SQL block.. It is on the wrong spot.
Hi Lothar, just added the comment in the discussion to indicate that I am getting error while creating function i.e. It was not there while running against database. And the quote, semicolon one was corrected. So only the function is having issue and getting shown error. Please advise.
Nope, I got it. You need execute priviledge on DBMS_SQL.If you want to call DBMS_SQL from a PL/SQL Block it needs to be explicity granted to the owner of the procedure.Grant via a role is not good enough.
Thank you. Let me request for the privilege to execute DBMS_SQL.
It looks like it is a tedious process to get the grant privilege on DBMS_SQL package. Meanwhile can someone please help on a code which will compare each column in 2 similar structured (similar data as well and one tend to change wherein I need to capture that change) oracle tables and to result in only row numbers, column name (which got updated), old value, new value. Like below. RNUM         CNAME               OLD_VAL                      NEW_VAL------------    -------------------- ------------------------------ -----------------------------     1               ENAME                Name11                         Name1     1               HIREDATE          13-may-2011                  13-may-2001     3               ENAME                Name32                         Name3     3               HIREDATE          13-jul-2011                     13-jul-2001     4               DESIGNATION    SENIOR ANALYST        ANALYST     5               SALARY              10000                              5000
Sorry forgot to add, I do not want to hard code any column names in the code, it should be like a generic script i.e. if I input 2 table names then it should fetch its column names from metadata and then should perform the comparison. Thanks in advance.
Well, I had to do it once and wrote generator (a script that generates a script) instead of using dynamic sql.The input was table_name1, table_name2.It generated a statement like:select column1, column2, column3 ...from table1minusselect column1, column2, column3 ...from table2; That is easier to write that the more beautiful 1 column approach. With one column it better if you want to do reporting on it.You have to take care of NUll values and convert them to something else. (You used a non printable character). Otherwise two null values will not be equal.)
Thank you Lothar. Can you please give the complete script?
I don't have the script anymore. Need to recreate it, In order do do so, I need two questions ansered:Do your tables have Primary Key constraints?Are the column names the same in both tables?
Hi Lothar, Yes, the table columns are the exact same and it has a primary key.
And i do not want the result set as complete row which will end up doing manual check on the column value that has changed in each row. Instead just the row number, column name, old value, new value should suffice. Thanks in advance.
I thought about your request. I am sorry, but doing a work of this size is more than what seems adequate for a help forum. 
Np Lothar. Thank you for the efforts.Anyone please share the code for my requirement if you have it handy or any snippet to achieve this with some changes should also suffice.
Ok,To generate your query dynamically you can use this two statements: For the select list use:select listagg(column_name, ', ') within group (order by column_id)   from user_tab_columns where table_name='&table_name'; for the where clause use:select listagg('tab1.'||column_name||'<>'||'tab2.'||column_name, ' AND ') within group (order by column_id)   from user_tab_columns where table_name='&table_name' and column_name not in (select column_name from user_cons_columns cc, user_constraints c  where c.table_name='&table_name' and cc.table_name=c.table_name and cc.constraint_name=c.constraint_name and c.constraint_type='P') ;

Data Validation non Veridata

Hi All, Time and time again i run into customers where there is a need for data validation after/during a migration.Have anyone come up with a good non veridata solution for this? Table counts are one option that always comes up but with data moving constantly it's challenge. Thanks,Alex
Hi Alex What you can do is to do multiple compares using count. The first pass will flag some tables being the same and some different. Pick out those that are different and compare them again. You should get a smaller set that is different, then do this again. If you loop this say 10 times and do this at a quiet time you might get no more differences.Obviously count is crude. You can improved this by using checksum. Take a checksum instead of count of the primary key or any set of columns you wish to combine, then do the same compare loops as mentioned.You can go further by using ora_rowscn and filter the select to within say sysdate - 2 hours. Then you might avoid the current frequent changes. Or your application tables might have a timestamp column (eg. if you are using Siebel it has a last update column). I wrote a script for comparing Siebel table changes based on this and generates a report for each database. Both report sits on a shared disk visible from both exadata servers and I simply ran diff against them.  CheersKee Gan
Thanks Kee.  I will see if i can come up with something. it's been in my back burn for a while but always comes back.
Hi Alex, Please check the below which helps you to compare the rows of the tables. This is something similar to the OGG Veridata... DBMS_UTILITY.GET_HASH_VALUE    (name IN VARCHAR2    ,base IN NUMBER    ,hash_size IN NUMBER)RETURN NUMBER;  (select * from A minus select * from B) -- Rows in A that are not in Bunion all( select * from B minus select * from A) -- rows in B that are not in A  select *from Awhere (col1,col2,?) not in   (select col1,col2,? from B)   union allselect * from Bwhere (col1,col2,?) not in   (select col1,col2,? from A); Regards,Veera
Veera,Thanks, this is what i am looking for.I am not sure where the GET_HASH_VALUE call is coming from.
This is my code, the interesting bit are the lines doing select count and ora_hash displays. My tables all have the columns row_id and last_upd (if you do Siebel you will be familar these columns)  v_query_str := 'SELECT count(*) FROM '||names(i)||' where last_upd >'||q'[to_date('01-01-00','dd-mm-yy')]'||' and last_upd < ' ||q'[to_date(']'||datetext||q'[','yyyy-mm-dd hh:mi:ss')]';  v_query_str := 'SELECT SUM(ORA_HASH(concat(row_id,last_upd))) FROM '||names(i)||' where last_upd >'||q'[to_date('01-01-00','dd-mm-yy')]'||' and last_upd < ' ||q'[to_date(']'||datetext||q'[','yyyy-mm-dd hh:mi:ss')]'; For the select count if you want everything just drop the where filter. For the ora_hash substitute the row_id and last_upd with a list of your choice.These is the list of tables you want to do, just put in one to test.names :=  tbl(table1, table2, table3, etc......); Other than that the rest should work as is.What you want to do is to do the work on the server where the database resides just like veridata with its agent. Then you are left with just the result that you compare with. In this case my spool file sits on a shared disk between my 2 exadatas and at the end of the run, from any node I just dodiff /mnt/share/chksum_DB01 /mnt/share/chksum_DB02This runs much faster than Veridata but it does not tell you where the differences are. Guess that is where Veridata earns its money. As a sanity check when you expect positive results this works better.On constantly updated tables, you can run this procedure many times. If the first set differences don't appear in the second or subsequent set you know it did matched at some stage so those tables are fine. I run them about 10 times at the same time each time on both sides. Needless to say run this when the databases are not busy.CheersKee Gan The procedure:column filename new_val filenameselect '/mnt/share/OGG/chksum_'|| (select instance_name from v$instance) filename from dual;spool &filenameALTER SESSION SET CURRENT_SCHEMA = SIEBEL;SET SERVEROUTPUT ONSELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') From dual;DECLAREhours number := &1;val NUMBER; cnt number := 0;v_exists number;tbl_exists number;cksum number;TYPE tbl IS TABLE OF VARCHAR2(50);names tbl;v_query_str VARCHAR2(500);datetext varchar2(100);BEGINnames :=  tbl(table1, table2, table3, etc......);select to_char(round(sysdate - hours/24,'mi'),'yyyy-mm-dd hh:mi:ss') into datetext from dual;DBMS_OUTPUT.PUT_LINE('Comparing last update before '|| datetext);FOR i IN names.FIRST .. names.LASTLOOPcnt := cnt + 1;SELECT COUNT(*) INTO tbl_exists FROM all_tables WHERE table_name = names(i);IF tbl_exists > 0 then  select count(column_name) into v_exists from all_tab_columns where column_name = 'ROW_ID' and table_name = names(i);  IF v_exists = 0 then  DBMS_OUTPUT.PUT_LINE(cnt || '   '||names(i) || ' ROW_ID DOES NOT EXIST');  ELSE  v_query_str := 'SELECT count(*) FROM '||names(i)||' where last_upd >'||q'[to_date('01-01-00','dd-mm-yy')]'||' and last_upd < ' ||q'[to_date(']'||datetext||q'[','yyyy-mm-dd hh:mi:ss')]';-- DBMS_OUTPUT.PUT_LINE(v_query_str);  EXECUTE IMMEDIATE v_query_str into val;  IF val > 0 then  v_query_str := 'SELECT SUM(ORA_HASH(concat(row_id,last_upd))) FROM '||names(i)||' where last_upd >'||q'[to_date('01-01-00','dd-mm-yy')]'||' and last_upd < ' ||q'[to_date(']'||datetext||q'[','yyyy-mm-dd hh:mi:ss')]';-- DBMS_OUTPUT.PUT_LINE(v_query_str);  EXECUTE IMMEDIATE v_query_str into cksum;  ELSE  cksum := 0;  END IF;  DBMS_OUTPUT.PUT_LINE(cnt || '   '||names(i) || ' row count = ' || val || '   Check = '|| cksum);  END IF;ELSEDBMS_OUTPUT.PUT_LINE(cnt || '   '||names(i) || ' DOES NOT EXIST');END IF;END LOOP;END;/SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') From dual;exit

Quiz 1 / Question 7

I've just done the Quiz 1, and I have a problem with Question 7In the declare section of the block we have :dept_name   departments.department_number%TYPE := 'Computer Science'; There is no definition of departments table in the Q7 so I suppose that it's the one from the setup, so :CREATE TABLE departments    ( department_id    NUMBER(4)    , department_name  VARCHAR2(30)CONSTRAINT  dept_name_nn  NOT NULL    , manager_id       NUMBER(6)    , location_id      NUMBER(4)    ) ; Which is strange because there is no department_number in the table definition. The question 7 ask us to find all errors in this pl/sql block, but this is not in the answer of this question.So is this an error in the question or is the departments table not related with the setup and the column name is just misleading ?  I suppose that the response to this question related to the INSERT statement is due to the fact that if dept_id was declared it should be a BOOLEAN ? Thanks,Thomas

Query in Homework Assignment.

Hi Team, I was doing the homework assignments for second week.I am facing the following issue in 3rd question of assignment: Error report -ORA-06550: line 9, column 3:PLS-00428: an INTO clause is expected in this SELECT statement06550. 00000 -  "line %s, column %s:\n%s"*Cause:    Usually a PL/SQL compilation error.*Action: The following is the PL/SQ code i have written for this question:SET SERVEROUTPUT ONDECLARE   v_dept_id AD_DEPARTMENTS.DEPARTMENT_ID%TYPE;BEGIN  SELECT MAX(DEPARTMENT_ID) INTO v_dept_id FROM AD_DEPARTMENTS;  dbms_output.put_line('Max no. of departmnts: '||v_dept_id);  INSERT INTO AD_DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME,HOD) VALUES(v_dept_id+10,'Education','null');  SELECT DEPARTMENT_ID ,DEPARTMENT_NAME ,HOD  FROM AD_DEPARTMENTS  WHERE DEPARTMENT_ID=(SELECT MAX(DEPARTMENT_ID) FROM AD_DEPARTMENTS);END;  Regards,Geethanjali C
I see this error: SELECT DEPARTMENT_ID ,DEPARTMENT_NAME ,HOD  FROM AD_DEPARTMENTS  WHERE DEPARTMENT_ID=(SELECT MAX(DEPARTMENT_ID)                      FROM AD_DEPARTMENTS); It should be something like: SELECT DEPARTMENT_ID ,DEPARTMENT_NAME ,HODINTO v_dept_id   -- Or any other record type variable like v_dept_id or 3 scalar variables that matches the 3 values being queried.FROM AD_DEPARTMENTS  WHERE DEPARTMENT_ID=(SELECT MAX(DEPARTMENT_ID)                      FROM AD_DEPARTMENTS);
Is the homework asking you to print the department you just added ? And you are using this statement to do it ? " SELECT DEPARTMENT_ID ,DEPARTMENT_NAME ,HOD  FROM AD_DEPARTMENTS  WHERE DEPARTMENT_ID=(SELECT MAX(DEPARTMENT_ID) FROM AD_DEPARTMENTS);" ? if yes, well you cannot use this SELECT here, you need to use it outside of the PLSQL anonymous block For ex change: INSERT INTO AD_DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME,HOD) VALUES(v_dept_id+10,'Education','null');  SELECT DEPARTMENT_ID ,DEPARTMENT_NAME ,HOD  FROM AD_DEPARTMENTS  WHERE DEPARTMENT_ID=(SELECT MAX(DEPARTMENT_ID) FROM AD_DEPARTMENTS);END; To INSERT INTO AD_DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME,HOD) VALUES(v_dept_id+10,'Education','null');END;/   SELECT DEPARTMENT_ID ,DEPARTMENT_NAME ,HOD  FROM AD_DEPARTMENTS  WHERE DEPARTMENT_ID=(SELECT MAX(DEPARTMENT_ID) FROM AD_DEPARTMENTS); And execute as a script
I did not review the requirements in the homework assignment, just pointed out the error inside the block . If the SELECT is to be included inside the block, it has to have the INTO clause. If not, then your comment is perfect and the poster needs to remove it from the block
Thank you.

Categories

Resources