Home » SQL & PL/SQL » SQL & PL/SQL » PL/sql script store data (11.2.0.3.0)
PL/sql script store data [message #576908] Sun, 10 February 2013 09:54 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi Experts,

My requirement is to get primary key column name and there values of a table and store in a stage table

create table easy(id integer, event_rowid integer,txn_rowid integer,txn_name varchar2(200));

begin
for i in 1..5 loop
insert into easy values(i,'777'||i,i||89,'Ris'||i||i);
end loop;
end;
commit;
/

alter table easy add constraint pk_easy primary key(id,event_rowid,txn_name);

create table stage(tab_name varchar2(200),v_prim_key varchar2(2000),v_prim_data varchar2(2000),dt date);


I was able to fetch primary keys and there values for a table and store them in stage table but only 1 record , I am stuck when there are more than 1 record
Here is what i tried
SQL> SET serveroutput ON
SQL> DECLARE
  2    V_prim_key VARCHAR2(2000);
  3    l_tab DBMS_UTILITY.uncl_array;
  4    u_tab DBMS_UTILITY.uncl_array;
  5    l_tablen    INTEGER;
  6    v_prim_data VARCHAR2(2000);
  7  BEGIN
  8    SELECT wm_concat(s.column_name)
  9    INTO v_prim_key
 10    FROM user_cons_columns s ,
 11      user_constraints c
 12    WHERE c.constraint_name=s.constraint_name
 13    AND c.constraint_type  ='P'
 14    AND s.table_name       ='EASY';
 15    dbms_output.put_line('Col val='||v_prim_key);
 16    DBMS_UTILITY.comma_to_table (
 17    list => v_prim_key, tablen => l_tablen, tab => l_tab);
 18    dbms_output.put_line(l_tablen);
 19    FOR i IN 1 .. l_tablen
 20    LOOP
 21      DBMS_OUTPUT.put_line(l_tab(i));
 22      EXECUTE immediate 'select ' ||l_tab(i)|| ' from easy where id =1' INTO
u_tab(i);
 23    END LOOP;
 24    DBMS_UTILITY.table_to_comma ( tab => u_tab, tablen => l_tablen,
 25    list => v_prim_data);
 26    DBMS_OUTPUT.put_line('v_prim_data : ' || v_prim_data);
 27    INSERT INTO stage VALUES
 28      ('EASY',v_prim_key,v_prim_data,sysdate
 29      );
 30    COMMIT;
 31  END;
 32  /
Col val=TXN_NAME,EVENT_ROWID,ID
3
TXN_NAME
EVENT_ROWID
ID
v_prim_data : Ris11,7771,1

PL/SQL procedure successfully completed.


If you look at the above code in the execute immediate where condition i have used id=1, which is 1 records only but if i change id <3 then there will be 2 records and i will get below error
ORA-01422: exact fetch returns more than requested number of rows


Other problem if there are 3 primary keys as in above case i am fetching there values 1 by 1 , is there any way if i can fetch those values once in for all
data to be stored in stage table in below format

TAB_NAME	   V_PEIM_KEY	             V_PRIM_DATA	  DT
-------        ---------                   -------             ------   
EASY	     TXN_NAME,EVENT_ROWID,ID	   Ris11,7771,1	       10-Feb-13
EASY	     TXN_NAME,EVENT_ROWID,ID	   Ris22,7772,2	       10-Feb-13


any help will be appreciated
Re: PL/sql script store data [message #576909 is a reply to message #576908] Sun, 10 February 2013 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 22506
Registered: January 2009
Senior Member
it is NEVER a wise approach to store multiple values in a single column!
It violates Third Normal Form.
Re: PL/sql script store data [message #576912 is a reply to message #576908] Sun, 10 February 2013 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read Normalization.

Regards
Michel
Re: PL/sql script store data [message #576913 is a reply to message #576912] Sun, 10 February 2013 10:51 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Okay i get it,

I won't store data in a table, let's say i want it to be printed, Now please help
Re: PL/sql script store data [message #576914 is a reply to message #576913] Sun, 10 February 2013 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you really think you now believe you?
It would be honest to say "I don't care, give me the solution".

Regards
Michel
Re: PL/sql script store data [message #576928 is a reply to message #576914] Sun, 10 February 2013 22:19 Go to previous message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Quote:
"I don't care, give me the solution"


will be rude to say!!
Don't give me solution just help me where i m stuck

Thanks in advance
Previous Topic: precedence
Next Topic: How to display the result horizontally
Goto Forum:
  


Current Time: Fri Jul 25 22:24:08 CDT 2014

Total time taken to generate the page: 0.06366 seconds