As part of a project, I'm working on data mapping of the peoplesoft records and fields in use at our company. There are more than 25K fields that I have to document but it gets tedious and will take a much more time than I have if I did it normal way. So, I wrote a stored procedure to reduce some of the work in documenting translate values.
Here is the code of my stored procedure:
CREATE OR REPLACE PROCEDURE SP_DATAMAPPINGINFO AS
TYPE EmpCurTyp IS REF CURSOR;
newrow_cursor EmpCurTyp;
txtable_cursor EmpCurTyp;
recname varchar2(30);
recdescr varchar2(200);
fieldnum number(3);
fieldname varchar2(30);
fieldescr varchar2(2000);
keyflag varchar2(1);
fieldtype varchar2(20);
distinctcount number(10);
query1_str varchar(300);
query2_str varchar(300);
query3_str varchar(300);
fieldvalue varchar2(200);
hyphen varchar2(5);
txvalue varchar2(200);
fielduse varchar2(500);
tablename varchar2(40);
intertxtabname varchar2(30);
txtablename varchar2(40);
CURSOR get_fields is
select A.RECNAME as "Record", A.RECDESCR as "Record Description"
, B.FIELDNUM as "FieldNum", B.FIELDNAME as "Field", C.DESCRLONG as "Field Description", CASE WHEN
EXISTS(select K.FIELDNAME FROM PSRECFLDDBKEYVW K WHERE K.RECNAME = A.RECNAME AND K.FIELDNAME=B.FIELDNAME)
THEN 'Y' ELSE 'N' END as "Key (Y/N)", DECODE (C.FIELDTYPE,
0, 'Character',
1, 'Long Char',
2, 'Number',
3, 'Signed Number',
4, 'Date',
5, 'Time',
6, 'DateTime',
8, 'Image/ Attachment',
9, 'Image Reference',
'Unknown') as "FieldType"
FROM PSRECDEFN A, PSRECFIELDDB B LEFT JOIN PSDBFIELD C ON (B.FIELDNAME = C.FIELDNAME)
WHERE B.RECNAME = A.RECNAME
AND A.RECNAME IN (select R.RECNAME from PSRECDEFN R, DBA_TABLES T
WHERE ('PS_'||R.RECNAME=T.TABLE_NAME)
AND T.NUM_ROWS > 0
AND R.RECTYPE=0)
order by A.RECNAME, B.FIELDNUM;
BEGIN
OPEN get_fields;
LOOP
FETCH get_fields INTO recname, recdescr, fieldnum, fieldname, fieldescr, keyflag, fieldtype;
fielduse := '';
tablename := 'PS_' || recname;
hyphen := ' - ';
fieldvalue := '';
txvalue := '';
intertxtabname := '';
txtablename := '';
if (fieldname <> '%EMPLID%' and fieldname <> '%DESCR%' and fieldname <> '%COMMENT%') THEN
query1_str := 'select RI.EDITTABLE FROM PSRECDEFN RD, PSRECFIELDDB RI WHERE RD.RECNAME = RI.RECNAME
AND RD.RECNAME = ' || recname || 'AND RI.FIELDNAME = ' || fieldname;
OPEN txtable_cursor FOR query1_str;
FETCH txtable_cursor INTO intertxtabname;
CLOSE txtable_cursor;
query2_str := 'select count(distinct T.' || fieldname || ') FROM ' || tablename;
IF (intertxtabname IS NOT NULL) THEN
txtablename := 'PS_' || intertxtabname;
query3_str := 'select distinct T.' || fieldname || ', TR.DESCR FROM ' || tablename || ' T left join ' || txtablename || ' TR ON T.'
|| fieldname || ' = TR.' || fieldname || ' order by T.' || fieldname;
ELSE
txtablename := '';
query3_str := 'select distinct DT.' || fieldname || ', DTR.XLATLONGNAME FROM ' || tablename || ' DT left join PSXLATITEM DTR on
(DTR.FIELDNAME = ''' || fieldname || ''' and DT.' || fieldname || ' = DTR.FIELDVALE) order by DT.' || fieldname;
END IF;
execute immediate query2_str into distinctcount;
if(distinctcount > 150) THEN
fielduse := 'More than 150';
ELSE
OPEN newrow_cursor FOR query3_str USING 'fieldname';
LOOP
FETCH newrow_cursor INTO fieldvalue, txvalue;
fielduse := fieldvalue || ' - ' || txvalue;
EXIT WHEN newrow_cursor%NOTFOUND;
END LOOP;
CLOSE newrow_cursor;
END IF;
ELSE
fielduse := 'SKIPPING';
END IF;
dbms_output.put_line(recname || ',' || recdescr || ',' || fieldnum || ',' || fieldname || ',' || fieldescr || ',' || keyflag || ',' || fieldtype || ',' || fielduse);
END LOOP;
CLOSE get_fields;
NULL;
END SP_DATAMAPPINGINFO;
The stored proc compiles without any errors but when I execute it, I get the following error:
Error starting at line : 1 in command -
BEGIN SP_DATAMAPPINGINFO; END;
Error report -
ORA-00933: SQL command not properly ended
ORA-06512: at "SYSADM.SP_DATAMAPPINGINFO", line 69
ORA-06512: at line 1
- 00000 - "SQL command not properly ended"
*Cause:
*Action:
Line 69 in the stored proc is OPEN txtable_cursor FOR query1_str;
I have tried using a bind variable but I still get the error and I might have used it incorrectly.
I may have other issues in the code. It'll be great if you can point those out. We are currently at Oracle 12c
Line 69 in the stored proc is
OPEN txtable_cursor FOR query1_str;.
Obviously we can't run your code as we don't have your schema so we can't compile and validate your dynamic SQL. However, looking at your assembled string, the striking thing is the concatenation of the boilerplate text and the columns:
AND RD.RECNAME = ' || recname || 'AND RI.FIELDNAME = ' || fieldname
If you look closely you will spot that there is no space after the quote in 'AND RI.FIELDNAME which means the value of recname is concatenated with AND. That's your ORA-00933 right there.
Once you fix that you'll probably run into ORA-00936: missing expression. From the variable declarations we know recname and fieldname are strings but you are not treating them as strings. The code doesn't include any quotes around the variables so the executed query will treat them as columns or something. So that's something you need to fix:
AND RD.RECNAME = ''' || recname || ''' AND RI.FIELDNAME = ''' || fieldname ||''''
Later on you open the cursor like this:
OPEN newrow_cursor FOR query3_str USING 'fieldname';
So you are passing a parameter to a dynamic query which doesn't include any parameter placeholders. That should throw
ORA-01006: bind variable does not exist
Dynamic SQL is hard because it turns compilation errors into runtime errors. You need to develop a cool eye for looking at your code to spot them. One thing which will help is using logging to record the dynamic statement (DBMS_OUTPUT is better than nothing). Usually when we have the actual statement in front of our eyes it is easy to spot our bloomers. But if we can't at least we can run the query and let the compiler point them out.