Aggregate functions like 'max' in XQuery.. - Data Service Integrator

Hello,
I am trying to find the max of one of the fields in the table using XQuery aggregate function: fn:max as described in the below URL:
http://e-docs.bea.com/aldsp/docs30/xquery/sql_pushdown.html
Here is what I have:
declare $lineNo as xs:int :=
     for $pd in ns4:getAllPalletDtls()
     group $pd/LINE_NO as $line_group
     by 1
     return fn:max($line_group);
However, it's giving me error at line # 3 above:
Invalid expression: Expecting AS, found '/'
I am getting this error in ALDSP3.0.1 Aqualogic Data Service Studio.
Could someone please let me know what is wrong with the above code.
Thanks a lot,
Mamta 

It should be
     for $pd in ns4:getAllPalletDtls()
     group $pd as $line_group
     by 1
     return fn:max($line_group/LINE_NO); 

Yes, this is a known error in the ALDSP 3.x documentation, it has been revised in the latest version:
[http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/xquery/sql_pushdown.html#wp1161705] 

looks like table 3-23 has been fixed, but table 3-24 has not.
Edited by: mikereiche on Feb 23, 2009 12:55 PM 

"group" expects to be followed by a variable (not an expression). If you add a 'let' with your expression you can create the following which is more inline with your original query
declare $lineNo as xs:int :=
for $pd in ns4:getAllPalletDtls()
let $lineNo := $pd/LINE_NO
group $lineNo as $line_group
by 1
return fn:max($line_group);

Related

SQLCODE = 100 problem.  Help please.

Hi all,
I'm getting SQLCODE = 100 returned from a database function following an attempted selection which I know should be returning one row of data.
I've proved this by doing a simple select..
SELECT xml_reference
        FROM mydmap
        WHERE source_table = 'PERSON' AND
              source_field = '<CHANGETYPE>';
             
XML_REFERENCE                          
---------------------------------------
employeeChangeType                     
1 rows selected I've also written a test function tries to emmulate the erroring function at the point of failure (the erroring function is 1500 lines long so a bit hefty to post here.)
Below is a snippet from the original. The problem occurs at the selection in line 134.
...
...
...
129   -- define change type item string
130   v_cChangeTypeMapping := '<CHANGETYPE>';
131
132   BEGIN
133      -- get employee change type xml reference
134      SELECT xml_reference
135        INTO v_cEmployeeChangeType
136        FROM mydmap
137         WHERE source_table = 'PERSON'
138                 AND source_field = v_cChangeTypeMapping;
139   EXCEPTION
140      WHEN OTHERS THEN
141         v_sys_error := SQLCODE;
142   END;
143
144   v_nError := v_sys_error;
145   v_nRowCount := SQL%ROWCOUNT;
146
147   IF v_nError <> 0
148     OR v_nRowCount = 0 THEN
149   BEGIN
150      -- RETURN;
151      RETURN v_sys_error ;
152
153   END;
154   END IF;
...
...
...Here is my test function. This performs perfectly.
create or replace
function my_test return varchar2 as
v_returnValue VARCHAR2(500) := '';
v_cChangeTypeMapping VARCHAR2(50);
BEGIN
  v_cChangeTypeMapping := '<CHANGETYPE>';
  BEGIN
    SELECT xml_reference
        INTO v_returnvalue
        FROM mydmap
        WHERE source_table = 'PERSON' AND
              source_field = '<CHANGETYPE>';
  EXCEPTION
    WHEN OTHERS THEN
      v_returnValue := SQLCODE;
  END;
  RETURN v_returnvalue;
END my_test;So now I'm confused :-|
Can anyone shed some light as to what might be going on?
Almost forgot.. I'm using Oracle 10g Express Edition and doing my debugging/coding from within SQL Developer.
Thanks in advance
Phil C. 
what is the data type and size of the variable v_cEmployeeChangeType? 
comment out your exception handlers for the present.
Use the Sqldeveloper debugger to step through the code.
Its likely that chnagetype is not what you think it is. Could be as simple as an uninitialised variable. 
00100, 00000, "no data found"
// *Cause: An application made reference to unknown or inaccessible data.
// *Action: Handle this condition within the application or make appropriate
//          modifications to the application code.
//             NOTE: If the application uses Oracle-mode SQL instead of
//             ANSI-mode SQL, ORA-01403 will be generated instead of ORA-00100.Your select statement does not return any data.
John 
The v_cEmployeeChangeType variable is declared as VARCHAR2(59)
The xml_reference column in the mydmap table is CHAR(39 BYTE) nullable.
As shown the value held in xml_reference for the required row is 'employeeChangeType'
I've commented out the exception handlers and stepped through the function and received ORA-1403 No Data Found exception when the selection is fired.
I've also tried changing the declaration for v_cEmployeeChangeType to CHAR(39) to match the column definition exactly, but continue to get the same error. 
As John said: "Your select statement does not return any data."
You seem to think that there is matching data in the table. The program says there is not.
Please provide a very simple test case using SQl*plus that shows us the error.
Here some steps how to prove it
- start sql*plus
- run the select to show that there is data in the table.
- run the most basic anonymous pl/sql block that is possible, e.g.
declare
   v_cEmployeeChangeType  mydmap.xml_reference%type;
   v_cEmployeeChangeMapping  mydmap.source_field%type := '<CHANGETYPE>';
begin
   select xml_reference
   into v_cEmployeeChangeType
   from mydmap
   WHERE source_table = 'PERSON' AND
              source_field =v_cEmployeeChangeMapping; /* test it also with  '<CHANGETYPE>' */
end;
/if this does not return the no_data_found error then we have proof that the "error" is somewhere else in your code.
If you get the error then please post the full output from the sql*plus session here.
Edited by: Sven W. on Oct 31, 2008 10:32 AM
You can also test if something changes when you decalre v_cChangeTypeMapping as char or as varchar2.
Edited by: Sven W. on Oct 31, 2008 10:38 AM
Edited by: Sven W. on Oct 31, 2008 10:38 AM
Edited by: Sven W. on Oct 31, 2008 10:39 AM 
Sven.W,
Ok, I've run the test you suggested and didn't get the "No Data Found" the error.
The value I expected was loaded into the v_cEmployeeChangeType variable. So I guess I'm back at square one.
I'm away from my desk for the next couple of days so will pick up from here then.
Thanks for all suggestions so far.
Phil 
When you stepped through the code using the debugger did you not inspect the variables to see that they were what you thought they should be.
If your code looks perfectly correct, then I would say that its 99% certain its a data error.
I've had many of these in the past, and they are by far , the hardest most frustrating errors to track down.
I would suggest you do the following:
Create a tracking table and have an anonymous transaction procedure which is used to insert the values into your tracking table.
(since its autonomous the commit (which it should have ) will not affect your existing session.
Call this everywhere you think may be relevant. 
There are multiple possible errors. Maybe it is as simple as a "delete from xml_reference" somewhere in the code before. 
Hi,
I've no doubt it'll be something simple. But its certainly not a delete on the xml_reference. There is no delete functionality in either the front end application of the database app on the MYDMAP table. It almost has the flavor of bad permissions. But I can't see where that might come from either.
Guess I'll just have to keep on chipping away at the code.
Phil 
Hi Keith,
Sounds like a plan worth following. I'll get onto that later today.
Thanks,
Phil 
I've solved my problem by placing RTRIM around the filter columns in the WHERE clause. Although I fail to see why this should have been a problem given that the data types of the variables and columns were matched. I'm not aware that I've needed to be this particular about trimming data before. But then I am very new to the Oracle environment and trying to pick it up as I go along. Could this be due to a setting on the database that I'm unaware of?
SELECT xml_reference
        INTO v_cEmployeeChangeType
        FROM mydmap
         WHERE RTRIM(source_table) = 'PERSON'
                 AND RTRIM(source_field) = v_cChangeTypeMapping;
   EXCEPTION
      WHEN OTHERS THEN
         v_sys_error := SQLCODE;
   END;Phil. 
You are either:
using a char datatype rather than a varchar2. Char retains trailing spaces varchar2 does not.
Your data is being inserted with a preceding space. Check your inserts.
There is no database setting.
You need to dump the value in the table to see what the ascii codes are.
here is an example of the dump statement with and without a preceding space
eg
select dump(' person') from dual
answer
Typ=96 Len=7: 32,112,101,114,115,111,110
select dump('person') from dual
answer
Typ=96 Len=6: 112,101,114,115,111,110note how the length is different.
You can run the dump on any database column 
Phil Cole wrote:
I've solved my problem by placing RTRIM around the filter columns in the WHERE clause. Although I fail to see why this should have been a problem given that the data types of the variables and columns were matched. I'm not aware that I've needed to be this particular about trimming data before. But then I am very new to the Oracle environment and trying to pick it up as I go along. Could this be due to a setting on the database that I'm unaware of?
SELECT xml_reference
INTO v_cEmployeeChangeType
FROM mydmap
WHERE RTRIM(source_table) = 'PERSON'
AND RTRIM(source_field) = v_cChangeTypeMapping;
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
END;Phil.Not a good solution. Even if it is a possible workaround.
By using a function like RTRIM on the column you prevent index access on those columns.
I suggest checking the type declaration of your v_cChangeTypeMapping variable.
Change it according to the test case provided to have the same datatype as the column.
Problem as Keith already explained is probably a different datatype between CHAR and VARCHAR2.
Oracle will do a data type conversion. Depending on what you do in your select this comparison might work in one way or the other.
example
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select '|'||cast('Test' as char(10))||'|' Test_Char,
  2         '|'||cast('Test' as Varchar2(10))||'|' Test_Varchar2,
  3         case when cast('Test' as char(10)) = cast('Test' as Varchar2(10))
  4              then 'identical strings'
  5     else 'different strings'
  6      end compare_strings
  7  from dual;
TEST_CHAR    TEST_VARCHAR COMPARE_STRINGS
------------ ------------ -----------------
|Test      | |Test|       different strings
SQL> As you can see, even if my string is only 'Test' it depends on the datatype whether some blanks are added to it or not. Always avoid implizit type conversions. Try to use the same datatypes for the variables as for the columns.
Edited by: Sven W. on Nov 3, 2008 2:49 PM 
Thanks for your thoughts guys. You're correct in the CHAR vs VARCHAR2 mismatch. The variable v_cEmployeeChangeType was declared to match the SOURCE_FIELD column, but the filter had 'PERSON' hard coded for the SOURCE_TABLE which is a CHAR(50) column. Need to get my eyes upgraded!! :0
Thanks again.
Phil

PLSQL function where the input is 1 field and return should be a result set

Please help write a PLSQL function where the input is 1 field and return should be a result set
I pass in the class_id-->result set is total no of students,total subjects,total_teachers,etc
create or replace function f(class_id test_class.class_id%TYPE)
return ..--->how to put a result set
as
example_resul
begin
cursor teachers--------->This gives error.Can we not use cursors in functions? I need to use CURSOR because a A CLASS-->MANY teachers
IS
SELECT
lastname ||','||firstname instructor_name,
p.person_id
FROM
class_teachers i,
person p
WHERE
i.class_person_id = person_id
AND i.class_id = class_id
Error:-PLS-00103: Encountered the symbol "C_INSTRUCTORS" when expecting one of the following:
:= . ( # % ; 
You don't show us all your code, where does 'c_instructors' come from? 
Ooopss..very sorry for that..!
Thing is I wanted an example how can we declare and use cursors in plsql FUNCTIONS
Error:-PLS-00103: Encountered the symbol "TEACHERS" when expecting one of the following:
:= . ( # % ;
The error is the cursor name
**************
create or replace function f(class_id test_class.class_id%TYPE)
return ..--->how to put a result set
as
example_resul
begin 
Well, it's not getting clearer ;-)
But what happens if you change
AND i.class_id = class_id into
AND i.class_id = class_id;? 
Maybe you're better off using a ref cursor.
Are you familiar with them?
If not I suggest you read about it, and find many examples here or on asktom.oracle.com, i.e.:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279
Edited by: hoek on Mar 22, 2009 2:57 PM (typo's argh >:( ) 
If you could post all of the code of the plsql function that you have written, we would be able to help you better...

uncertain where to place a code in my application page

hello good day,
I'm uncertain where i should place a code in my Application. i will like to prevent the user from adding courses to an employee if he have exceeded his credit limit of 24 for the year.
i have initially place the code under Page Processing -> Process -> Process Row of LOAD -> Conditional Processing -> Selected PL/SQL Function Body Returning a Boolean
but when i did this and try to record no information is being saved to the report page.
OR
I was wondering if the formatting of the code is executed incorrectly, where it should be able to take in the values from the P16_EMPNUM and P16_YR
DECLARE
lv_total      number := 0;
lv_credit      number;
cursor c is
select      "LOAD"."EMPNUM",
      "COURSE"."CREDIT",
      "LOAD"."YR"
from      "COURSE" "COURSE",
         "LOAD" "LOAD"
where    "LOAD"."TITLE_CODE"="COURSE"."TITLE_CODE"
and        "LOAD"."EMPNUM" = P16_EMPNUM
and       "LOAD"."YR" = P16_YR;
BEGIN
for i in c loop
     lv_total := lv_total + i.credit;
end loop;
IF lv_total >= 24 THEN
     RETURN false;
ELSE
     RETURN true;
END IF;
EXCEPTION
     WHEN no_data_found THEN
     RETURN false;
END;I'm currently using APEX 3.2.1 built on Oracle 10g Release 10.2.1
any form of assistance will be greatly appreciated,
Richie 
Hi Richie,
I think that you can make your code shorter:
DECLARE
lv_total      number := 0;
begin
  select  sum("COURSE"."CREDIT") into lv_total
   from      "COURSE" "COURSE",
            "LOAD" "LOAD"
   where    "LOAD"."TITLE_CODE"="COURSE"."TITLE_CODE"
   and        "LOAD"."EMPNUM" = :P16_EMPNUM
   and       "LOAD"."YR" = :P16_YR;
   IF lv_total >= 24 THEN
     RETURN false;
  ELSE
     RETURN true;
  END IF;
END;You can put this code in validation process or as a condition for the process that adds data.
Regards,
Lev 
hey thanks for your reply Lev,
I have tried your code, it was able to insert the data which is great. but it still didn't prevent the user from inserting any new information for the employee after he/she exceeded their credit limit of 24 credits for the year.
There is another problem arises now, when i try to delete the information i have just inserted I'm unable to do so for that employee because i believe this employee has greater than 24 credits now. But I'm able to delete other employee's records within the same table who has less than 24 credits without any problems.
any other suggestions.
Thanks in advance,
Richie 
Most likely for whatever reason this code is not called.
Could you put your example on apex.oracle.com?
It will be easier to help you.
Lev
Edited by: le on Aug 10, 2010 11:52 AM 
hey good day Lev,
i am having troubles import my application to http://apex.oracle.com. I'm getting the following error
Help
ORA-20001: GET_BLOCK Error. ORA-20001: Execution of the statement was unsuccessful. ORA-06550: line 8, column 1: PLS-00306: wrong number or types of arguments in call to 'CREATE_PAGE_ITEM' ORA-06550: line 8, column 1: PL/SQL: Statement ignored <pre>declare h varchar2(32767) := null; begin h := null; h:=h||'If a picture has not been given for the Employee please select the default picture by following this information: - '||chr(10)|| 'click on the ‘Browse’ button, navigate to the destop (by
Error installing application.
Return to application.
any further help,
Thanks
Richie 
Probably you are using different version of APEX.
Is it possible just to recreate 1 page on apex.oracle.com?
Lev 
hey Lev,
is it possible for us to work on it over teamviewer, to be able to create this one page i believe will be tough to do over. this page is tied to other pages in the application. but if there is no other chose i will try to recreate the pages from scratch, it will take me some time through.
any other help or suggestions?
thanks again
Richie 
It is probably possible, but what is teamviewer ?
Lev
Edited by: le on Aug 10, 2010 2:13 PM 
Hey Lev,
teamviewer is a program that allows someone to remotely access your computer.
http://www.teamviewer.com/download/index.aspx 
But It looks like we need opposite - I need to connect to your machine.
Anyway - I installed teamwork.
Edited by: le on Aug 10, 2010 2:42 PM 
hey lev
could you send me your email address so we can discuss it over messenger. 
It is :
erusalimskiy#hotmail.com
Lev

How to supress the header if there is no detail record in the report

Hi,
I am trying to create a report using the TEXT_IO utility in oracle forms. The header part is created in one program unit and detail part is created in another program unit. How can I supress the header if there is no detail part? I came to know that I can use HOST command . How can I use this? Can any one help me in resolving this?
My report should apppear in the format specified below.
case number : 1
Name Address1 Address2 State ZIp
XXXXX XXXXXXX XXXXXXXx XXXXX XXXxx
case number: 2
Name Address1 Address2 State Zip
YYYYY YYYYYY YYYYYYY YYYYY YYYY
Edited by: 837462 on Feb 16, 2011 6:12 PM 
Hi
i think it's a better idea to paste ur code here...
but logically... i supposed u have 2 function header_function & detail_function e.g. both functions returns either 1 or 0 if there is data then it returns and the contrary if returns 0
In the calling these functions i e.g. WHEN-BUTTON-PRESSED u need to validate as follow
DECLARE
v_header_output  varchar(2);
v_detail_output  varchar(2);
BEGIN
v_header_output :=  header_function ( PARAMETERS ... , ,);
v_detail_output :=  header_function ( PARAMETERS ... , ,);
IF  v_detail_output = 0 THEN
v_header_output := 0;   -- or u can do some works that change the return of the   header_function  to null or 0
END IF;
END;Hope it works...
Regards,
Abdetu...

Trouble referencing return values of a Function in Stored Procedure

Hello all,
I have the following Function and Stored Procedure (simplified for this post). The error I am getting is in the SELECT, WHERE clause of the UPDATE command. My function is returning a sys_refcursor and I am trying to reference column names but keep getting the error
ORA-00904: "PUBLISHEDDOCVERSIONID": invalid identifier
What am I doing wrong?
Function
CREATE OR REPLACE FUNCTION &HKDB_Schema_Name..QueryKnowdeDocVersion(aKGID int)
RETURN sys_refcursor
IS
aCur sys_refcursor;
BEGIN
OPEN aCur FOR
SELECT pkd.KnowdeID as "PublishedKnowdeID", pkd.DocumentID as "PublishedDocID", pkd.VersionID as "PublishedDocVersionID"
FROM KnowdeDocument pkd
WHERE pkd.KGID = aKGID;
RETURN aCur;
END;Stored Procedure
UPDATE KnowdeDocument
SET DocumentID = (SELECT PublishedDocID FROM (SELECT QueryKnowdeDocVersion(aKGID, aPublishedKGID, aTextVersionID) FROM DUAL)
                  WHERE PublishedKnowdeID = KnowdeDocument.KnowdeID
                  AND PublishedDocID = KnowdeDocument.DocumentID
                  AND ((PublishedDocVersionID is NULL and KnowdeDocument.VersionID is NULL) OR (PublishedDocVersionID is not NULL AND
                           PublishedDocVersionID = KnowdeDocument.VersionID)))Regards,
Toby 
Try this sql query in your function.
SELECT pkd.KnowdeID as PublishedKnowdeID, pkd.DocumentID as PublishedDocID, pkd.VersionID as PublishedDocVersionID
FROM KnowdeDocument pkd
WHERE pkd.KGID = aKGID; 
Thankyou for the reply, but removing the " " still gives the same error...... 
Hi redeye,
Your select is from:
(select queryknowdedocversion(akgid
                              ,apublishedkgid
                              ,atextversionid)
                     from dual)This has no publisheddocversionid. Unfortunately I cannot figure out what you are trying to do with that ref_cursor.
Edit:
Or perhaps publisheddocversionid is a column in KnowdeDocument? Could this be case sensitive as well? (Bad idea to use quotations when naming things)
Regards
Peter
Edited by: Peter on Aug 24, 2009 3:39 AM 
Your function is returning a REF CURSOR. You cannot use it in SELECT statement. If you intend to use your function as a table then you must create it as a PIPELINED function. Read about it in the Oracle Document. 
From a performance point of view which would be better? Bearing in mind that within the SProc I would have called the function several times
1. Getting rid of the function and moving the SELECT into the SProc, then doing a cast on the table whenever i need to query it.
or
2. Calling the function multiple times from within the SProc whenever I need to query the data
Regards,
Toby 
Check this:
http://www.oracle.com/technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf
and:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:60122715103602
First of all you'd always test and measure your various scenario's, check the execution plans and or trace/tkprof and pick the best one, based on your requirements.
But based on your description option 1 would be imo preferred.
But again: only running some tests will give you the exact answer.

Categories

Resources