PL/SQL Fix Record Length file

[bebe]

Does anyone know how to write data as a FIX RECORD LENGTH file in PL/SQL?

[Dave44]

Does anyone know how to write data as a FIX RECORD LENGTH file in PL/SQL?

One approach i have used is to figure out the length of each column and use the rpad or lpad functions to get each field the correct length.

select rpad(my_field,<col_length as number>,' '),
rpad(my_next_field,<col_length as number>,' ')
from my_table

...

[bebe]

One approach i have used is to figure out the length of each column and use the rpad or lpad functions to get each field the correct length.

select rpad(my_field,<col_length as number>,' '),
rpad(my_next_field,<col_length as number>,' ')
from my_table

...

Thank you, this diffinitely gives me a starting point. Basically I am reading data from a table which has Fname, LName, Salary, EmployeeID, Dept. I need a cursor to get all the columns example,
Select * from
COLS
where TABLE_NAME = "my_table"

basically I need to get the COL_NAME, SIZE, PRECISION, SCALE.


select rpad(COL,size-length(COL)),
rpad(next_COL, size-length(COL))
from my_table


do I have the right idea?

[Dave44]

Thank you, this diffinitely gives me a starting point. Basically I am reading data from a table which has Fname, LName, Salary, EmployeeID, Dept. I need a cursor to get all the columns example,
Select * from
COLS
where TABLE_NAME = "my_table"

basically I need to get the COL_NAME, SIZE, PRECISION, SCALE.


select rpad(COL,size-length(COL)),
rpad(next_COL, size-length(COL))
from my_table


do I have the right idea?

Your initial post says that you are creating a fixed position string, correct? if so that FP string will have the lengths for each column in a spec somewhere.

so when you query your table you will use the rpad function to add the necessary length (where needed) to ensure that each column from the table is the correct number of characters long.

so if the first column in the fixed position string is 30 characters long and is supposed to be the Fname then you would :

select rpad(Fname,30,' '),
...
from my_table

