-
hi
how to create variables dynamically?
is this possible?
write back
jegannathan@sakinfotech.co.in
Cheers!
OraKid.
-
In what kind of environment? Need more information on what you are looking for to assist you.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
hi
i will be fetching the total no of column count from all_tab_columns for a given table
if the count is 10 then i should create 10 variables.
if the count is 150 then i should create 150 variables...so on...
i want to create a single procedure to handel all the tables
the max column what i hv is 957 columns and the min is 4 columns
write back
Cheers!
OraKid.
-
Hi!
May be such code will help:
declare
stmt varchar(32767) := 'declare ';
begin
for r in (select * from all_tab_columns where ...)
loop
stmt := stmt || 'p_' || r.column_name ||' varchar2(4000); ';
end loop;
stmt := stmt ||'
begin
-- do some work here
end;';
execute immediate stmt;
end;
Victor
www.dynamicpsp.com
-
declare
stmt varchar(32767) := 'declare '||CHR(10);
table_name varchar(32) := 'TEST' ;
col_name varchar(32) ;
datatype_name varchar(32) ;
begin
for r in (select column_name,data_type,DATA_LENGTH from all_tab_columns
where TABLE_NAME ='EMP')
loop
stmt := stmt || 'p_' || r.column_name ||' EMP.'||r.column_name||'%TYPE;'|| CHR(10);
end loop;
stmt := stmt ||'
begin
select ename,empno from dual;
end;';
execute immediate stmt INTO ?????;
exception
when others then
dbms_output.put_line('ERROR'||SQLERRM);
NULL;
end;
IN the above example i hv selected ename & empno
how to assign the values? in the execute immediate ONTO statement???
thanx
Cheers!
OraKid.
-
thanx visctor
i hv solved the problem
Cheers!
OraKid.
-
hi guys
i have written a PL?SQL block
can we write more simpler than this?
DECLARE
lv_table_name VARCHAR(32) := 'SEC_D_ACCOUNT' ;
lv_dest_table_name VARCHAR(32) := 'SEC_D_ACCOUNT_SEC' ;
col_name VARCHAR(32) ;
datatype_name VARCHAR(32) ;
stmt_col VARCHAR(4000) ;
stmt_column VARCHAR(4000) ;
sele_stmt VARCHAR(4000) ;
stmt VARCHAR(32767) ;
stmt_1 VARCHAR(10) := 'DECLARE '||CHR(10);
stmt_2 VARCHAR(4000) := '
TYPE c1 IS REF CURSOR;
y1 c1;
PROCEDURE oc (
ct1 IN VARCHAR2,
co1 IN OUT c1
)
IS
BEGIN
OPEN co1 FOR ct1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;';
BEGIN
FOR r IN (SELECT column_name,data_type,data_length FROM all_tab_columns
WHERE table_name = lv_table_name)
LOOP
stmt_column := stmt_column || ' , ' || r.column_name ;
sele_stmt := SUBSTR(stmt_column,3);
stmt := stmt || 'p_' || r.column_name ||' ' ||lv_table_name||'.'||r.column_name||'%TYPE;'|| CHR(10);
stmt_col := stmt_col || 'p_' || r.column_name || ',';
END LOOP;
stmt := stmt_1 || stmt || stmt_2 ||'
BEGIN
oc( ''SELECT' || sele_stmt || ' FROM ' || lv_table_name || ' '',y1);
LOOP
FETCH y1 INTO ' || SUBSTR(stmt_col,1,LENGTH(stmt_col)-1) || ';
EXIT WHEN y1%notfound;
INSERT INTO '|| lv_dest_table_name || ' VALUES( ' || SUBSTR(stmt_col,1,LENGTH(stmt_col)-1) || ' );
COMMIT;
END LOOP;
END;';
EXECUTE IMMEDIATE stmt ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR'||SQLCODE||SQLERRM);
NULL;
END;
jegannathan@sakinfotech.co.in
Cheers!
OraKid.
-
Hi balajiyes!
I think your code must work too, it can possibly be reduced, but if i understand your final aim right, this task has a much easier solution:
procedure copy_table(src_name in varchar2, dest_name in varchar2)
is
stmt varchar2(10000);
begin
stmt := ' insert into '||dest_name||' (select * from '||src_name||')';
execute immediate stmt;
commit;
exception when others then
dbms_output.put_line(sqlerrm);
end;
Victor
www.dynamicpsp.com
-
hi victor
thanx
ya! i just gave a example as INSERT actually there will be lot of business login within that dynamic generated query...
some calculations....
good job. help me in giving fine tuning stuff
regards
balajiyes@yahoo.com
Cheers!
OraKid.
-
Hi!
Just a few small changes.
DECLARE
lv_table_name VARCHAR(32) := 'SEC_D_ACCOUNT' ;
lv_dest_table_name VARCHAR(32) := 'SEC_D_ACCOUNT_SEC' ;
stmt_col VARCHAR(4000) ;
stmt_var VARCHAR(4000) ;
stmt_column VARCHAR(4000) ;
stmt VARCHAR(32767) ;
stmt_1 VARCHAR(10) := 'DECLARE
TYPE c1 IS REF CURSOR;
y1 c1; '||CHR(10);
BEGIN
FOR r IN (SELECT column_name,data_type,data_length FROM all_tab_columns
WHERE table_name = lv_table_name)
LOOP
stmt_column := stmt_column || r.column_name ||',';
stmt_var := stmt_var || 'p_' || r.column_name ||' ' ||lv_table_name||'.'||r.column_name||'%TYPE;'|| CHR(10);
stmt_col := stmt_col || 'p_' || r.column_name || ',';
END LOOP;
stmt_column := rtrim(stmt_column, ',');
stmt_col := rtrim(stmt_col, ',');
stmt := stmt_1 || stmt_var ||'
BEGIN
open y1 for ''SELECT ' || stmt_column || ' FROM ' || lv_table_name ||' '';
LOOP
FETCH y1 INTO ' || stmt_col || ';
EXIT WHEN y1%notfound;
INSERT INTO '|| lv_dest_table_name || ' VALUES( ' || stmt_col || ' );
COMMIT;
END LOOP;
close y1;
END;';
EXECUTE IMMEDIATE stmt ;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR'||SQLCODE||SQLERRM);
END;
Victor
www.dynamicpsp.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|