Jump to content

itzkashi

Members
  • Content count

    2
  • Joined

  • Last visited

Community Reputation

0 Neutral

About itzkashi

  • Rank
    Newbie
  1. itzkashi

    Error : invalid identifier

    Hi, please find the sample data below ; Requirement is to build the insert statement dynamically through the look up table(test_lkp) so that if any change in the columns can be handled through the look up table rather than modifying the script. To achieve this i have created the look up table TEST_LKP with the same structure as TEST and inserting values as V_COL. i am getting an error for variable "v_col" Is there any way to achieve this DROP TABLE TEST ; DROP TABLE TEST_LKP; DROP TABLE TEST_REF; DROP TABLE TEST_Q; CREATE TABLE TEST (COL VARCHAR2(10)); CREATE TABLE TEST_LKP (COL VARCHAR2(10)); CREATE TABLE TEST_REF (VAL VARCHAR2(10)); CREATE TABLE TEST_Q (ENAME VARCHAR2(10)); INSERT INTO TEST_LKP VALUES ('v_col'); INSERT INTO TEST_REF VALUES ('ENAME'); INSERT INTO TEST_Q VALUES ('TOM'); code : SET SERVEROUTPUT ON; declare v_col VARCHAR2(30); q VARCHAR2(1000); CURSOR c1 IS SELECT 'INSERT INTO TEST ' || 'SELECT ' || COL || ' FROM TEST_Q ' DMLS FROM TEST_LKP; BEGIN SELECT val INTO v_col FROM TEST_REF; dbms_output.put_line(v_col); FOR i IN c1 loop dbms_output.put_line(i.DMLS); execute immediate i.dmls; end loop; END; / erorr : Error report - ORA-00904: "V_COL": invalid identifier ORA-06512: at line 17 00904. 00000 - "%s: invalid identifier" *Cause: *Action: ENAME INSERT INTO TEST SELECT v_col FROM TEST_Q Expected : i want v_col to be replaced with ENAME as INSERT INTO TEST SELECT ENAME FROM TEST_Q what wrong i am doing here?
  2. itzkashi

    generate fixed length txt file

    hi, i am using below query to generate the fixed length txt file. this sql is being called from shell script. This is supposed to be a fixed record file with the below definitions. There must be 2 byte filler after the CAT_ID AND each line should have total of 270 bytes. field length EMAIL_ADDR 1-255 CAT_ID 256-268 FILLER 269-270 issues ------- 1) i am not getting 270 byte file for one record. 2) 3rd col doesn't have 2 byte length as it is null. please advise. test.sql set linesize 1000; set newpage 0; SET PAGESIZE 0; SET ECHO OFF; SET FEEDBACK OFF; SET HEADING OFF; SET VERIFY OFF; whenever sqlerror exit failure; whenever oserror exit failure; alter session enable parallel dml; SELECT DISTINCT rpad(EMAIL_ADDR,255),rpad(CAT_ID,13),rpad(' ',3) filler FROM T1
×