This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Sep 19, 2013 4:43 AM by 58882234-5742-419b-ad62-73e81e7b028a RSS

Need assistance with Forms, plsql

58882234-5742-419b-ad62-73e81e7b028a Newbie
Currently Being Moderated

Hi, am new to oracle and doing a project on the same

I have the following code and would like to store the variable 'wid' for my next step but unfortunately am getting an error > bad bind variable. How can i go about it? Any assistance will be highly appreciated

 

Code>>

 

DECLARE

  U_ID VOTERS.V_ID%TYPE;

  PASS VOTERS.PASSWORD%TYPE;

  CURSOR LOGIN_CUR IS

  SELECT V_ID,PASSWORD FROM VOTERS;

 

BEGIN

  SELECT W_ID INTO :WID FROM VOTERS WHERE V_ID=:V_ID;

BEGIN

  OPEN LOGIN_CUR;

  LOOP

  FETCH LOGIN_CUR INTO U_ID,PASS;

  IF :V_ID=U_ID AND :PASSWORD=PASS THEN

  OPEN_FORM ('........');

  EXIT WHEN LOGIN_CUR%FOUND;

 

  END IF;

END LOOP;

CLOSE LOGIN_CUR;

END;

END;

  • 2. Re: Need assistance with pl/sql
    Priyasagi Pro
    Currently Being Moderated

    Hi,

     

    Post the complete error message.

    If :wid is the field name then mention that field name with preceding block name.

    Ex.

    :<block_name>.<field_name>

    :voters.wid

  • 3. Re: Need assistance with pl/sql
    Christian Erlinger Guru
    Currently Being Moderated

    Where did you declare :password, :v_id and :wid? Are those forms items? If so this works if there is only one block containing items with those names. If there are multiple blocks with a wid item this won't work. So the first rule is: always reference your items with :block_name.item_name.

     

    Then you use open_form which will terminate the execution of all the code afterwards. So even if your cursor actually does return more then one records you'd only call your form once - if this is what you want then fine but if you want to be the form called for every record in your cursor your code is wrong.

     

    So your code translates to this:

     

    declare
     U_ID VOTERS.V_ID%TYPE;
      PASS VOTERS.PASSWORD%TYPE;
    begin
    select v_id, password
    into u_id, pass
    from voters
    where password = :your_block.password
    and v_id = :your_block.u_id;
    --
    open_form('....');
    exception
    when no_data_found then
    null;
    end;
    
    

     

    I don't know why you select the :wid first, but I guess you have your reasons...

     

    If this is some sort of logon/password verification then I sincerely hope you have hashed the passwords you store in your voters table btw.

     

    cheers

     

    Message was edited by: ChristianErlinger How on earth did I get to the SQL and PL/SQL forum? Ah, I guess this was the @mentioned tag thingie which pulled this one to the forms forum...

  • 4. Re: Need assistance with pl/sql
    58882234-5742-419b-ad62-73e81e7b028a Newbie
    Currently Being Moderated

    Hi Priyasagi,

    Actually it is voters.w_id (am just querying one table 'voters' at the moment for all columns). if its still unclear i can re post the whole code with changes?

    The exact error message in forms is error 49 at line 8, column 20 bad bind variable 'WID'

  • 5. Re: Need assistance with pl/sql
    58882234-5742-419b-ad62-73e81e7b028a Newbie
    Currently Being Moderated

    Hi Christian, haven't declared :password and :v_id because they are form items yes and they are in one block. will sort out the referencing, thanks.

    Not declaring :w_id is because am trying to 'hold' it (if i may say) as a bind for my next step and don't know how to declare binds in forms/plsql.

    selecting w_id first is because i want to get a specific w_id generated from the :v_id input in the inner block(second select).

    Hashing the passwords, thats atleast one thing i did right, hehe.

     

    Didn't understand what you meant by this part 'So even if your cursor actually does return more then one records you'd only call your form once - if this is what you want then fine but if you want to be the form called for every record in your cursor your code is wrong.' Do clarify.

     

    One more thing, is there a way i can hold the w_id because i need to call it on my next step on a totally different form

  • 6. Re: Need assistance with pl/sql
    Christian Erlinger Guru
    Currently Being Moderated

    From the documentation on open_form for the +activate_mode+ parameter:

     

    When you open a form with ACTIVATE specified (the default), the opened form receives focus immediately; trigger statements that follow the call to OPEN_FORM never execute. When you open a form with NO_ACTIVATE specified, trigger statements that follow the call to OPEN_FORM will execute after the opened form has been loaded into memory and its initial start-up triggers have fired.

    So this means the rest of your loop won't get executed if you reach the open_form built-in.

     

    Not declaring :w_id is because am trying to 'hold' it (if i may say) as a bind for my next step and don't know how to declare binds in forms/plsql.

    Well, there are parameters, forms globals, package globals which you could use depending on your needs. See your forms builder documentation on how to make use of them.

     

    cheers

  • 7. Re: Need assistance with pl/sql
    Priyasagi Pro
    Currently Being Moderated

    Hi,

     

    How many blocks you are having at present?

    If you are working with master-detail form, you should give block_name.field_name other wise form will return bad bind variable error message.

  • 8. Re: Need assistance with pl/sql
    58882234-5742-419b-ad62-73e81e7b028a Newbie
    Currently Being Moderated

    Hi Priyasagi,


    For this particular form, i only have one block called LOG_IN which refers to voters.v_id and voters.password.

    It is not a master detail.

     

    Are you suggesting i name the fields> log_in.w_id using the block name or voters.w_id using the table name

  • 9. Re: Need assistance with pl/sql
    58882234-5742-419b-ad62-73e81e7b028a Newbie
    Currently Being Moderated

    Thanks Christian, will have a look and see what i can come up with.

  • 10. Re: Need assistance with pl/sql
    Priyasagi Pro
    Currently Being Moderated

    On which trigger you placed above said query?

    What are all the fields exists on your log_id block?

  • 11. Re: Need assistance with pl/sql
    58882234-5742-419b-ad62-73e81e7b028a Newbie
    Currently Being Moderated

    I have placed the query in a 'Button' > the trigger is 'when button pressed'

    My form has three items

    (1) Text item (v_id) which is linked to my Voters.v_id

    (2) Text item (password) linked to Voters.password

    (3) Button (login) in which am placing the code in > when button pressed

     

    The block Name is Log_In

  • 12. Re: Need assistance with pl/sql
    Priyasagi Pro
    Currently Being Moderated

    My god, Where is :wid?

     

    Your are selecting a value w_id from voters table and putting into a variable.
    If the variable is the field of the block then you call it as :wid or otherwise you have to declare it as local variable in the declaration part.

     

    SELECT W_ID INTO :WID FROM VOTERS WHERE V_ID=:V_ID;

     

    WID is not declared, so you have to declare or otherwise add a field wid to log_in block.

  • 13. Re: Need assistance with pl/sql
    58882234-5742-419b-ad62-73e81e7b028a Newbie
    Currently Being Moderated

    Haha, Priyasagi,

    :wid is not a field on the form because am not inputting the value manually, it is being picked from voters.w_id as a result of knowing the value of  :w_d (in the inner block)

    And i haven't declared wid at the declare section because it is a bind(:wid), Declaring it as wid(normal) will not store the variable for another form to access, will it?

    Am looking for a way to hold the value of w_id to be able to call it from another block/form, what other way can i use apart from bind? Are you conversant with procedures, records,collections? Is there any i can use to hold a value for a particular session?

    And thanks for the efforts, highly appreciated!

  • 14. Re: Need assistance with pl/sql
    Priyasagi Pro
    Currently Being Moderated

    Hi,

     

    By declaring global variable you can pass values between forms.

     

    For example

    --- to declare a global variable in pre-form trigger

     

    :global.wid:=null;

    or

    default_value(null,'global.wid');

     

    --- then on your when-button-pressed trigger of login button

    SELECT W_ID INTO :GLOBAL.WID FROM VOTERS WHERE V_ID=:V_ID;


    --- to destroy a global variable and release its memory, use Erase built-in procedure;


    erase('global.wid');


    I think the following discussion is helpful to you:

    Pass global variable between two forms

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points