MacLochlainns Weblog

Easier way than NDS

with 7 comments

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;
/

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;
/

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;
/

-- Conditionally drop table and sequence before attempting to create them.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN (UPPER('item_type')
                                 ,UPPER('item'))) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.TABLE_NAME||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN (UPPER('item_type_s1')
                                    ,UPPER('item_s1'))) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create item_type table, index on the natural key, and sequence; then seed data.
CREATE TABLE item_type
( item_type_id NUMBER        CONSTRAINT pk_item_type   PRIMARY KEY
, TABLE_NAME   VARCHAR2(30)  CONSTRAINT nn_item_type_1 NOT NULL
, column_name  VARCHAR2(30)  CONSTRAINT nn_item_type_2 NOT NULL
, type_name    VARCHAR2(30)  CONSTRAINT nn_item_type_3 NOT NULL
, code         VARCHAR2(5)
, meaning      VARCHAR2(255) CONSTRAINT nn_item_type_4 NOT NULL);
 
CREATE UNIQUE INDEX item_type_u1
  ON item_type(TABLE_NAME,column_name,type_name);
 
CREATE TABLE item
( item_id        NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_type      NUMBER        CONSTRAINT nn_item_1  NOT NULL
, item_title     VARCHAR2(60)  CONSTRAINT nn_item_2  NOT NULL
, item_rating_id NUMBER        CONSTRAINT nn_item_3  NOT NULL);
 
CREATE SEQUENCE item_type_s1 START WITH 1001;
CREATE SEQUENCE item_s1 START WITH 1001;
 
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','DVD',NULL,'DVD');
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','VHS',NULL,'VHS');
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','Blu-ray',NULL,'BLRY');

7 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 [...]

   

   Reset START WITH value

   22 Nov 12 at 2:03 pm

Leave a Reply