DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: bind variables

  1. #1
    Join Date
    Jun 2001
    Posts
    316
    hi
    Is it possible for me to bind and array?

    If no..is there a possiblity and i send more than 1 value using a stored procedure?
    cause rite now using bind varibales i am able to send only 1 value of result and not all of it...
    I hope i was clear enuff
    thnx

  2. #2
    Join Date
    Jun 2001
    Posts
    316
    a reminder
    hi
    Is it possible for me to bind an array?

    If no..is there a possiblity and i send more than 1 value using a stored procedure?
    cause rite now using bind varibales i am able to send only 1 value of result and not all of it...
    I hope i was clear enuff
    thnx

  3. #3
    Join Date
    Jun 2001
    Posts
    316

    Unhappy

    marist89..

    pls help me
    :(

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    It's not really clear what you want to do.
    Jeff Hunter

  5. #5
    Join Date
    Jun 2001
    Posts
    316
    hi.. heres the porblem

    DECLARE
    a2 varchar2(20):='#';
    TYPE p_tab IS TABLE OF varchar2(200) index by binary_integer;
    lAnswer p_tab;
    i number:=0;
    BEGIN
    FOR x IN (SELECT distinct id FROM TEST_SAM group by id) LOOP
    lAnswer(i):='#';
    FOR y IN (SELECT name FROM TEST_SAM where id=x.id) LOOP
    if a2 ='#' then
    a2 := x.id||','||y.name;
    else
    a2 := a2||','||y.name;
    end if;
    end loop;
    lAnswer(i):=a2;
    i:=i+1;
    a2:='#';
    END LOOP;
    END;

    This works fine in sql plus..with me binding the plsql table lAnswer.

    When i try to bind lAnswer(some thing lisk this :lAnswer) ,it gives me an error in sql plus asking me to define the bind variable(lAnswer)

    Actually i need to bind this varibale and use it in php for displaying in browser.

    Thanx

  6. #6
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Gee, surely this was a convoluted exposition, if I have seen one before , but let´s try :

    BIND variables are variables created in your programming environment used with direct values (NO references). The sql*plus env can create only a few types of bind vars. See :
    when I try :

    scott#PO7::SQL>> variable x array

    sql*plus says : hey, I can´t create this type, only :

    Usage: VAR[IABLE] [ variable [ NUMBER | CHAR | CHAR (n) |
    VARCHAR2 (n) | REFCURSOR ] ]
    scott#PO7::SQL>

    BUT, if sql*plus can´t cope with this, PL/SQL can - it know many types of vars, including arrays. Better yet, you CAN create a PL/SQL var to be "public", putting it inside a package spec. This way :

    SQL>> create OR REPLACE package MY_VARS is
    -- put vars here
    TYPE p_tab IS TABLE OF varchar2(200) index by binary_integer;
    v_answer p_tab;
    -- =====================================
    END MY_VARS;


    And it´s all, folks ! We can put values and read values in this "public" array, using stored procedures or whatever, see :

    1 create or replace procedure P_ANSWERS is
    2 BEGIN
    3 MY_VARS.v_answer(1) := 'Line 1 of answers';
    4 MY_VARS.v_answer(2) := 'Line 2 of answers';
    5 MY_VARS.v_answer(3) := 'Line 3 of answers';
    6* END;
    scott#PO7::SQL>/

    Procedure created.

    scott#PO7::SQL>exec P_ANSWERS

    PL/SQL procedure successfully completed.

    scott#PO7::SQL>set serveroutput on
    scott#PO7::SQL>exec dbms_output.put_line('1 =>' || MY_VARS.v_answer(1));
    1 =>Line 1 of answers

    PL/SQL procedure successfully completed.

    scott#PO7::SQL>exec dbms_output.put_line('2=>' || MY_VARS.v_answer(2));
    2=>Line 2 of answers

    PL/SQL procedure successfully completed.

    scott#PO7::SQL>exec dbms_output.put_line('3=>' || MY_VARS.v_answer(3));
    3=>Line 3 of answers

    PL/SQL procedure successfully completed.

    scott#PO7::SQL>


    I don´t know PHP, but surely it works right away in PL/SQL : See the PL/SQL manual for more details.

    []s
    Chiappa





Posting Permissions

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


Click Here to Expand Forum to Full Width