Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Unanswered: bind variables in select into

    Hi Gurus,

    Can i run bind variable in select into, please suggest. I get errors as below everytime as below

    SP2-0552: Bind variable "V_SIM_COUNT" not declared.
    ni71 SQL>@/u/carun/MTNN/bindvariables.sql
    SP2-0552: Bind variable "V_SIM_COUNT" not declared.
    ni71 SQL>@/u/carun/MTNN/bindvariables.sql
    SP2-0552: Bind variable "V_ERRORS_ALLOWED" not declared.

    code is as below. please suggest

    Code:
    DECLARE
    	v_udl_pk NUMBER;
    	v_errors_allowed NUMBER;
    	--var v_sim_count NUMBER;
    	--v_track_errors NUMBER;
    BEGIN
    -- Get the maximum errors allowed parameter
    BEGIN
    	execute immediate 'SELECT udl.user_data_link_pk INTO :v_udl_pk
    	FROM ni_user_data_types udt,
    	ni_user_data_link udl
    	WHERE udt.user_data_type_pk = udl.related_user_data_type
    	AND udt.name              = 'SOP-MAX-ERROR-RECORDS'
    	AND udl.object_type       = 60002';
    END;
    BEGIN
    	execute immedate 'SELECT ud.data_value into :v_errors_allowed
    	FROM ni_sim_order_ud ud,
    	ni_sim_order simo
    	WHERE simo.sim_order_pk     = 2
    	AND simo.sim_order_pk     = ud.sim_order_fk
    	AND ud.user_data_link_fk  = :v_udl_pk';
    	DBMS_OUTPUT.PUT_LINE("Errors:"||v_errors_allowed);
    END;
    
    END;
    /

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    maybe you shall realize the difference between SQL and PL/SQL with correct EXECUTE IMMEDIATE syntax, especially its INTO and USING clauses.
    You may find similar problem in this thread on OraFAQ: http://www.orafaq.com/forum/t/172580/0/
    For your sanity (and keeping the code simple), stick to the very first (the one which "works" in the very first post) method.

    [Edit: added the following part]

    And, finally, as there is nothing dynamic in your SQL statement, you should not use EXECUTE IMMEDIATE as well and simply run it statically:
    Code:
    DECLARE
      v_udl_pk NUMBER;
      v_errors_allowed NUMBER;
    BEGIN
      SELECT udl.user_data_link_pk INTO v_udl_pk
      FROM ni_user_data_types udt, ni_user_data_link udl
      WHERE udt.user_data_type_pk = udl.related_user_data_type
        AND udt.name              = 'SOP-MAX-ERROR-RECORDS'
        AND udl.object_type       = 60002;
    
      SELECT ud.data_value into v_errors_allowed
      FROM ni_sim_order_ud ud, ni_sim_order simo
      WHERE simo.sim_order_pk     = 2
        AND simo.sim_order_pk     = ud.sim_order_fk
        AND ud.user_data_link_fk  = v_udl_pk;
    END;
    Last edited by flyboy; 01-08-12 at 08:12. Reason: added final suggestion

  3. #3
    Join Date
    Jan 2012
    Posts
    12
    Quote Originally Posted by flyboy View Post

    And, finally, as there is nothing dynamic in your SQL statement, you should not use EXECUTE IMMEDIATE as well and simply run it statically:
    Code:
    DECLARE
      v_udl_pk NUMBER;
      v_errors_allowed NUMBER;
    etc ..
    One little observation., why use NUMBER and not INTEGER?

Posting Permissions

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