Home » Infrastructure » Unix » Shell script calling sql*plus file (database version Oracle 10g v.10.2.0.4)
| Shell script calling sql*plus file [message #434540] |
Wed, 09 December 2009 11:19  |
californiagirl
Messages: 79
Registered: May 2007
|
Member |
|
|
I'm using a shell script to call a sql*plus file, but getting errors in the output file. See below:
v_striden_id := stunt_rec.striden_id;
*
ERROR at line 128:
ORA-06550: line 128, column 37:
PLS-00302: component 'STRIDEN_ID' must be declared
ORA-06550: line 128, column 5:
PL/SQL: Statement ignored
ORA-06550: line 129, column 44:
PLS-00302: component 'STRIDEN_LAST_NAME' must be declared
ORA-06550: line 129, column 5:
PL/SQL: Statement ignored
ORA-06550: line 130, column 45:
The typical answer for this type of problem is
Quote:PLS-00302: component "string" must be declared
Cause: In a reference to a component (for example, in the name "A.B", "B" is a component of "A"), the component has not been declared. The component might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the component. Also confirm that the declaration is placed correctly in the block structure.
I have checked the spelling and declarations and all looks fine. c.striden_id is the column in the striden table and has a place holder or declared variable called v_striden_id, so I'm not sure why c.striden_id is a problem. Any advice would help please. See code below:
set serveroutput on size 1000000
set pagesize 0
set linesize 900
set echo off
set feedback off
set heading off
set timing off
set verify off
--
spool /home/stestinstance/brownarc/testoutput.txt
DECLARE
-- Paramters passed
--
v_blank_space varchar2(1) := ' ';
v_striden_id varchar2(9);
v_field_delimiter varchar2(1):='^';
v_striden_last_name varchar2(30);
--Main driver cursor
CURSOR cur_GetStuntData IS
SELECT DISTINCT c.striden_id AS "StuntPerson ID",
c.striden_last_name AS "Last Name",
FROM striden c,
ORDER BY c.striden_id;
--Begin looping data
BEGIN
FOR stunt_rec in cur_GetStuntData LOOP
v_striden_id := stunt_rec.striden_id;
v_striden_last_name := stunt_rec.striden_last_name;
--Creating output for file
dbms_output.put(rpad(nvl(v_striden_id,' '),9,v_blank_space));
dbms_output.put(rpadv_field_delimiter,2,v_field_delimiter ));
dbms_output.put(rpad(substr(v_striden_last_name,1,30),30,v_blank_space ));
dbms_output.put(rpad(v_field_delimiter,1,v_field_delimiter ));
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('some other error. call your administrator');
END;
/
spool off
/
exit;
[Updated on: Wed, 09 December 2009 11:21] by Moderator Report message to a moderator |
|
|
|
| Re: Shell script calling sql*plus file [message #434541 is a reply to message #434540] |
Wed, 09 December 2009 11:24  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
1/ ERROR at line 128:
There is not 128 lines in what you posted
2/ WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('some other error. call your administrator');
This is simple a BUG, remove that, it is MANDATORY.
3/ Whatever you think Oracle is right in its error message, so double-check, triple-check and so on until you find why it says so. Check owned objects, synonyms...
Regards
Michel
[Updated on: Wed, 09 December 2009 11:29] Report message to a moderator |
|
|
|
|
|
| Re: Shell script calling sql*plus file [message #434549 is a reply to message #434543] |
Wed, 09 December 2009 12:12 |
californiagirl
Messages: 79
Registered: May 2007
|
Member |
|
|
DDL from striden table
CREATE TABLE NEPTUNE.STRIDEN
(
STRIDEN_PID NUMBER(8) NOT NULL,
STRIDEN_ID VARCHAR2(9 BYTE) NOT NULL,
STRIDEN_LAST_NAME VARCHAR2(60 BYTE) NOT NULL,
STRIDEN_FIRST_NAME VARCHAR2(15 BYTE),
STRIDEN_MI VARCHAR2(15 BYTE),
STRIDEN_CHANGE_IND VARCHAR2(1 BYTE),
STRIDEN_ENTITY_IND VARCHAR2(1 BYTE),
STRIDEN_ACTIVITY_DATE DATE NOT NULL,
STRIDEN_USER VARCHAR2(30 BYTE),
STRIDEN_ORIGIN VARCHAR2(30 BYTE),
STRIDEN_SEARCH_LAST_NAME VARCHAR2(60 BYTE),
STRIDEN_SEARCH_FIRST_NAME VARCHAR2(15 BYTE)
)
I also changed my variables as well and re-ran the script and removed the exception and still got the same errors.
Changed variables to:
v_striden_id varchar2(9);
v_field_delimiter varchar2(1):='^';
v_striden_last_name varchar2(60);
Any advice on what else to look at would be greatly appreciated.
|
|
|
|
| Re: Shell script calling sql*plus file [message #434552 is a reply to message #434549] |
Wed, 09 December 2009 12:28 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
it works when you use valid syntax
SQL> set serveroutput on size 1000000
SQL> set pagesize 0
SQL> set linesize 900
SQL> set echo on
SQL> set term on
SQL> set feedback off
SQL> set heading off
SQL> set timing off
SQL> set verify off
SQL> --
SQL> spool /tmp/testoutput.txt
SQL>
SQL>
SQL> DECLARE
2
3 -- Paramters passed
4 --
5
6 v_blank_space varchar2(1) := ' ';
7 v_striden_id varchar2(9);
8 v_field_delimiter varchar2(1):='^';
9 v_striden_last_name varchar2(30);
10
11 --Main driver cursor
12 CURSOR cur_GetStuntData IS
13 SELECT DISTINCT c.striden_id,
14 c.striden_last_name
15 FROM striden c
16 ORDER BY c.striden_id;
17 --Begin looping data
18 BEGIN
19 DBMS_OUTPUT.ENABLE(100000);
20 FOR stunt_rec in cur_GetStuntData LOOP
21 v_striden_id := stunt_rec.striden_id;
22 v_striden_last_name := stunt_rec.striden_last_name;
23 dbms_output.put(rpad(nvl(v_striden_id,' '),9,v_blank_space));
24 dbms_output.put(rpad(v_field_delimiter,2,v_field_delimiter ));
25 dbms_output.put(rpad(substr(v_striden_last_name,1,30),30,v_blank_space ));
26 dbms_output.put(rpad(v_field_delimiter,1,v_field_delimiter ));
27
28 END LOOP;
29 END;
30 /
SQL> spool off
SQL> /
SQL>
|
|
|
|
| Re: Shell script calling sql*plus file [message #434567 is a reply to message #434552] |
Wed, 09 December 2009 14:25 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
That last "/" warrants the biggest comment... What happens in sqlplus, is that your anonymous pl/sql gets loaded into the sqlppus buffer, then / executes the content of the buffer.
To stop spooling you enter just "spool off" or optionally "spool off;", but no /. The second / simply executes the content of the buffer again (2nd blah output). "l" lists the buffer content (the pl/sql - not the spool off) and so you know what / will run...
SQL> set serveroutput on
SQL> spool t.log
SQL> begin
2 dbms_output.put_line('blah');
3 end;
4 /
blah
PL/SQL procedure successfully completed.
SQL> spool off
SQL> /
blah
PL/SQL procedure successfully completed.
SQL> l
1 begin
2 dbms_output.put_line('blah');
3* end;
SQL>
|
|
|
|
| Re: Shell script calling sql*plus file [message #434583 is a reply to message #434552] |
Wed, 09 December 2009 17:00 |
californiagirl
Messages: 79
Registered: May 2007
|
Member |
|
|
Thanks for the advice. Now I added more columns and getting the following errors that are simular to the other errors I received.
v_stele_phone_number_a:= stunt_rec.stele_phone_number;
*
ORA-06550: line 134, column 44:
PLS-00302: component 'STELE_PHONE_NUMBER' must be declared
ORA-06550: line 134, column 5:
PL/SQL: Statement ignored
ORA-06550: line 135, column 44:
PLS-00302: component 'STELE_PHONE_AREA' must be declared
ORA-06550: line 135, column 5:
PL/SQL: Statement ignored
ORA-06550: line 141, column 38:
PLS-00302: component 'ROLLED_IND' must be declared
ORA-06550: line 141, column 5:
PL/SQL: Statement ignored
There are more lines to the code but I only added the pertinent information.
set serveroutput on size 1000000
set pagesize 0
set linesize 900
set echo on
set term on
set feedback off
set heading off
set timing off
set verify off
spool /tmp/testoutput.txt
DECLARE
-- Paramters passed
--
v_blank_space varchar2(1) := ' ';
v_striden_id varchar2(9);
v_field_delimiter varchar2(1):='^';
v_striden_last_name varchar2(30);
v_stele_phone_area_b VARCHAR2(3);
v_stele_phone_number_a VARCHAR2(7);
v_stat varchar2(1);
--Main driver cursor
CURSOR cur_GetStuntData IS
SELECT DISTINCT c.striden_id,
c.striden_last_name,
REPLACE ( (e.stele_phone_area
|| e.stele_phone_number), ',', '-')
,
DECODE
(
b.rolled_ind,
'Y',
'A',
'N',
'I'
)
FROM striden c, stele e
where c.striden_id = e.stele_id
ORDER BY c.striden_id;
--Begin looping data
BEGIN
DBMS_OUTPUT.ENABLE(100000);
FOR stunt_rec in cur_GetStuntData LOOP
v_striden_id := stunt_rec.striden_id;
v_striden_last_name := stunt_rec.striden_last_name;
v_sprtele_phone_number_a:= stunt_rec.stele_phone_number;
v_sprtele_phone_area_b := stunt_rec.stele_phone_area;
v_stat := stunt_rec.rolled_ind;
dbms_output.put(rpad(nvl(v_striden_id,' '),9,v_blank_space));
dbms_output.put(rpad(v_field_delimiter,2,v_field_delimiter ));
dbms_output.put(rpad(substr(v_striden_last_name,1,30),30,v_blank_space ));
dbms_output.put(rpad(v_field_delimiter,1,v_field_delimiter ));
dbms_output.put(rpad(nvl(v_stele_phone_area_b||v_stele_phone_number_a,' ') ,12,v_blank_space ));
dbms_output.put(rpad(v_field_delimiter,3,v_field_delimiter ));
dbms_output.put(rpad(nvl(v_stat ,' '),1,v_field_delimiter ));
END LOOP;
END;
/
spool off
Any advice...I don't know why I can't seem to figure out this problem.
|
|
|
|
|
|
| Re: Shell script calling sql*plus file [message #434587 is a reply to message #434584] |
Wed, 09 December 2009 17:51 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
You need to Alias any columns in the query after you manipulate them. Also - implicit cursor is much easier, faster etc that explicit cursors and you can eliminate local variables (unless you need them for something else).
-- simplified example...
SQL> set serveroutput on
SQL>
SQL> -- "set serveroutput on" enables DBMS_OUTPUT buffer. and looks for output in the buffer and print out
SQL> --begin DBMS_OUTPUT.ENABLE (100000); end;
SQL>
SQL> declare
2 v_blank_space varchar2(1) := ' ';
3 v_field_delimiter varchar2(1):='^';
4 BEGIN
5 FOR i IN (SELECT empno, ename || ' - '|| job name_job from emp)
6 LOOP
7 DBMS_OUTPUT.put (RPAD (NVL (to_char(i.empno), ' '), 9, v_blank_space));
8 DBMS_OUTPUT.put (RPAD (v_field_delimiter, 2, v_field_delimiter));
9 DBMS_OUTPUT.put_line (RPAD (SUBSTR (i.name_job, 1, 30),30,v_blank_space));
10 END LOOP;
11 END;
12 /
7839 ^^KING - PRESIDENT
7698 ^^BLAKE - MANAGER
7782 ^^CLARK - MANAGER
7566 ^^JONES - MANAGER
7654 ^^MARTIN - SALESMAN
7499 ^^ALLEN - SALESMAN
7844 ^^TURNER - SALESMAN
7900 ^^JAMES - CLERK
7521 ^^WARD - SALESMAN
7902 ^^FORD - ANALYST
7369 ^^SMITH - CLERK
7788 ^^SCOTT - ANALYST
7876 ^^ADAMS - CLERK
7934 ^^MILLER - CLERK
PL/SQL procedure successfully completed.
SQL> |
|
|
|
| Re: Shell script calling sql*plus file [message #434739 is a reply to message #434587] |
Thu, 10 December 2009 08:17 |
californiagirl
Messages: 79
Registered: May 2007
|
Member |
|
|
What about the variables..shouldn't that eliminiate the problem? so your saying to take the query itself and make it all implicit which would eliminate the variables all together?
There is no way to work around the current issue, other than what you suggested?
|
|
|
|
|
|
|
|
| Re: Shell script calling sql*plus file [message #434771 is a reply to message #434753] |
Thu, 10 December 2009 11:32 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I mis-spoke - you arent using explicit cursors (even though you declare the cursor) because you don't explicitly open, fetch and close it. So using your cursor definition, it takes a form like this...
-- Beware that dbms_buffer defaults for 20,000 bytes (or chars?)
-- so it will blow out if too small for your resultset size.
-- 10.2 goes up to "unlimited", else 1,000,000 is max for earlier versions.
SQL> st serveroutput on size unlimited
SQL> declare
2 v_blank_space varchar2(1) := ' ';
3 v_field_delimiter varchar2(1):='^';
4 cursor c1 is SELECT empno, ename || ' - '|| job name_job from emp;
5 BEGIN
6 FOR i IN c1
7 LOOP
8 DBMS_OUTPUT.put (RPAD (NVL (to_char(i.empno), ' '), 9, v_blank_space));
9 DBMS_OUTPUT.put (RPAD (v_field_delimiter, 2, v_field_delimiter));
10 DBMS_OUTPUT.put_line (RPAD (SUBSTR (i.name_job, 1, 30),30,v_blank_space));
11 END LOOP;
12 END;
13 /
7839 ^^KING - PRESIDENT
7698 ^^BLAKE - MANAGER
7782 ^^CLARK - MANAGER
7566 ^^JONES - MANAGER
7654 ^^MARTIN - SALESMAN
7499 ^^ALLEN - SALESMAN
7844 ^^TURNER - SALESMAN
7900 ^^JAMES - CLERK
7521 ^^WARD - SALESMAN
7902 ^^FORD - ANALYST
7369 ^^SMITH - CLERK
7788 ^^SCOTT - ANALYST
7876 ^^ADAMS - CLERK
7934 ^^MILLER - CLERK
PL/SQL procedure successfully completed.
SQL>
To see if you need to alias a column, just see what heading sqlplus gives you. If it's not a simple name, then you need to alias it. e.g.
-- ENAME||'-'||JOB isn't a name you can refer to in your code
SQL> SELECT empno, ename || ' - '|| job from emp where rownum=1;
EMPNO ENAME||'-'||JOB
---------- ----------------------
7839 KING - PRESIDENT
-- so Alias it as "name_job". Now sqlplus shows that as column heading - a name you can refer to in your loop.
SQL> SELECT empno, ename || ' - '|| job name_job from emp where rownum=1;
EMPNO NAME_JOB
---------- ----------------------
7839 KING - PRESIDENT
SQL>
Unless you really want your local variables, you can just do the whole thing in SQL...SQL> set pagesize 0
SQL> select RPAD (NVL (to_char(empno), ' '), 9, ' ')||
2 RPAD ('^', 2, '^')||
3 RPAD (SUBSTR (name_job, 1, 30),30,' ') The_Line
4 from(SELECT empno, ename || ' - '|| job name_job from emp);
7839 ^^KING - PRESIDENT
7698 ^^BLAKE - MANAGER
7782 ^^CLARK - MANAGER
7566 ^^JONES - MANAGER
7654 ^^MARTIN - SALESMAN
7499 ^^ALLEN - SALESMAN
7844 ^^TURNER - SALESMAN
7900 ^^JAMES - CLERK
7521 ^^WARD - SALESMAN
7902 ^^FORD - ANALYST
7369 ^^SMITH - CLERK
7788 ^^SCOTT - ANALYST
7876 ^^ADAMS - CLERK
7934 ^^MILLER - CLERK
14 rows selected.
SQL>
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 02 07:06:19 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
