OPEN-FETCH-BULK COLLECT-CLOSE

===> One of the Right Methods-

OPEN C1;
LOOP
    EXIT WHEN C1%NOTFOUND;
    FETCH C1 BULK COLLECT INTO C1_TBL LIMIT 100;
    <>
END LOOP;
CLOSE C1;

===> One of Many Wrong Methods-

OPEN C1;
LOOP
    FETCH C1 BULK COLLECT INTO C1_TBL LIMIT 100;
    EXIT WHEN C1%NOTFOUND;
    <>
END LOOP;
CLOSE C1;

Problem is, In first method, when I have fetched the CURSOR, it moves ahead and when it will pass last record, then NOTFOUND will evaluate to true. So if I do exit after fetching like I am doing in 2nd method, then I wont be able to iterate through the loop in last execution. I will always miss this last cycle.

Making EXIT as the first statement as in first method above, I moved EXIT statement before the FETCH. Now next fetch in loop will give me back remaining rows and I can iterate through the loop this last time and will also move the cursor to EOF so that in next cycle I can EXIT the loop..

PL/SQL Ada a Love

PL/SQL is derived from Ada, which was originally designed for US Department of Defense by "Ada  Lovelace". Main idea to use Ada was to capitalize on Idea of STANDARD package, so that needs not be used with dot notation every time we want to use it. Oracle adopted it and created two default packages STANDARD and DBMS_STANDRD and these are the only standard packages available in Oracle, even users cannot define their own default standard packages unlike Ada.

DEFINE vs VARAIBLE

DEFINE is just a replacement of Strings and VARIABLE actually crates a bind variable -

SQL>DEFINE X='DEFINE X HERE';

SQL> BEGIN :X:='SET X HERE'; END;
/

SQL> SELECT :X,'&X' FROM DUAL ;
old   1: SELECT :X,'&X' FROM DUAL
new   1: SELECT :X,'DEFINE THE X HERE' FROM DUAL

:X 'DEFINETHEXHERE'
----------- -----------------
SET X HERE DEFINE THE X HERE

&X simply replace the String, see below, we have omitted the single quotes around &X and it errors out, because it simply replaces &X with string which SQL cannot parse.

SQL> SELECT &X , :X FROM DUAL ;
old   1: SELECT &X , :X FROM DUAL
new   1: SELECT DEFINE THE X HERE , :X FROM DUAL
SELECT DEFINE THE X HERE , :X FROM DUAL
                  *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Mutating PARALLEL Operation

Table T1 has parallel parameter set to DEFAULT, so parallel operation was running

INSERT /*+append*/ INTO T1

SELECT * FROM T2

         AND NOT EXISTS (

                        SELECT 'x'

                          FROM T1 inn_t1

                         WHERE T2.id= inn_t1.ih_id

                           );

ORA-12838:	cannot read/modify an object after modifying it in parallel
Cause:	Within the same transaction, an attempt was made to add read or modification statements on a table after it had been modified in parallel or with direct load. This is not permitted.
Action:	Rewrite the transaction, or break it up into two transactions: one containing the initial modification and the second containing the parallel modification operation.

My Solution for this Problem – I removed the /*append*/ hint, because you cannot query a table after direct pathing into it until you commit

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484

Pipelined Functions

1. create object (t_row )
     CREATE TYPE t_row AS OBJECT (
         id           NUMBER,
         name         VARCHAR2(100)
      );

2. create table type (t_tab ) using object created in step1
        --  CREATE TYPE t_tab IS TABLE OF t_row;

3. CREATE FUNCTION fn() RETURN typ_created_above_in_2 PIPELINED and PIPE the ROWS

        CREATE OR REPLACE FUNCTION get_students (p_rows IN NUMBER) RETURN t_tab PIPELINED AS
        BEGIN
          FOR i IN 1 .. p_rows LOOP
            PIPE ROW(t_row(i, 'Student '||i));  
          END LOOP;
          RETURN;
        END;

   P.S. - MUST use a blank RETURN call in the end

4. Test it.
    SELECT *
    FROM   TABLE(get_students(10))
    ORDER BY id DESC;

Schema!!!

Schema is the structure of the database, in a way, in which we want to fit in our data. 

Retrying a Failed Transaction

After an exception is raised, rather than abandon your transaction, you might want to retry it. The technique is:

1. Encase the transaction in a sub-block.

2. Place the sub-block inside a loop that repeats the transaction.

3. Before starting the transaction, mark a savepoint. If the transaction succeeds, commit, then exit from the loop. If the transaction fails, control transfers to the exception handler, where you roll back to the savepoint undoing any changes, then try to fix the problem.