the 3 parameters in RPAD are the field or string to pad, the number of spaces total for the length(so if the field in the table is 10 characters in row x it will pad 20 characters to it, and the last paramter is what to pad it with (most cases its a space ' ')

[bebe]

Your initial post says that you are creating a fixed position string, correct? if so that FP string will have the lengths for each column in a spec somewhere.

so when you query your table you will use the rpad function to add the necessary length (where needed) to ensure that each column from the table is the correct number of characters long.

so if the first column in the fixed position string is 30 characters long and is supposed to be the Fname then you would :

select rpad(Fname,30,' '),
...
from my_table

the 3 parameters in RPAD are the field or string to pad, the number of spaces total for the length(so if the field in the table is 10 characters in row x it will pad 20 characters to it, and the last paramter is what to pad it with (most cases its a space ' ')

hmmm I will try this thank you :)

[bebe]

the code is long but the bit before my RPAD statement is:

SELECT DATA_LENGTH, DATA_PRECISION, DATA_SCALE
INTO V_DATA_LEN, V_PRE, V_SCALE
FROM COLS
WHERE TABLE_NAME = P_TABLE
AND COLUMN_NAME = V_COL;

RPAD()....ETC

[bebe]

I dont have a specific length .... but here is the entire code:

PROCEDURE EXP_TABLE_TO_FRL(P_DIR IN VARCHAR2, P_TABLE IN VARCHAR2)
AS
V_FILE VARCHAR2(50);
V_SQL VARCHAR2(500) := 'SELECT ROWID FROM ' || P_TABLE;
C_CUR SYS_REFCURSOR;
V_ROWID VARCHAR2(30);
V_XML_SQL VARCHAR2(500);
V_XML CLOB;
V_COL VARCHAR2(30);
V_VAL CLOB;
V_LINE CLOB;
V_POS BINARY_INTEGER;
V_DATA_LEN NUMBER;
V_PRE NUMBER;
V_SCALE NUMBER;
BEGIN
OPEN C_CUR FOR V_SQL;
LOOP
FETCH C_CUR INTO V_ROWID;
EXIT WHEN C_CUR%NOTFOUND;
V_XML_SQL := 'SELECT * FROM ' || P_TABLE || ' WHERE ROWID =' ||''''|| V_ROWID||'''';
V_XML := XML.GET_XML(V_XML_SQL, P_TABLE);
V_XML := REPLACE(V_XML,'<?xml version="1.0"?>'||CHR(10)||'<'||P_TABLE||'>'||CHR( 10)||' <DATA>'||CHR(10)||' ');
V_XML := REPLACE(V_XML,'</DATA>'||'</'||P_TABLE||'>');
LOOP
IF LENGTH(V_XML)= 0 THEN
EXIT;
ELSE
-- find pos of first '>'
V_POS := INSTR(V_XML, '>',1,1);
-- Get attribute
V_COL := SUBSTR(V_XML, 1, V_POS);
V_XML := REPLACE(V_XML, V_COL, '');
V_POS := INSTR(V_XML, '<',1,1);
V_VAL := SUBSTR(V_XML,1, V_POS - 1);
-- Get rid of '<' and '>' to get column name
V_COL := SUBSTR(V_COL,2, LENGTH(V_COL)-2);
V_XML := REPLACE(V_XML, V_VAL||'</'||V_COL||'>');

SELECT DATA_LENGTH, DATA_PRECISION, DATA_SCALE
INTO V_DATA_LEN, V_PRE, V_SCALE
FROM COLS
WHERE TABLE_NAME = P_TABLE
AND COLUMN_NAME = V_COL;


-- Do the RPAD thing and then write the line to a file
I have no idea how to write the RPAD

END IF;
END LOOP;

END LOOP;
CLOSE C_CUR;
END;

[bebe]

I have a new project which involves triggers. Now let me state that I am completely new to PL/SQL. So everything is very confusing to me at the moment.

I am supposed to write triggers that replicate data from one table to another. I have a function I can call for replication but how do i write the trigger itself.

trigger should be in a schema called purple and it should be AFTER insert/update for each row at a statement level.

start with a table called color

trigger shall call the database palett.purple_replication procedure. the reason why this procedure is in a seperate database is because the data mappings are defined in palett.

HELP!

[rectoverso]

I have a new project which involves triggers. Now let me state that I am completely new to PL/SQL. So everything is very confusing to me at the moment.

I am supposed to write triggers that replicate data from one table to another. I have a function I can call for replication but how do i write the trigger itself.

trigger should be in a schema called purple and it should be AFTER insert/update for each row at a statement level.

start with a table called color

trigger shall call the database palett.purple_replication procedure. the reason why this procedure is in a seperate database is because the data mappings are defined in palett.

HELP!

You say that the table is in a separate database. So you must use a database link or create a synonym in your schema for the destination table.

Thank you

[Dave44]

I have a new project which involves triggers. Now let me state that I am completely new to PL/SQL. So everything is very confusing to me at the moment.

I am supposed to write triggers that replicate data from one table to another. I have a function I can call for replication but how do i write the trigger itself.

trigger should be in a schema called purple and it should be AFTER insert/update for each row at a statement level.

start with a table called color

trigger shall call the database palett.purple_replication procedure. the reason why this procedure is in a seperate database is because the data mappings are defined in palett.

HELP!

Do you mean in a separate schema or separate database? the former inplies a different user within the same database and the latter implies a different database (requiring a database link).

Also is pallet a user name or a package name?

[bebe]

Do you mean in a separate schema or separate database? the former inplies a different user within the same database and the latter implies a different database (requiring a database link).

Also is pallet a user name or a package name?

No just a different schema.
The trigger is supposed to be against a schema called AC3DB and should fire AFTER INSERT/UPDATE AT STATEMENT LEVEL AND ROW LEVEL. The calls this procedure for data replication which is in another schema called MEX. here is the code:

PROCEDURE JON_REPLICATION(P_TABLE IN VARCHAR2, P_DATA_MAPPING IN VARCHAR2, P_TARGET_OWNER IN VARCHAR2)
AUTHID CURRENT_USER
AS
PRAGMA AUTONOMOUS_TRANSACTION;
-- -----------------------------------------------------------------------------
-- Description: Moves data received by the MEX inbound process
-- to the final data structures in AC3DB
-- --------- ------ -------------------------------------------
-- -----------------------------------------------------------------------------
MIGRATE_DATA MIGRATE_DATA_TY;
RESULT BOOLEAN;
-- Declare program variables as shown above
BEGIN
MIGRATE_DATA := MIGRATE_DATA_TY(P_TABLE, P_DATA_MAPPING, P_TARGET_OWNER);
RESULT := MIGRATE_DATA.MIGRATE;
END;

[Dave44]

if the call to palett.purple_replication is the same then like this:

CREATE OR REPLACE TRIGGER my_trigger_name

AFTER INSERT OR UPDATE ON color
FOR EACH ROW
BEGIN

palett.purple_replication(my_parameters);
END;

else like this:

CREATE OR REPLACE TRIGGER my_trigger_name

AFTER INSERT OR UPDATE ON color
FOR EACH ROW
BEGIN

IF UPDATING THEN
palett.purple_replication(my_parameters);
ELSIF INSERTING THEN
palett.purple_replication(my_parameters);
END IF;
END;

each user will need to have execute privilege on paletts function though.

Did you figure out the rpad question earlier?