Home » SQL & PL/SQL » SQL & PL/SQL » Column Names assigned to a cursor at Runtime (Oracle 10g)
Column Names assigned to a cursor at Runtime [message #342644] Sat, 23 August 2008 09:48 Go to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Hi All,

I have been working on a query where the column names of a table might change randomly and I need to write a code that can help reuse that.

var varchar2(20); -- this is the column name fetched dynamically


value := Cursor_rec.||var;
dbms_output.put_line(value);

this gives an error :
PLS-00103: Encountered the symbol "|" when expecting one of the following:

Please Suggest.

Thanks,
Jash
Re: Column Names assigned to a cursor at Runtime [message #342645 is a reply to message #342644] Sat, 23 August 2008 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it that way, search for "dynamic query", "execute immediate" or use "if then else" or "case" depending on what is your issue and code and version.

Regards
Michel

[Updated on: Sat, 23 August 2008 09:54]

Report message to a moderator

Re: Column Names assigned to a cursor at Runtime [message #342647 is a reply to message #342644] Sat, 23 August 2008 10:03 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Thanks Michel,

I am using a dynamic / execute immediate part but only after fetching this data , and passing it on to further processing.

But I somehow want to avoid tha whole If then else...

can there be a solution using ref cursor.

like

get_value(dyn_cur,col_name)
and this get_value will have an execute immediate...
but I don't know for what string?

can this be possible,
if so, how?
Re: Column Names assigned to a cursor at Runtime [message #342651 is a reply to message #342647] Sat, 23 August 2008 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know. I don't know your problem. I don't know your code...

Regards
Michel
Re: Column Names assigned to a cursor at Runtime [message #342653 is a reply to message #342644] Sat, 23 August 2008 11:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Re: Column Names assigned to a cursor at Runtime [message #342721 is a reply to message #342653] Sun, 24 August 2008 11:19 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Thanks anacedent,,, thanks for that link.

I'll try to explain the problem that I'm facing (hopefully, clearer than before)

- I have cursor record, whose column names I don't know at compile time, as I fetch them from another lookup table.

- I fetch the cursor into a record variable.

Fetch Cursor into Cursor_Rec

- Now I have strColName. I want to get the value at that column name. that is the current requirement.

Cursor_Rec.||StrColName -- does not work.

Cant this work,, I dont have access right now (working from home)

execute immediate (select :b1 from:b2)
into Col_Val
using StrColName,Cursor_Rec;


- I would use that further into another query, which would be dynamically generated.

Please advice.
Re: Column Names assigned to a cursor at Runtime [message #342722 is a reply to message #342644] Sun, 24 August 2008 11:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
From where I sit, the design is (fatally) flawed.
If you do not know the name, then by definition you don't know the datatype.
The INTO variable needs to be the same datatype as in the SELECT.
Since you don't know the datatype until runtime, you can't make the INTO succeed.
Forget this approach & design something that you can actually implement.
Re: Column Names assigned to a cursor at Runtime [message #342726 is a reply to message #342644] Sun, 24 August 2008 12:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You might want to have a look at this article. It is a long read, but sounds to me like it is quite relevant to your needs.

method 4 dynamic sql in pl/sql

This is fairly advanced stuff. My estimation is that there is no simple solution for what you want to do (though I admit I really do not have a full understanding of your need and this is all just a "best guess"). So if you think you will find an easy way out of your dilema, you likely will be disappointed.

For those who have never seen the ORACLE DATA CARTRIDGE INTERFACE, this will be a hard read. The problem mostly being trying to get an idea of what ODCI is all about. ODCI is not commonly practiced by most, and not well documented (at least not that I can see). In short, there is a mental block one must jump; a need to understand the bigger picture of ODCI which is never really explained well anywhere. I would offer up my own explanation but I am still stumbling myself.

Still, this is cool stuff to see and learn so have fun.

Good luck, Kevin
Re: Column Names assigned to a cursor at Runtime [message #342727 is a reply to message #342726] Sun, 24 August 2008 13:02 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Thanks Anacedent, Kevin...

I was wondering if there is a way to actually solve this...
(i.e. get the content of a cursor record)

1. Use a ref cursor, as that acts like a pointer, and call the ref cursor from a execute immediate... is this possible, and the get the data of the column.

2. get the contents of the cursor_rec in to array, just for your information, the datatype that I'll be processing would be varchar2,,, its a default (hope anacedent has an answer on that one), and once the array is here


There is a solution I found ,,, here is the LINK


I can't try them out myself at this hour,

please advise?

Re: Column Names assigned to a cursor at Runtime [message #342728 is a reply to message #342644] Sun, 24 August 2008 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>the datatype that I'll be processing would be varchar2
Until some time in the future somebody decides to use it on a different datatype & it errors out.

The first rule of holes is as follows:
When you find yourself in a hole, the FIRST thing you should do is stop digging!

I don't want to be close when the collapse occurs.
You've been warned by multiple folks with more experience that you have.
You choose to ignore the advice & proceed down this path.

You're On Your Own (YOYO)!
Re: Column Names assigned to a cursor at Runtime [message #342738 is a reply to message #342728] Sun, 24 August 2008 15:37 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Thanks Anacedent,, for that term,, YOYO!!! Yo mate,,
I guess I need to check my premises,,,,

but need to consider this,,,

I'm able to implement what they is runtime polymorphism in C ,,, in pl/sql,,, I need to get the data at runtime...it is possible as you might have in the link that I posted,,,

hope the help and support keeps coming from all you folks,, this one is really getting interesting.....

Keep going....
Re: Column Names assigned to a cursor at Runtime [message #342739 is a reply to message #342644] Sun, 24 August 2008 15:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
With software one can do (practically) anything,
but just be cause you can do some thing does not mean you should actually do it.

Implementing complex code that does not scale, can not be tested completely because it is data driven,
& is sure to be a maintenance nightmare in the future is a recipe for a disaster waiting to occur.
Re: Column Names assigned to a cursor at Runtime [message #342742 is a reply to message #342644] Sun, 24 August 2008 16:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You must think for yourself. Assess your own need and act in what way seems reasonable to you. If the procedure you have found works for you then use it (as long as you understand what it is doing).

You are to be commended for doing you own research and finding this thing.

As anacedent points out, and I am sure you are aware, DYNAMIC SQL is not particularly fast and surely will not scale well. Only you can judge if scaling will be a high priority in the context of the problem you are solving.

Good job researching, and good luck, Kevin
Re: Column Names assigned to a cursor at Runtime [message #342871 is a reply to message #342742] Mon, 25 August 2008 07:20 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Thanks Anacedent, Kevin,, for your replies,,,

If somehow we can do This,, as I think this would help you guys would be much more clear on what I'm actually looking for,,


As far as scalability is concerned I would like to just explain you the situation I'm in,,

I have data ready in a staging table, that needs to be validated and the then bulk loaded in the database, all data is in varchar (as it requires validation of datatype)

The staging tables column could vary anytime ,,,
so I thought that rather than going and changing code all the time, I'll write a generic program to handle unforseen changes.

the only point where I'm stuck is the link above, It would be very kind of you guys to advise.

Thanks.
Re: Column Names assigned to a cursor at Runtime [message #342883 is a reply to message #342644] Mon, 25 August 2008 07:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You need to come clean about what it is you really want to do. Your posts are starting to go all over with different requirements.

Please post examples of code you have currently that works. Then we might be able to tell you how to make it generic. Still, the reality is, you have been directed to the information you need. Either you use one of the dynamic sql methods (EXECUTE IMMEDIATE, DBMS_SQL), or use the ODCI variation as offered in the link of my prior post.

If what you want is to validate datatypes from a generic table then there is nothing dynamic at all in your needs.

create or replace package pk_validate_type is

   function is_date (value_p in varchar2,format_p in varchar2) return date;
   function is_number (value_p in varchar2) return number;

end;
/
show errors

create or replace package body pk_validate_type is

   function is_date (value_p in varchar2,format_p in varchar2)  return date is
      return_v date;
   begin
      begin
         return_v := to_date(value_p,format_p);
      exception when others then null;
      end;
      return (return_v);
   end;

   function is_number (value_p in varchar2) return number is
      return_v number;
   begin
      begin
         return_v := to_number(value_p);
      exception when others then null;
      end;
      return (return_v);
   end;

end;
/
show errors

select pk_validate_type.is_date (to_char(sysdate,'dd-mon-rrrr'),'dd-mon-rrrr') from dual;
select pk_validate_type.is_date ('crap','dd-mon-rrrr hh24:mi:ss') from dual;
select pk_validate_type.is_number('1') from dual;
select pk_validate_type.is_number('a') from dual;


Good luck, Kevin
Re: Column Names assigned to a cursor at Runtime [message #342957 is a reply to message #342883] Mon, 25 August 2008 11:31 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Thanks Kevin,, for those validation procedures,,

What I'm specifically looking for here is

I have a variable that contains the name of a column. Now I would like to access these column values, something like

myvalue := myrecord_rec.MYVARIABLE;
^^^^^^^^^^ EVAL() ???

How is this done in PL/SQL?

I found a solution to eval an expression
declare
expr varchar2(40);
result varchar2(40);
begin
expr := '1 + 1';
--result :='';
execute immediate 'begin :res := '||expr||'; end;' using out result;
dbms_output.put_line(result);
end;


this seeems to be working fine,, but I'm looking for the split value to be evaluated at runtime,,

any suggestions?

Thanks,
Re: Column Names assigned to a cursor at Runtime [message #342966 is a reply to message #342644] Mon, 25 August 2008 12:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If I understand your need (and I am still not sure I do), then it is not possible using plsql record variables, but is possible using oracle object types. Consider this:

SQL> create or replace type o_some_record is object (some_column number)
  2  /

Type created.

Elapsed: 00:00:00.00
SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2     o_some_record_v o_some_record := o_some_record(1357);
  3     a_date date;
  4     a_number number;
  5     checker_query_v varchar2(32000) :=
  6                                          ' DECLARE'
  7                                        ||'   o_some_record_v o_some_record := :1;'
  8                                        ||'BEGIN'
  9                                        ||'  :2 := pk_Validate_Type.Is_Date(o_Some_Record_v.Some_Column,''dd-mon-rrrr hh24:Mi:ss'');'
 10                                        ||'  '
 11                                        ||'  :3 := pk_Validate_Type.Is_Number(o_Some_Record_v.Some_Column);'
 12                                        ||'END;'
 13     ;
 14  begin
 15     execute immediate checker_query_v using in out o_some_record_v, in out a_date, in out a_number;
 16  dbms_output.put_line('a_date='||to_char(a_date,'dd-mon-rrrr hh24:mi:ss')||'...');
 17  dbms_output.put_line('a_number='||a_number||'...');
 18  end;
 19  /
a_date=...
a_number=1357...

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> 


Still, I think there is something you are not telling us.

Else this is a lot of work for something that really should be redesigned.

Kevin
Re: Column Names assigned to a cursor at Runtime [message #343103 is a reply to message #342966] Tue, 26 August 2008 05:09 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Thanks Kevin,,, looks like this would help me solve the issue,, I'll be getting into it and working it out....

Then I'll get back and explain the code and what I'm looking for....

Thanks once again..
Re: Column Names assigned to a cursor at Runtime [message #343123 is a reply to message #342966] Tue, 26 August 2008 06:12 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Jash - Oracle does have a full object oriented
implementation which does provide robust polymorphism
with SQL. I am just getting into the Object oriented
side of Oracle because my current project necessitates it greatly.

The topics to read up on are table functions,
and user-defined data-types of the object type.
I'm going to approach this as

(1) the field names can change on the table
(2) the number of fields on the table are constant
(3) the data type of the fields are constant.

Below is just a conceptual overview of a solution,
I've yet to implement something of this nature.

Let's say your table is EMPLOYEE and it has right now
EMPLOYEE_ID NUMBER(10),
EMPLOYEE_NAME VARCHAR2(30),
HIRE_DATE DATE

You will create a TYPE object for each of the above
and a table type to hold them.

Your select would
go inside a table function as (select * from employee)
And you would bulk collect into
the table types for each data object.

You would select against that table function using the
generic object VALUE clause in place of the column name.
I am not sure if the table function needs to first
CAST to the ANYDATA type where you would include
the TREAT AS clause in the Select against the function
...Which would require those types to be
children of a superclass, perhaps EMPFIELD.

I wish i could provide the syntax for all of
this but I'm far from proficient in this area.
These are the concepts to look into for
Polymorphism and data objects in Oracle.

I can give you a smaller scale example of something I have built that did not use field names in it's selection.

I created an object type AbstractStringSet as
table of varchar2.

I created a table function - decomposeString -
to accept any string and break it into letter
groups delimited by spaces. It populated and returned the AbstractStringSet

My SQL was
SELECT COLUMN_VALUE from 
(CAST (TABLE (DecomposeString('ABC DEF G H'))) as AbstractStringSet) 

which would return 
ABC,
DEF,
G,
H 
as four rows
 


Hopefully I have the parenthesis right in that example; i'm not connected to Oracle right now.

If I get some spare time at work today I will try to implement
my description above and get back to you.

Regards,
Harry








Re: Column Names assigned to a cursor at Runtime [message #343844 is a reply to message #343123] Thu, 28 August 2008 06:02 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Guys,,, So I have come up with what I was looking for,
thanks all of you for you support.
here is the basic model...

-- The Table that is fed (kind of staging table you may say).
create table jfed_tbl
(
Sector varchar2(30),
Start_Date varchar2(30)
);
insert into jfed_tbl values ('Sector_One','07/13/2007');
insert into jfed_tbl values ('Sectort_Two','07/13/1984');
-- This table is my lookup validation table
create table jcheck_tbl
(
col_name varchar2(30),
validate_type varchar2(30)
);
-- Sectort and start date are the column name of the staging
-- table.
insert into jfed_tbl values ('Sector','varchar');
insert into jfed_tbl values ('Start_Date','date');
-- just to check the values, (two rows each)
select * from jcheck_tbl
select * from jfed_tbl

-- now here comes the package, that contains the value of a
-- cursor record at runtime, column names assigned dynamically

CREATE OR REPLACE PACKAGE dyn_assign_cur
IS
data_rec jfed_tbl%ROWTYPE;

PROCEDURE dyn_fetch;
procedure val_varchar (str_validate varchar2);
procedure val_date (str_validate varchar2);

END dyn_assign_cur;
/


CREATE OR REPLACE PACKAGE BODY dyn_assign_cur
IS
PROCEDURE dyn_fetch
IS
TYPE cur_typ IS REF CURSOR;

dyn_cur cur_typ;
iVar integer :=1;
strquery VARCHAR2 (400);
BEGIN
OPEN dyn_cur FOR
SELECT *
FROM jfed_tbl;

LOOP
FETCH dyn_cur
INTO data_rec;

EXIT WHEN dyn_cur%NOTFOUND;

dbms_output.put_line('Validating Row # '||iVar);
iVar:=iVar+1;

FOR lookup_rec IN (SELECT *
FROM jcheck_tbl)
LOOP

strquery := 'declare strdyn_data varchar2(30); begin strdyn_data := dyn_assign_cur.data_rec.'
|| lookup_rec.col_name ||';dyn_assign_cur.val_'
|| lookup_rec.validate_type
|| '(strdyn_data); end;';
-- dbms_output.put_line(strquery);
EXECUTE IMMEDIATE (strquery);
END LOOP;
END LOOP;
END;

PROCEDURE val_varchar (str_validate VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line ('varchar to validate -> ' || str_validate);
END;

PROCEDURE val_date (str_validate VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line ('date to validate -> ' || str_validate);
END;
END dyn_assign_cur;

-----------------

call dyn_assign_cur.dyn_fetch() -- for the output.

Validating Row # 1
varchar to validate -> Sector_One
date to validate -> 07/13/2007
Validating Row # 2
varchar to validate -> Sector_Two
date to validate -> 07/13/1984
---

I hope to here a word or two from Anacedent,, about the design,
For basically I'm just a beginner,,, pros and cons etc

Thanks Kevin for your encouragement
Thanks Harry for your object oriented concepts.


Keep going......
Re: Column Names assigned to a cursor at Runtime [message #343872 is a reply to message #343844] Thu, 28 August 2008 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Column Names assigned to a cursor at Runtime [message #343929 is a reply to message #343872] Thu, 28 August 2008 08:17 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Pardon me for that,,, I'll make sure of it next time onwards
Re: Column Names assigned to a cursor at Runtime [message #344032 is a reply to message #343929] Thu, 28 August 2008 11:53 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
(New Revised post to the previuous one)
Guys,,, So I have come up with what I was looking for,
thanks all of you for your support.
here is the basic model...

I hope to get a few points on feasibility of the method.

-- The Table that is fed (kind of staging table you may say).
CREATE TABLE jFed_Tbl (
  Sector     VARCHAR2(30),
  Start_Date VARCHAR2(30));

INSERT INTO jFed_Tbl
VALUES     ('Sector_One',
            '07/13/2007');

INSERT INTO jFed_Tbl
VALUES     ('Sectort_Two',
            '07/13/1984');
-- This table is my lookup validation table

CREATE TABLE jCheck_Tbl (
  col_Name      VARCHAR2(30),
  Validate_Type VARCHAR2(30));
-- Sectort and start date are the column name of the staging
-- table.

INSERT INTO jCheck_Tbl
VALUES     ('Sector',
            'varchar');

INSERT INTO jCheck_Tbl
VALUES     ('Start_Date',
            'date');



-- just to check the values, (two rows each)
SELECT *
FROM   jCheck_Tbl;

SELECT *
FROM   jFed_Tbl

-- now here comes the package, that contains the value of a
-- cursor record at runtime, column names assigned dynamically

CREATE OR REPLACE PACKAGE dyn_Assign_Cur
IS
  Data_rec  jFed_Tbl%ROWTYPE;
  
  PROCEDURE dyn_Fetch;
  
  PROCEDURE val_vArchar(str_Validate  VARCHAR2);
  
  PROCEDURE val_Date(str_Validate  VARCHAR2);
END dyn_Assign_Cur;
/


CREATE OR REPLACE PACKAGE BODY dyn_Assign_Cur
IS
  PROCEDURE dyn_Fetch
  IS
    TYPE Cur_typ IS REF CURSOR;
    dyn_Cur   CUR_TYP;
    ivar      INTEGER := 1;
    strQuery  VARCHAR2(400);
  BEGIN
    OPEN dyn_Cur FOR
      SELECT *
      FROM   jFed_Tbl;
    
    LOOP
      FETCH dyn_Cur INTO Data_rec;
      
      EXIT WHEN dyn_Cur%NOTFOUND;
      
      dbms_Output.Put_Line('Validating Row # '
                           ||ivar);
      
      ivar := ivar + 1;
      
      FOR LookUp_rec IN (SELECT *
                         FROM   jCheck_Tbl)
      LOOP
        strQuery := 'declare strdyn_data varchar2(30); begin strdyn_data := dyn_assign_cur.data_rec.'
                    ||LookUp_rec.col_Name
                    ||';dyn_assign_cur.val_'
                    ||LookUp_rec.Validate_Type
                    ||'(strdyn_data); end;';
        -- dbms_output.put_line(strquery);
        
        EXECUTE IMMEDIATE (strQuery);
      END LOOP;
    END LOOP;
  END;
  
  PROCEDURE val_vArchar
       (str_Validate  VARCHAR2)
  IS
  BEGIN
    dbms_Output.Put_Line('varchar to validate -> '
                         ||str_Validate);
  END;
  
  PROCEDURE val_Date
       (str_Validate  VARCHAR2)
  IS
  BEGIN
    dbms_Output.Put_Line('date to validate -> '
                         ||str_Validate);
  END;
END dyn_Assign_Cur;
-----------------


call dyn_assign_cur.dyn_fetch() -- for the output.


Validating Row # 1
varchar to validate -> Sector_One
date to validate -> 07/13/2007
Validating Row # 2
varchar to validate -> Sector_Two
date to validate -> 07/13/1984
---

I hope to here a word or two from Anacedent,, about the design,
For basically I'm just a beginner,,, pros and cons etc

Thanks Kevin for your encouragement
Thanks Harry for your object oriented concepts.


Keep going......

[Updated on: Thu, 28 August 2008 11:56]

Report message to a moderator

Re: Column Names assigned to a cursor at Runtime [message #344039 is a reply to message #342644] Thu, 28 August 2008 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I hope to here a word or two from Anacedent,, about the design,
Design?
I have no idea what problem you are trying to solve.
It appears you hope to do some sort of "validation" but I don't know what is the "standard" against which you hope/plan to compare some variable data.
Contrary to having the supposed ability for different datatypes, it appears you treat every thing as a string.

It is a BAD design to store "dates" in VARCHAR2 or as strings.

INSERT INTO jFed_Tbl
VALUES     ('Sector_One',
            '07/13/2007');

INSERT INTO jFed_Tbl
VALUES     ('Sectort_Two',
            '08/13/1984');


In this case Sectort_Two date > Sector_One date
Re: Column Names assigned to a cursor at Runtime [message #344062 is a reply to message #344039] Thu, 28 August 2008 12:54 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Hi Anacedent,

Basically my problem is that data is coming into a staging table from a csv file to the database from another application, I cannot validate it while loading, because a third party application is loading it to the database,

what I wanted was to validate this data, which would be about 200 columns, using that small piece of code, and I really wanted to avoid that if then else messy thing, and any sort hard code of the column names.

Also, as and when required , if there is change in the table then too by only changing the lookup table, i can make the code work, so this would be a kind of a generic code.

Hope you are able to get what I'm looking for.


Re: Column Names assigned to a cursor at Runtime [message #344072 is a reply to message #342644] Thu, 28 August 2008 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>what I wanted was to validate this data
Admirable goal, but EXACTLY how do you implement the validation???

I frequently write SQL to write SQL; which can be used against a table with a couple of hundred columns
or against a schema with a couple of hundred tables.

I see little difference between INSERT or UPDATE a few rows as the columns get added, changed,
or deleted against manually editing SQL which operates against any existing long list of elements.

Here are some words of wisdom:
First make it work, then make it fancy.

If you can't make it work, you'll waste a lot of time making it fancy.
IMO, you are spending your time trying to make it fancy;
& I have yet to see you have the basics working.

Good Luck!

[Updated on: Thu, 28 August 2008 13:28] by Moderator

Report message to a moderator

Re: Column Names assigned to a cursor at Runtime [message #344115 is a reply to message #344072] Thu, 28 August 2008 15:59 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
-->First make it work, then make it fancy.
I guess as far as making it work is concerned, you can see the code that I put up, that is the first part of problem sovled, that is 'Column Names Assigned to a cursor at runtime"


--> I frequently write SQL to write SQL

I basically thought that you might want to consider the performance or feasibility of the SQL code written by SQL code, and you give me your words of wisdom as in you are seasoned programmer, would give me leads.


My basic intension is for my code to run generically for any sort of table that I want, considering I can get the metadata information of that table ready before hand, like what validation I'm supposed to do, and other related information.

-->Exactly how to you implement the validation.
That you can figure out easily from the code that I put.

Thanks again


Re: Column Names assigned to a cursor at Runtime [message #344120 is a reply to message #342644] Thu, 28 August 2008 16:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What we have here is a failure to communicate!

I may be blind, but I do not recognize where you actually "validate" contents of some field.

based upon what criteria is the value of some field deemed valid or not?

EXACTLY how is the decision made where any particular value for any specific field for any given row is considered "invalid"?

What does your code do with data that fails "validation"?
Re: Column Names assigned to a cursor at Runtime [message #344123 is a reply to message #344120] Thu, 28 August 2008 16:32 Go to previous messageGo to next message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
Hey Anacedent,

-->I may be blind, but I do not recognize where you actually "validate" contents of some field.

Basically, the purpose of a generic code is to have it 'As you like it', I'll give you a few leads on my next moves...

what one wants to with the validation,, you can consider this from Kevin's message up above,,, and then.....

Take this for a table :

Rowid|Data_State|Comments|val_col_1|val_col_2......n|

Data State:Valid, Invalid, Awaiting.

When a validation is done , I update the Rowid, Data_State, and related commments after the validation, in case I find an invalid state, I mark that row as invalid, and move to the next row. Note: Validation process begins only for rows having data_state as awaiting.

there goes the model......

hope you get something out of this one,, and then you might want to give your personal expertise on this design.

Thanks

p.s. Its almost morning, heading to bed,, I'll see you again, Keep going....
Re: Column Names assigned to a cursor at Runtime [message #344146 is a reply to message #342644] Thu, 28 August 2008 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Since you choose to not follow posting guidelines & not answer my questions, I choose no longer respond; so You're On Your Own (YOYO)!
Re: Column Names assigned to a cursor at Runtime [message #344272 is a reply to message #344146] Fri, 29 August 2008 05:03 Go to previous message
jash_hirani
Messages: 21
Registered: August 2008
Location: India
Junior Member
@ Anacedent.
What I was looking for is to make the code generic and dynamic in its nature so that we can use this for a validation, I thought senior members like you would help me advise on the approach to solve these kind of problems, but you seem to be prejudiced on your answers, from the moment you wrote the noteabout the design being (fatally) flawed, I guess your comments on the design of the code (that I posted) would have been appreciated more than those about soft skills.

Thanks Everyone for helping!!!!

Good Luck!!
Previous Topic: Number datatype Format
Next Topic: User defined Exception
Goto Forum:
  


Current Time: Thu Dec 08 10:23:20 CST 2016

Total time taken to generate the page: 0.19875 seconds