In the following example, the INSERT statement might raise an exception because of a duplicate value in a unique column. In that case, we change the value that needs to be unique and continue with the next loop iteration. If the INSERT succeeds, we exit from the loop immediately. With this technique, you should use a FOR or WHILE loop to limit the number of attempts.

SET SERVEROUTPUT ON;

DECLARE

name VARCHAR2(20);

ans1 VARCHAR2(3);

ans2 VARCHAR2(3);

ans3 VARCHAR2(3);

suffix NUMBER := 1;

BEGIN

FOR i IN 1..10 LOOP -- try 10 times

BEGIN -- sub-block begins

SAVEPOINT start_transaction; -- mark a savepoint

/* Remove rows from a table of survey results. */

DELETE FROM results WHERE answer1 = 'NO';

/* Add a survey respondent's name and answers. */

INSERT INTO results VALUES (name, ans1, ans2, ans3);

-- raises DUP_VAL_ON_INDEX if two respondents have the same name

COMMIT;

EXIT;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK TO start_transaction; -- undo changes

suffix := suffix + 1; -- try to fix problem

name := name || TO_CHAR(suffix);

END; -- sub-block ends

END LOOP;

END;

/

Using Locator Variables to Identify Exception Locations

Using one exception handler for a sequence of statements, such as INSERT, DELETE, or UPDATE statements, can mask the statement that caused an error. If you need to know which statement failed, you can use a locator variable:

DECLARE

stmt INTEGER;

name VARCHAR2(100);

BEGIN

stmt := 1; -- designates 1st SELECT statement

SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'ABC%';

stmt := 2; -- designates 2nd SELECT statement

SELECT table_name INTO name FROM user_tables WHERE table_name LIKE 'XYZ%';

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('Table name not found in query ' || stmt);

END;

/

Handling Exceptions Raised in Declarations

SET SERVEROUTPUT ON;

DECLARE

BEGIN

DECLARE

credit_limit CONSTANT NUMBER(3) := 5000; -- raises an exception

BEGIN

NULL;

EXCEPTION

WHEN OTHERS THEN

-- Cannot catch the exception. This handler is never called.

dbms_output.put_line('Can''t handle an exception in a declaration.');

END;

EXCEPTION

WHEN OTHERS THEN

-- Cannot catch the exception. This handler is never called.

dbms_output.put_line('Dont worry I am here.');

END;

To implement BEx Key Date at BO Universe

1. Make a predefined Filter At Universe with the following definition
Name of predefined Filter: KeyDate
Where Clause : <filter key="[0P_ECKDT]"><condition key="[0P_ECKDT]" operatorcondition="Equal" tech_name="@Prompt('Key Date','D',,,)"/><CONDITION></FILTER>

Note : [0P_ECKDT] is the technical name of key date object in SAP BW for key date

2. Check the option “Use filter as mandatory in query -> Apply on Universe” so that key date should be asked every time user executes the query.


3. In Universe Parameters , set the “KEYDATE_ENABLED” to “No”, as shown in below image

Now the keydate will appear as normal prompt in webI documents and you can use them in xcelsius by using prompt binding.

Error On LOV Refresh in WebI - ERR_WIS_30270

For Error "ERR_WIS_30270" follow the following steps. I faced this problem while refreshing the LOVs in webI reports.

Select All the Check boxes in “Associate a List of Values” section except “Delegate Search”



Although I used this solution, But I am not sure about the reason ebhind this.

Simultaneous Refreshing More Than 1 LiveOffice in Xcelsius

1.Insert LiveOffice Connections in Xcelsius, say you inserted Live1, Live2 and Live3. In this example they are LO_OP_PlantParamterts_Yearly_C Actual (Live1), LO_OP_PlantParamterts_Monthly_C Actual (Live2) and LO_OP_PlantParamterts_Last7Days_C Actual (Live3)

2.For the first connection in chain set “Refresh on Trigger-> Refresh Cell” to the cell where you are storing the input from the user, say It is Company Name and Brand Name and your are using Filter component to ask the values from user.

3.Select “When Cell Updates” option.

4.Set “Loading Message” to “x” (Value which you will never user)

5.Set “Idle Message” to 1 and insert it into a cell say “D2”, as shown in below image



6.Now, set the Usage Options for the 2nd Live Office connection (Live2). Set “Refresh on Trigger-> Refresh Cell” to the cell where you are storing the “Idle Message” of the Live Office connection1 (Live1)

