Spool Command and Select INTO

[getjbb]

Hello,

Can a select... into .... be used when using the spool command:

spool abc.txt

select a, b into :ls_a, ls_b
from abtable;

< manipulate ls_a and ls_b>
.
.
.

spool off

Is there anyway to write the results of ls_a, ls_b to
abc.txt after manipulating it.

getjbb

 

[SantaMufasa]

JBB, I'm certain that we can offer methods to achieve what you want to do.

My impression of what you want to do is:

1) read in data from a table.
2) store the data in variables
3) manipulate the data in the variables
4) display/spool/write to a file the results.

Before offering any specific suggestions, however, I have a few questions:

1) How many rows of data are we talking about?
2) What specific manipulations do you wish to do to the data?
3) Are you familiar with Oracle's PL/SQL?
4) Is your actual/final objective to store results in a flat file after manipulating values from a table? If so, can you not just produce the manipulated results "on the fly" in SQL rather than storing the values in variables?

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[getjbb]

SantaMufasa,

1) How many rows of data are we talking about?
The number of rows are about 200,000.
2) What specific manipulations do you wish to do to the
data?
For background purposes I have a select statement which
I created to use for data conversion/extract:

SELECT to_char(SYSDATE, 'DD-MON-') "LAST_UPDATE_DATE",
'PA Conversion' "LAST_UPDATED_BY",
NULL "VENDOR_SITE_ID",
VENDOR_SFX "VENDOR_SITE_CODE",
NULL "ORG_ID",
NULL "OPERATING_UNIT_NAME",
'PA Conversion' "LAST_UPDATE_LOGIN",
to_char(SYSDATE, 'DD-MON-YYYY') "CREATION_DATE",
'PA Conversion' "CREATED BY",
NULL "INACTIVE_DATE",
CONTRACT_NAME "CONTACT_NAME",
CONTACT_TITLE "TITLE",
NULL "MAIL_STOP",
.
.
.
FROM CO_VENDOR;

The CONTACT_NAME field has different type of formats for the CONTACT_NAME. What I plan on during is selecting all of the above into a variable

SELECT to_char(SYSDATE, 'DD-MON-') "LAST_UPDATE_DATE",
'PA Conversion' "LAST_UPDATED_BY",
NULL "VENDOR_SITE_ID",
VENDOR_SFX "VENDOR_SITE_CODE",
NULL "ORG_ID",
NULL "OPERATING_UNIT_NAME",
'PA Conversion' "LAST_UPDATE_LOGIN",
to_char(SYSDATE, 'DD-MON-YYYY') "CREATION_DATE",
'PA Conversion' "CREATED BY",
NULL "INACTIVE_DATE",
CONTRACT_NAME "CONTACT_NAME",
CONTACT_TITLE "TITLE",
NULL "MAIL_STOP",
.
.
.
INTO: ls_last_update, ... ls_contract_name, etc.
FROM CO_VENDOR;

And then I will call a function with ls_contact_name as an argument. The function will parse ls_contract_name and return a concatenated string with the needed information. I will then use the string and attached it onto the other variables created through the INTO and eventually have print to the flat file.

3) Are you familiar with Oracle's PL/SQL?
Yes I am familiar with PL/SQL

4) Is your actual/final objective to store results in a
flat file after manipulating values from a table? If so,
can you not just produce the manipulated results "on the
fly" in SQL rather than storing the values in variables?

Yes, my actual/final objective is to store the results
in a flat file after manipulation. I should mention that
the UTL FILE would be my choice as a way to create the
flat file, but that is not an option. I was trying to
figure out how to produce the manipulated results "on
the fly", in the SQL, but have not been successful.
Whatever you can do to help me will be greatly
appreciated.

getjbb

 

[SantaMufasa]

JBB, Thanks for your clarifications. If you would like to post a couple of rows of sample output that you want in the flat file, I'm happy to produce a script to achieve your objective(s). (Please confirm whether you want the output to be fixed-length or comma-separated values.) I'll leave up to you the coding of the name-transformation function...just tell me what you want to call the function and tell me the incoming-argument-column name(s)...I'll put a stub function with that name and the argument column(s) in the code. Please confirm the order of the source columns to produce the output in the column order you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Plus, one more thought...Could you please post a DESCRIBE of the CO_VENDOR table? Thanks.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[getjbb]

SantaMufasa,

The layout of the flat file will be comma delimited
and it will should look like:

23-JAN-2005,PA Conversion,,,09,,,PA Conversion,23-JAN-2005,PA Conversion,,James,Lloyd,Elliot,Mr.,Manager,
80-North

07-MAR-2005,PA Conversion,,,19,,,PA Conversion,07-MAR-2005,PA Conversion,,Mary,,Young,Ms,Supervisor,100-East


DESCRIBE of the CO_VENDOR table:

