Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: Creating a stored procedure which return a ResultSet.

    Hi Guys,

    I have a question here,

    Im planning to call an oracle procedure from external application (ASP.NET) and pass some parameters, then this oracle procedure will process the records, and return back the results as a resultset.

    Is it possible for a oracle procedure to do the following operations and return
    a resultSet after it completes

    Workflow
    1) Perform a query
    2) Process each record in the query, based on the parameter received, check
    whether the parameter plus the first column in the record exists in table A,
    If exists, change value of some column in the query results (dont update
    the table),
    If the parameter and the first column in the record does not exists in table
    A, the the record remains and process with the next record
    3) Return back the results as a ResultSet. I dont want to use DBMS_OUTPUT.PUT_LINE to display the record, but i want to return it back as a recordset


    Im aware that by using cursor, it can process each records, but im not really sure how to get started.Is the operation above possible by the way (updating certain columns in the query results without updating the actual table in an oracle procedure) and returning back the results as resultset?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Re returning a result set from a procedure to ASP, the easiest way is to create a function that returns a REF CURSOR:
    Code:
    SQL> create or replace function f1 return sys_refcursor is
      2   rc sys_refcursor;
      3  begin
      4    open rc for select * from emp;
      5    return rc;
      6  end;
      7  /
    
    Function created.
    Now you can Execute that function from ASP just like you'd Execute a select statement, and process the recordser in the same way.

    Updating columns in the query results can be done in the SELECT statement of the cursor in various ways:
    select col+1 as x ...
    select case when col = 'Y' then 'Yes' else 'No' end as x ...
    select (select count(*) from b where b.id = a.id) as x from a...
    ...

    Without knowing your precise requirements it's hard to be more specific.

  3. #3
    Join Date
    Feb 2005
    Posts
    116

    Question

    [QUOTE=andrewst]Re returning a result set from a procedure to ASP, the easiest way is to create a function that returns a REF CURSOR:
    Code:
    SQL> create or replace function f1 return sys_refcursor is
      2   rc sys_refcursor;
      3  begin
      4    open rc for select * from emp;
      5    return rc;
      6  end;
      7  /
    
    Function created.
    QUOTE]


    thanks for the response..

    just a question,

    lets say i have three queries. just an example, not the real queries, just sample

    FIRST QUERY
    Code:
    select id from name;
    SECOND QUERY
    Code:
    select age from age from nameid = 'the first id in the first query'
    THIRD QUERY
    Code:
    select address from address from nameid = 'the first id in the first query'
    Actually we can combine all this queries to get the results by writing the sql query, but i just want to understand how to write a function to output the following results

    So this is what i plan to achive

    The first query will act as an input to the second and third query
    Meaning the second and third query will loop through all records in the first query and combine the results. Lets say there are three items in the FIRST Query, it will then loop QUERY2 and QUERY3 for three times and combine the results

    Meaning it will combine and become like this

    First time loop
    Query 2 results
    Query 3 results
    Second time loop
    Query 2 results
    Query 3 results
    Third time loop
    Query 2 results
    Query 3 results

    So how do i loop and append the resultset of the firstloop+secondloop+thirdloop to the sys_refcursor variable..

    Really appreciate if can provide some assistance to get me started with this..
    Hope this operation is possible

  4. #4
    Join Date
    Feb 2005
    Posts
    116
    Hi Guys,

    Im near to what i plan to achieve, just like normal, there is some problem here.Below is my code

    Code:
    SQL> CREATE PROCEDURE f2 is  
      2          /* Output variables to hold the result of the query: */
      3        a shortrulename.shortrulenameid%TYPE;
      4          /* Cursor declaration: */
      5       CURSOR T1Cursor IS
      6            SELECT shortrulenameid FROM shortrulename;
      7    BEGIN
      8       OPEN T1Cursor;
      9       LOOP
     10              /* Retrieve each row of the result of the above query
     11                 into PL/SQL variables: */
     12           FETCH T1Cursor INTO a;
     13              /* If there are no more rows to fetch, exit the loop: */
     14           EXIT WHEN T1Cursor%NOTFOUND;
     15              /* My Query: */
     16           select rulenameid from rulename where shortrulenameid = a union all
     17           select trackid from cggrouplink,shortrulename where shortrulename.shortrulenameid = cg
    grouplink.shortrulenameid;
     18           commit;
     19       END LOOP;
     20          /* Free cursor used by the query. */
     21       CLOSE T1Cursor;
     22   END f2;
     23  /
    And i get this error, its saying i must select into something? Im not sure
    16/10 PLS-00428: an INTO clause is expected in this SELECT statement

    And how do i put the results of each query results from loop into some cursor so that if i retrieve data from this procedure later, it will output a resultset?

    Meaning the output that i achieve will be in queryresults

    Loop 1
    Query Results (From line 16 & 17)
    Loop 2
    Query Results (From line 16 & 17)
    Loop n
    Query Results (From line 16 & 17)

    Thank you very much
    Last edited by a1jit; 03-28-06 at 02:48.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You cannot append results to a SYS_REFCURSOR variable. All you can do is open the SYS_REFCURSOR variable for a SELECT statement. The SELECT statement must get all the data in one go.

    To do it your way you would need to first store the results somewhere (like another table or a collection) and then open the cursor to select from those results. It is much easier and better practice to do it all in a SELECT statement if that can be done, which it clearly can in this case.

  6. #6
    Join Date
    Feb 2005
    Posts
    116
    Quote Originally Posted by andrewst
    You cannot append results to a SYS_REFCURSOR variable. All you can do is open the SYS_REFCURSOR variable for a SELECT statement. The SELECT statement must get all the data in one go.

    To do it your way you would need to first store the results somewhere (like another table or a collection) and then open the cursor to select from those results. It is much easier and better practice to do it all in a SELECT statement if that can be done, which it clearly can in this case.

    thanks for the reply..i tried out your solution on trying to do it using a query, it worked, but now i have to blocks of query, like below

    first query
    ========

    Code:
    SELECT
       J.NAME || '_' || A.NAME as MODEL, 
    case
       WHEN I.FORMAT = '<area>:<checkname>' then E.AREA || ':' || 
    
    D.NAME
       WHEN I.FORMAT = '<ar' THEN E.AREA
       ELSE NULL
    END NAME,
    
    case when
    exists (select 1 from checkcycleshortrulename where 
    
    checkcycleshortrulename.rulenameid = E.rulenameid and 
    
    checkcycleshortrulename.checkcycleid = g.checkcycleid) 
    then F.RANGEVALUE1 || ',' || F.RANGEVALUE2 || ',' || F.RANGEVALUE3
    else '-3,-2,-1'
    end VALUE
    
    FROM
       TEST.TECHNOLOGYCLASS A
     , TEST.SUBGROUPMODEL B
     , TEST.MODELRULENAME F
     , TEST.RULENAME E
     , TEST.CHECKFORMAT I
     , TEST."GROUP" H
     , TEST.CHECKCYCLE J
     , TEST.MODEL G
     , TEST.SHORTRULENAME D
     , TEST.SUBGROUP C
    WHERE
         (B.SUBGROUPID = C.SUBGROUPID)
     AND (D.SUBGROUPID = C.SUBGROUPID)
     AND (E.SHORTRULENAMEID = D.SHORTRULENAMEID)
     AND (F.RULENAMEID = E.RULENAMEID)
     AND (F.SUBGROUPMODELID = B.SUBGROUPMODELID)
     AND (B.MODELID = G.MODELID)
     AND (G.TECHNOLOGYCLASSID = A.TECHNOLOGYCLASSID)
     AND (C.GROUPID = H.GROUPID)
     AND (D.CHECKFORMATID = I.CHECKFORMATID)
     AND (G.CHECKCYCLEID = J.CHECKCYCLEID)
     AND (H.GROUPID = (SELECT GROUPID FROM TEST."GROUP" WHERE NAME = 
    
    'Fabrication'))
     AND (C.SUBGROUPID = (SELECT SUBGROUPID FROM TEST.SUBGROUP WHERE 
    
    NAME = 'Drill Checks')) 
     order by g.modelid, D.shortrulenameid


    second query
    ==========


    Code:
    SELECT 
       H.NAME || '_' || A.NAME AS MODEL, D.NAME AS NAME, E.VALUE AS 
    
    VALUE
    FROM
       TEST.TECHNOLOGYCLASS A
     , TEST.MODELPARAMETER E
     , TEST.PARAMETER D
     , TEST.MODEL F
     , TEST."GROUP" GR
     , TEST.CHECKCYCLE H
     , TEST.SUBGROUP C
     , TEST.SUBGROUPMODEL B
    WHERE
         (B.SUBGROUPID = C.SUBGROUPID)
     AND (D.SUBGROUPID = C.SUBGROUPID)
     AND (E.SUBGROUPMODELID = B.SUBGROUPMODELID)
     AND (E.PARAMETERID = D.PARAMETERID)
     AND (B.MODELID = F.MODELID)
     AND (F.TECHNOLOGYCLASSID = A.TECHNOLOGYCLASSID)
     AND (C.GROUPID = GR.GROUPID)
     AND (F.CHECKCYCLEID = H.CHECKCYCLEID)
     AND (GR.GROUPID = (SELECT "GROUP".GROUPID FROM "GROUP" WHERE 
    
    "GROUP".NAME = 'Fabrication'))
     AND (C.SUBGROUPID = (SELECT SUBGROUP.SUBGROUPID FROM SUBGROUP 
    
    WHERE SUBGROUP.NAME = 'Drill Checks'))
     ORDER BY F.MODELID;

    Now, i plan to merge both the queries using 'UNION ALL'
    but it doesnt work..
    (Error): ORA-00933: SQL command not properly ended

    Its highlighting the union all statement and throwing out the error above..
    When i throw out the 'order by clause' in both queries, i works.

    Actually i want to merge and sort it the whole query results again according to data in first column. Appreciate if someone could guide me.

    Actually the first query returns some data shown as below

    Model Name value
    xx abc 10
    yy abd 20


    The second query returns some data shown as below

    Model Name value
    xx ab 101
    yy abr 201

    So im planning to merge both of this and give me the following

    Model Name value
    xx abc 10
    xx ab 101
    yy abd 20
    yy abr 201

    Meaning it merges all data in both queries and sorts it again according to the 'model' (data in first column)

    Really appreciate if someone could help me out here..thank you very much
    Last edited by a1jit; 03-29-06 at 06:13.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Please post the failing UNION ALL query.

  8. #8
    Join Date
    Feb 2005
    Posts
    116
    Quote Originally Posted by andrewst
    Please post the failing UNION ALL query.
    hi andrewst, manage to rectify it, i was not aware that i cannot have order by clause within each query when im using union all operation, strange..


    How can i accomplish this..


    Currently im doing the following (bad idea i feel)

    query 1
    select col1, col2, someid as id

    query 2
    select col1, col2, 99999999 as id

    then i combine using the union all

    select t.col1,t.col2,t.id from
    (select col1, col2, someid as id
    union all
    select col1, col2, 99999999 as id) t order by t.col1, t.id


    im just assigning some random number(large number) as the id in query-2 as i always want the results of second query to appear below the results of first...

    but if im unlucky, there might be a day where the id in query-1 might exceed 99999999, so can can i solve this problem..?

    thanks a lot
    Last edited by a1jit; 03-29-06 at 07:23.

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You can add a pseudo-column with 1 for the first subquery and 2 in your second subquery and then use it in first position in your ORDER BY clause. BTW you should use NULL as id in the second subquery instead of 99999999 as "unknown" will be more correct than an arbitrary value :

    Code:
    select t.col1,t.col2,t.id from
    (select col1, col2, someid as id, 1 as tag 
    union all
    select col1, col2, NULL as id, 2 as tag ) t 
    order by t.tag, t.col1, t.id
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  10. #10
    Join Date
    Feb 2005
    Posts
    116
    Quote Originally Posted by RBARAER
    You can add a pseudo-column with 1 for the first subquery and 2 in your second subquery and then use it in first position in your ORDER BY clause. BTW you should use NULL as id in the second subquery instead of 99999999 as "unknown" will be more correct than an arbitrary value :

    Code:
    select t.col1,t.col2,t.id from
    (select col1, col2, someid as id, 1 as tag 
    union all
    select col1, col2, NULL as id, 2 as tag ) t 
    order by t.tag, t.col1, t.id
    HTH & Regards,

    rbaraer
    Hi rbaraer,

    thank you very very much, that really helped a lott, just a question,

    Can i sort the record according to the field (ie: t.id) but i dont want to select it in the outer query?
    Last edited by a1jit; 03-29-06 at 21:27.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, you can:
    Code:
    SQL> SELECT t.ename FROM
      2  (SELECT ename, job FROM EMP WHERE deptno = 10
      3   UNION
      4   SELECT ename, job FROM EMP WHERE deptno = 20
      5  ) t
      6  ORDER BY t.job;
    
    ENAME
    ----------
    FORD
    SCOTT
    MILLER
    ADAMS
    SMITH
    CLARK
    JONES
    KING
    
    8 rows selected.
    
    SQL>
    However, this wouldn't be possible if you had an aggregate function, such as
    Code:
    SQL> SELECT t.ename, COUNT(*) FROM
      2  (SELECT ename, job FROM EMP WHERE deptno = 10
      3   UNION
      4   SELECT ename, job FROM EMP WHERE deptno = 20
      5  ) t
      6  GROUP BY t.ename
      7  ORDER BY t.job;
    ORDER BY t.job
             *
    ERROR at line 7:
    ORA-00979: not a GROUP BY expression
    
    SQL>

  12. #12
    Join Date
    Feb 2005
    Posts
    116
    hi,

    thanks a lot for the help, appreciate it..

    thanks again..

  13. #13
    Join Date
    Feb 2005
    Posts
    116
    Hi Guys,

    I have a small problem here..just take the query below as a sample

    Code:
    select t.col1,t.col2,t.tag,t.type from
    (select col1, col2, someid as id, 1 as tag, 'rule' as type
    union all
    select col1, col2, NULL as id, 2 as tag, 'para' as type ) t 
    order by t.tag, t.col1, t.id
    Its returns results in the following manner, which is just what i wanted

    col1 col2 tag type
    ====================
    mdl1 somdata 1 rule
    mdl1 somdata 1 rule
    mdl1 somdata 1 rule
    mdl1 somdata 2 para
    mdl2 somdata 1 rule
    mdl2 somdata 1 rule
    mdl2 somdata 2 para
    mdl2 somdata 2 para

    Now i plan to convert the resultset into xml format. Actually my first plan was do it at the application level, meaning i loop through
    each record and add the xml tags (time consuming), but now since i was the full query constructed,
    I plan to convert this into xml at database level but how can i achieve the results in following manner

    Code:
    <col1 = mdl1>
        <rule>
            <col2>somdata</col2>
            <col2>somdata</col2>
            <col2>somdata</col2>
        </rule>
        <para>
            <col2>somdata</col2>
        </para>
    </col1>
    <col1 = mdl2>
        <rule>
            <col2>somdata</col2>
            <col2>somdata</col2>
        </rule>
        <para>
            <col2>somdata</col2>
            <col2>somdata</col2>
        </para>
    </col1>
    thank you very much for any suggestions

  14. #14
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Well, you can easily get an XML document that would look like
    <RECORD>
    <FIELD1>
    value
    </FIELD1>
    ...
    </RECORD>
    ...
    Code:
    rbaraer@Ora10g> create table t1 (col1 varchar2(20 CHAR), col2 varchar2(20 CHAR), someid number);
    
    Table created.
    
    rbaraer@Ora10g> create table t2 (col1 varchar2(20 CHAR), col2 varchar2(20 CHAR));
    
    Table created.
    
    rbaraer@Ora10g> insert into t1 (col1, col2, someid) values ('mdl1','somdata',1);
    
    1 row created.
    
    rbaraer@Ora10g> insert into t1 (col1, col2, someid) values ('mdl1','somdata',2);
    
    1 row created.
    
    rbaraer@Ora10g> insert into t1 (col1, col2, someid) values ('mdl1','somdata',3);
    
    1 row created.
    
    rbaraer@Ora10g> insert into t1 (col1, col2, someid) values ('mdl2','somdata',3);
    
    1 row created.
    
    rbaraer@Ora10g> insert into t2 (col1, col2) values ('mdl1','somdata');
    
    1 row created.
    
    rbaraer@Ora10g> insert into t2 (col1, col2) values ('mdl2','somdata');
    
    1 row created.
    
    rbaraer@Ora10g> select t.col1,t.col2,t.tag,t.type from
    (select col1, col2, someid as id, 1 as tag, 'rule' as type from t1
    union all
    select col1, col2, NULL as id, 2 as tag, 'para' as type from t2) t
    order by t.tag, t.col1, t.id;  2    3    4    5
    
    COL1                                                         COL2
    ------------------------------------------------------------ ------------------------------------------------------------
           TAG TYPE
    ---------- ----
    mdl1                                                         somdata
             1 rule
    
    mdl1                                                         somdata
             1 rule
    
    mdl1                                                         somdata
             1 rule
    
    mdl2                                                         somdata
             1 rule
    
    mdl1                                                         somdata
             2 para
    
    mdl2                                                         somdata
             2 para
    
    
    6 rows selected.
    
    rbaraer@Ora10g> declare
      2      ctxXMLContext DBMS_XMLGEN.ctxHandle;
      3      cXmlText CLOB;
      4      iTmp Number;
      5      rc sys_refcursor;
      6  begin
      7
      8      open rc for
      9          select t.col1,t.col2,t.tag,t.type from
     10          (select col1, col2, someid as id, 1 as tag, 'rule' as type from t1
     11          union all
     12          select col1, col2, NULL as id, 2 as tag, 'para' as type from t2) t
     13          order by t.tag, t.col1, t.id;
     14
     15      ctxXMLContext := DBMS_XMLGEN.NEWCONTEXT(rc);
     16
     17      DBMS_XMLGEN.SETROWTAG(ctxXMLContext, 'RESULT'); -- defaults to ROW
     18
     19      cXmlText := DBMS_XMLGEN.GETXML(ctxXMLContext);
     20
     21      iTmp := length(cXmlText);
     22
     23      WHILE (iTmp >= 250) LOOP
     24          DBMS_OUTPUT.PUT_LINE(SUBSTR(cXmlText,length(cXmlText) - iTmp + 1,250));
     25          iTmp := iTmp - 250;
     26      END LOOP;
     27
     28      IF (iTmp > 0) THEN
     29          DBMS_OUTPUT.PUT_LINE(SUBSTR(cXmlText,length(cXmlText) - iTmp + 1,iTmp));
     30      END IF;
     31
    end;
     32   33  /
    <?xml version="1.0"?>
    <ROWSET>
     <RESULT>
      <COL1>mdl1</COL1>
      <COL2>somdata</COL2>
      <TAG>1</TAG>
      <TYPE>rule</TYPE>
     </RESULT>
    
    
    <RESULT>
      <COL1>mdl1</COL1>
      <COL2>somdata</COL2>
      <TAG>1</TAG>
      <TYPE>rule</TYPE>
     </RESULT>
     <RESULT>
      <COL1>mdl
    1</COL1>
      <COL2>somdata</COL2>
      <TAG>1</TAG>
      <TYPE>rule</TYPE>
     </RESULT>
     <RESULT>
      <COL1>mdl2</COL1>
      <COL2>somdata</COL2>
    
      <TAG>1</TAG>
      <TYPE>rule</TYPE>
     </RESULT>
     <RESULT>
      <COL1>mdl1</COL1>
      <COL2>somdata</COL2>
      <TAG>2</TAG>
      <TY
    PE>para</TYPE>
     </RESULT>
     <RESULT>
      <COL1>mdl2</COL1>
      <COL2>somdata</COL2>
      <TAG>2</TAG>
      <TYPE>para</TYPE>
     </RESULT>
    </ROWS
    ET>
    
    
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g>
    Sorry the results are not optimally formatted .

    At this point you would have to use DBMS_XSLPROCESSOR with the appropriate XSL file in order to re-format the XML output, but I fear that I won't be able to help you much with XSL , sorry .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  15. #15
    Join Date
    Feb 2005
    Posts
    116
    Helo RBARAER,

    thanks a milionn for the example, really appreciate it..
    thankss alot..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •