Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Thumbs up Unanswered: converting SQL query into Stored procedure

    hi this is naresh.......
    this is my sql query and i want to convert the same into Stored procedure and i do i am getting sme errors plz specify solution....
    i excuted this in oracle sql developer..

    SELECT distinct ti.task_instance_id AS TASK_ID ,
    wti.work_trans_inst_id AS WORK_TRANS_INST_ID ,
    ti.create_dt AS FALLOUT_CREATED_DATE ,
    ti.task_instance_status AS FALLOUT_STATUS ,
    pi1.master_request_id ,
    pi1.process_instance_id AS pid ,
    nee.backoffice_order_id ,
    nee.order_id AS requestId ,
    nee.engg_order_id ,
    pi.master_request_id
    FROM work_transition_instance wti,
    process_instance pi1 ,
    activity_instance ai ,
    task_instance ti ,
    networx_external_event nee ,
    variable_instance vi ,
    process_instance pi
    WHERE pi1.owner_id =wti.process_inst_id
    AND ti.task_instance_owner_id=pi1.process_instance_id
    AND wti.dest_inst_id = ai.activity_instance_id
    AND ai.status_message LIKE '%com.qwest.networx.workflow.activity.impl.adapter %'
    AND ai.status_cd NOT IN(4,5)
    AND wti.status_cd NOT IN (6,10)
    AND pi1.status_cd NOT IN (4)
    and vi.variable_value = nee.networx_external_event_id
    AND vi.variable_id = '110'
    AND pi.process_instance_id = vi.process_inst_id
    AND pi.master_request_id = pi1.master_request_id



    and my Stored procedure is

    create OR REPLACE PROCEDURE MyProc4
    (
    new_WORK_TRANS_INST_ID OUT NUMBER,
    new_TASK_ID OUT NUMBER,
    new_FALLOUT_CREATED_DATE OUT date ,
    new_FALLOUT_STATUS OUT VARCHAR2 ,
    new_master_request_id OUT NUMBER,
    new_pid OUT NUMBER,
    new_backoffice_order_id OUT NUMBER,
    new_requestId OUT NUMBER,
    new_engg_order_id OUT NUMBER,
    new_master_request_id OUT NUMBER
    )

    As

    BEGIN

    SELECT distinct ti.task_instance_id AS TASK_ID,
    wti.work_trans_inst_id AS WORK_TRANS_INST_ID ,
    ti.create_dt AS FALLOUT_CREATED_DATE ,
    ti.task_instance_status AS FALLOUT_STATUS ,
    pi1.master_request_id ,
    pi1.process_instance_id AS pid ,
    nee.backoffice_order_id ,
    nee.order_id AS requestId ,
    nee.engg_order_id ,
    pi.master_request_id
    FROM work_transition_instance wti,
    process_instance pi1 ,
    activity_instance ai ,
    task_instance ti ,
    networx_external_event nee ,
    variable_instance vi ,
    process_instance pi
    WHERE pi1.owner_id =wti.process_inst_id
    AND ti.task_instance_owner_id=pi1.process_instance_id
    AND wti.dest_inst_id = ai.activity_instance_id
    AND ai.status_message LIKE '%com.qwest.networx.workflow.activity.impl.adapter %'
    AND ai.status_cd NOT IN(4,5)
    AND wti.status_cd NOT IN (6,10)
    AND pi1.status_cd NOT IN (4)
    and vi.variable_value = nee.networx_external_event_id
    AND vi.variable_id = '110'
    AND pi.process_instance_id = vi.process_inst_id
    AND pi.master_request_id = pi1.master_request_id;

    END



    and i am getting these errors
    Error starting at line 1 in command:
    Execute myproc4;
    Error report:
    ORA-06550: line 1, column 7:
    PLS-00905: object NTWRX.MYPROC4 is invalid
    ORA-06550: line 1, column 7:PL/SQL: Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why would you convert it into PL/SQL?

    PL/SQL's SELECT requires INTO. What will you with those values, then? Note that you might (and, most probably, will) end up with the TOO-MANY-ROWS (if SELECT returns more than a single record).

    Anyway: you should read PL/SQL User's guide and reference, as well as Application Developer's Guide - Fundamentals as it appears that *fundamentals* is what you are missing.

  3. #3
    Join Date
    Sep 2004
    Posts
    15
    As littlefoot mentioned!

    I too am cofused by your question! What do you want to achieve by converting your SQL into a Stored Procedure?

    Looking at your "OUT" parameters, it seems like you want to return the values of the SQL "OUT" to the calling module. Why dont just use the SQL in the place where you want the values returned?

    There are too many things misisng in your post, please explain what is it that you are trying to achieve and why.

    Do read the links shared by littlefoot

  4. #4
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    As said you need to fetch values in to your out params and if your query returns more than a row, they have to be a collection type. Pls let know ur req.
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

Posting Permissions

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