MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Easier way than NDS

with 9 comments

Somebody posted a question about a dynamic NDS example found in the Oracle Database 11g PL/SQL Programming book on page 388. They asked if there was an easier way.

The answer is yes. Here’s a different example implementing the same concept on Native Dynamic SQL (NDS) with an input parameter. I borrowed it from the example I used for an Oracle framework to mimic the MySQL ENUM data type.

Basically, the following shows how you write a function using a dynamic NDS statement with an input parameter.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a weakly typed system reference cursor.
  item_cursor   SYS_REFCURSOR;
 
  -- Define a target variable for the query result.
  item_type_out VARCHAR2(30);
 
  -- Create NDS statement, with a bind or placeholder variable.
  stmt          VARCHAR2(2000) := 'SELECT type_name '
                               || 'FROM   item_type '
                               || 'WHERE  UPPER(type_name) = UPPER(:type_name_in)';
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  OPEN item_cursor FOR stmt USING item_type_in;
 
  -- Fetch the first row return and return the value.
  FETCH item_cursor INTO item_type_out;
 
  -- CLose the cursor.
  CLOSE item_cursor;
 
  -- Return the value.
  RETURN item_type_out;
END;
/

This is certainly overkill if you only want to substitute a single parameter into a cursor. A simpler approach would be to write a dynamic cursor, and then open the cursor by passing the actual parameter. Here’s that example.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a dynamic cursor.
  CURSOR c (item_type_name VARCHAR2) IS
    SELECT type_name
    FROM   item_type
    WHERE  UPPER(type_name) = UPPER(item_type_name);
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  FOR i IN c(item_type_in) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

An even more primitive approach relies on implicit assignment, like the following:

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  -- Open the cursor and rely on implicit assignment within the cursor.
  FOR i IN (SELECT type_name
            FROM   item_type
            WHERE  UPPER(type_name) = UPPER(item_type_in)) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

I hope this answers the question. You can click on the Setup Code line to unfold the code. Let me know if you like this approach to posting setup code.

Written by maclochlainn

February 24th, 2009 at 9:22 pm

Posted in Oracle,pl/sql

9 Responses to 'Easier way than NDS'

Subscribe to comments with RSS or TrackBack to 'Easier way than NDS'.

  1. very helpful its make cursor more simple.
    Thanks :)

    jerry

    26 Oct 11 at 1:35 am

  2. Is there a way to add column names dynamically in the where clause for preparing the dynamic cursor.

    Thanks.

    Naveen

    3 Dec 11 at 3:11 am

  3. I think you’re looking for something like this post, but if not let me know.

    maclochlainn

    5 Dec 11 at 10:50 pm

  4. Replace table name with your table,column name with your column

    SELECT LTRIM (MAX (SYS_CONNECT_BY_PATH (column_name, '|')), '|') alias_name
          FROM (SELECT column_name, ROW_NUMBER () OVER (ORDER BY column_name) rw
                  FROM TABLE_NAME)
    START WITH rw = 1
    CONNECT BY PRIOR rw = rw - 1;

    values in table
    guna
    sameer
    ramesh

    Result will be like

    guna|sameer|ramesh

    Magesh

    9 Apr 12 at 5:34 am

  5. SELECT ' CREATE or replace SEQUENCE WRITESOURCE_PROD_SUPP.SEQ_NTSITEM_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 
    INCREMENT BY 1 START WITH ' || S.MAX_VALUE || ' CACHE 100 NOORDER NOCYCLE; ' FROM 
    ( SELECT MAX(NETTEXT_STAGE_ITEM_ID) + 1 AS MAX_VALUE FROM WRITESOURCE_PROD_SUPP.nettext_stage_items) S ;

    Saurabh Sinha

    3 Nov 12 at 4:00 am

  6. I’ve no idea why you typed your query in a comment. If you need help with it, try this:

    -- Create test table.
    CREATE TABLE nettext_sage_items
    (nettext_stage_item_id  NUMBER);
     
    -- Insert a seeding row.
    INSERT INTO nettext_stage_items VALUES (10);
     
    -- Declare an anonymous block to test sequence renumbering.
    DECLARE
      -- Declare the sequence value.
      lv_sequence  VARCHAR2(400) := 'SEQ_NET_ITEMS_S';
      lv_stmt      VARCHAR2(400);
    BEGIN
      -- Dynamically drop sequence.
      FOR i IN (SELECT sequence_name FROM user_sequences WHERE sequence_name = lv_sequence) LOOP
        EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
      END LOOP;
     
      -- Query dynamically creates a DDL to create a sequence.
      SELECT   'CREATE SEQUENCE SEQ_NET_ITEMS_S MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH '
      ||        S.MAX_VALUE || ' CACHE 100 NOORDER NOCYCLE'
      INTO      lv_stmt
      FROM     (SELECT   MAX(nettext_stage_item_id) + 1 AS MAX_VALUE
                FROM     nettext_stage_items) S;
     
      -- Execute statement.
      EXECUTE IMMEDIATE lv_stmt;
    END;
    /

    maclochlainn

    4 Nov 12 at 12:23 am

  7. […] blog comment on an NDS statement […]

  8. Is there a way to use dynamic sql to produce statements to create a trigger for each sequence in a schema?

    Jerry

    27 Nov 13 at 9:19 am

  9. Yes, you can do it in a DDL trigger that fires on a CREATE SEQUENCE trigger. You need to write the trigger to read the name of the sequence minus the suffix and then dynamically write a DDL statement to create an ON INSERT on the same table. This approach couples the names of your table and sequence.

    maclochlainn

    9 Dec 13 at 10:09 pm

Leave a Reply