7.Select “When Value Equals” option and enter the value which you inserted in Idle Message of connection Live1. In this case it is 1

8.Set “Loading Message” to “x” (Value which you will never user)

9.Set “Idle Message” to 2 and insert it into a cell say “D3”, as shown in below image



10.Now, set the Usage Options for the 3rd Live Office connection (Live3). Set “Refresh on Trigger-> Refresh Cell” to the cell where you are storing the “Idle Message” of the Live Office connection2 (Live2)

11.Select “When Value Equals” option and enter the value which you inserted in Idle Message of connection Live2. In this case it is 2

12.Set “Loading Message” to “x” (Value which you will never user)

13.Set “Idle Message” to 3 and insert it into a cell say “D4”, as shown in below image


Same steps can be repeated for as many connections as you want. I have used 8 live office connection to refresh with same logic.

My Learnings from Xcelsius Project

1. Do not change the name of the sheets once the components mapped otherwise Xcelsius file will not open next time
2. Do not use the " Disable Mouse Input on Load" option when using Clender component in dashboard. Doing this will make calender hanged and multiple dates will be selected, thus value will not update in binded cell.
3. Excel formulas cant be written using reference of LiveOffice rows, as LiveOffice deletes the old rows and then insert new rows while Refreshing. But while using these liveoffice excels, in Xcelsius 2008, which contains formulae and referencing to the liveoffice data cells, formulae work correctly.
4. Always use Detail objects in SAP Cube/BEx based universes, while filtering data in WebI report. It could happen that you are using Dimension Object and at the time of using it there is no TEXT available for the Key then you can filter on Key, but later on if there will be data in Cube for the field against the key you had used and text for that InfoObject in cube is enabled, then your query won't filter results correctly. For e.g. I have One dimension object in universe Employement Type which I was filtering in WebI as, Employement Type : 3 (3 means Active), but later on in cube value against this key is entered and Text is made enabled at InfoObject level, my WebI qury is not filtering and I had changed it to Employement Type : Active, and my WebI then worked correctly.

5. Do not refresh more then one the LiveOffice connections in Xcelsius dashboard simultaneoulsy, must referesh them in serial oreder. Try some logic to refresh them in serial order as I used Idle and Loading messages as flags to know the refresh status of previuos connection and submit the next connection refresh only after previous liveoffice successfully refreshed. For details write to me and add your email I will send you the sample Xcelsius file.

Please add-on more :)

Xcelsius Dashboard from WebI report (with prompt) using LiveOffice

Environment Details:
BOXI R3.1

SAP BI Backend
Live Office XI R3.1
Xcelsius 2008

1. Create webI report for prompt display values which will be used to get input from user in Xcelsius Dashboard. Let this report is exported to CMS with name “prompt values”
Note: Use Detail values instead of dimension objects



2. Now create the report with prompt (Customer Key in this case) and export this report to CMS, in this case report name is “Test Webi Report(1)”

3. Now insert these webI documents in Excel using Live office
First insert the prompt values webi report

5. Now Import webI report which contains the dashboard data.

6. Refresh all the connections

7. Select webI report contains dashboard data.



8. Bind the prompt with excel cell



9. Select “Choose Excel data range” option and bound one excel cell (B18 in this case) with prompt values.
Note: Unselect “Append parameter list to the dropdown of the binding cell” if you don’t want list of values to he displayed as drop down box in excel sheet.



Test the connection change the value (from ‘10007 to ‘10008 for this case, may the value will be different in your case so select accordingly) in selected cell and check if the data is getting refreshed, if yes, then you have successfully created WebI – LO Connection and it is ready to be used in Xcelsius 2008.

10. Save this excel sheet on your machine.

11. Open Xcelsius and import above saved excel sheet as data source



12. Goto Data->Connections menu and Add Live Office connection
Note: Must not forget to enter your webserver name/IP address where BOBJ is installed.



13. On usage Tab of Live Office connection which is being used to get dashboard data , set options “Refresh On Load” and “Refresh on Trigger” and select the cell where we bounded the prompt values (B18) in this case.



14. Now select List Box (You also can use some other control as per the requirement) and give it Label range (C5 to C16 in this case) from the prompt values which we had selected in WebI report “prompts values”
In Source give full range of prompt values and in destination must include which is bounded to webI prompt (B18).



15. Add One more control (dial in this case) and set its value from dashboard data. Its value must be changed as we select different customers from the list box we created



16. Preview the file, enter credentials and data should be updated as you select different customers.

17.Export it to CMS and open it from Infoview.

Please revert back if you any problem.

Enjoy
Sandeep Manocha