LAST_UPDATE_DATE DATE
LAST_UPDATED_BY NUMBER(15)
VENDOR_SITE_ID NUMBER
VENDOR_SITE_CODE VARCHAR2(15)
ORG_ID NUMBER(15)
OPERATING_UNIT_NAME VARCHAR2(240)
LAST_UPDATE_LOGIN NUMBER(15)
CREATION_DATE DATE
CREATED_BY NUMBER(15)
INACTIVE_DATE DATE
CONTACT_NAME VARCHAR2(90)
CONTACT_TITLE VARCHAR2(30)
MAIL_STOP VARCHAR2(35)


I will call the function, name_parser and the incoming argument will be only, CONTACT NAME.

Thanks you,

getjbb

 

[SantaMufasa]

JBB,

I'm nearly there with you, but I have some follow-on questions:

1) Is it your intent to override the following values that may already exist in your CO_VENDOR table:

-- columns "LAST_UPDATED_BY", "LAST_UPDATE_LOGIN", and "CREATED BY" with the literal value, 'PA Conversion'

-- columns "VENDOR_SITE_ID", "ORG_ID", "OPERATING_UNIT_NAME", "INACTIVE_DATE", and "MAIL_STOP" with NULLs

-- "LAST_UPDATE_DATE" and "CREATION_DATE" with Today's date?

If this is all correct, then this means that of the 13 original columns in the CO_VENDOR table, that only 3 of the columns (VENDOR_SFX, CONTRACT_NAME, and CONTACT_TITLE) end up with true values from the table itself...and the other 10 columns take on literal/fixed values. Am I correct in my inferrence that you are using this code to "seed" values into some target for existing VENDOR_SFX, CONTRACT_NAME, and CONTACT_TITLE combinations?

2) Is there any WHERE or ORDER BY clauses that apply to your CO_VENDOR "SELECT..."?

Let me/us know, and we can put together a solution for you rather quickly.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

JBB,

Since I shall be "out-of-pocket" for much of tomorrow, and I don't want a solution that you expect from me to hold you up, I'll respond with a solution now (in the absence of your responses to my questions, above), based upon my "best guesses" to fill in my "blanks".

First, I'll just point out some inconsistencies in your earlier specifications (and code samples), as I compare those to your output examples:

1) Your date-format masks are missing a 'YYYY' component.

2) There is either one-too-many commas or a missing expression in your SELECT-clause samples (prior to the VENDOR_SFX column).

3) Multiple instances of the column, "CONTRACT_TITLE" appear in your early SELECT statements, but I believe that instead, you want "CONTACT_TITLE".

4) Your sample output contains values for MAIL_STOP, yet your early SELECT samples evaluate the output for mail stop to NULL.

Here, then, (with my presumptions) is my code assertion to resolve your need:

set pagesize 0
set linesize 500
set trimspool on
set feedback off
spool flat-file-name.txt
select to_char(SYSDATE, 'DD-MON-YYYY')
     ||',PA Conversion,,,'
     ||VENDOR_SFX
     ||',,,PA Conversion,'
     ||to_char(SYSDATE, 'DD-MON-YYYY')
     ||',PA Conversion,,
     ||parse_name_function(CONTACT_NAME)
     ||','||CONTACT_TITLE||','
     ||MAIL_STOP
from co_vendor
/* insert WHERE and ORDER BY clauses here, if needed. */
;
spool off
set feedback on
set pagesize 35

Let us know if the above assertion(s) help resolve your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[getjbb]

SantaMufasa

I am getting error message, ORA-00904. The function parse_name does exist, but it seem to think it is invalid.

SQL> select to_char(SYSDATE, 'DD-MON-YYYY')
2 ||',PA Conversion,,,'
3 ||VENDOR_SFX
4 ||',,,PA Conversion,'
5 ||to_char(SYSDATE, 'DD-MON-YYYY')
6 ||',PA Conversion,,'
7 ||parse_name(CONTACT_NAME)
8 ||','||CONTACT_TITLE||',,'
9 from co_vendor
10 /* insert WHERE and ORDER BY clauses here, if needed. */
11 ;
||parse_name(CONTACT_NAME)
*
ERROR at line 7:
ORA-00904: "PARSE_NAME": invalid identifier

 

[getjbb]

SantaMufasa

Thank you so much. It worked perfectly.

getjbb

 

[SantaMufasa]

I will call a function with ls_contact_name as an argument. The function will parse ls_contract_name and return a concatenated string with the needed information.

You never disclosed the name of the function you plan to use to parse the CONTACT_NAME into its respective components, so I had to guess/make up a stub name for the function that you did not name for me.

You are receiving the error because "PARSE_NAME" is the stub function/place holder for YOUR name-parsing function that you must replace with the actual name of your function (whose name I do not know yet/cannot guess).

Let us know how it works for you once you provide your user-defined function name.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[getjbb]

SantaMufasa,

Yes, it did work. Thanks.

getjbb