1 2 Previous Next 18 Replies Latest reply: Nov 27, 2012 9:05 AM by 643412 Go to original post RSS
      • 15. Re: New to a developer/DBA role...have a code question
        723623
        Well...

        If you want to guess which values use in the function (that's what I am undestanding you want)

        For p_checkout_name you can query this

        SELECT DISTINCT checkout_name FROM nrv_spatial_checkouts;

        For p_vpdunit_id:

        SELECT DISTINCT vpdunit_id, schema_name FROM nrv_spatial_installations


        And exams_cur .... this is an out paramters...

        Hope this helps, regards
        • 16. Re: New to a developer/DBA role...have a code question
          rp0428
          >
          But I can't track backwards to find WHERE it gets the parameters filled FROM. And since I can't track that, I don't know what format is valid.
          >
          Then where did this come from that you posted earlier?
          >
          example: NRV_CHECKOUT_0604_7
          >
          Whatever code calls the procedure provides a value for the 'p_checkout_name' parameter.
          This line of code
          'WHERE a.checkout_name = :1 ' 
          uses the value passed as the parameter. If you pass 'NRV_CHECKOUT_0604_7' for the parameter it is as if the code actually said this
          'WHERE a.checkout_name = NRV_CHECKOUT_0604_7 
          That code actually has a bug, however, since string literals need to be enclosed in single quotes. The original code should actually be
          'WHERE a.checkout_name = '':1''' 
          So that when the bind variable is replaced the code use is actually
          WHERE a.checkout_name = 'NRV_CHECKOUT_0604_7' 
          The 'a' is a table alias assigned from this line of code
          'FROM nrv_spatial_checkouts a, 
          So to summarize, the parameter 'p_checkout_name' that is passed to the procedure needs to be a value for the CHECKOUT_NAME column of the NRV_SPATIAL_CHECKOUTS table.
          >
          v_schema_name is only called/declared in this block of code. No where else is it referenced in the spec or the body.
          >
          That isn't right. That variable gets populated by this code that you posted
              -- Get the schema
              SELECT schema_name
                INTO v_schema_name
                FROM nrv_spatial_installations
               WHERE vpdunit_id = p_vpdunit_id;
          As for this
          >
          I have to figure out why the main part of this procedure is all concatenated together and how I can hack it up to integrate the change.
          >
          The reason for that (i.e. the reason dynamic SQL is being used) is because of the need for that schema name; you can't use a bind variable for the schema prefix so you have to use dynamic sql.

          That schema is only used to access one table: nris_vegpolylinks_aprv

          A developer SHOULD only use a schema prefix if the table from a different schema should be used. The 'Get the schema' query above is what determines the schema that will be used. If you put a different schema in the NRV_SPATIAL_INSTALLATIONS table for the 'p_vpdunit_id' parameter then the code will use a different schema table.

          We have no idea why that is necessary for your use case or environment.

          And since you never actually mentioned what type of change you need to make we can't help you with that either.
          • 17. Re: New to a developer/DBA role...have a code question
            John Spencer
            rp0428 wrote:
            That code actually has a bug, however, since string literals need to be enclosed in single quotes. The original code should actually be
            'WHERE a.checkout_name = '':1''' 
            So that when the bind variable is replaced the code use is actually
            WHERE a.checkout_name = 'NRV_CHECKOUT_0604_7' 
            No, the quotes are not required, and are, in fact, a syntax error. As long as it is a bind variable, the quotes are not required. They would be if the OP was pushing the actual value of the variable into the query instead of binding it in.
            SQL> declare
              2     l_col varchar2(10) := 'X';
              3     l_ret varchar2(10);
              4  begin
              5     execute immediate 'select dummy from dual where dummy = :1'
              6        into l_ret using l_col;
              7     dbms_output.put_line('Got: '||l_ret);
              8  end;
              9  /
            Got: X
            
            PL/SQL procedure successfully completed.
            
            SQL> declare
              2     l_col varchar2(10) := 'X';
              3     l_ret varchar2(10);
              4  begin
              5     execute immediate 'select dummy from dual where dummy = '':1'''
              6        into l_ret using l_col;
              7     dbms_output.put_line('Got: '||l_ret);
              8  end;
              9  /
            declare
            *
            ERROR at line 1:
            ORA-01006: bind variable does not exist
            ORA-06512: at line 5
            
            SQL> declare
              2     l_col varchar2(10) := 'X';
              3     l_ret varchar2(10);
              4  begin
              5     execute immediate 'select dummy from dual where dummy = '''||
              6                       l_col||''''
              7        into l_ret;
              8     dbms_output.put_line('Got: '||l_ret);
              9  end;
             10  /
            Got: X
            
            PL/SQL procedure successfully completed.
            John
            • 18. Re: New to a developer/DBA role...have a code question
              643412
              Thanks for all the input. I haven't spoken about what is changing, because I haven't gotten to that point yet. I'm trying to understand what is in place first.
              The kind of code that the developers use around here is extremely different from anywhere else I've worked. I've never had a hard time double checking people's code. It's ridiculous and embarrassing.

              As for the variables. This code doesn't get passed one single check out name. It gets passed mass amounts. From what I understand it's more of a check. The main function of it is to decide if the data being passed to it is an original piece of data, a piece of data that has been segmented, or divided up, whether that divided data is current or archived...the data is all map/spatial based. This makes perfect sense as far as the vpdunit goes. But it's very difficult to just go grab something out of the table and throw it in there for test data. The data in the table is derived from some extremely intricate calculations. The checkout_name is actually from what I understand pieces of many columns concatenated into one 'name', or entry. That's why I need to understand what calls it.
              1 2 Previous Next