1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help to convert stored procedure to dynamic sql query to get out value in select statement

Discussion in 'SQL PL/SQL' started by kosum, Sep 21, 2016.

  1. kosum

    kosum Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    usa
    I need help with changing the stored procedure to dynamic Sql query to get the output

    create or replace
    PROCEDURE STAR_SEARCH_LABELS
    (
    P_LABELLIST IN VARCHAR2,
    P_STAR_LABELS OUT SYS_REFCURSOR
    )
    IS
    --- passing p_LabelList (Barcode1,Barcode2,Barcode3,...) p_labelList VARCHAR2(1000);
    v_labelList VARCHAR2(1000);
    TYPE BarCodeList IS TABLE OF VARCHAR2(34)
    INDEX BY BINARY_INTEGER;
    v_Labels BarCodeList;
    idx NUMBER := 0;
    pos NUMBER := 0;
    v_STAR_labels_tab CAR.STAR_web_labels_ntt := CAR.STAR_web_labels_ntt();
    l_comma VARCHAR2(1) := ',';
    l_arrayCount INTEGER;
    l_is_exists NUMBER ;
    l_me_exists NUMBER ;


    BEGIN
    ----begin to create Array ----------------------------------------------------
    --call to put Bar Code List into an Array
    BEGIN
    v_labellist := p_labellist ;
    -- determine first chuck of string
    pos := instr(v_labellist,l_comma,1,1);
    -- while there are chunks left, loop
    WHILE ( pos != 0) LOOP
    -- increment counter
    idx := idx + 1;
    -- create array element for chuck of string
    v_labels(idx) := trim(substr(v_labellist,1,pos-1));
    -- remove chunk from string
    v_labellist := substr(v_labellist,pos+1,length(v_labellist));
    -- determine next chunk
    pos := instr(v_labellist,l_comma,1,1);

    -- no last chunk, add to array
    IF pos = 0 THEN
    v_labels(idx+1) := trim(v_labellist);
    l_arrayCount := (idx + 1);
    END IF;
    END LOOP;
    END;
    ---end of creating Array------------------------------------------------------
    IF v_labels.COUNT > 0 THEN
    FOR idx in v_labels.FIRST .. v_labels.LAST
    LOOP
    -- CAR_bar_ code is v_labels(idx);

    v_STAR_labels_tab.EXTEND;
    -- initialize composite v_STAR_labels_tab()
    v_STAR_labels_tab(v_STAR_labels_tab.LAST) := CAR.STAR_web_labels_ot(v_labels(idx) , 'N', NULL, NULL, NULL,0,0,'N' );
    SELECT count(1) into L_IS_EXISTS FROM CAR.INBOUND_STATUS_T
    WHERE LABELID = v_labels(idx) ;
    CASE WHEN L_IS_EXISTS = 1
    THEN v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_adv_info := 'Y';
    ELSE v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_adv_info := 'N';
    END CASE;

    -- could have multiple barcode records in medici_event_t, so do not decode over previous selection
    SELECT count(*) into L_ME_EXISTS FROM CAR.MEDICI_EVENT_T
    WHERE ME_CAR_BAR_CODE = v_labels(idx) ;
    IF L_ME_EXISTS >= 1
    THEN
    FOR j in (select me_CAR_bar_code, me_event_code, me_event_source, me_comments
    FROM CAR.MEDICI_EVENT_T
    WHERE me_CAR_bar_code = v_labels(idx))
    LOOP
    IF j.me_event_code = 'G1' THEN
    v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_hold := 1;
    -- always present the STAR comment over the CBP comment
    IF j.me_event_source = 'STAR' and j.me_comments IS NOT NULL THEN
    v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_hold_comment := j.me_comments;
    ELSE
    IF v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_hold_comment IS NULL THEN
    v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_hold_comment := j.me_comments;
    END IF;
    END IF;
    END IF;
    IF j.me_event_code = 'E3' THEN
    v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_release := 1;
    -- always present the STAR comment over the CBP comment
    IF j.me_event_source = 'STAR' and j.me_comments IS NOT NULL THEN
    v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_release_comment := j.me_comments;
    ELSE
    IF v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_hold_comment IS NULL THEN
    v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_release_comment := j.me_comments;
    END IF;
    END IF;
    END IF;
    IF j.me_event_code = 'F' THEN
    v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_dispatched := 'Y';
    END IF;
    v_STAR_labels_tab(v_STAR_labels_tab.LAST).ul_event_source := j.me_event_source;

    END LOOP;
    END IF;

    END LOOP;
    END IF;

    -- p_STAR_labels := v_STAR_labels_tab;

    -- open the table to reference for java program
    OPEN P_STAR_LABELS FOR
    SELECT ul_labelid,
    ul_adv_info,
    ul_hold_comment,
    ul_release_comment,
    ul_event_source,
    ul_hold,
    ul_release,
    ul_dispatched
    FROM Table(Cast(v_STAR_labels_tab As STAR_WEB_LABELS_NTT))
    ORDER BY UL_LABELID;

    END STAR_SEARCH_LABELS;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Last edited: Sep 28, 2016