Home » SQL & PL/SQL » SQL & PL/SQL » decode functionality in DBMS_OUTPUT (10g)
| decode functionality in DBMS_OUTPUT [message #462946] |
Mon, 28 June 2010 15:51  |
geneeyuss
Messages: 53
Registered: June 2010
|
Member |
|
|
I would like to use the below decode in DBMS_OUTPUT in place of cur_rec.data_type. Could I know how can I achieve this.
The DBMS_OUTPUT is inside a loop and output is shown below as an example.
NOT NULL -- EMP_ID NUMBER
,NOT NULL -- EMP_NAME NUMBER
,NOT NULL -- HIRE_DT DATE
,NOT NULL -- SALARY NUMBER
DBMS_OUTPUT STMT:
Dbms_Output.Put_Line(nullable ||CHR(9)||CHR(9)||' -- ' ||upper(cur_rec.column_name)||CHR(9)||CHR(9)||cur_rec.data_type);
STMT to use in place of cur_rec.data_type:
substr(decode( data_type, 'NUMBER', decode( data_precision, NULL, NULL,'('||data_precision||','||data_scale||')' ),data_length),1,11)
Also I am not able to align the output using CHR(9). Could I know a better method to do this.
Thanks
geneeyuss
|
|
|
|
|
|
|
|
|
|
| Re: decode functionality in DBMS_OUTPUT [message #462966 is a reply to message #462946] |
Tue, 29 June 2010 00:44  |
 |
Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:STMT to use in place of cur_rec.data_type:
substr(decode( data_type, 'NUMBER', decode( data_precision, NULL, NULL,'('||data_precision||','||data_scale||')' ),data_length),1,11)
Why don't you use this expression in the query/cursor instead of just "data_type"?
Regards
Michel
|
|
|
|
| Re: decode functionality in DBMS_OUTPUT [message #463062 is a reply to message #462966] |
Tue, 29 June 2010 09:59 |
geneeyuss
Messages: 53
Registered: June 2010
|
Member |
|
|
My aim is to create an insertTemplate for my oncvinience in testing my data.
The script I wrote for that is below:
SET LINESIZE 1000
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET PAGESIZE 0
SET VERIFY OFF
SET TRIMOUT ON
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
DECLARE
CURSOR c_columns (p_table_name IN VARCHAR2,
p_owner IN VARCHAR2) IS
SELECT Lower(a.column_name) column_name,
a.data_type, a.nullable, a.data_precision, a.data_scale, a.data_length
FROM all_tab_columns a
WHERE a.table_name = p_table_name
AND a.owner = p_owner
AND a.data_type IN ('CHAR','VARCHAR2','DATE','NUMBER','INTEGER');
v_table_name VARCHAR2(30) := Upper('&TABLE_NAME');
v_owner VARCHAR2(30) := Upper('&SCHEMA');
nullable VARCHAR2(10);
FUNCTION Format_Col(p_column IN VARCHAR2,
p_datatype IN VARCHAR2,
p_precision IN NUMBER,
p_scale IN NUMBER,
p_length IN NUMBER)
RETURN VARCHAR2 IS
result VARCHAR2(20);
BEGIN
IF p_datatype IN ('NUMBER') THEN
IF p_precision IS NULL THEN
result := NULL;
ELSE
result := '('||p_precision||','||p_scale||')';
END IF;
ELSIF p_datatype IN ('DATE') THEN
result := NULL;
ELSE
result := '('||p_length||')';
END IF;
RETURN substr(result, 1, 11);
END;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('INSERT INTO ' || Lower(v_owner) || '.' || Lower(v_table_name) || ' VALUES');
Dbms_Output.Put_Line('(');
FOR cur_rec IN c_columns (v_table_name, v_owner) LOOP
IF c_columns%ROWCOUNT != 1 THEN
Dbms_Output.Put(',');
END IF;
IF cur_rec.nullable = 'Y' THEN
nullable := 'NULL';
ELSE
nullable := 'NOT NULL';
END IF;
Dbms_Output.Put_Line(nullable || CHR(9)||CHR(9)||CHR(9)||CHR(9)||CHR(9)||CHR(9)||' -- ' ||upper(cur_rec.column_name)||CHR(9)||CHR(9)||cur_rec.data_type||Format_Col(cur_rec.column_name, cur_rec.data_type, cur_rec.data_precision, cur_rec.data_scale, cur_rec.data_length));
END LOOP Columns_Loop;
Dbms_Output.New_Line;
Dbms_Output.Put_Line(');');
END;
/
SET LINESIZE 100
SET PAGESIZE 14
SET FEEDBACK ON
When I run this the output I get is:
10:31:30 IR2_DXU_TOOL@doirtx01 29-JUN-2010 10:31:30 SQL> @insertTemplate
Elapsed: 00:00:00.01
Enter value for table_name: emp
Enter value for schema: ar
INSERT INTO ar.emp VALUES
(
NOT NULL -- EMP_ID NUMBER(15,0)
,NOT NULL -- ENAME VARCHAR2(30)
,NULL -- HIRE_DT DATE
,NOT NULL -- SALARY NUMBER(10,2)
);
I want this template to be formatted to appear like below:
INSERT INTO ar.emp VALUES
(
NOT NULL -- EMP_ID NUMBER(15,0)
,NOT NULL -- ENAME VARCHAR2(30)
,NULL -- HIRE_DT DATE
,NOT NULL -- SALARY NUMBER(10,2)
);
I tried using CHR(9) for that but doesn't seem to be working properly. Could I know a better solution.
thanks
geneeyuss
|
|
|
|
|
|
| Re: decode functionality in DBMS_OUTPUT [message #463065 is a reply to message #463062] |
Tue, 29 June 2010 10:06 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
geneeyuss wrote on Tue, 29 June 2010 15:59
When I run this the output I get is:
10:31:30 IR2_DXU_TOOL@doirtx01[/email] 29-JUN-2010 10:31:30 SQL> @insertTemplate
Elapsed: 00:00:00.01
Enter value for table_name: emp
Enter value for schema: ar
INSERT INTO ar.emp VALUES
(
NOT NULL -- EMP_ID NUMBER(15,0)
,NOT NULL -- ENAME VARCHAR2(30)
,NULL -- HIRE_DT DATE
,NOT NULL -- SALARY NUMBER(10,2)
);
I want this template to be formatted to appear like below:
INSERT INTO ar.emp VALUES
(
NOT NULL -- EMP_ID NUMBER(15,0)
,NOT NULL -- ENAME VARCHAR2(30)
,NULL -- HIRE_DT DATE
,NOT NULL -- SALARY NUMBER(10,2)
);
I tried using CHR(9) for that but doesn't seem to be working properly. Could I know a better solution.
thanks
geneeyuss
I can't actually see any difference between the two. It might help if you used [code] tags to preserve formatting.
|
|
|
|
|
|
|
|
|
|
| Re: decode functionality in DBMS_OUTPUT [message #463089 is a reply to message #462946] |
Tue, 29 June 2010 12:04 |
geneeyuss
Messages: 53
Registered: June 2010
|
Member |
|
|
it doesnt omit tabs. but doesnt get exactly aligned because of the NULL and NOT NULL. It does give 5 tabs if we have CHR(9) written 5 times. But its not exactly aligned.
Any suggestions on this?
INSERT INTO ar.emp VALUES
(
NOT NULL -- EMP_ID NUMBER(15,0)
,NOT NULL -- ENAME VARCHAR2(30)
,NULL -- HIRE_DT DATE
,NOT NULL -- SALARY NUMBER(10,2)
);
|
|
|
|
| Re: decode functionality in DBMS_OUTPUT [message #463090 is a reply to message #462946] |
Tue, 29 June 2010 12:05 |
geneeyuss
Messages: 53
Registered: June 2010
|
Member |
|
|
it doesnt omit tabs. but doesnt get exactly aligned because of the NULL and NOT NULL. It does give 5 tabs if we have CHR(9) written 5 times. But its not exactly aligned.
Any suggestions on this?
INSERT INTO ar.emp VALUES
(
NOT NULL -- EMP_ID NUMBER(15,0)
,NOT NULL -- ENAME VARCHAR2(30)
,NULL -- HIRE_DT DATE
,NOT NULL -- SALARY NUMBER(10,2)
);
|
|
|
|
| Re: decode functionality in DBMS_OUTPUT [message #463094 is a reply to message #463090] |
Tue, 29 June 2010 12:12 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Maybe instead of using tabs you should try rpading the NULL and NOT NULL with spaces to a pre-determined length and then concatenate the rest on the end.
Otherwise you're going to have to use a different number of tabs depending on whether it's NULL or NOT NULL.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Feb 16 07:22:43 CST 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
